locked
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?

     

     

    Tuesday, September 04, 2007 7:16 PM

Answers

  • 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

    Friday, September 07, 2007 5:19 PM

All replies

  • 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"

     

    Friday, September 07, 2007 3:26 AM
  • 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

    Friday, September 07, 2007 5:19 PM
  • 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

    Sunday, September 09, 2007 7:27 AM