vb.net SQL Server 中的更新语句

Update Statement in SQL Server

提问by coder

I am trying to update some records after a new record was inserted where it was not updating and I'm not getting any error.

我试图在插入新记录后更新一些记录，但没有更新，我没有收到任何错误。

Here is my code:

这是我的代码：

    If txtSearch.Text <> txtUserID.Text Then
        Try
            Dim connectionString As String = "Data Source=.\SqlExpress;Initial Catalog=Subscription;Integrated Security=True"
            Using cn As New SqlConnection(connectionString)
                cn.Open()
                cmd.CommandText = "INSERT INTO Customers (UserID, RegisteredDate, ExpiryDate, FirstName, LastName, Address, State, City, Phone, Mobile, Email) VALUES(@UserID, @RegisteredDate, @ExpiryDate, @FirstName, @LastName, @Address, @State, @City, @Phone, @Mobile, @Email)"

                Dim param1 As New SqlParameter()
                param1.ParameterName = "@UserID"
                param1.Value = txtUserID.Text.Trim()
                cmd.Parameters.Add(param1)

                Dim param2 As New SqlParameter()
                param2.ParameterName = "@RegisteredDate"
                param2.Value = RegisteredDate.Value
                cmd.Parameters.Add(param2)

                Dim param3 As New SqlParameter()
                param3.ParameterName = "@ExpiryDate"
                param3.Value = ExpiryDate.Value
                cmd.Parameters.Add(param3)

                Dim param4 As New SqlParameter()
                param4.ParameterName = "@FirstName"
                param4.Value = txtFirstName.Text.Trim()
                cmd.Parameters.Add(param4)

                Dim param5 As New SqlParameter()
                param5.ParameterName = "@LastName"
                param5.Value = txtLastName.Text.Trim()
                cmd.Parameters.Add(param5)


                Dim param6 As New SqlParameter()
                param6.ParameterName = "@Address"
                param6.Value = txtAddress.Text.Trim()
                cmd.Parameters.Add(param6)

                Dim param7 As New SqlParameter()
                param7.ParameterName = "@State"
                param7.Value = cboState.SelectedItem.ToString
                cmd.Parameters.Add(param7)

                Dim param8 As New SqlParameter()
                param8.ParameterName = "@City"
                param8.Value = cboCity.SelectedItem.ToString
                cmd.Parameters.Add(param8)

                Dim param9 As New SqlParameter()
                param9.ParameterName = "@Phone"
                param9.Value = txtPhone.Text.Trim()
                cmd.Parameters.Add(param9)

                Dim param10 As New SqlParameter()
                param10.ParameterName = "@Mobile"
                param10.Value = txtMobile.Text.Trim()
                cmd.Parameters.Add(param10)

                Dim param11 As New SqlParameter()
                param11.ParameterName = "@Email"
                param11.Value = txtEmail.Text.Trim()
                cmd.Parameters.Add(param11)

                cmd.Connection = cn
                cmd.ExecuteNonQuery()
                cn.Close()
            End Using
            Successlbl.Show()
            Successlbl.ForeColor = Color.DarkBlue
            Successlbl.Text = "Record Saved Successfully!."

        Catch
            Successlbl.Show()
            Successlbl.ForeColor = Color.Red
            Successlbl.Text = "Error in creating record!"
        End Try
    Else
        Try
            Dim connectionString As String = "Data Source=.\SqlExpress;Initial Catalog=Subscription;Integrated Security=True"
            Using cn As New SqlConnection(connectionString)
                cn.Open()
                cmd.CommandText = "UPDATE Customers SET (UserID, RegisteredDate, ExpiryDate, FirstName, LastName, Address, State, City, Phone, Mobile, Email) VALUES(@UserID, @RegisteredDate, @ExpiryDate, @FirstName, @LastName, @Address, @State, @City, @Phone, @Mobile, @Email) WHERE UserID=" & txtUserID.Text & ""

                Dim param1 As New SqlParameter()
                param1.ParameterName = "@UserID"
                param1.Value = txtUserID.Text.Trim()
                cmd.Parameters.Add(param1)

                Dim param2 As New SqlParameter()
                param2.ParameterName = "@RegisteredDate"
                param2.Value = RegisteredDate.Value
                cmd.Parameters.Add(param2)

                Dim param3 As New SqlParameter()
                param3.ParameterName = "@ExpiryDate"
                param3.Value = ExpiryDate.Value
                cmd.Parameters.Add(param3)

                Dim param4 As New SqlParameter()
                param4.ParameterName = "@FirstName"
                param4.Value = txtFirstName.Text.Trim()
                cmd.Parameters.Add(param4)

                Dim param5 As New SqlParameter()
                param5.ParameterName = "@LastName"
                param5.Value = txtLastName.Text.Trim()
                cmd.Parameters.Add(param5)


                Dim param6 As New SqlParameter()
                param6.ParameterName = "@Address"
                param6.Value = txtAddress.Text.Trim()
                cmd.Parameters.Add(param6)

                Dim param7 As New SqlParameter()
                param7.ParameterName = "@State"
                param7.Value = cboState.SelectedItem.ToString
                cmd.Parameters.Add(param7)

                Dim param8 As New SqlParameter()
                param8.ParameterName = "@City"
                param8.Value = cboCity.SelectedItem.ToString
                cmd.Parameters.Add(param8)

                Dim param9 As New SqlParameter()
                param9.ParameterName = "@Phone"
                param9.Value = txtPhone.Text.Trim()
                cmd.Parameters.Add(param9)

                Dim param10 As New SqlParameter()
                param10.ParameterName = "@Mobile"
                param10.Value = txtMobile.Text.Trim()
                cmd.Parameters.Add(param10)

                Dim param11 As New SqlParameter()
                param11.ParameterName = "@Email"
                param11.Value = txtEmail.Text.Trim()
                cmd.Parameters.Add(param11)

                cmd.Connection = cn
                cmd.ExecuteNonQuery()
            End Using
            Successlbl.Show()
            Successlbl.ForeColor = Color.DarkBlue
            Successlbl.Text = "Record updated Successfully!."

        Catch
            Successlbl.Show()
            Successlbl.ForeColor = Color.Red
            Successlbl.Text = "Error in updating record!"
        End Try
    End If

Can anyone say me where am I going wrong?

谁能告诉我我哪里错了？

回答by marc_s

Your UPDATEstatement is all wrong for SQL Server / T-SQL. You cannot use the same syntax as the INSERTstatement does. Read more about what the detailed syntax of UPDATEis on MSDN SQL Server Books Online.

你的UPDATE说法对于 SQL Server / T-SQL 都是错误的。您不能使用与INSERT语句相同的语法。UPDATE在MSDN SQL Server 联机丛书中阅读有关详细语法的更多信息。

You need to specify:

您需要指定：

UPDATE dbo.Customers 
SET 
   RegisteredDate = @RegisteredDate, 
   ExpiryDate = @ExpiryDate, 
   FirstName = @FirstName, 
   LastName = @LastName, ...... -- and so on for all relevant columns
WHERE UserID = @UserID

Also: you're using parameters everywhere (+1 for that!), but notfor the WHEREclause.... change that!

另外：您正在使用参数到处（+1为！），但不为WHEREthat从句....改变！

回答by user3167664

    Try
        If con.State = ConnectionState.Open Then con.Close()
        con.Open()
        global_command = New SqlCommand("UPDATE products_tbl set running_no = '" & txt_running.Text & "' where template_code = 'n'and prod_no = '" & txt_product.Text & "'", con)
        global_command.ExecuteNonQuery()
        global_command.Dispose()

        MsgBox("Successfully updated!", MsgBoxStyle.Information, "Message")
        where = vbNullString

    Catch ex As Exception
        MsgBox("Trace No 4: System Error or Data Error!" + Chr(13) + ex.Message + Chr(13) + "Please Contact Your System Administrator!", vbInformation, "Message")
    End Try

End Sub