Solved

Error when getting data from SQL Server

Posted on 2006-11-30
18
196 Views
Last Modified: 2010-04-23
Hello Gurus,

I was trying to read the Wrox book on VB.NET and was on the database section. I tried one of the example where i was getting data from a SQL database (pubs). I keep getting this error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

Even when i tried to use their source code, i get the same error. Any idea what i need to change on my system to make this work?

Thanks,
KalluMama
0
Comment
Question by:KalluMama
  • 9
  • 9
18 Comments
 

Author Comment

by:KalluMama
ID: 18046452
...and BTW the program breaks at this point in the code:

        ' Open the database connection...
        objConnection.Open()
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18046471
check the connection string... i'm sure its something to do with the connection string
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18046481
could you also post the stack... we can find out more from that...
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

Author Comment

by:KalluMama
ID: 18046486
this is what the conn string looks like

    Dim objConnection As SqlConnection = New _
        SqlConnection("server=(local);database=pubs;user id=sa;password=")

This is exactly as mentioned in the book...do i need to change the server settings?
0
 

Author Comment

by:KalluMama
ID: 18046501
how do i get the stack :-(
0
 

Author Comment

by:KalluMama
ID: 18046516
'Import Data and SqlClient namespaces...
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim objConnection As SqlConnection = New _
        SqlConnection("server=(local);database=pubs;user id=sa;password=")
    Dim objDataAdapter As New SqlDataAdapter
    Dim objDataSet As DataSet = New DataSet

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'set the SelectCommand properties...
        objDataAdapter.SelectCommand = New SqlCommand
        objDataAdapter.SelectCommand.Connection = objConnection
        objDataAdapter.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, au_fname"
        objDataAdapter.SelectCommand.CommandType = CommandType.Text

        'Open the database connection...
        objConnection.Open()

        'Fill the dataset object with data...
        objDataAdapter.Fill(objDataSet, "authors")

        'close the database conn...
        objConnection.Close()

        'set the datagrid properties to bind it to our data...
        grdAuthorTitles.DataSource = objDataSet
        grdAuthorTitles.DataMember = "authors"

        'cleanup
        objDataAdapter = Nothing
        objConnection = Nothing

    End Sub
End Class
0
 

Author Comment

by:KalluMama
ID: 18046647
I think this is the call stack...not sure what this means

>      DatasetExample.exe!DatasetExample.Form1.Form1_Load(Object sender = {DatasetExample.Form1}, System.EventArgs e = {System.EventArgs}) Line 88 + 0xe bytes      Basic
0
 
LVL 5

Accepted Solution

by:
Collindsouza earned 500 total points
ID: 18046688
please try and do the following

add a new aspx page to the project

lets say Default2.aspx

then go to the design view of the page and... drag and drop SqlDatasource onto the web page ... right click on it and select Configure data source

Click new connection and in server name type (local).. and then select 'Use SQL Server Authentication' type username as 'sa' and leave password blank

then select pubs database from dropdown and see if your able to select it from the dropdown.. if you can then click test connection and then click ok...
then go back to the aspx page and select SqlDatasource and click on properties and see the connectionstring property and replace this  connectionstring property
with the one above
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18046736
if your unable to see the pubs database in the dropdown when you select select 'Use SQL Server Authentication' then select 'Use Windows Authentication'
then select pubs database from dropdown and see if your able to select it from the dropdown.. if you can then click test connection and then click ok...
then go back to the aspx page and select SqlDatasource and click on properties and see the connectionstring property and replace this  connectionstring property
with the one above
0
 

Author Comment

by:KalluMama
ID: 18046811
thanks...i used the wizard to make a connection string and then just used that connection string and pasted it into my code that worked just fine

"workstation id=xxx;packet size=4096;user id=sa;integrated security=SSPI;da" & _
        "ta source=""xxx\SQLEXPRESS"";persist security info=False;initial catalog=pu" & _
        "bs"


is this the way to do it?
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18046894
well.. it depends on how you set the SQL Server database as well.. from the looks of your Connectionstring it looks like when you installed your SQL server you set it on windows authentication mode and not not SQL Server Authentication..

But whenever you need to set a connectionstring to anydatabase you can use this as a way to set it up...

Glad to help!!!
0
 

Author Comment

by:KalluMama
ID: 18046926
Thanks for the help!
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18049676
hey.. just a question.. how did u come up with this user name.. Did u watch the Hindi movie by any chance called Satya..

there's a song in that movie called Mama.. Kallu Mama ;)
0
 

Author Comment

by:KalluMama
ID: 18049692
that's where is came from ;-)
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18049879
nice to meet a desi.. so there are you based man?
0
 

Author Comment

by:KalluMama
ID: 18049888
Houston, TX....u?
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18050403
London... england
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18050408
try n get me to US yaar.. i'm bored of UK.. need a change
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 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