在SQL Server 2014中使用以下查询获取此错误 [英] Getting this error with the following query in SQL server 2014

问题描述

我有UPDATE和DELETE按钮。它们有相同的错误......





在预期条件的上下文中指定的非布尔类型的表达式，接近'ID'。



我的尝试：

I have buttons "UPDATE" and "DELETE". They have the same errors...


An expression of non-boolean type specified in a context where a condition is expected, near 'ID'.

What I have tried:

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Try
            conn = New SqlConnection(strcon)
            conn.Open()
            Dim str As String = "Update PatientInfo set Surname=@Surname,Firstname=@Firstname,MI=@MI,Gender=@Gender,Birthday=@Birthday,Address=@Address,Contact=@Contact,Email=@Email Where Patient ID=@PID"
            cmd = New SqlCommand(str, conn)
            cmd.Parameters.AddWithValue("@Surname", txtSurname.Text)
            cmd.Parameters.AddWithValue("@Firstname", txtFirstname.Text)
            cmd.Parameters.AddWithValue("@MI", txtMI.Text)
            cmd.Parameters.AddWithValue("@Gender", CmbxGender.Text)
            cmd.Parameters.AddWithValue("@Birthday", dtpBday.Text)
            cmd.Parameters.AddWithValue("@Address", txtAdd.Text)
            cmd.Parameters.AddWithValue("@Contact", txtContact.Text)
            cmd.Parameters.AddWithValue("@Email", txtEmail.Text)
            cmd.Parameters.AddWithValue("@PID", txtPID.Text)
            cmd.ExecuteNonQuery()
            MessageBox.Show("Data has been updated!")
            loadData()
            cmd.Dispose()
            conn.Close()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
'==========================================================================

Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click

        Dim cmd As New SqlCommand
        Try

            conn.ConnectionString = strcon
            conn.Open()
            cmd.Connection = conn
            cmd.CommandText = "Delete from PatientInfo Where Patient ID=" + txtPID.Text
            cmd.ExecuteNonQuery()
            MessageBox.Show("Data has been deleted")
            loadData()

            conn.Close()


        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

推荐答案

这是一个在表和列名称中包含空格字符的坏主意。在您的SQL查询中，您显然有一个名为患者ID的列。它可能应该是PatientID，但是如果你坚持要在其中保留这个列名，你必须在所有带有方括号的SQL语句中指定列名：

It's a bad idea to have space characters in table and column names. In your SQL query you apparently have a column called "Patient ID". It should probably be "PatientID", but if you insist on keeping this column name with a space in it you MUST specify the column name in all SQL statements with square brackets:

DELETE FROM PatientInfo WHERE [Patient ID]=@PID"

哦，只是因为你只有一个参数DELETE查询，这并不意味着您可以在查询结束时使用字符串连接Id。总是，总是，总是使用参数化查询，即使您只使用一个参数。

Oh, and just because you only have one parameter in the DELETE query, that does NOT mean you can get away with string concatenating the Id on the end of the query. ALWAYS, ALWAYS, ALWAYS use parameterized queries, even if you're only using one parameter.

这篇关于在SQL Server 2014中使用以下查询获取此错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！