CODEWITHSUNDEEP
asp.netsql-servervb.netvisual-studiovisual-studio-2010
Dobriyal Sumit
Dobriyal Sumit

Reputation: 31

What's wrong with my Count Query asp.net

Public state_name as String
state_name = Textbox1.Text

Dim constr As String = ConfigurationManager.ConnectionStrings("ApplicationServices").ConnectionString
Dim query As String = "SELECT Count(cities) FROM state_table WHERE state_name=" & state_name
Using conn As New SqlConnection(constr)
    Using comm As New SqlCommand()
        conn.Open()
        With comm
            .Connection = conn
            .CommandText = query
            .CommandType = CommandType.Text
        End With

        Dim count As Int16 = Convert.ToInt16(comm.ExecuteScalar())
        Label1.Text = count
    End Using
End Using

The code shows an error

Invalid column name 'California'.

But California is already present in my State table, I want to count all the cities comes under state_name= california which I have entered in my State table.

I want the output as

California (3)

Upvotes: 0

Views: 51

Answers (2)

Win
Win

Reputation: 62290

You want to use Parameterized Query to avoid SQL Injection.

Dim constr As String = ConfigurationManager.ConnectionStrings("ApplicationServices").ConnectionString
Dim query As String = "SELECT Count(cities) FROM state_table WHERE state_name=@State_Name"
Using conn As New SqlConnection(constr)
   Using comm As New SqlCommand()
      conn.Open()
      With comm
         .Connection = conn
         .CommandText = query
         .CommandType = CommandType.Text
         .Parameters.AddWithValue("@State_Name", state_name)
      End With
      Dim count As Int16 = Convert.ToInt16(comm.ExecuteScalar())
      Label1.Text = count
   End Using
End Using

Upvotes: 2

Andy Wiesendanger
Andy Wiesendanger

Reputation: 643

Because you didn't surround your variable with quotes. "state_name = '" + state_name + "'"

But, you should use a parameter instead.

Upvotes: 1

Related Questions