Updating tables -- what am I missing?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

eagle - 14 Jan 2006 19:00 GMT

This is my code, and nothing appears to happen. The dataset has 3 rows, I
do an insert, and the dataset still has 3 rows. What am I missing or doing
wrong? There is nothing wrong with the query, or the connection, and I get
no errors, it just doesn't return the dataset with the inserted row. I
thank you so much for your help.

Public Function UpdateDataset as Dataset

Dim ds as dataset = GetDataSet("tblClients")
Dim cn as SqlConnection = New SqlConnection(mstrSQLConn)
Dim qry as String = "Insert into tblClients (firstname, lastname)
values ('Jane', 'Smith')
Dim cmd As New SqlCommand(qry, cn)
cn.open

Dim dataAdapter As New SqlDataAdapter
dataAdapter.InsertCommand = cmd
dataAdapter.Update(ds, "tblClients")
ds.AcceptChanges()
Return ds

End Function

Reply to this Message

VB Programmer - 14 Jan 2006 19:10 GMT

First off, your SQL syntax is wrong. You need an end double quote at the
end of the INSERT staement...

> This is my code, and nothing appears to happen. The dataset has 3 rows, I
> do an insert, and the dataset still has 3 rows. What am I missing or
[quoted text clipped - 18 lines]
>
> End Function

Reply to this Message

eagle - 14 Jan 2006 19:48 GMT

Thanks but that's not the problem, I just typed it wrong here. The query
works, I did it directly into sql. There is nothing wrong with the query,
and there is nothing wrong with the connection.

Second of all ?

> First off, your SQL syntax is wrong. You need an end double quote at the
> end of the INSERT staement...
[quoted text clipped - 23 lines]
>>
>> End Function

Reply to this Message

Sericinus hunter - 14 Jan 2006 20:44 GMT

What are you trying to achieve? Add a row to a table in the dataset?
Then you need to use DataTable.NewRow() and DataTable.Rows.Add() functions.

> This is my code, and nothing appears to happen. The dataset has 3 rows, I
> do an insert, and the dataset still has 3 rows. What am I missing or doing
[quoted text clipped - 18 lines]
>
> End Function

Reply to this Message

W.G. Ryan eMVP - 15 Jan 2006 00:45 GMT

A few things... (these aren't related to your problem but worth mentioning).

First, you may want to paramaterize your query, using sql like that is
problematic potentially although if you are using hard coded values those
problems are minimized (vs getting values from user input). nonetheless
paramaterizing the query is defintily the way to go.

Next, you don't need to call AcceptChanges, as each row is updated, the
adapter calls AcceptChanges so it's just an unnecessary line. Also, you
want to make sure you close that connection (in a finally block or using
statement in VB.NET 9.0). Ok, enough about general.

Check the rowstate before update... Debug.Assert(ds.HasChanges). If there
are no changes present then the update won't do anything. This may help
http://www.knowdotnet.com/articles/rowstateupdate.html

Let me know about the rowstate and we'll take it from there.

Cheers,

Bill

> This is my code, and nothing appears to happen. The dataset has 3 rows, I
> do an insert, and the dataset still has 3 rows. What am I missing or
[quoted text clipped - 18 lines]
>
> End Function

Reply to this Message

Updating tables -- what am I missing?

Free Magazines