Question Error upon filling dataAdapter (SQL2005)

Super_Grover

Active member
Joined
Apr 7, 2010
Messages
32
Programming Experience
Beginner
Hi all,

I'm trying to fill a dataadapter from my sql2005 server. After filling i'm planning on generating a chart (VS2008) from the datarows.

But upon filling the data adapter I'm getting: "A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll" in the immediate window.

Here's my code:
VB.NET:
Chart1.Series.Clear()
        Chart1.Titles.Add("Demo1")
        Dim d As New Series
        d.Name = "Demo"
        d.ChartType = SeriesChartType.Line

        Dim objConnection As SqlConnection = New SqlConnection(gs_ConnRead)
        Dim objCommand As SqlCommand = New SqlCommand()
        objCommand.Connection = objConnection
        'objCommand.CommandText = "SELECT Benaming, Percentage FROM Insurances"
        objCommand.CommandText = "SELECT UserID, MONTH(Datum) AS month, SUM(Aantal_toetsingen) AS Expr1,  SUM(Totaal_bedrag) AS Expr2 FROM(Toetsingen) WHERE UserID = '102' GROUP BY MONTH(Datum), UserID ORDER BY month"
        
        Dim objDataAdapter As New SqlDataAdapter(objCommand)
        Dim objDataTable As New Data.DataTable("Toetsingen")
        Dim objDataRow As DataRow

        Try
            objConnection.Open()
            
            objDataAdapter.Fill(objDataTable)
            
            For Each objDataRow In objDataTable.Rows
                
             d.Points.AddXY(objDataRow.Item("month"), objDataRow.Item("Expr1"))
            Next

        Catch sqlExceptionErr As SqlException
            'Write the exception
            Console.WriteLine(sqlExceptionErr.Message)
        Catch InvalidOperationExceptionErr As InvalidOperationException
            'Write the exception
            Console.WriteLine(InvalidOperationExceptionErr.Message)
        End Try
        
        objConnection.Close()
       
        objDataRow = Nothing
        objDataTable.Dispose()
        objDataTable = Nothing
        objDataAdapter.Dispose()
        objDataAdapter = Nothing
        objCommand.Dispose()
        objCommand = Nothing
        objConnection.Dispose()
        objConnection = Nothing

        Chart1.Series.Add(d)
Now, at 'TRY' it jumps from "objDataAdapter.Fill(objDataTable)" to the catch with the error mentioned above.
The SQL query works fine in the SQL management studio and gives me the monthly totals.
The above code works fine when I use a simple query, like the 'commented' query in the code fragment.

So, what am I doing wrong? Is VS unable to handle my select statement of is there something else wrong?

Thanks in advance!
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,314
Location
Sydney, Australia
Programming Experience
10+
That's not an error message. That's a message that an error occurred, not the message that describes the error. That's the message we need. Also, for a SqlException, you're likely to need the message from each SqlError in the Errors collection.
 

Super_Grover

Active member
Joined
Apr 7, 2010
Messages
32
Programming Experience
Beginner
Ah, sorry for that!
I displayed the actual error message and it states: Incorrect syntax near ')', though I tested the query in the SQL management studio first.

But some mistake must have been occured though I couldn't see the syntax error.

I rebuild the select query and now it works.
Yesterday I tried and tried with different queries but no succes. So I got confused.

But it's solved. Just a dumb syntax error. :(
 

Super_Grover

Active member
Joined
Apr 7, 2010
Messages
32
Programming Experience
Beginner
No sure. The query now works fine, even with the 'month' alias... I couldn't really figure out what the error was.

Thanks anyway!
(I don't know how to change thread name to 'solved'...)
 
Top Bottom