Link to home
Start Free TrialLog in
Avatar of CR_Office
CR_OfficeFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Brian Mulder
Brian Mulder
Flag of Netherlands image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sara110
sara110
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
Avatar of sara110
sara110
and in fill
  myDataAdapter.Fill(myDataSet)
Avatar of sara110
sara110
 myDataAdapter = New sqldatadapter(strcommandtext, myConnection )
Avatar of PockyMaster
PockyMaster
Flag of Netherlands image
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.
Avatar of Brian Mulder
Brian Mulder
Flag of Netherlands image
the sql is fine, that worked in a test :)
Avatar of PockyMaster
PockyMaster
Flag of Netherlands image
 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
Avatar of PockyMaster
PockyMaster
Flag of Netherlands image
Ofcourse fill in your own connectionstring at the ...  hehehe
Avatar of Sancler
Sancler
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

Avatar of CR_Office
CR_Office
Flag of United States of America image

ASKER

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