使用SQLDataAdapter查询动态列名称
问题描述:
我想动态更改基于查询字符串的SQLDataAdapter中的查询。我尝试将它作为参数传递,但这不起作用。还有其他建议吗?使用SQLDataAdapter查询动态列名称
现在我有:
Dim conn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(WebConfigurationManager.ConnectionStrings("MyConnection").ToString())
Dim ad As New SqlDataAdapter
Dim selectSQL As String = "SELECT BookTitle, Publisher, Location, CallNumber, [Extra_Description] AS Description, [ISBN] FROM [New_Book_Table] WHERE [Export_to_list]='True' AND [@Category]='True'"
Dim selectCMD As SqlCommand = New SqlCommand(selectSQL, conn)
ad.SelectCommand = selectCMD
selectCMD.Parameters.Add("@Category", SqlDbType.NVarChar, 30).Value = getCategoryString()
Dim ds As New DataSet
ad.Fill(ds)
ListView1.DataSource = ds
ListView1.DataBind()
但得到的错误:System.Data.SqlClient.SqlException: Invalid column name '@Category'.
我猜是因为这是不是这样做的正确方法。我怎样才能做到这一点?我可以通过查询字符串获得列名。我想将该列名称传入查询并将其识别为列。
答
好吧,我想通了这一点,这是一个比试图动态添加参数,由于初始SelectSQL只是一个字符串更容易的方法,我动态构造该字符串机智以下功能:
Protected Function getSQLQuery() As String
Dim baseQuery As String = "SELECT BookTitle, Publisher, Location, CallNumber, [Extra_Description] AS Description, [ISBN] FROM [New_Book_Table] WHERE [Export_to_list]='True'"
Dim category As String = getCategoryString()
Return baseQuery & "AND [" & category & "]='True'"
End Function