使用 SQLDataAdapter 查询动态列名

发布于 2024-10-17 09:30:06 字数 939 浏览 8 评论 0原文

我正在尝试根据查询字符串动态更改 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：无效的列名'@Category'。

我想是因为这不是执行此操作的正确方法。我该怎么做？我可以通过查询字符串获取列名称。我想将该列名称传递到查询中并将其识别为列。

原文

I am trying to dynamically change a query in a SQLDataAdapter based off the querystring. I tried passing it in as a parameter, but that doesn't work. Any other suggestions?

Right now I have:

    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()

But get the error: System.Data.SqlClient.SqlException: Invalid column name '@Category'.

I guess because this isn't the correct way to do this. How can I do this? I can get the column name via query string. I want to pass in that column name into the query and have it be recognized as a column.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

染年凉城似染瑾 2024-10-24 09:30:06

好吧，我想通了，这是比尝试动态添加参数更容易的方法，因为初始 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

Well I figured this out, and it's a much easier method than trying to dynamically add parameters, since the initial SelectSQL is just a string, I dynamically constructed that string wit the following function:

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

回复收藏 0 原文

~没有更多了~