下拉列表的SQL字符串调整

Question

我将用下面的例子来说明。在这里我有两个下拉列表(ddlInsertEmployee + ddlInsertCustomer)，这两个都应该被绑定到从表[雇员]中获取数据。[雇员id]和[客户。并将其插入到表[Task]中。

问题是它插入了来自Employee的数据。全名和客户。全名表示尊重。员工。EmployeeID和Customer.CustomerID。如果我将下拉列表更改为文本框，并手动插入id，它就像一个魅力，但这不是很有效，我希望能够看到整个Fullname。

我不知道如何改变我的Sqlstring以使此工作正确。我希望这能说得通。任何帮助非常感激!

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.DropDownList

   Protected Sub btnSubmit_Click(sender As Object, e As System.EventArgs) Handles btnSubmit.Click
        Dim MyConn As OleDbConnection
        Dim cmd As OleDbCommand
        Dim Sqlstring, TaskDesc, TaskSolved, Employee, Customer, DateIn, DateOut, TaskHours As String
        TaskDesc = txtTaskDesc.Text
        TaskSolved = txtTaskSolved.Text
        Employee = ddlInsertEmployee.Text
        Customer = ddlInsertCustomer.Text
        DateIn = txtDateReg.Text
        DateOut = txtDateSolved.Text
        TaskHours = txtHours.Text
        MyConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|DATABASE.accdb;")
        MyConn.Open()
        Sqlstring = "INSERT INTO Task (TaskDesc, TaskSolved, EmployeeID, CustomerID, DateIn, DateOut, TaskHours) VALUES ('" + TaskDesc + "', '" + TaskSolved + "', '" + Employee + "', '" + Customer + "', '" + DateIn + "', '" + DateOut + "', '" + TaskHours + "')"
        cmd = New OleDbCommand(Sqlstring, MyConn)
        cmd.ExecuteNonQuery()
        MyConn.Close()
        lblMessage.Text = "Task Added Successfully !!!"
    End Sub
End Class

--------
 Employee 
    <asp:DropDownList ID="ddlInsertEmployee" runat="server" AutoPostBack="True" 
        DataSourceID="SqlDataSource5" DataTextField="EmployeeFullName" 
        DataValueField="EmployeeFullName">
    </asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource5" runat="server" 
        ConnectionString="<%$ ConnectionStrings:conntoDB %>" 
        ProviderName="<%$ ConnectionStrings:conntoDB.ProviderName %>" 
        SelectCommand="SELECT EmployeeFullName, EmployeeID FROM Employee">
    </asp:SqlDataSource>
    &nbsp;&nbsp;&nbsp;
    <br />
    <br />
    Customer&nbsp;<asp:DropDownList ID="ddlInsertCustomer" runat="server" 
        AutoPostBack="True" DataSourceID="SqlDataSource6" 
        DataTextField="CustomerFullName" DataValueField="CustomerFullName">
    </asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource6" runat="server" 
        ConnectionString="<%$ ConnectionStrings:conntoDB %>" 
        ProviderName="<%$ ConnectionStrings:conntoDB.ProviderName %>" 
        SelectCommand="SELECT CustomerID, CustomerFullName FROM Customer">
    </asp:SqlDataSource>

我收到错误:

Exception Details: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.
Source Error:
Line 60:         Sqlstring = "INSERT INTO Task (TaskDesc, TaskSolved, EmployeeID, CustomerID, DateIn, DateOut, TaskHours) VALUES ('" + TaskDesc + "', '" + TaskSolved + "', '" + Employee + "', '" + Customer + "', '" + DateIn + "', '" + DateOut + "', '" + TaskHours + "')"
Line 61:         cmd = New OleDbCommand(Sqlstring, MyConn)
[B]Line 62:         cmd.ExecuteNonQuery() Line 63:         MyConn.Close()[/B]
Line 64:         lblMessage.Text = "Task Added Successfully !!!"

Source File: C:ProjektNewTask.aspx.vb    Line: 62

Answer 1

尝试将ddlInsertCustomer的DataValueField设置为CustomerID(对于您的员工下拉列表也类似)。然后可以使用ddl.SelectedValue属性保存到数据库，但是用户仍然会看到name属性。

另外，将文本框中的文本插入到SQL查询中是一个非常糟糕的主意，因为它打开了SQL注入。使用SQL参数是一个非常好的主意。