Help with adding stuff to a database please

[JamesB]

Im just converting an app to .net (using VB) and I decided to use ado.net
also, rather than sticking with plain ado.

I have got my queries running fine with datareaders, but I'm having a little
trouble inserting records. I dont really want to use an SQL command as
single quotes in strings often cause headaches, so I have the code below.

I get an exception of "Object reference not set to an instance of an object"
at the line marked with * and checking my debug, workTable is still
"nothing".
Any clues or am I completely off track here!
Thanks
James.

Dim MyDataSet As New DataSet
Dim Adapter As New SqlDataAdapter
Adapter.SelectCommand = New SqlCommand("SELECT * FROM msg", DBConn)
Adapter.Fill(MyDataSet)
Dim workTable As New DataTable
workTable = MyDataSet.Tables.Item("msg")
* Dim workRow As DataRow = workTable.NewRow
workRow("msg_client") = client
workRow("msg_username") = username
workRow("msg_message") = Message
workRow("msg_recipient") = CStr(Recipient)
workRow("msg_submittime") = Now()
workRow("msg_status") = 0
workTable.Rows.Add(workRow)
Adapter.Update(MyDataSet)

 

[JamesB]

Im just converting an app to .net (using VB) and I decided to use ado.net
also, rather than sticking with plain ado.

I have got my queries running fine with datareaders, but I'm having a
little trouble inserting records. I dont really want to use an SQL command
as single quotes in strings often cause headaches, so I have the code
below.

I get an exception of "Object reference not set to an instance of an
object" at the line marked with * and checking my debug, workTable is
still "nothing".
Any clues or am I completely off track here!
Thanks
James.

Dim MyDataSet As New DataSet
Dim Adapter As New SqlDataAdapter
Adapter.SelectCommand = New SqlCommand("SELECT * FROM msg", DBConn)
Adapter.Fill(MyDataSet)
Dim workTable As New DataTable
workTable = MyDataSet.Tables.Item("msg")
* Dim workRow As DataRow = workTable.NewRow
workRow("msg_client") = client
workRow("msg_username") = username
workRow("msg_message") = Message
workRow("msg_recipient") = CStr(Recipient)
workRow("msg_submittime") = Now()
workRow("msg_status") = 0
workTable.Rows.Add(workRow)
Adapter.Update(MyDataSet)

** Update - I changed the worktable = My.... line to read:

workTable = MyDataSet.Tables.Item(0)

now the code gets to the update statement at the end but fails with...

"Update requires a valid InsertCommand when passed DataRow collection with
new rows"

 

[Elton Wang]

Look at following URL:

http://msdn.microsoft.com/library/d...conupdatingdatabasewithdataadapterdataset.asp

HTH

Elton Wang

 

[JamesB]

Look at following URL:

http://msdn.microsoft.com/library/d...conupdatingdatabasewithdataadapterdataset.asp

Thanks, I've looked at that and a few other sites. I think I've made some
progress, but now I have a different error (bet you saw that coming)

Here is the current code:

Dim MyDataSet As New DataSet
Dim MyAdapter As New SqlDataAdapter
MyAdapter.SelectCommand = New SqlCommand("SELECT * FROM msg", DBConn)
MyAdapter.Fill(MyDataSet)
Dim SQLStr As String
SQLStr = "INSERT INTO msg " & _
"(msg_client, msg_username, msg_message, msg_recipient, msg_submittime,
msg_status) VALUES " & _
" (" & client & ", '" & username & "', '" & Message & "', '" & Recipient &
"', " & Now() & ", 0);"
MyAdapter.InsertCommand = New SqlCommand(SQLStr, DBConn)
Dim workRow As DataRow
workRow = MyDataSet.Tables(0).NewRow
workRow("msg_client") = client
workRow("msg_username") = username
workRow("msg_message") = Message
workRow("msg_recipient") = CStr(Recipient)
workRow("msg_submittime") = Now()
workRow("msg_status") = 0
MyDataSet.Tables(0).Rows.Add(workRow)
MsgBox(MyDataSet.Tables(0).Rows.Count)
MsgBox(MyDataSet.Tables(0).Rows(MyDataSet.Tables(0).Rows.Count -
1).Item("msg_message"))
MyAdapter.Update(MyDataSet)

The two msgboxes are just me testing to see if my record is in the dataset,
which it seems to be. The update command is failing though, with just
"System Error"... which is about as helpful as "General Protection Fault"
really, but there you go... further thoughts?
Thanks
James.

 

[Guest]

Since you only deal with one database table, you can use CommandBuilder to
automatically generate update command. Try following code:

Dim MyDataSet As New DataSet
Dim MyAdapter As New SqlDataAdapter
MyAdapter.SelectCommand = New SqlCommand("SELECT * FROM msg", DBConn)

' Add commandbuilder
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(MyAdapter)

MyAdapter.Fill(MyDataSet)

Dim workRow As DataRow = MyDataSet.Tables(0).NewRow
workRow("msg_client") = client
workRow("msg_username") = username
workRow("msg_message") = Message
workRow("msg_recipient") = CStr(Recipient)
workRow("msg_submittime") = Now()
workRow("msg_status") = 0
MyDataSet.Tables(0).Rows.Add(workRow)
MyAdapter.Update(MyDataSet.Tables(0))

BTW, if you create any sql query yourself, it's better to use parameters. It
protects you from SQL injection.

HTH


Elton