Urgent DataAdapter

[michaelw]

When i create my data adapter using the wizard it does not create the update and delete, this has thrown me

 

[nakracreative]

be more clear in your question as i am unable to figure out ur problem.

if u are using sqlclient.sqlcommandbuilder then u must have a primary key in any of the field of your database.

 

[michaelw]

I am using the wizard to create a oledbdataadapter. i can fill the dataset but when i change a field value and update it brings back an error. When i created the adapter it created all but the update and delete statements.


My code is

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

DataSet31.Clear()

OleDbDataAdapter1.Fill(DataSet31)

DataGrid1.DataSource = DataSet31



End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

OleDbDataAdapter1.Update(DataSet31)

MessageBox.Show("Datbase Updated")

End Sub

 

[nakracreative]

PrivateSub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

OleDbDataAdapter1.Update(DataSet31)

MessageBox.Show("Datbase Updated")

EndSub

this was your code

right one is : OleDbDataAdapter1.Update(DataSet31,"<tablename>")

 

[michaelw]

Where you put table name is that the table in sql server or the datagrid name and do you have to put it in the <>??

 

[nakracreative]

right one is : OleDbDataAdapter1.Update(DataSet31,"<tablename>")

no u dont have to put "<>"

just put tablename in quotes.

tablename will the name of the table of your database to which you are sending the updating values.

 

[michaelw]

This seems to have done somthing but not update the table, it does not crash anymore but it does not update the table either and i cant figure out why? any suggestions my code now is>


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

DataSet31.Clear()

OleDbDataAdapter1.Fill(DataSet31)

DataGrid1.DataSource = DataSet31



End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

OleDbDataAdapter1.Update(DataSet31)

MessageBox.Show("Datbase Updated")

End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

SqlDataAdapter1.Fill(DataSet11)

DataGrid2.DataSource = (DataSet11)

End Sub

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

Dim objcommandbuilder As New SqlClient.SqlCommandBuilder

OleDbDataAdapter1.Update(DataSet31, "jobs")

MessageBox.Show("database Updated")

End Sub

 

[nakracreative]

here goes the complete code

suppose u have two fields in table: name and s_no

dim objconnection as new sqlclient.sqlconnection("<connection string>")
dim objadapter as new sqlclient.sqldataadapter("select * from <tablename>",objconnection)

dim objadatset as new dataset()
objadapter.fill(objdataset,"<tablename">)

dim objbuilder as new sqlclient.sqlcommandbuilder(objadapter)

now suppose we have to find s_no and change the name field.

dim objdataview as new dataview(objdataset.tables(0))
dim inum as integer

objdataview.sort="s_no"

inum=objdataview.find(<source s_no>)
objdataview.tables(0).row(inum).item("name")=updated name is written here

objadapter.update(objdataset,"<tablename>")

 

[michaelw]

u are dircetly using the update mehod of dataadapter ( OK no problem i understand this)

but nowhere have u used the update query of sql ( sorry do not understand where i would place the SQL to relate to the coloums and values)

and there are also no paramaters collection. ( this is new to me)

I have read so many articales on dataset updates and although they gave me the basics, i stll cant get the update working.

thanks for your help

Michael

 

[nakracreative]

here goes the complete code

suppose u have two fields in table: name and s_no

dim objconnection as new sqlclient.sqlconnection("<connection string>")
dim objadapter as new sqlclient.sqldataadapter("select * from <tablename>",objconnection)

dim objadatset as new dataset()
objadapter.fill(objdataset,"<tablename">)

dim objbuilder as new sqlclient.sqlcommandbuilder(objadapter)

now suppose we have to find s_no and change the name field.

dim objdataview as new dataview(objdataset.tables(0))
dim inum as integer

objdataview.sort="s_no"

inum=objdataview.find(<source s_no>)
objdataview.tables(0).row(inum).item("name")=updated name is written here

objadapter.update(objdataset,"<tablename>")

 

[michaelw]

Ok this is what i come up with thanks for the help

I am having a problem with the

objadapter
objdataview
inum

are telling me that declartions are needed??

I thought this would be done when i dim them??

Dim objconnection as New SqlClient.SqlConnection("Initial catalog=testbooking;" & "Data Source=(Install_BDC); Integrated Security=SSPI
Dim objadapter As New SqlClient.SqlDataAdapter("Select Urn, invoiceno FROM jobs", objconnection)

Dim objadataset As New DataSet

objadapter.fill (objadapter, "Jobs")

Dim objbuilder As New SqlClient.SqlCommandBuilder(objadapter)

Dim objdataview As New DataView(objadataset.Tables(0))

Dim inum As Integer

objdataview.sort="Urn"

inum = objdataview.find(<source s_no>)

objdataview.tables(0).ow(inum).item("URN")=TEST

objadapter.update(objdataset, "Jobs")