Updating tables -- what am I missing?

[eagle]

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

 

[VB Programmer]

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

 

[eagle]

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 ?

 

[Sericinus hunter]

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.

 

[W.G. Ryan eMVP]

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