How do I Update with Parameters

Question

 

I'm Using this part of code to save all he information in the Database:

 

Dim strDB1 As String = "Data Source=.\SQLEXPRESS;" & _

"Initial Catalog=CustomerInformation;Integrated Security=true"

Dim conDB1 As SqlConnection

Dim cmdDB1 As SqlCommand

Dim strSQL1 As String = "INSERT INTO Name (ID, LastName, FirstName, Status, Amount, StatusActive, DateBirth, CreatedDate, Phone, email)" & _

"VALUES (@ID, @LastName, @FirstName, @Status, @Amount, @StatusActive, @DateBirth, @CreatedDate, @Phone, @Email)"

conDB1 = New SqlConnection(strDB1)

cmdDB1 = New SqlCommand(strSQL1, conDB1)

conDB1.Open()

Dim Phone = (Mid(msktxtPhone.Text, 2, 3) & Mid(msktxtPhone.Text, 7, 3) & Mid(msktxtPhone.Text, 11, 4))

With cmdDB1.Parameters

.Add(New SqlParameter("@ID", txtID.Text))

.Add(New SqlParameter("@LastName", txtLastName.Text))

.Add(New SqlParameter("@FirstName", txtFirstName.Text))

.Add(New SqlParameter("@Status", cboxStatus.SelectedIndex))

.Add(New SqlParameter("@Amount", txtAmount.Text))

.Add(New SqlParameter("@StatusActive", ckbInactive.CheckState))

.Add(New SqlParameter("@DateBirth", txtDateBirth.Text))

.Add(New SqlParameter("@CreatedDate", Now()))

.Add(New SqlParameter("@Phone", Phone))

.Add(New SqlParameter("@email", txtEmail.Text))

End With

cmdDB1.ExecuteNonQuery()

conDB1.Close()

 

I would like to do the same to update the records, how do I do it?

 

 

Answer 1

I was trying to do it by myself, this is the code:

 

Dim intRowsUpdate As Integer

Dim strUpdateSQL As String

Dim cmd As New SqlCommand()

Dim cn As New SqlConnection()

 

'SET UP COMMAND AND CONNECTION

cn.ConnectionString = "Data Source=.\SQLEXPRESS;" & _

"Initial Catalog=CustomerInformation;Integrated Security=true"

 

'BUILD THE SQL STRING

Dim Amount As Double = CDbl(txtAmount.Text)

Dim Phone = (Mid(msktxtPhone.Text, 2, 3) & Mid(msktxtPhone.Text, 7, 3) & Mid(msktxtPhone.Text, 11, 4))

With cmd.Parameters

.Add(New SqlParameter("@ID", txtID.Text))

.Add(New SqlParameter("@LastName", txtLastName.Text))

.Add(New SqlParameter("@FirstName", txtFirstName.Text))

.Add(New SqlParameter("@Status", cboxStatus.SelectedIndex))

.Add(New SqlParameter("@Amount", Amount))

.Add(New SqlParameter("@StatusActive", ckbInactive.CheckState))

.Add(New SqlParameter("@DateBirth", txtDateBirth.Text))

.Add(New SqlParameter("@Phone", Phone))

.Add(New SqlParameter("@email", txtEmail.Text))

End With

 

strUpdateSQL = "UPDATE Name SET " & _

"LastName = @LastName" & _

"FirstName = @FirstName" & _

"Status = @Status" & _

"Amount = @Amount" & _

"StatusActive = @StatusActive" & _

"Phone = @Phone" & _

"DateBirth = @DateBirth" & _

"Email = @Email" & _

"WHERE ID = @ID"

 

cn.Open()

cmd.Connection = cn

cmd.CommandText = strUpdateSQL

 

'Execute Command

intRowsUpdate = cmd.ExecuteNonQuery()

 

'Close Connection

cn.Close()

 

But I receive an error:

 

SqlException Unhandled

Must declare the scalar variable "@LastNameFirstName"

 

I really do not understand from where appears "@LastNameFirstName"

 

Answer 2

What Fucking Idiot I was, I just forget the comma.

 

strUpdateSQL = "UPDATE Name SET " & _

"LastName = @LastName, " & _

"FirstName = @FirstName, " & _

"Status = @Status, " & _

"Amount = @Amount, " & _

"DateBirth = @DateBirth, " & _

"Phone = @Phone, " & _

"Email = @Email, " & _

"StatusActive = @StatusActive " & _

"WHERE ID = @ID"

 

 

Problem Solved!

 

At least it's good for reference for anybody who wants to use parameters, because this method avoids SQL Injection.

 

Using Parameterized Queries in ASP.Net
http://aspnet101.com/aspnet101/tutorials.aspx?id=1

Answer 3

Hi Abasilis,

 

Glad to hear that you have solved the issue by yourself. Cheer!

Thank you for sharing your experience with us here. I believe it will be beneficial to other community members having similar questions.

 

Best wishes,

Martin