What is causing this System.Data.SqlClient.SqlException Error. Issue with my SqlDataAdapter

Question

I am not sure why I am getting this error. The description of my problem is below the code. I have excluded some of the Select Cases and fields in the below code to keep it as short as possible.

 Private Sub SetClassGrid(ByVal ClassCategory As String)
    Dim connectionString As String = WebConfigurationManager.ConnectionStrings("theDataBase").ConnectionString
    Dim con As New SqlConnection(connectionString)
    Dim sqlCommand As StringBuilder = New StringBuilder()

    sqlCommand.Append("SELECT Name, City, State FROM Classes$ WHERE (ClassesCategoryID = ")

    Select Case ClassCategory
        Case "Art"
            sqlCommand.Append("1)")
        Case "Drama"
            sqlCommand.Append("2)")
        Case "Aquarium"
             sqlCommand.Append("2)")         
    End Select

    Dim cmd As String = sqlCommand.ToString()
    Dim da As New SqlDataAdapter(cmd, con)
    Dim ds As New DataSet()

    Try
        con.Open()
        da.Fill(ds, "Classes$")

    Finally
        con.Close()
    End Try

    GridView1.DataSource = ds.Tables("Classes$")
    GridView1.DataBind()


End Sub

For the Select Case- when ClassCategory = "Art" it works fine; however when ClassCategory equals anything else, I get an error.

Also for the Select Case- if the case is "Art" and if I change sqlCommand from ="1)" to ="2)" it works as intended.

So the issues are that the above code only works for the first Case.

I am getting this exception: System.Data.SqlClient.SqlException: Incorrect syntax near '='. At da.Fill(ds, "Classes$") — KRob
– KRob, Commented Apr 20, 2013 at 22:37
What does the debugger show? What's the value of sqlCommand when the error occurs? — Linus Caldwell
– Linus Caldwell, Commented Apr 20, 2013 at 22:38

Linus Caldwell · Accepted Answer · 2013-04-20 22:36:34Z

3

What does the debugger show? What's the value of sqlCommand when the error occurs?

Just to make sure the query is valid anyway, add a default behavior for your select case:

Select Case ClassCategory
    Case "Art"
        sqlCommand.Append("1)")
    Case "Drama"
        sqlCommand.Append("2)")
    Case "Aquarium"
        sqlCommand.Append("2)")
    Case Else
        sqlCommand.Append("-1)")
End Select

Even better, parameterize your query:

SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand command = new SqlCommand("SELECT Name, City, State FROM Classes$ WHERE " + 
                                    "ClassesCategoryID = @Id", connection);

// Add the parameters for the SelectCommand.
command.Parameters.Add("@Id", SqlDbType.Int);

adapter.SelectCommand = command;

And fill the parameter in your select case:

Select Case ClassCategory
    Case "Art"
        command.Parameters["@Id"].Value = 1;
    Case "Drama"
        command.Parameters["@Id"].Value = 2;
    Case "Aquarium"
        command.Parameters["@Id"].Value = 2;
    Case Else
        command.Parameters["@Id"].Value = -1;
End Select

edited Apr 20, 2013 at 22:36

answered Apr 20, 2013 at 22:16

Linus Caldwell

11.1k12 gold badges48 silver badges61 bronze badges

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

5 Comments

KRob Over a year ago

Linus, the ID is added in the Select Case with the string builder variable 'sqlCommand'

Linus Caldwell Over a year ago

But there is no default behavior in case ClassCategory does not match any predefined Case.

KRob Over a year ago

I have Case Else, I just excluded here because ClassCategory always executes the correct case, I just have an error with the sql somewhere. The query executes correctly on Case "Art", but nothing else. I changed sqlCommand = "2)" for Case "Art", and the query returned the correct data for the ClassCategoryID of 2 -which is "Drama."

Linus Caldwell Over a year ago

Sure you postet the original code? Because I can't see an error. Just one thing: You do not need the brackets.

KRob Over a year ago

Thank you, it worked! As you can see, I was using command as a string. The reason for this was because, when I declared da As New SqlDataAdapter(), I was passing it the arguments 'command' and 'con.' Doing so- da had an error. Then from your code, I realized that I should be passing con to command, not da.

joe · Accepted Answer · 2013-04-20 22:17:14Z

0

consider adding break; ? try to go step by step, and right before your code executes the query, try to copy the query sql and execute it in the server manually, it looks like the query is messed up

answered Apr 20, 2013 at 22:17

joe

5561 gold badge6 silver badges20 bronze badges

3 Comments

KRob Over a year ago

VB Select Cases don't have breaks (that I am aware of). Yea, the query being messed up was my first thought. I have stepped through the code line by line, and the select command is as it should be, so I am lost from here.

joe Over a year ago

Right, i appologize...i was reading VB but in my head it was a switch statement. the last thing i sugest is to check your connection string !

KRob Over a year ago

It was a connection issue, but not the string. I was passing the connection string to the SqlDataAdapter, opposed to my SqlCommand. Thank you for your responses.

Collectives™ on Stack Overflow

What is causing this System.Data.SqlClient.SqlException Error. Issue with my SqlDataAdapter

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related