|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Updating tables -- what am I missing?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 First off, your SQL syntax is wrong. You need an end double quote at the
end of the INSERT staement... Show quote :) "eagle" <eagletender2***@yahoo.com> wrote in message news:%231kE4yTGGHA.208@tk2msftngp13.phx.gbl... > 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 > > > 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 ? Show quote "VB Programmer" <d***@emailme.com> wrote in message news:ul6nB5TGGHA.2680@TK2MSFTNGP09.phx.gbl... > First off, your SQL syntax is wrong. You need an end double quote at the > end of the INSERT staement... > > :) > > "eagle" <eagletender2***@yahoo.com> wrote in message > news:%231kE4yTGGHA.208@tk2msftngp13.phx.gbl... >> 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 >> >> >> > > 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. eagle wrote: Show quote > 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 > > > 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 Show quote "eagle" <eagletender2***@yahoo.com> wrote in message news:%231kE4yTGGHA.208@tk2msftngp13.phx.gbl... > 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 > > >  |
|||||||||||||||||||||||
Other interesting topics