vb.net 搜索 sql 数据库以验证客户帐号

vb.net search the sql database to validate a customer account number

提问by Don Wilson

Group, forgive me for what may be a very simple explanation and answer. However I'm self teaching myself Visual Basic and very new to this altogether. So you know, I'm using Visual Studio 2010 Express.

组，请原谅我的解释和答案可能非常简单。但是，我正在自学 Visual Basic，并且对此完全陌生。您知道，我使用的是 Visual Studio 2010 Express。

I've created a database that includes a customer records table. Via the order entry module I'm working on, I want to allow the user to key in a customer account number. However I need to validate this entry before proceeding with the rest of the program routines. I THINK I know how to connect to the database, but I'm not sure how to have the user entered number compared against the table to validate the customer number. Here is what I've written:

我创建了一个包含客户记录表的数据库。通过我正在处理的订单输入模块，我希望允许用户输入客户帐号。但是，在继续执行其余的程序例程之前，我需要验证此条目。我认为我知道如何连接到数据库，但我不确定如何将用户输入的数字与表格进行比较以验证客户编号。这是我写的：

            Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection
            Dim dbSource As String = "Data Source = DataDesign.mdf"
            Dim dbProvider As String = "PROVIDER=SQL Server 10.0.5500;"
            Dim ds As New DataSet
            Dim sql As String = "SELECT * FROM AR_CUSTOMERS"
            Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(sql, conn)
            conn.ConnectionString = dbProvider & dbSource 
            Dim search1 As Integer = Integer.Parse(txbCustomerNo.Text)
            Dim cmd1 As SqlCommand = New SqlCommand
            conn.Open()

I'm now stuck. I've defined my "search" (search1), but how do I now tell the program to look through the table to see if there is a matching customer number?

我现在卡住了。我已经定义了我的“搜索”(search1)，但是我现在如何告诉程序查看表格以查看是否有匹配的客户编号？

I hate to ask you to help me write the code. But I'm clueless here and I've not been able to find a good example to help me understand the process and write the code myself.

我讨厌请你帮我写代码。但是我在这里一无所知，而且我无法找到一个很好的例子来帮助我理解这个过程并自己编写代码。

If you see any syntax errors, don't hesitate to speak up!!

如果您看到任何语法错误，请不要犹豫，大声说出来！！

---

---

Based on some of your answers and further research, I've modified my code. It now reads:

根据您的一些答案和进一步研究，我修改了我的代码。现在是这样写的：

  Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection
  Dim dbSource As String = "Data Source=.;AttachDbFilename=C:\Users\Don\Documents\DataDesign.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
  Dim dbProvider As String = "PROVIDER=System.Data.SqlClient;"
  Dim ds As New DataSet
  Dim sql As String = "SELECT FROM AR_CUSTOMER"
  Dim da As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(sql, conn)
  conn.ConnectionString = dbProvider & dbSource
  Dim search1 As String = "%" & txbCustName & "%"
  Dim sqlQ1 As String = sql & " WHERE ARC-CUSTOMER-NAME LIKE search1"
  Dim cmd1 As New SqlCommand(sqlQ1, conn)
  conn.Open()

Hopefully this syntax (search) looks better and more accurate. If not, please help me correct it.

希望这个语法（搜索）看起来更好更准确。如果不是，请帮我改正。

Assuming this is correct, what command codes do I need to get the full ARC-CUSTOMER-NAME (and there could potentially be multiple finds) as well as the corresponding ARC-CUSTOMER-NUMBER(s)? I'm assuming that this data would go into two strings (do I need to "Dim" these to receive the data?) so that it could be put into the checkbox(es) and textbox(es) on the form.

假设这是正确的，我需要什么命令代码才能获得完整的 ARC-CUSTOMER-NAME（并且可能有多个发现）以及相应的 ARC-CUSTOMER-NUMBER(s)？我假设这些数据将进入两个字符串（我是否需要将它们“变暗”以接收数据？），以便将其放入表单上的复选框和文本框。

I'm sorry I don't understand this better. I'm hoping through this the lightbulb in my head will finally come on!!

对不起，我没有更好地理解这一点。我希望通过这个，我脑子里的灯泡最终会亮起来！！

回答by Steve

If you are just interested to find if the customer exist or not in the database, you could write code like this

如果你只是想知道客户是否存在于数据库中，你可以写这样的代码

Using conn = new SqlConnection(connectionString)
    conn.Open()
    Dim cmd1 = new SqlCommand("SELECT COUNT(*) FROM AR_CUSTOMERS WHERE customerNo = @custNo")
    cmd1.Parameters.AddWithValue("@custNo", Convert.ToInt32(txbCustomerNo.Text))
    Dim result = cmd1.ExecuteScalar()
    if Convert.ToInt32(result) > 0 then
        MessageBox.Show("Customer Found")
    End If
End Using

the ExecuteScalarmethod of the SqlCommand object could be used if you are interested to find just a single row with a single result and it is usually very fast in cases like this.

SqlCommand 对象的ExecuteScalar方法可以用于如果您只想找到具有单个结果的单行，并且在这种情况下它通常非常快。

Apart from this, I'm very perplexed by your connection string. It doesn't seems to be a valid one.
Here you can find numerous examples of connectionstrings valid for Sql Server: http://www.connectionstrings.com/sql-server-2012

除此之外，我对您的连接字符串感到非常困惑。它似乎不是一个有效的。
在这里您可以找到许多对 Sql Server 有效的连接字符串示例：http: //www.connectionstrings.com/sql-server-2012

EDITBased on your comment it si now clear that a simple ExecuteScalar is not enough. We should use a SqlDataReader and use an ExecuteReader. Of course the query is completely different

编辑根据您的评论，现在很清楚一个简单的 ExecuteScalar 是不够的。我们应该使用 SqlDataReader 并使用 ExecuteReader。当然查询是完全不同的

Using conn = new SqlConnection(connectionString)
    conn.Open()
    Dim cmd1 = new SqlCommand("SELECT CustomerNo, CustomerName FROM AR_CUSTOMERS " + 
                              "WHERE customerName LIKE= @custName")
    cmd1.Parameters.AddWithValue("@custName", "%" + txtSearchText.Text.Trim() + "%")

    Dim reader As SqlDataReader = cmd1.ExecuteReader()
    If Not reader.HasRows Then
        txbCustName.Text = "No customer found!"
    Else
        While reader.Read()
           txbCustName.Text = reader(0).ToString
           cbxCustNo.Text = reader(1).ToString
        Loop
    End If
End Using

However this approach has its problems. What if the search using LIKE returns more than one rows? In this case your textboxes will be filled with the values of the latest customer found.
Perhaps it is better to use a DataTable and, if there are more than one customer ask the user to select from the available ones

然而，这种方法有其问题。如果使用 LIKE 的搜索返回多于一行怎么办？在这种情况下，您的文本框将填充最新找到的客户的值。
也许使用 DataTable 更好，如果有多个客户要求用户从可用的中进行选择

Using conn = new SqlConnection(connectionString)
    conn.Open()
    Dim cmd1 = new SqlCommand("SELECT CustomerNo, CustomerName FROM AR_CUSTOMERS " + 
                              "WHERE customerName LIKE= @custName")
    cmd1.Parameters.AddWithValue("@custName", "%" + txtSearchText.Text.Trim() + "%")

    Dim adapter As SqlDataAdapter = new SqlDataAdapter(cmd1)
    Dim table As DataTable = new DataTable()
    adapter.Fill(table)
    If table.Rows.Count = 0 Then
        txbCustName.Text = "No customer found!"
    Else if table.Rows.Count = 1 Then
        Dim row as DataRow = table.Rows(0)
        txbCustName.Text = row("CustomerName").ToString
        cbxCustNo.Text = row("CustomerNo").ToString
    Else
        MessageBox.Show("More  than one customer found!")
        ..... code to show the table in a datagridview .... 
    End If
End Using

回答by fabricio

The easiest way should be writing sql query already filtering by costumer number... SELECT * FROM AR_CUSTOMERS WHERE costumerNo = txbCosumerNo.Text, and then you can use DataSet to store data retrieved from DataBase... (following your code..)

最简单的方法应该是编写已经按客户编号过滤的 sql 查询... SELECT * FROM AR_CUSTOMERS WHERE costerNo = txbCosumerNo.Text，然后您可以使用 DataSet 来存储从 DataBase 检索的数据...（按照您的代码...）

DataSet data = new DataSet();
da.fill(data);

... you can get a DataTable from data.. (DataSet is a collection of DataTables), could be easy to work with.

...您可以从数据中获取 DataTable..（DataSet 是 DataTable 的集合），很容易使用。