SQL 和 VB.NET：使用文本框中的数据进行 SELECT 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15525955/
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
SQL and VB.NET : SELECT query using data in textbox
提问by Fares K. A.
I'm trying to create a search, where the SQL query depends on a radio button and a textbox.
我正在尝试创建一个搜索，其中 SQL 查询取决于一个单选按钮和一个文本框。
SELECT * FROM [Sales] WHERE (text in radio button) = (text in textbox)
How can I implement this?
我该如何实施？
Below is my full code.
下面是我的完整代码。
Protected Sub btnSearch_Click(sender As Object, e As ImageClickEventArgs) Handles btnSearch.Click
If radClient.Checked = True Then
Dim connection As New OleDb.OleDbConnection
Dim provider As String
Dim source As String
provider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
source = "Data Source=|DataDirectory|DeltaOneMDB.mdb"
connection.ConnectionString = provider & source
GridView1.Visible = False
connection.Open()
SqlDataSource3.SelectCommand = "SELECT * FROM Sales WHERE ClientID = '" & txtSearch.Text & "'"
connection.Close()
ElseIf radItem.Checked = True Then
Dim connection As New OleDb.OleDbConnection
Dim provider As String
Dim source As String
provider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
source = "Data Source=|DataDirectory|DeltaOneMDB.mdb"
connection.ConnectionString = provider & source
GridView1.Visible = False
connection.Open()
SqlDataSource3.SelectCommand = "SELECT * FROM Sales WHERE ItemID = '" & txtSearch.Text & "'"
connection.Close()
ElseIf radUser.Checked = True Then
Dim connection As New OleDb.OleDbConnection
Dim provider As String
Dim source As String
provider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
source = "Data Source=|DataDirectory|DeltaOneMDB.mdb"
connection.ConnectionString = provider & source
GridView1.Visible = False
connection.Open()
SqlDataSource3.SelectCommand = "SELECT * FROM Sales WHERE UserID = '" & txtSearch.Text & "'"
connection.Close()
ElseIf radUser.Checked = False And radClient.Checked = False And radItem.Checked = False Then
ErrorLabel.Text = "Error: You have not chosen a search criteria."
End If
End Sub
The table, textbox and radio button are all on the same form. I'm using Microsoft Visual Studio 2012 for Web, and trying to change the data source for a GridView object on the form. However, I keep getting the error:
表格、文本框和单选按钮都在同一个表单上。我正在使用 Microsoft Visual Studio 2012 for Web，并尝试更改表单上 GridView 对象的数据源。但是，我不断收到错误消息：
The ConnectionString property has not been initialized.
ConnectionString 属性尚未初始化。
Apologies for not giving my code earlier.
很抱歉没有早点提供我的代码。
Thank you!
谢谢！
采纳答案by Steve
Looking at your code I see a lot of unnecessary lines. I wish to give you an example of what I think you could do to simplify your code. First, remove the code not needed for the SqlDataSource (I suppose that you have already opened the connection with the database and however this could be done just one time at click entry. Second, in each switch prepare the command to be executed and the parameter (name and value) to pass for that command At the end update your SqlDataSource and rebind everything
查看您的代码，我看到很多不必要的行。我想给你一个例子，说明我认为你可以做些什么来简化你的代码。首先，删除 SqlDataSource 不需要的代码（我假设您已经打开了与数据库的连接，但是这可以在单击条目时完成一次。其次，在每个开关中准备要执行的命令和参数（名称和值）传递给该命令最后更新您的 SqlDataSource 并重新绑定所有内容
Protected Sub btnSearch_Click(sender As Object, e As ImageClickEventArgs) Handles btnSearch.Click
Dim sqlQuery As String
Dim prm as String
Dim prmValue as String
prmValue = txtSearch.Text
If radClient.Checked = True Then
sqlQuery = "SELECT * FROM Sales WHERE ClientID = @clientID"
prm = "@clientID"
ElseIf radItem.Checked = True Then
sqlQuery = "SELECT * FROM Sales WHERE ItemID = @itemID"
prm = "@itemID"
ElseIf radUser.Checked = True Then
sqlQuery = "SELECT * FROM Sales WHERE UserID = @userID"
prm = "@userID"
ElseIf radUser.Checked = False And radClient.Checked = False And radItem.Checked = False Then
ErrorLabel.Text = "Error: You have not chosen a search criteria."
return
End If
SqlDataSource3.SelectCommand = sqlQuery
SqlDataSource3.SelectParameters.Add(prm, prmValue)
SqlDataSource3.DataBind()
End Sub
回答by Muhammad Omar ElShourbagy
when using SQLDatasource, you dont have to specify Connections, it manages its own connection.
使用 SQLDatasource 时，您不必指定 Connections，它管理自己的连接。
what you need to do is:
你需要做的是：
- Update the Select Statement based on the Radio Button Status
- Call the DataSource DataBind Function
SqlDataSource3.DataBind()
- 根据单选按钮状态更新 Select 语句
- 调用数据源数据绑定函数
SqlDataSource3.DataBind()
Also, Better Approach is Using Stored Procedure that allow Null Values. better for SQL Injection (Threats for your Database)
此外，更好的方法是使用允许空值的存储过程。更适合 SQL 注入（对数据库的威胁）
SELECT * FROM Sales WHERE (@ClientID IS NULL OR ClientID = @ClientID)
AND (@ItemID IS NULL OR ItemID = @ItemID )
AND (@UserIDIS NULL OR UserID= @UserID)
回答by Saransh Bansal
HORRIBLE SOLUTIONS everywhere on internet. This is probably the simplest in the world.
互联网上到处都是可怕的解决方案。这可能是世界上最简单的了。
PrintWriter out=response.getWriter();
out.print("<html>");
out.print("<body>");
out.print("<form>");
out.print("<textarea rows='5' columns='500' name='t1'></textarea>");
//out.print("<input type='text' name='t2'>");
//out.print("c<input type='checkbox' value='c' name='t1'>");
//out.print("d<input type='checkbox' value='d' name='t1'>");
out.print("<input type='submit' value='execute' name='b1'>");
// out.print("<input type='submit' value='show' name='b1'>");
//out.print("<input type='submit' value='query' name='b1'>");
String a,c;
a=request.getParameter("t1");
//b=request.getParameter("t2");
c=request.getParameter("b1");
if(c!=null)
{
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/saransh","root","");
Statement st=con.createStatement();
if(a.startsWith("delete"))
{
out.print("<br>");
out.print("delete query execution...");
//st.executeUpdate("insert into student2(name,address) values('"+a+"',"+b+"')") ;
st.executeUpdate(a);
}
else if(a.startsWith("select"))
{ out.print("<br>");
out.print("select query execution...");
ResultSet rs=st.executeQuery(a);
ResultSetMetaData m1=rs.getMetaData();
out.print("<br>");
for(int i=1;i<=m1.getColumnCount();i++)
{String x1=m1.getColumnLabel(i);
out.print(" "+x1);}
out.print("<br>");//name of label
while(rs.next())
{ String s0=rs.getString("Stu_roll");
String s1=rs.getString("name");
String s2=rs.getString("address");
out.print(s0+" "+s1+" "+s2);
out.print("<br>");
}}
else if(a.startsWith("insert"))
{ out.print("<br>");
out.print("insert query execution...");
st.executeUpdate(a);
}
else if(a.startsWith("drop"))
{ out.print("<br>");
out.print("drop query execution...");
st.executeUpdate(a);
}
} catch (Exception e) {
out.print(e);
// TODO: handle exception
}
}
out.print("</form>");
out.print("</body>");
out.print("</html>");
remove commented lines. only problem is that queries are case sensitive. You can figure out what string operation to use to make it 'ignore case'.
删除注释行。唯一的问题是查询区分大小写。您可以找出使用什么字符串操作来使其“忽略大小写”。
