查询 SQL 数据库并将结果存储在变量 VB.Net 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21246106/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query SQL Database and Store Results in a Variable VB.Net
提问by user3217059
I am trying to query a SQL DB using a textbox field and retreive a column from the DB and store it in a variable so I can use it in other places within my site. My web form requires the user to enter a few items such as name and zip code.
我正在尝试使用文本框字段查询 SQL 数据库，并从数据库中检索一列并将其存储在一个变量中，以便我可以在我网站的其他地方使用它。我的 Web 表单要求用户输入一些项目，例如姓名和邮政编码。
My database has 3 columns; email address, zip code, and id. I need the input form to query the database and return the "email address" that matches the user's inputted "zip code"
我的数据库有 3 列；电子邮件地址、邮政编码和 ID。我需要输入表单来查询数据库并返回与用户输入的“邮政编码”匹配的“电子邮件地址”
I understand the SQL SELECT statement and the connection string is correct. My queries are working, I just can't seem to figure out how to get the returned "email address" to store in a variable. Any help would be appreciated.
我理解 SQL SELECT 语句并且连接字符串是正确的。我的查询有效，我似乎无法弄清楚如何将返回的“电子邮件地址”存储在变量中。任何帮助，将不胜感激。
Dim strconnection As String, strSQL As String, strZipCheck As String
Dim objconnection As OleDbConnection = Nothing
Dim objcmd As OleDbCommand = Nothing
Dim RREmail As String = Nothing
Dim zipQuery As String = zipCodeBox.Text
'connection string
strconnection = "provider=SQLOLEDB;Data Source=XXX.XXX.XXX.XXX;Initial Catalog=XXXXXXX;User ID=XXXX;Password=XXXXXXXX;"
objconnection = New OleDbConnection(strconnection)
objconnection.ConnectionString = strconnection
'opens connection to database
objconnection.Open()
strSQL = "SELECT [EMAIL ADDRESS] FROM ZIPCODEDATA WHERE [ZIP CODE] = @ZIP CODE "
objcmd = New OleDbCommand(strSQL, objconnection)
RREmail = CType(objcmd.ExecuteScalar(), String)
lblRREmail.Text = RREmail
objconnection.Close()
回答by
While the other comments do point out particular deficiencies with your syntax, I would like to address the question of storing a variable and take it a bit further. I generally do not use parameterized connections unless I am calling stored procedures and need an output parameter. Instead, here is what I often do to create a database connection and get my results.
虽然其他评论确实指出了您的语法的特定缺陷，但我想解决存储变量的问题并更进一步。我通常不使用参数化连接，除非我正在调用存储过程并需要一个输出参数。相反，这是我经常执行的操作来创建数据库连接并获取结果。
First I create a public class called dbConn so I dont have to write it out a million times.
首先，我创建了一个名为 dbConn 的公共类，因此我不必将其写出一百万次。
Imports System.Data.SqlClient
Public Class dbConn
Public Property strSQL As String
Private objConn As SqlClient.SqlConnection = _
New SqlClient.SqlConnection("Data Source=(local)\dev;Initial Catalog=testDataBase;Persist Security Info=False;Integrated Security = true;")
Public Function getDt() As DataTable
Dim Conn As New SqlClient.SqlConnection
Dim da As SqlClient.SqlDataAdapter
Dim dt As New DataTable
Try
objConn.Open()
da = New SqlClient.SqlDataAdapter(strSQL, objConn)
da.Fill(dt)
da = Nothing
objConn.Close()
objConn = Nothing
Catch ex As Exception
objConn.Close()
objConn = Nothing
End Try
Return dt
End Function
End Class
Then from another class (lets assume its form1) I call up that function to get a datatable returned to me. In this case I select TOP 1 to save your sanity in case by chance there is more than one email address per zip code.
然后从另一个类（假设它的形式 1）我调用该函数以获取返回给我的数据表。在这种情况下，我选择 TOP 1 来保存您的理智，以防万一每个邮政编码有多个电子邮件地址。
Public sub getdata()
Dim strEmailAddress As String = Nothing
Dim dt As New DataTable
Dim da As New dbConn
da.strSQL = " select top 1 [email address] from [zipcode] " _
& " where [zip code] = '" & strZipCode & "'" _
& " order by [email address] asc"
dt = da.getDt
If dt.Rows.Count > 0 Then
If Not IsDBNull(dt.Rows(0)(0).ToString) Then
strEmailAddress = dt.Rows(0)(0).ToString
End If
End If
End Sub
From there you can use strEmailAddress within the sub after it is set, or you can move the string declaration outside of the sub as a public string declaration to use it elsewhere, or you can create other classes like an email class with a public property for strEmailAddress to pass it off to, etc.
从那里您可以在设置 sub 后使用 strEmailAddress ，或者您可以将字符串声明移到 sub 之外作为公共字符串声明以在其他地方使用它，或者您可以创建其他类，例如具有公共属性的电子邮件类strEmailAddress 将其传递给等。
I hope something in there helps you understand how to deal with your problem.
我希望那里的内容可以帮助您了解如何处理您的问题。
