如何在SQL WHERE query中引用ASP.net中的变量

Question

这可能是一个简单的问题，但是如何在SQL查询中引用字符串变量呢？我只是在构建一个测试页面来输出我的数据，但是我尝试的任何东西都会抛出语法错误。下面是我所拥有的，但不确定如何在ASp.nET中引用它。

<%@ Page Language="VB" debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
DIM username As string = "Convert.ToString(User.Identity.Name.Substring(User.Identity.Name.IndexOf("\") + 1))"
<script language="VB" runat="server">

Sub Page_Load(sender As Object, e As EventArgs)

    Dim myConnection  As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
    Dim myDataSet     As DataSet

    Dim strSQL        As String
    Dim iRecordCount  As Integer

        myConnection = New MySqlConnection("server=localhost; user id=Directory_Admin; password=IMCisgreat2014; database=imc_directory_tool; pooling=false;")

        strSQL = "SELECT username FROM tbl_staff WHERE username  = "


    myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
    myDataSet = New Dataset()
    myDataAdapter.Fill(myDataSet, "tbl_staff")

    MySQLDataGrid.DataSource = myDataSet
    MySQLDataGrid.DataBind()

End Sub

</script>

<html>
<head>
<title>Simple MySQL Database Query</title>
</head>
<body>

<form runat="server">

<asp:DataGrid id="MySQLDataGrid" runat="server" />

</form>

</body>
</html> 

更新代码：

<%@ Page Language="VB" debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
        Dim username As String = Request.ServerVariables("LOGON_USER")
    Dim myConnection  As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
    Dim myDataSet     As DataSet
        Dim strSQL As String
    Dim iRecordCount  As Integer

        myConnection = New MySqlConnection("server=localhost; user id=Directory_Admin; password=IMCisgreat2014; database=imc_directory_tool; pooling=false;")

        strSQL = "SELECT username FROM tbl_staff WHERE username = '" & username & "'"

    myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
    myDataSet = New Dataset()
    myDataAdapter.Fill(myDataSet, "tbl_staff")

    MySQLDataGrid.DataSource = myDataSet
    MySQLDataGrid.DataBind()

End Sub

</script>

<html>
<head>
<title>Simple MySQL Database Query</title>
</head>
<body>

<form runat="server">

<asp:DataGrid id="MySQLDataGrid" runat="server" />

</form>

</body>
</html> 

Answer 1

以下是解决我的问题的最终代码：

@ Page Language="VB" debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="VB" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
    Dim username As String = Convert.ToString(User.Identity.Name.Substring(User.Identity.Name.IndexOf("\") + 1))
    Dim myConnection  As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
    Dim myDataSet     As DataSet
    Dim strSQL As String
    Dim iRecordCount  As Integer

        myConnection = New MySqlConnection("server=localhost; user id=Directory_Admin; password=IMCisgreat2014; database=imc_directory_tool; pooling=false;")

        strSQL = "SELECT username FROM tbl_staff WHERE username = '" & username & "'"

    myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
    myDataSet = New Dataset()
    myDataAdapter.Fill(myDataSet, "tbl_staff")

    MySQLDataGrid.DataSource = myDataSet
    MySQLDataGrid.DataBind()

End Sub

</script>

<html>
<head>
<title>Simple MySQL Database Query</title>
</head>
<body>

<form runat="server">

<asp:DataGrid id="MySQLDataGrid" runat="server" />

</form>

</body>
</html>