updating a record in the database

[srivalli]

for deleting a particular record in a database i wrote the following code:

s = "Delete bankdet1 Where facode1=@facode1"

cmd = New SqlCommand(s, cn)

cmd.Parameters.Add("@facode1", TextBox16.Text)

cn.Open()

i = cmd.ExecuteNonQuery()

cn.Close()

MsgBox(i & " records deleted!")


now that particular record is being deleted.

now if i want to know the code for updating a record in the database
let me know with a small example.
thnaks

 

[jmcilhinney]

UPDATE Table SET field1 = @field1, field2 = @field2 WHERE key = @key

 

[srivalli]

displaying error

i wrote the query as

Try

ss = "update bankdet1 set bankname=@bankname,bankacc=@bankacc where facode=@facode"

cmd = New SqlCommand(ss, cn)

cn.Open()

cmd.ExecuteNonQuery()

cn.Close()

MsgBox("record updated")

Catch x As Exception

MsgBox(x.Message)

End Try

it is displaying error as

" MUST DECLARE @BANKNAME"

actually what i am doing is

i am giving facode as input and if i click "EDIT " button ,then the corresponding fields r displaying in 2 textboxes.

after displaying ,i am trying to update one column say bankname

then it is showing error as
"@bankname must be declared"
any quick suggestions plsssss

UPDATE Table SET field1 = @field1, field2 = @field2 WHERE key = @key

 

[jmcilhinney]

You have to add parameters for each field as you did for your delete statement.

 

[srivalli]

hello,
do u mean
Try

ss = "Update bankdet1 Set bankname1=@bankname1,bankacc1=@bankacc1,facode1=@facode1"

cmd = New SqlCommand(ss, cn)

cmd.Parameters.Add("@bankname1", TextBox14.Text)

cmd.Parameters.Add("@bankacc1", TextBox15.Text)

cmd.Parameters.Add("@facode1", TextBox16.Text)

cn.Open()

cmd.ExecuteNonQuery()

cn.Close()

MsgBox("record updated")

Catch x As Exception

MsgBox(x.Message)

End Try


here the record is being updated.
but the problem here is
suppose i have 10 rows in the table,
if i am updating one record ,all the records are getting updated,with the same data.
can u tell me where the error is?
thank u

You have to add parameters for each field as you did for your delete statement.

 

[jmcilhinney]

If your data is in a DataTable then the best course of action is to use an SqlDataAdapter. It has SelectCommand, DeleteCommand, InsertCommand and UpdateCommand properties, each of which is an SqlCommand object. You can create the DataAdapter in the designer and add the commands and their parameters in the Properties window. In your SQL statements you use "?" symbols to stand for your parameters. You call the Fill method of the SqlDataAdapter to retrieve data from a database to a DataTable or DataSet and the Update method to save data from the DataTable or DataSet back to the database.

Dim adapter As New SqlDataAdapter("SELECT * FROM bankdet1", cn)
Dim table As New DataTable

adapter.Fill(table)

adapter.UpdateCommand As New SqlCommand("UPDATE bankdet1 SET bankname1 = ?, bankacc1 = ? WHERE facode1 = ?", cn)
adapter.UpdateCommand.Parameters.Add("@bankname1", SqlType.VarChar, 0, "bankname1")
'Repeat for other parameters.

adapter.Update(table)

 

[srivalli]

as u mentioned i added dataadapter in ther designer and add the commands and their parameters in the Properties window.

but the error shown is

adapter.UpdateCommand As New SqlCommand("UPDATE bankdet1 SET bankname1 = ?, bankacc1 = ? WHERE facode1 = ?", cn)
it is showing error at AS (Expression expected) and at the end as "end of the statement expected".

any modifications needed
?
thks

If your data is in a DataTable then the best course of action is to use an SqlDataAdapter. It has SelectCommand, DeleteCommand, InsertCommand and UpdateCommand properties, each of which is an SqlCommand object. You can create the DataAdapter in the designer and add the commands and their parameters in the Properties window. In your SQL statements you use "?" symbols to stand for your parameters. You call the Fill method of the SqlDataAdapter to retrieve data from a database to a DataTable or DataSet and the Update method to save data from the DataTable or DataSet back to the database.

Dim adapter As New SqlDataAdapter("SELECT * FROM bankdet1", cn)
Dim table As New DataTable
 
adapter.Fill(table)
 
adapter.UpdateCommand As New SqlCommand("UPDATE bankdet1 SET bankname1 = ?, bankacc1 = ? WHERE facode1 = ?", cn)
adapter.UpdateCommand.Parameters.Add("@bankname1", SqlType.VarChar, 0, "bankname1")
'Repeat for other parameters.
 
adapter.Update(table)

 

[srivalli]

instead of

adapter.UpdateCommand As New SqlCommand("UPDATE bankdet1 SET bankname1 = ?, bankacc1 = ? WHERE facode1 = ?", cn)

i used

adapter.UpdateCommand = New SqlCommand("UPDATE bankdet1 SET bankname1 = ?, bankacc1 = ? WHERE facode1 = ?", cn)

now no errors

but the record is not getting updated in the backend.

what to do

as u mentioned i added dataadapter in ther designer and add the commands and their parameters in the Properties window.

but the error shown is

adapter.UpdateCommand As New SqlCommand("UPDATE bankdet1 SET bankname1 = ?, bankacc1 = ? WHERE facode1 = ?", cn)
it is showing error at AS (Expression expected) and at the end as "end of the statement expected".

any modifications needed
?
thks

 

[jmcilhinney]

Sorry for the coding error. I wrote it straight into the message rather than pasting from the IDE. I have no time at the moment but I'll get back to you again soon.

 

[jmcilhinney]

SqlDataAdapter.Update() is a function that returns the number of rows that were affected, so the first thing I would do is show the result in a messabe box like this:

MessageBox.Show(adapter.Update(table))

This will show you whether any rows are actually being affected by your call to Update(). Keep in mind that the figure shown is the collective total of deleted, inserted and updated rows. If the figure returned is zero, you need to make sure that the DataTable is being updated. The values you were originally using were in TextBoxes. Are you sure these values are being transferred to the DataTable itself? Unless your data is bound to the controls it is up to you to update the DataTable with the values from the TextBoxes. You do this by calling BeginEdit on the DataRow, then explicitly setting each changed field, then calling EndEdit.

 

[srivalli]

hello,
i have 3 fields in my form and 3 textboxes ,can u tell me how to TRANSFER the textbox values into a datatable and how to bind . u mentioned that it is better to call beginEdit and EndEdit ,if u dont mind can u mention the code ,its veryyyyy urgent.
thanks

SqlDataAdapter.Update() is a function that returns the number of rows that were affected, so the first thing I would do is show the result in a messabe box like this:

MessageBox.Show(adapter.Update(table))

This will show you whether any rows are actually being affected by your call to Update(). Keep in mind that the figure shown is the collective total of deleted, inserted and updated rows. If the figure returned is zero, you need to make sure that the DataTable is being updated. The values you were originally using were in TextBoxes. Are you sure these values are being transferred to the DataTable itself? Unless your data is bound to the controls it is up to you to update the DataTable with the values from the TextBoxes. You do this by calling BeginEdit on the DataRow, then explicitly setting each changed field, then calling EndEdit.

 

[jmcilhinney]

You simply need to set each field value to achieve the TRANSFER. Assuming you have the DataRow already, your code would look something like this:

row.BeginEdit()
row("field1") = Me.TextBox1.Text 'assign the text from TextBox1 to the field named field1
row("field2") = Me.TextBox2.Text
row("field3") = Me.TextBox3.Text
row.EndEdit()

As for data binding, that is a fairly big topic. I'd suggest you do some reading in the help or on MSDN. If you have specific questions we might be able to help but I can't explain the whole topic.

 

[srivalli]

query in updating

atlast i got the query for updating without using any datatable.thks for ur suggestions.

You simply need to set each field value to achieve the TRANSFER. Assuming you have the DataRow already, your code would look something like this:

row.BeginEdit()
row("field1") = Me.TextBox1.Text 'assign the text from TextBox1 to the field named field1
row("field2") = Me.TextBox2.Text
row("field3") = Me.TextBox3.Text
row.EndEdit()

As for data binding, that is a fairly big topic. I'd suggest you do some reading in the help or on MSDN. If you have specific questions we might be able to help but I can't explain the whole topic.