?
Solved

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

Posted on 2006-04-14
10
Medium Priority
?
2,658 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:CR_Office
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 2000 total points
ID: 16458768
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
 
LVL 6

Expert Comment

by:sara110
ID: 16458796
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
 
LVL 6

Expert Comment

by:sara110
ID: 16458800
and in fill
  myDataAdapter.Fill(myDataSet)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:sara110
ID: 16458806
 myDataAdapter = New sqldatadapter(strcommandtext, myConnection )
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16460230
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
 
LVL 44

Expert Comment

by:bruintje
ID: 16460251
the sql is fine, that worked in a test :)
0
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16460414
 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
 
LVL 14

Expert Comment

by:PockyMaster
ID: 16460416
Ofcourse fill in your own connectionstring at the ...  hehehe
0
 
LVL 34

Expert Comment

by:Sancler
ID: 16462047
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
 

Author Comment

by:CR_Office
ID: 16544330
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question