EltonW
Thu Sep 29 09:56:02 CDT 2005
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
"JamesB" wrote:
>
> "Elton Wang" <elton_wang@hotmail.com> wrote in message
> news:eucMCTCxFHA.3152@TK2MSFTNGP10.phx.gbl...
> > Look at following URL:
> >
> >
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconupdatingdatabasewithdataadapterdataset.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.
>
>
>