How do I fix this error-"An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll"?

I hope someone can help me figure out this problem I have tried many things. I am using Visual Studio 2003 and have SQL 2000 loaded my local system. Below is the the sample code that I have been trying to get to work. I am new a programming so I will try not to ask to many stupid questions. Here is the code that I was testing to see if I could pull a dataset into a form. I am doing some sample so that I can understand the SQL connection for a project that I am working on.

 ' Imports Data and SqlClient namespaces...
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim myConnection As SqlConnection = New _
        SqlConnection("Server=(local);Database=pubs;User ID=sa;Password=sa;Trusted_Connection=True")
    Dim myDataAdapter As New SqlDataAdapter
    Dim myDataSet As DataSet = New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Set the SelectCommand properties...
        myDataAdapter.SelectCommand = New SqlCommand
        myDataAdapter.SelectCommand.Connection = myConnection
        myDataAdapter.SelectCommand.CommandText = _
            "SELECT au_lname, au_fname, title, price " & _
            "FROM authors " & _
            "JOIN titleauthor ON authors.au_id = titleauthor.au_id " & _
            "JOIN titles ON titleauthor.title_id = titles.title_id " & _
            "ORDER BY au_lname, aufname"
        myDataAdapter.SelectCommand.CommandType = CommandType.Text

        'Open the database connection...
        myConnection.Open()
        'Now execute the command...
        'myDataAdapter.SelectCommand.ExecuteNonQuery()

        'Fill the DataSet object with data...
        myDataAdapter.Fill(myDataSet, "authors") ' <<< Receive the error message here "An unhandled exception of type 'System.Data.SqlClient.SqlException'
                                                                     ' occurred in system.data.dll"?
        'Close the database connection...
        myConnection.Close()

        'Set the DataGrid properties to bind it to our data...
        grdAuthorTitles.DataSource = myDataSet
        grdAuthorTitles.DataMember = "authors"

    End Sub
End Class
CR_OfficeAsked:
Who is Participating?
 
bruintjeCommented:
Hello CR_Office,

you hade this line quoted
'myDataAdapter.SelectCommand.ExecuteNonQuery()

but i assume that was a typo if not unquote an run again
myDataAdapter.SelectCommand.ExecuteNonQuery()

else what you can try is to put the db part in a try block to see if there is more detail in the stack trace like
-----------
Try
    'Open the database connection...
    myConnection.Open()
    'Now execute the command...
    myDataAdapter.SelectCommand.ExecuteNonQuery()

    'Fill the DataSet object with data...
    myDataAdapter.Fill(myDataSet, "authors")
Catch ex As Exception
   MsgBox(ex.Message)
Finally
   SqlConn.Close()
End Try
-----------

and maybe this is related to
source: http://support.microsoft.com/default.aspx?scid=kb;en-us;321698

hope this helps a bit
bruintje
0
 
sara110Commented:
change it like this , copy and paste this code,   you should define datatable instead of dataset



  Dim myConnection As SqlConnection = New _
        SqlConnection("Server=(local);Database=pubs;User ID=sa;Password=sa;Trusted_Connection=True")
    Dim myDataAdapter As SqlDataAdapter
    Dim myDataSet As datatable = New datatable


  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Set the SelectCommand properties...
dim strcommandtext as string= "SELECT au_lname, au_fname, title, price " & _
            "FROM authors " & _
            "JOIN titleauthor ON authors.au_id = titleauthor.au_id " & _
            "JOIN titles ON titleauthor.title_id = titles.title_id " & _
            "ORDER BY au_lname, aufname"

        myDataAdapter = New myDataAdapter(strcommandtext, myConnection )
 
        'Fill the DataSet object with data...
        myDataAdapter.Fill(myDataSet, "authors")
        'Close the database connection...
        myConnection.Close()

        'Set the DataGrid properties to bind it to our data...
        grdAuthorTitles.DataSource = myDataSet
        grdAuthorTitles.DataMember = "authors"

    End Sub
0
 
sara110Commented:
and in fill
  myDataAdapter.Fill(myDataSet)
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
sara110Commented:
 myDataAdapter = New sqldatadapter(strcommandtext, myConnection )
0
 
PockyMasterCommented:
didnt you forget INNER before your join?
I  might be mistaken and have no SQL server here to test it, but I believe you can only INNER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN in SQL Server.
0
 
bruintjeCommented:
the sql is fine, that worked in a test :)
0
 
PockyMasterCommented:
 Dim sqlConn As New SqlClient.SqlConnection(".....")
  sqlConn.Open()
 Dim strSQL As String =     "SELECT au_lname, au_fname, title, price " & _
            "FROM authors " & _
            "JOIN titleauthor ON authors.au_id = titleauthor.au_id " & _
            "JOIN titles ON titleauthor.title_id = titles.title_id " & _
            "ORDER BY au_lname, aufname"
    Dim cmd As New SqlCommand(strSQL, sqlConn)

      Dim ta As New SqlClient.SqlDataAdapter(cmd)
       Dim ds As New DataSet
        ta.Fill(ds)

sqlConn.Dispose()


That should work
0
 
PockyMasterCommented:
Ofcourse fill in your own connectionstring at the ...  hehehe
0
 
SanclerCommented:
I'm not sure whether this would produce the particular error message reported, but

   "SELECT au_lname, au_fname ...

and

   "ORDER BY au_lname, aufname"

are incompatible.  Compare "au_fname" and "aufname".

Roger

0
 
CR_OfficeAuthor Commented:
I would like to thanks everyone for comments about my problem. It ended up to be a connection problem and the link that bruintje referenced in his comment help to me resolve my problem so he is the one that got out of a bind.

Thanks  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.