VB：无效的列名称？ [英] VB: Invalid column name?

问题描述

我想实现在VB Web应用程序我有一个搜索功能，它抛出一个错误，我不明白为什么它给我的提示错误。

I am trying to implement a search function on a VB web app I have, and its throwing an error and I can't see why its giving me the error its suggesting.

我按照本指南实施网格视图搜索。

I have followed this guide to implement a search on the grid view.

http://www.devasp.net/net/articles/display/ 1425.html

我得到的错误是：异常详细信息：System.Data.SqlClient.SqlException：无效的列名称Sfdc_Account_Owner_ID

the error I get is: Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'Sfdc_Account_Owner_ID'

指向该线（32）

 Line 30:         Dim da As New SqlDataAdapter(cmd)
 Line 31:         Dim ds As New DataSet()
 Line 32:         da.Fill(ds)
 Line 33:         conn.Close()
 Line 34:         Return ds

下面是code背后：

Imports System.Data.SqlClient
Imports System.Data

Public Class WebForm1
Inherits System.Web.UI.Page

Dim cbSelect As Object

Private Property contacted As Object

Private Function CustomersData() As DataSet
    Dim text As String = "SELECT ID, contact,Sales_followUp, case_closed,week,year,
     [account_id], [product_name], [begin_date], [begin_request_date],
     [billing_country_code], [region], [billing_zip], [clear_name], 
     [clear_lower_email], [Territory], [Market Segment] AS column1, [AR Period] AS
     AR_Period, [Revenue Type] AS Revenue_Type, [Service Group] AS Service_Group, 
     [Sfdc Customer Id] AS Sfdc_Customer_Id, 
     [Sfdc Account Owner ID] AS Sfdc_Account_Owner_ID,  
     [Comments] FROM [BusinessSignups]"

    Dim connString As String = "Data Source=xxxxxxxx;Initial Catalog=Support_Metrics;Persist Security Info=True;User ID=xxxxxxxxxx;Password=xxxxxxxx"
    Dim conn As New SqlConnection(connString)
    Dim cmd As New SqlCommand(text, conn)
    conn.Open()
    Dim da As New SqlDataAdapter(cmd)
    Dim ds As New DataSet()
    da.Fill(ds)
    conn.Close()
    Return ds
End Function

Private Function CustomersDataWithSearchText(ByVal field As String, ByVal searchText As String) As DataSet
    Dim text As String = "SELECT ID, [Territory],  [Sfdc Account Owner ID] AS Sfdc_Account_Owner_ID FROM [BusinessSignups] WHERE " & field & " LIKE '%" & searchText & "%'"
    Dim connString As String = "Data Source=xxxxxxxx;Initial Catalog=Support_Metrics;Persist Security Info=True;User ID=xxxxxxxxxx;Password=xxxxxxxx"
    Dim conn As New SqlConnection(connString)
    Dim cmd As New SqlCommand(text, conn)
    conn.Open()
    Dim da As New SqlDataAdapter(cmd)
    Dim ds As New DataSet()
    da.Fill(ds)
    conn.Close()
    Return ds
End Function


Public Sub checkbox_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs)
    'Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) 'Handles Button3.Click
    For Each gvRow As GridViewRow In GridView1.Rows 'itterate tru all rows
        Dim chkBox As CheckBox = CType(gvRow.FindControl("cbSelect"), CheckBox) 'find the checkBox inside GridView
        Dim chkBox1 As CheckBox = CType(gvRow.FindControl("Sales"), CheckBox) 'find the checkBox inside GridView
        Dim chkBox2 As CheckBox = CType(gvRow.FindControl("Case"), CheckBox) 'find the checkBox inside GridView

        Dim sqlcon As New SqlConnection("Data Source=xxxxxxxx;Initial Catalog=Support_Metrics;Persist Security Info=True;User ID=xxxxxxxxxx;Password=xxxxxxxx")
        Dim sqlComm As New SqlCommand("update BusinessSignups set contact=@cbSelect, comments=@comments, Sales_followUp=@sales, Case_Closed=@Case where ID=@ID", sqlcon) 'this is an insert example, you can do update you can get the current gridView row id using gvRow.Cells(0).Text
        'Dim sqlComm As New SqlCommand("insert into BusinessSignups (contacted) values (@cbSelect)", sqlcon) 'this is an insert example, you can do update you can get the current gridView row id using gvRow.Cells(0).Text
        sqlComm.Parameters.AddWithValue("@comments", gvRow.Cells(10).Text)
        sqlComm.Parameters.AddWithValue("@cbSelect", chkBox.Checked)
        sqlComm.Parameters.AddWithValue("@Sales", chkBox1.Checked) 'passing the @cbSelect parameter to the command
        sqlComm.Parameters.AddWithValue("@Case", chkBox2.Checked)
        sqlComm.Parameters.AddWithValue("@ID", gvRow.Cells(0).Text)
        Using (sqlcon)
            sqlcon.Open() 'open connection
            sqlComm.ExecuteNonQuery() 'execute the command
        End Using
    Next
End Sub

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    If Not IsPostBack Then
        GridView1.DataSourceID = ""
        GridView1.DataSource = CustomersData()
        GridView1.DataBind()
    Else
        If ddlSearch.SelectedItem.Text = "Sfdc_Account_Owner_ID" Then
            lblName.Text = "Enter Sfdc_Account_Owner_ID:"
            lblMessage.Text = ""
        Else
            lblName.Text = "Enter Territory:"
            lblMessage.Text = ""
        End If
    End If
End Sub

Protected Sub btnSearch_Click(sender As Object, e As System.EventArgs) Handles btnSearch.Click
    If txtSearch.Text = "" Then
        lblMessage.Text = "Enter " & ddlSearch.SelectedItem.Text & " to search"
        lblMessage.ForeColor = Drawing.Color.Red
        GridView1.DataSourceID = ""
        GridView1.DataSource = CustomersData()
        GridView1.DataBind()
    Else
        lblMessage.Text = ""
        GridView1.DataSourceID = ""
        GridView1.DataSource = CustomersDataWithSearchText(ddlSearch.SelectedItem.Text, txtSearch.Text)
        GridView1.DataBind()
    End If
End Sub

End Class

有的在ASPX水平code的

Some of the code at the ASPX level

   <div>
    <asp:Label ID="lblSearch" runat="server" Text="I want to Search"></asp:Label>
       <asp:DropDownList ID="ddlSearch" runat="server" AutoPostBack="true">
       <asp:ListItem>Sfdc_Account_Owner_ID</asp:ListItem>
       <asp:ListItem>Territory</asp:ListItem>
       </asp:DropDownList>

        <asp:Label ID="lblName" runat="server" Text="Enter Contact Name"></asp:Label>
        <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
        <asp:Button ID="btnSearch" runat="server" Text="Search" />
        <asp:Label ID="lblMessage" runat="server"></asp:Label>

  </div>

任何帮助将是AP preciated。

Any help would be appreciated.

推荐答案

检查列名Sfdc_Account_Owner_ID在你的SQL Server表BusinessSignups，无论是列名不存在，或者它是不同的。

Check the column name Sfdc_Account_Owner_ID in your sql server table BusinessSignups, either the column name doesn't exist or it is different.

这篇关于VB：无效的列名称？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！