[RESOLVED] 'System.Data.SqlClient.SqlException'

[Golfreak]

hey guys

just wondering if anyone can help. I'm getting this error:

An unhandled exception of type [B]System.Data.SqlClient.SqlException [/B] occurred in system.data.dll
 
Additional information: System error.

and it occurs in this Sub: (in red)

Public Sub UpdateCombo()
 
        Dim RSAllCategories As SqlClient.SqlDataReader
 
        SqlUpdateCommand1.CommandText = "Exec ProblemCategoryList"
        [COLOR=Red]RSAllCategories = SqlUpdateCommand1.ExecuteReader()[/COLOR]
       
        Do Until RSAllCategories.IsClosed
 
            cmbSelect.Items.Add(RSAllCategories.GetValue("ProblemCategory").Value)
            RSAllCategories.NextResult()
 
        Loop
 
    End Sub

whats wrong with the code?

[Aspnot]

Which line is giving you the error? Does ProblemCategoryList really exist in the DB? Does it require a parameter and you aren't passing it?

One thing I do when I get that cryptic message is start up Profiler and watch to see exactly what the application is passing to SQL Server. That usually gives a good idea of what is happening. If I can't figure it all out from there, I copy the necessary lines from Profiler into Query Analyzer to test it from there. QA seems to give a lot better information about errors.

[mar_zim]

Try to remove the 'Exec' in your commandtext

SqlUpdateCommand1.CommandText = "ProblemCategoryList"

and be sure on what commandtype is that.

SqlUpdateCommand1.CommandType = CommandType.StoredProcedure

[Golfreak]

Thanks guys for reponding to my problem. Yesterday right after posting I had to run.

To answer ASPNOT, yes ProblemCategoryList is a stored proceedure in the DB and as for mar zim, I tried your suggestion and came up with the same error.

An unhandled exception of type System.Data.SqlClient.SqlException  occurred in system.data.dll
 
Additional information: System error.

My code now looks like this (error in RED):

Public Sub UpdateCombo()
 
        Dim RSAllCategories As SqlClient.SqlDataReader
 
        SqlUpdateCommand1.CommandText = "ProblemCategoryList"
        SqlUpdateCommand1.CommandType = CommandType.StoredProcedure
        [COLOR=Red]RSAllCategories = SqlUpdateCommand1.ExecuteReader()[/COLOR]
 
       Do Until RSAllCategories.IsClosed
 
            cmbSelect.Items.Add(RSAllCategories.GetValue("ProblemCategory").Value)
            RSAllCategories.NextResult()
 
        Loop
 
    End Sub

Any Suggestions?????

[Aspnot]

Just to make sure, you are creating a connection object and associating it with the command object, right? Does the Stored Procedure require any parameters to be passed in?

[Golfreak]

I am not sure I completely follow. I am kinda new to this field. Here is the stored proceedure:

CREATE PROCEDURE ProblemCategoryList

AS
BEGIN
SELECT * FROM ProblemCategories

ORDER BY ProblemCategory

END
GO

[techgnome]

Do this, it'll tell you exactly what is wrong:

Try
  RSAllCategories = SqlUpdateCommand1.ExecuteReader()
Catch sqlex as SqlClient.SQLException
  MessageBox.Show sqlex.ToString
Catch ex as SystemException '<-- I think this is right
  MessageBox.Show ex.ToString
End Try

-tg

[Golfreak]

Thanks techgnome

I tried it out and got this error:

System.Data.SqlClient.SqlException: Proceedure ProblemCategoryList has no parameters and arguments were supplied.
  at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
  atSystem.Data.SqlClient.SqlCommand.ExecuteReader()
  at HD1.frmProblemCategories.UpdateCombo() vb:line231

[Golfreak]

What the heck does that mean???

[Asgorath]

Try this

Public Sub UpdateCombo()
 
        Dim RSAllCategories As SqlClient.SqlDataReader
 
        SqlUpdateCommand1.CommandText = "ProblemCategoryList"
        SqlUpdateCommand1.CommandType = CommandType.StoredProcedure
        SqlUpdateCommand1.Parameters.Clear
        RSAllCategories = SqlUpdateCommand1.ExecuteReader()
 
       Do Until RSAllCategories.IsClosed
 
            cmbSelect.Items.Add(RSAllCategories.GetValue("ProblemCategory").Value)
            RSAllCategories.NextResult()
 
        Loop
 
    End Sub

Regards
Jorge

[Golfreak]

Hey Asgorath

Thanks for the input

Unfortunately, my code is full of errors here is my next one:

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll
 
Additional information: Cast from string "ProblemCategory" to type 'Integer' is not valid.

And it hits on the line that is Bolded:

Public Sub UpdateCombo()
 
        Dim RSAllCategories As SqlClient.SqlDataReader
 
        SqlUpdateCommand1.CommandText = "ProblemCategoryList"
        SqlUpdateCommand1.CommandType = CommandType.StoredProcedure
        SqlUpdateCommand1.Parameters.Clear()
        RSAllCategories = SqlUpdateCommand1.ExecuteReader()
        cmbSelect.Items.Clear()
 
        Do Until RSAllCategories.IsClosed
 
           [B] cmbSelect.Items.Add(RSAllCategories.GetValue("ProblemCategory").Value)[/B]            
              RSAllCategories.NextResult()
 
        Loop
 
    End Sub

[Golfreak]

I have this same problem on some of my other forms.

I think if I can get through this one, I will be close to making the whole thing work.

Anybody have a suggestion?

[Asgorath]

I don't know how may columns your problemcaterogies table has but i am assuming your only interested in the 1st collumn.

Public Sub UpdateCombo()
 
        Dim RSAllCategories As SqlClient.SqlDataReader
 
        SqlUpdateCommand1.CommandText = "ProblemCategoryList"
        SqlUpdateCommand1.CommandType = CommandType.StoredProcedure
        SqlUpdateCommand1.Parameters.Clear()
        RSAllCategories = SqlUpdateCommand1.ExecuteReader()
        cmbSelect.Items.Clear()
 
        Do Until RSAllCategories.IsClosed
 
            cmbSelect.Items.Add(RSAllCategories.GetValue(0).Value)            
              RSAllCategories.NextResult()
 
        Loop
 
    End Sub

Regards
Jorge

[Asgorath]

as a addenda
RSAllCategories.GetValue(0).Value is equivalent to RSAllCategories(0)

Regards
Jorge

[Golfreak]

thanks again
now theres a new error:

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
 
Additional information: Invalid attempt to read when no data is present.

heres the code with error BOLDED:

Do Until RSAllCategories.IsClosed
 
            [B]cmbSelect.Items.Add(RSAllCategories(0))[/B]
            RSAllCategories.NextResult()
 
        Loop
 
    End Sub

Does that mean it can't read the DB?

[Golfreak]

Like I said, I'm new to the programming world and especially to VB.net

I am beginning to think I built the whole thing incorrectly

[Asgorath]

Strange run your stored procedure in query analyser to see if it returns any row.

Regards
Jorge

[techgnome]

I wondered about that.... the loop is bad....

RSAllCategories.IsClosed - the connection is still open, so this will continue to run, even after you run out of values.

Do While RSAllCategories.Read
            cmbSelect.Items.Add(RSAllCategories(0))
        Loop

.Read will return true as long as there is something in the reader to read. It also has the added bonus of moving to the next record automatically if there is one.

-tg

[Golfreak]

Thanks Techgnome

That solved a ton of my problems.

I have more but I think I can get it from here.

If not then I'll be back.

[Golfreak]

Oooooooh NOOOOOOO!!!!

I spoke too soon! Now I'm getting the error in a different sub on the same form.

The error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
 
Additional information: System error.

is back.

The Code:

Public Sub cmdAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click
 
        If txtNew.Text <> "" Then
 
           SqlInsertCommand1.CommandText = "ProblemCategoryAdd '" & txtNew.Text & "'"
            SqlInsertCommand1.CommandType = CommandType.StoredProcedure
            SqlInsertCommand1.Parameters.Clear()
            [B]SqlInsertCommand1.ExecuteReader()[/B]
            txtNew.Text = ""
            txtNew.Select()
        End If
 
        UpdateCombo()
 
End Sub

is different.

What did I screw up now???

[techgnome]

Sigh....
Ok.... time for some ADO.NET 100.

There are several ways to execute SQL statements.
.ExecuteNonQuery - this is for when you want to do something like a Delete or an Update or even an Insert, or any statement where no data will be returned to the application.
.ExecuteReader - this does return records, but put it into an efficient forward only, read only stream. One record at a time.
.Execute - standard, normal execute method. Returns a recordset (datatable) that can then be manipulated.

So, based on that, can you determine what method you should be using to execute your statement there?

-----
Next.
I'm going to tell you right now, this is the worst way to execute stored procedures:
SqlInsertCommand1.CommandText = "ProblemCategoryAdd '" & txtNew.Text & "'"

It opens you to what is known as SQL Injection, where someone can manipulate your database by modifying what's in the text box.

SqlInsertCommand1.CommandText = "ProblemCategoryAdd"
SqlInsertCommand1.CommandType = CommandType.StoredProcedure
SqlInsertCommand1.Parameters.Clear()
SqlInsertCommand1.Parameters.Add (new SqlClient.SqlParameter("@MyParam", SqlDbType.VarChar, 50)
SqlInsertCommand1.Parameters("@MyParam").Value = txtNew.Text

-tg

[Golfreak]

I once again thank you techgnome for your help.

Now my form is almost complete (or should I say your form )

I can add a name to my DB w/ the form, but I can't delete it

I am getting the same Error message in my cmdDelete Sub

This is my code:

Public Sub cmdDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
 
        If cmbSelect.SelectedIndex <> -1 Then
 
            SqlDeleteCommand1.CommandText = "ProblemCategoryDelete" & cmbSelect.Text & "'"
            SqlDeleteCommand1.CommandType = CommandType.StoredProcedure
            SqlDeleteCommand1.Parameters.Clear()
            SqlDeleteCommand1.ExecuteNonQuery()
            cmbSelect.Select()
 
        End If
 
        UpdateCombo()
 
    End Sub

I have been playing with the code for about an hour or so and I know its close but its still not right

Any suggestions??

[Golfreak]

Anyone????

[techgnome]

You're missing a space after your stored proc name:
"ProblemCategoryDelete" & cmbSelect.Text & "'"

-tg

[Golfreak]

Actually the error

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
 
Additional information: System error.

is bolded:

Public Sub cmdDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
 
        If cmbSelect.SelectedIndex <> -1 Then
 
            SqlDeleteCommand1.CommandText = "ProblemCategoryDelete" & cmbSelect.Text & "'"
            SqlDeleteCommand1.CommandType = CommandType.StoredProcedure
            SqlDeleteCommand1.Parameters.Clear()
            [B]SqlDeleteCommand1.ExecuteNonQuery()[/B]
            cmbSelect.Select()
 
        End If
 
        UpdateCombo()
 
    End Sub

[techgnome]

You're missing a space after your stored proc name:
"ProblemCategoryDelete" & cmbSelect.Text & "'"

-tg

The answer is bolded.

-tg

[Golfreak]

I am not sure what you mean.........

Are you saying make it look like this?

SqlDeleteCommand1.CommandText = "ProblemCategoryDelete " & cmbSelect.Text & "'"

[Golfreak]

Because I tried that and got the same error message

[Golfreak]

When I run the program it iniciates the form.

I can add a name to the DB, but when I try to delete a name from the drop down, it throughs the error.

I am open for suggestions from anyone

[techgnome]

Yes, that's exactly what I meant. The fact that you still get an error though means there is something else wrong.

Try wrapping your executenonquery into a Try...Catch block and trap for the sql error and display it.

-tg

[Asgorath]

You need to pass the apropriate parameter of your stored procedure ProblemCategoryDelete ...

Public Sub cmdDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
 
        If cmbSelect.SelectedIndex <> -1 Then
 
            SqlDeleteCommand1.CommandText = "ProblemCategoryDelete"  
            SqlDeleteCommand1.CommandType = CommandType.StoredProcedure
            SqlDeleteCommand1.Parameters.Clear()
SqlDeleteCommand1.Parameters.Add("@YourStoredProcedureParameterPassed",SqlDbType.VarChar).Value = cmbSelect.Text
            SqlDeleteCommand1.ExecuteNonQuery()
            cmbSelect.Select()
 
        End If
 
        UpdateCombo()
 
    End Sub

Regards
Jorge

[Golfreak]

Thanks again tg. I'll give it a try

[Golfreak]

thanks Asgorath, but would you use SqlDeleteCommand1.Parameters.Add() in that situation?

[Asgorath]

yes you have to add the parameter your stored procedure , post your ProblemCategoryDelete code....

[Golfreak]

This is the code:

Public Sub cmdDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
 
        If cmbSelect.SelectedIndex <> -1 Then
 
            SqlDeleteCommand1.CommandText = "ProblemCategoryDelete " & cmbSelect.Text & "'"
            SqlDeleteCommand1.CommandType = CommandType.StoredProcedure
            SqlDeleteCommand1.Parameters.Clear()
            SqlDeleteCommand1.Parameters.Add("@ProblemCategory", SqlDbType.VarChar).Value = cmbSelect.Text
            SqlDeleteCommand1.ExecuteNonQuery()
            cmbSelect.Select()
 
        End If
 
        UpdateCombo()
 
    End Sub

This is the error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
 
Additional information: System error.

[Asgorath]

try this ... the .commantText is just the name of stored procedure only....

Public Sub cmdDelete_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDelete.Click
 
        If cmbSelect.SelectedIndex <> -1 Then
 
            SqlDeleteCommand1.CommandText = "ProblemCategoryDelete"
            SqlDeleteCommand1.CommandType = CommandType.StoredProcedure
            SqlDeleteCommand1.Parameters.Clear()
            SqlDeleteCommand1.Parameters.Add("@ProblemCategory", SqlDbType.VarChar).Value = cmbSelect.Text
            SqlDeleteCommand1.ExecuteNonQuery()
            cmbSelect.Select()
 
        End If
 
        UpdateCombo()
 
    End Sub

[Golfreak]

Thanks Asgorath

That did the trick

[Asgorath]

well finally