SqlClient command error: "invalid column name 'A'"

Question

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
    Dim SQLData As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
    Dim cmdSelect As New System.Data.SqlClient.SqlCommand("SELECT * FROM a1_bustype where bustype=" & DropDownList1.SelectedItem.Text.ToString, SQLData)
    SQLData.Open()
    Dim dtrReader As System.Data.SqlClient.SqlDataReader = cmdSelect.ExecuteReader()
    If dtrReader.HasRows Then
        While dtrReader.Read()
            TextBox1.Text = dtrReader("buscode")
        End While
    End If

    dtrReader.Close()
    SQLData.Close()
End Sub

I have following entry in database tabel ...in bustype column i have ..A/c seater volvo and bus code is S41

when i run the webpage it shows the following error invalid column name 'A'

x2. · Accepted Answer · 2010-12-07 07:09:33Z

1

If bustype is text or varchar you have to add single quotes to your query

UPD: I don't realy sure how it'l be in vb, something like this:

"SELECT * FROM a1_bustype where bustype='" & DropDownList1.SelectedItem.Text.ToString & "'"

answered Dec 7, 2010 at 7:09

x2.

9,6586 gold badges43 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hans Olsson · Accepted Answer · 2010-12-07 07:32:56Z

As X2 said in his answer, you need to surround your string with single quotes, so change the line that starts Dim cmdSelect... to be:

Dim cmdSelect As New System.Data.SqlClient.SqlCommand(String.Format("SELECT * FROM a1_bustype where bustype='{0}'", DropDownList1.SelectedItem.Text.Replace("'", "''")), SQLData)

This will also make it support if the values in the DropDownList could contain single quotes themselves.

However, you don't close down things properly in your code in case of errors, I'd suggest changing it to something like:

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
    Using SQLData As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True")
        Using cmdSelect As New System.Data.SqlClient.SqlCommand(String.Format("SELECT * FROM a1_bustype where bustype='{0}'", DropDownList1.SelectedItem.Text.Replace("'", "''")), SQLData)
            SQLData.Open()
            Using dtrReader As System.Data.SqlClient.SqlDataReader = cmdSelect.ExecuteReader()
                If dtrReader.HasRows Then
                    While dtrReader.Read()
                        TextBox1.Text = dtrReader("buscode")
                    End While
                End If
            End Using
        End Using
    End Using
End Sub

The Using / End Using statements will make sure that the resources gets disposed of correctly even if an exception is raised somewhere.

Collectives™ on Stack Overflow

SqlClient command error: "invalid column name 'A'"

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related