CODEWITHSUNDEEP
sqlsql-servervb.netsql-server-2008sqldataadapter
KRob
KRob

Reputation: 80

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

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. enter image description here

Upvotes: 0

Views: 802

Answers (2)

Linus Caldwell
Linus Caldwell

Reputation: 11058

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

Upvotes: 3

joe
joe

Reputation: 556

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

Upvotes: 0

Related Questions