Updating database from a dataset

[VBnikosnyc]

Hi everybody,

i am writing an application that connects to a database and i am stuck at the point where i made my changes in my dataset and i want to write back from my dataset to my db. I tried it and my data are can be altered in the dataset but never on the database...

do i update line by line to my database?
do i update with a command the whole dataset?

below is a sample of my code
any ideas or recommendations please tell me

---by clicking the button update the follow commands should run
 
'check first if the dataset is loaded
If (cmydataset.Tables.Contains("course")) Then
 
cmyadapter = New OleDb.OleDbDataAdapter("select * from student", cmycon)
'normally here i would have more than one field but i omitted for simplicity reasons
cmyadapter.UpdateCommand = New OleDb.OleDbCommand("Update course set cour_name = Textbox1.text " & _
" WHERE Cour_ID = Textbox2.text")
 
'check if there are null values
If (TextBox1.Text = "") Then
MsgBox("you have nulls, you cannot enter null values")
Else
'chekc update student constraints
'if ok
'update student data
 
cmyadapter.Update(cmydataset, "Course")
cmydataset.AcceptChanges()
End If
End If

 

[ashishnaicker]

normally i would use the visual designer handle the insert, update, and delete commands because these commands also require the need to use the original values for concurrency reasons.

as far as line by line updating goes, its definitely possible with a

foreach row as DataRow in myDataSet.Table(0).Rows ... Next

structure.

 

[vis781]

VBnikosnyc,

One of yuor biggest problems there is that you've used string literals in a string. SQL doesn't detect strings inside strings, that is to say that above you have an SQL string, inside that string you have variables referring to a string value, SQL doesn't care that you've done this and will carry one regardless. So we need to privude imformation that will inform our database/SQL that there are values we want to provide outside of our SQL statement. Below is a revised example of your code that uses parameters, a question mark will be placed where we want to show that we have an outside value. The question mark is just a placeholder for the parameter....


Dim _UPCmd as new OleDbCommand("UPDATE course SET cour_name = ? WHERE cour_id = ?", your connection)
_upCmd.Parameters.Add("@cour_name",OleDbType.VarWChar,255,"cour_name").Value = TextBox1.Text
UP_Cmd.Parameters.Add("@cour_id",OleDbType.Integer,0,"cour_id").SourceVersion = DataRowVersion.Original


So what we've done is to add the parameters to the oledbcommand parameters collection in the order that they appear in the SQL statement. You may notice that the first parameter has a .Value at the end, thisis the value we want to pass in to the first question mark in the SQL statement. The Second Parameter differs in that is has sourceversion at the end. When we fill a datatable, much more that just your data is retrieved, other information is also gathered to help with updates and such. So in our second parameter we are basically telling the dataadpater to update cour_name where cour_id's information, is that same as when the data was gathered. With me? I know that sounds a bit complicated, but if you need anything explained further then let me know.

I've also attached a .vb file. One of my first ever attempts at creating a SQL builder class, i know it sounds like i tried to re-invent the wheel since ado.net already has a commandbuilder, but i just wanted to have a go, it was just a project i begun when i first started out so the coding isn't great. But you may find it ueful.