在 vb.net 中添加记录并使用 elseif 检查记录是否存在 [英] Adding records in vb.net and Checking if records exist using elseif

问题描述

我是 vb.net 的新手......所以提前抱歉.谁能帮我看看我的 elseif 代码行出了什么问题.

 Dim con As SqlConnection = New SqlConnection("Data Source=PC11-PC\kim;Initial Catalog=ordering;User ID=sa;Password=123")Dim cmd1 As SqlCommand = New SqlCommand("Select * from Customer", con)Dim first1 As StringDim second2 作为字符串first1 = "名字"second2 = "姓氏"con.Open()如果 TextBox1.Text = "" 或 TextBox2.Text = "" 那么MsgBox("请填写所有字段！", MsgBoxStyle.Exclamation, "添加新客户！")'这应该会显示用户已经存在"的错误消息' ElseIf textbox1.text = first1 and textbox2.text = second2 Then' MsgBox("用户已经存在！", MsgBoxStyle.Exclamation, "添加新用户！")别的Dim cmd As SqlCommand = New SqlCommand("Insert into [ordering].[dbo].[Customer] ([FirstName],[LastName]) values ('" + TextBox1.Text + "','" + TextBox2.Text +"')", con)cmd.ExecuteNonQuery()MsgBox("记录添加成功！", MsgBoxStyle.Information, "添加新客户！")TextBox1.Text = ""TextBox2.Text = ""关闭()万一

解决方案

您需要通过执行 SELECT * FROM Customer 查询来实际检查用户是否已经存在，但您需要添加WHERE 子句，像这样:

If TextBox1.Text = "" 或 TextBox2.Text = "" 然后MsgBox("请填写所有字段！", MsgBoxStyle.Exclamation, "添加新客户！")别的Dim theQuery As String = "SELECT * FROM Customer WHERE FirstName=@FirstName AND LastName=@LastName"Dim cmd1 As SqlCommand = New SqlCommand(theQuery, con)cmd1.Parameters.AddWithValue("@FirstName", TextBox1.Text)cmd1.Parameters.AddWithValue("@LastName", TextBox2.Text)使用阅读器作为 SqlDataReader = cmd1.ExecuteReader()如果 reader.HasRows 那么' 用户已存在MsgBox("用户已经存在！", MsgBoxStyle.Exclamation, "添加新用户！")别的' 用户不存在，添加Dim cmd As SqlCommand = New SqlCommand("Insert into [ordering].[dbo].[Customer] ([FirstName],[LastName]) values ('" + TextBox1.Text + "','" + TextBox2.Text +"')", con)cmd.ExecuteNonQuery()MsgBox("记录添加成功！", MsgBoxStyle.Information, "添加新客户！")TextBox1.Text = ""TextBox2.Text = ""万一结束使用关闭()万一

<小时><块引用>

注意:我在 SELECT * 查询中添加了参数化查询的用法.与内联 SQL 相比，您应该更喜欢参数化查询，因为它可以保护您的代码免受 SQL 注入攻击.永远不要相信用户输入的数据.

I'm new to vb.net.. so sorry in advance. can anyone help me what's wrong with my elseif line of code.

    Dim con As SqlConnection = New SqlConnection("Data Source=PC11-PC\kim;Initial Catalog=ordering;User ID=sa;Password=123")
    Dim cmd1 As SqlCommand = New SqlCommand("Select * from Customer", con)

    Dim first1 As String
    Dim second2 As String
    first1 = "FirstName"
    second2 = "LastName"

    con.Open()
        If TextBox1.Text = "" Or TextBox2.Text = "" Then
            MsgBox("Please fill-up all fields!", MsgBoxStyle.Exclamation, "Add New Customer!")
        'this will supposedly display error message for "User Already Exist"
        ' ElseIf textbox1.text = first1 and textbox2.text = second2 Then
        '   MsgBox("User Already Exist!", MsgBoxStyle.Exclamation, "Add New User!")
        Else
            Dim cmd As SqlCommand = New SqlCommand("Insert into [ordering].[dbo].[Customer] ([FirstName],[LastName]) values ('" + TextBox1.Text + "','" + TextBox2.Text + "')", con)
            cmd.ExecuteNonQuery()
            MsgBox("Records Successfully Added!", MsgBoxStyle.Information, "Add New Customer!")
            TextBox1.Text = ""
            TextBox2.Text = ""
            con.Close()

        End If

解决方案

You need to actually check to see if the user already exists by executing the SELECT * FROM Customer query, but you need to add the WHERE clause, like this:

If TextBox1.Text = "" Or TextBox2.Text = "" Then
    MsgBox("Please fill-up all fields!", MsgBoxStyle.Exclamation, "Add New Customer!")
Else
    Dim theQuery As String = "SELECT * FROM Customer WHERE FirstName=@FirstName AND LastName=@LastName"
    Dim cmd1 As SqlCommand = New SqlCommand(theQuery, con)
    cmd1.Parameters.AddWithValue("@FirstName", TextBox1.Text)
    cmd1.Parameters.AddWithValue("@LastName", TextBox2.Text)

    Using reader As SqlDataReader = cmd1.ExecuteReader()
        If reader.HasRows Then
            ' User already exists
            MsgBox("User Already Exist!", MsgBoxStyle.Exclamation, "Add New User!")
        Else
            ' User does not exist, add them
            Dim cmd As SqlCommand = New SqlCommand("Insert into [ordering].[dbo].[Customer] ([FirstName],[LastName]) values ('" + TextBox1.Text + "','" + TextBox2.Text + "')", con)
            cmd.ExecuteNonQuery()
            MsgBox("Records Successfully Added!", MsgBoxStyle.Information, "Add New Customer!")
            TextBox1.Text = ""
            TextBox2.Text = ""
        End If
    End Using    

    con.Close()
End If

---

Note: I added the usage of a parameterized query in the SELECT * query. You should prefer parameterized queries to in-line SQL because it will protect your code from SQL Injection attacks. Never trust the data typed in by the user.

这篇关于在 vb.net 中添加记录并使用 elseif 检查记录是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！