Update SQL database using dataadapter

M

mcharaia

New member
Joined
Oct 22, 2003
Messages
4
Hi,

I am really new to VB.net & trying this first time. I want to update my huge database using SQLdataadapter. I am using following codes which gives me no error but does not make any changes either to my database. Please help.....

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
SqlDataAdapter1.UpdateCommand.CommandText = "UPDATE dbo.MKTest SET ID = @ID, Lname = @Lname, Fname = @Fname WHERE (ID = @Origi" & _
"nal_ID) AND (Fname = @Original_Fname OR @Original_Fname IS NULL AND Fname IS NUL" & _
"L) AND (Lname = @Original_Lname OR @Original_Lname IS NULL AND Lname IS NULL); S" & _
"ELECT ID, Lname, Fname FROM dbo.MKTest WHERE (ID = @ID)"

SqlUpdateCommand1.Connection = SqlConnection1
SqlDataAdapter1.UpdateCommand.Connection.Open()

Dim myParm As SqlParameter = SqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_ID", SqlDbType.Decimal, 9, "ID")
myParm.SourceColumn = "ID"
myParm.SourceVersion = DataRowVersion.Original

Dim myParm1 As SqlParameter = SqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_Fname", SqlDbType.VarChar, 10, "Fname")
myParm1.SourceColumn = "Fname"
myParm1.Value = Trim(TextBox2.Text.ToString)
myParm1.SourceVersion = DataRowVersion.Current

Dim myParm2 As SqlParameter = SqlDataAdapter1.UpdateCommand.Parameters.Add("@Original_Lname", SqlDbType.VarChar, 10, "Lname")
myParm2.SourceColumn = "Lname"
myParm2.Value = Trim(TextBox3.Text.ToString)
myParm2.SourceVersion = DataRowVersion.Current

SqlDataAdapter1.Update(DataSet11, "MKTest")
Catch ex As Exception
Errormessages.Text = ex.ToString
End Try
SqlDataAdapter1.UpdateCommand.Connection.Close()
Errormessages.Text = "Update Done"
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Errormessages.Text = " "
If Trim(TxtEmpBugtNum.Text) = "" Then
Errormessages.Text = "Please enter the Employee/Budget Authorization Number; you want to change"
Exit Sub
End If
SqlDataAdapter1.SelectCommand.Parameters.Clear()
SqlDataAdapter1.SelectCommand.Parameters.Add("@Param1", System.Data.SqlDbType.Decimal, 9, "ID").Value = TxtEmpBugtNum.Text
Try
SqlDataAdapter1.Fill(DataSet11)
Catch
Errormessages.Text = "Record not found"
Exit Sub
End Try
If DataSet11.MKTest.Rows.Count = 0 Then
Errormessages.Text = "Record not found"
Exit Sub
Else
TextBox2.Text = DataSet11.MKTest(0).Fname
TextBox3.Text = DataSet11.MKTest(0).Lname
End If


Thanks in advance,
mcharaia
 
A few things to point out here:

With the data-adapter, you wont have to issue an "open"/"close" on your connection for the update, just call dataadapter.update(...). Its an implicit part of issuing the Update.

When you build your update command, it should look more like:
mySqlDataAdapter.UpdateCommand = new SqlCommand("Update .... Set ... = @... WHERE .... = @..." , SqlConnection1)

Then add your parms (which look good as they are)

Finally call the update:
try
mySqlDataAdapter.Update(DataSet11, "MKTest")
catch ex as exception
messagebox.show(ex.tostring)
end try


Hope that helps. I generally use stored procedures to update from my apps since Im only updating specific records and our security requirements dont allow the apps to issue their own ad-hoc updates (have to be done through stored procs), so take the above with a grain of salt....
:D
 
Thank you Mocella,
I have changes it as you suggested, it still does not work. Can you see where I am wrong? Thanks.

Dim mySqlDataAdapter As New SqlDataAdapter()
Try
mySqlDataAdapter.UpdateCommand = New SqlCommand("Update dbo.MKTest SET Lname = @Original_Lname, Fname = @Original_Fname WHERE (ID = @Original_ID)", SqlConnection1)

Dim myParm As SqlParameter = mySqlDataAdapter.UpdateCommand.Parameters.Add("@Original_ID", SqlDbType.Decimal, 9, "ID")
myParm.SourceColumn = "ID"
myParm.SourceVersion = DataRowVersion.Original

Dim myParm1 As SqlParameter = mySqlDataAdapter.UpdateCommand.Parameters.Add("@Original_Fname", SqlDbType.VarChar, 10, "Fname")
myParm1.SourceColumn = "Fname"
myParm1.Value = Trim(TextBox2.Text.ToString)
myParm1.SourceVersion = DataRowVersion.Current

Dim myParm2 As SqlParameter = mySqlDataAdapter.UpdateCommand.Parameters.Add("@Original_Lname", SqlDbType.VarChar, 10, "Lname")
myParm2.SourceColumn = "Lname"
myParm2.Value = Trim(TextBox3.Text.ToString)
myParm2.SourceVersion = DataRowVersion.Current

mySqlDataAdapter.Update(DataSet11, "MKTest")
Catch ex As Exception
Errormessages.Text = ex.ToString
End Try
Errormessages.Text = "Update Done"
 
In the first version, your dataadapter is called "SqlDataAdapter1" and your dataset is called "Dataset11", my guess is you configured the dataadapter and dataset through the IDE. Make sure the "#Region " Windows Form Designer generated code " section does not include a definition of the update command.
In the most recent version, you dim the sqladapter as in "Dim mySqlDataAdapter As New SqlDataAdapter()" but there is no .fill statement prior to any update statement.

Start over.

Look here.

Jon
 
Good call jfackler, I didnt even think of that. I usually just code all my stuff from scratch, so I dont tend to use the IDE for these situations. I like to know exactly whats going on and for it to behave as I "tell" it to behave.
 
Thank you, jfackler for the hint and link. It worked by coding from scratch & was easy to understand each step.
 
Back
Top