Update SQL database using dataadapter

[mcharaia]

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

[mocella]

A few things to point out here:

 

With the data-adapter, you won't have to issue an "open"/"close" on your connection for the update, just call dataadapter.update(...). It's 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 I'm only updating specific records and our security requirements don't 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

[mcharaia]

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"

[jfackler]

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

[mocella]

Good call jfackler, I didn't even think of that. I usually just code all my stuff from scratch, so I don't tend to use the IDE for these situations. I like to know exactly what's going on and for it to behave as I "tell" it to behave.

[mcharaia]

Thank you, jfackler for the hint and link. It worked by coding from scratch & was easy to understand each step.

[robertsams23]

connection = New SqlConnection(connetionString)

sql = "update product set product_price = 1001 where Product_name ='Product7'"

connection.Open()

adapter.UpdateCommand = connection.CreateCommand

adapter.UpdateCommand.CommandText = sql

adapter.UpdateCommand.ExecuteNonQuery()

 

 

http://vb.net-informations.com/dataadapter/dataadapter-deletecommand-sqlserver.htm

 

robert