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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
sara110Commented:
and in fill
  myDataAdapter.Fill(myDataSet)
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

sara110Commented:
 myDataAdapter = New sqldatadapter(strcommandtext, myConnection )
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.
bruintjeCommented:
the sql is fine, that worked in a test :)
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
PockyMasterCommented:
Ofcourse fill in your own connectionstring at the ...  hehehe
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

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  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.