Solved

Error when getting data from SQL Server

Posted on 2006-11-30
18
193 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now