problem in updating database using dataset

L

lakkadghat

Member
Joined
Jul 26, 2003
Messages
12
Hi
i am trying to update my database by just changing one column in row inside the dataset
the problem is that it is updating the dataset but not updating in the database.
does anyone know where i am lacking because all the refrences available shows me similar codes for updating but i am not able to work it out.
below is the code i have used


Imports System
Imports System.Data
Imports System.Data.SqlClient

Dim strconn As String = "Data source=(local);initial catalog=Taiseer;user id=sa;password=;"
Dim conn As New SqlConnection(strconn)

Dim str As String
Dim da As New SqlDataAdapter()
Dim ds As New DataSet()
Dim dt As New DataTable()
Dim dr As DataRow

str = "Select * from Reg where id=1"
da = New SqlDataAdapter(str, conn)
da.Fill(ds, "dt")
dr = ds.Tables("dt").Rows(0)

dr("col1") = Me.txtbox.Text

method 1
conn.Open()
ds.GetChanges(System.Data.DataRowState.Modified)
da.Update(ds)
conn.Close()

method2

ds.AcceptChanges()
conn.Open()
da.Update(ds, "dt")
conn.Close()


THANKS IN ADVANCE

:o mU$t@f@
 
lakkadghat,

You are very close here, but what you are missing is a way to tell the dataadapter how to update your data source. Its actually beneficial to me that you are using the SqlClient here, because it is much easier to do this in Sql Server than in an OLEDB data source.

Add the following line of code in your variable declarations section.

Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)

Without the command builder, the adapter has no way of knowing how to update your datasource.

(btw, in an oledb data source, the process is much more manual.)
 
Hi
thanx for your reply.
now i am not getting any errors but my changes are not being transmitted to the database.
what should i do?????????

Mu$t@f@
 
method 2 is wrong because you are calling ds.AcceptChanges()before you updated the db.

Try this code:
Code: 
conn.Open()
Me.BindigContext(ds,"dt").EndCurrentEdit() this might help if youre using  DataGrid
da.Update(ds, "dt")
ds.AcceptChanges()
conn.Close()

:D
 
Back
Top