problem in updating database using dataset

[lakkadghat]

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@

[CyberHawke]

lakkadghat,

 

You are very close here, but what you are missing is a way to tell the dataadapter how to update your data source. It's 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.)

[lakkadghat]

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@

[sizer]

method 2 is wrong because you are calling ds.AcceptChanges()before you updated the db.

 

Try this code:

conn.Open()
Me.BindigContext(ds,"dt").EndCurrentEdit() ''this might help if you're using  DataGrid
da.Update(ds, "dt")
ds.AcceptChanges()
conn.Close()

 

:D