An unhandled exception of type 'System.Data.SqlCli

BB · December 2nd, 2003, 03:17 PM

Can someone give me some ideas on how to solve this. I am working on the example on page 638 in the book. The only differences I made was I used one of my databases out on the server and a different query. I get this error right at myConnection.Open.

See code below:

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim myConnection As SqlConnection = New _
SqlConnection("server=Servername; database=databasename; uid=UUU; pwd=PPP")

    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 col1, col2, col3, col4" & _
            "from table1 order by col1"
        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, "Test")

        'Close the database connection...
        myConnection.Close()

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

    End Sub
End Class

Any thoughts? Thanks!

jlick · December 2nd, 2003, 03:25 PM

What error? It was truncated in the subject.
What book?

I think I know what your issue is anyway:

myDataAdapter.SelectCommand.CommandText = _
"Select col1, col2, col3, col4" & _
"from table1 order by col1"

There is a space missing between col4 & from. Your command would be:
"Select col1, col2, col3, col4from table1 order by col1"

BTW:
It is faster in VB.NET to use a string builder rather than the &.

John R Lick
[email protected]

BB · December 2nd, 2003, 03:37 PM

Here is the full error

"An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
Additional information: System error."

I took away the space, but still get the error. I will look in to trying the string builder.

Thanks

jlick · December 2nd, 2003, 05:34 PM

Do you mean that you added a space? I was telling you that there should have been a space where there wasn't one.

Another thing you may want to try, is having the code print out the SQL statement that you want to try. Copy & Paste the SQL statement into a Query program (SQL Query Analyzer for MS-SQL). And see what error you get. Resolve it there, then fix your VB to build the correct statement.

John R Lick
[email protected]

risu · December 4th, 2003, 05:19 PM

BB,

I'm not familiar with the example in the book but my connection string uses this for id and password parameters not uid or pwd

Code:

user id=XXXXX; password=XXXXX

Also enclose the conn.open() with a try block and have a printline or msgbox show you the ex.message or ex.stacktrace

Code:

Try
   SqlConn.Open()
   SqlDataAdapter.Fill(ds, "Data")
Catch ex As Exception
   MsgBox(ex.Message)
Finally
   SqlConn.Close()
End Try

Thanks,
Risu

chaudharis57 · February 21st, 2014, 10:22 AM

Quote:

Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
Dim CMD As SqlCommand
If con.State = ConnectionState.Open Then con.Close()
con.Open()

query = "select * from USERLOGIN where USERID='" & txtUsername.Text & "'and UPASSWORD='" & txtPasswd.Text & "'"

da = New SqlDataAdapter(query, con)
ds = New DataSet
da.Fill(ds)
If ds.Tables(0).Rows.Count > 0 Then
'valid user
USERNAME = txtUsername.Text

USERTYPE = ds.Tables(0).Rows(0).Item(3)
logintime = Now
DELETETIME()

If con.State = ConnectionState.Open Then con.Close()
con.Open()

'query = "UPDATE USERLOG SET ULOGDATE='" & Today & "',ULOGINTIME='" & Now & "' WHERE USERID='" & USERNAME & "'"
query = "INSERT INTO USERLOG VALUES('" & txtUsername.Text & "','" & Today & "','" & Now.TimeOfDay.ToString & "')"

CMD = New SqlCommand(query, con)
CMD.ExecuteNonQuery()

con.Open()

con.Close()

frmMDIMain.Show()
Me.Hide()

Else
MsgBox("INVALID LOGIN")

End If

txtUsername.Text = ""
txtPasswd.Text = ""
txtUsername.Focus()

End Sub

this my code.

when I run It

pop up window shows
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System. Data.dll

Additional information: Cannot open database "MULTIPLEX" requested by the log in. The log in failed.
Log in failed for user 'magicbox\Administrator'.

this message.

how can I solved it?