Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

AN unhandled exception of type 'system.data.sqlclient.sqlException' occured in system.data.dll

chaudharis57chaudharis57 INDIAPosts: 1Member

` 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`

when I try to run it run-time error is obtained
at 1st n 3rd con.open()

also LOgin failed for user 'magicbox\admin'

how can I overcome from it

Comments

  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭

    You code needs a little love...

    First, I see you openning and closing the connection a lot needlessly. Below the MSDN offers a nicer way to do it, but I don't personally use that a lot but it is really nice

    I believe SQLCommand throws an exception when it has an error in your SQL Query. This way, you know that the query failed and you can get the details that the Database Server returned. Because you are not handling the exception, your code is crashing.

    I cannot know this for sure without seeing it crashing myself (or without the actual exception message) but I think that your insert query is erroring. INSERT INTO table VALUES('','','') means your table has only 3 columns. You have to make sure that if the date field in your database table is set to Date, that the format of your date string matches what the Database engine is looking for.

    Best way for you to know is to dig deeper into the exception. Learn to handle them and you can master bug fixing. Try Catches are how you handle exceptions. It keeps your program from crashing, and you can access the exact details of the crash (including the line number and function it was in if you know how to read a StackTrace)

    Public Sub ShowSqlException(ByVal connectionString As String)
    Dim queryString As String = "EXECUTE NonExistantStoredProcedure"
    Dim errorMessages As New StringBuilder()

    Using connection As New SqlConnection(connectionString)
    Dim command As New SqlCommand(queryString, connection)

    Try
    command.Connection.Open()
    command.ExecuteNonQuery()

    Catch ex As SqlException
    Dim i As Integer
    For i = 0 To ex.Errors.Count - 1
    errorMessages.Append("Index #" & i.ToString() & ControlChars.NewLine _
    & "Message: " & ex.Errors(i).Message & ControlChars.NewLine _
    & "LineNumber: " & ex.Errors(i).LineNumber & ControlChars.NewLine _
    & "Source: " & ex.Errors(i).Source & ControlChars.NewLine _
    & "Procedure: " & ex.Errors(i).Procedure & ControlChars.NewLine)
    Next i
    Console.WriteLine(errorMessages.ToString())
    End Try
    End Using
    End Sub

    This code came from MSDN.

    This code will write the exception data out to the console (View>Console, I think thats how you get the window to show up if its not open already for you, usually underneath your code)

    The "Using" block is how they open connections in the new frameworks. Using will open the connection first. Then, after everything is done in side, it will close the connection and dispose of it. This way, the connection never remains open (best to practice this even when it seems unreasonable, large scale applications have lots of problems with database connecting if it is done sloppy)

    The Everything between Try and Catch is what normally runs. If it crashes (throws an exception) then (and only then) the catch block code runs. The object, ex as SqlException, will contain the errors of your details. SqlException is specific for this set of code. Normally I use "Catch ex As Exception" with that generic exception because every kind of exception is compatible with it.

    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()
    End If

    'Probably shouldn't be declaring this variable (query) outside of this function, I see what you did there o_o
    query = "select * from USERLOGIN where USERID='" & txtUsername.Text & "'and UPASSWORD='" & txtPasswd.Text & "'"

    'Same here, you should be creating all of the variables inside of this function
    'so when the function ends, they will get disposed of by garbage collection
    da = New SqlDataAdapter(query, con)
    ds = New DataSet
    da.Fill(ds)

    'If it didn't crash here, good i might be right with my nonsense

    If ds.Tables(0).Rows.Count > 0 Then
    'valid user
    USERNAME = txtUsername.Text
    USERTYPE = ds.Tables(0).Rows(0).Item(3)
    logintime = Now
    DELETETIME()
    'Why!!!!!!! The computers like "oh god, please stop making my flap my wings"
    If con.State = ConnectionState.Open Then con.Close()
    con.Open()
    'query = "UPDATE USERLOG SET ULOGDATE='" & Today & "',ULOGINTIME='" & Now & "' WHERE USERID='" & USERNAME & "'"

    'This query is possibly wrong. If you use VALUES() without defining columns, you have to put a value
    'in for every single column, in the order they are defined
    query = "INSERT INTO USERLOG VALUES('" & txtUsername.Text & "','" & Today & "','" & Now.TimeOfDay.ToString & "')"
    CMD = New SqlCommand(query, con)
    CMD.ExecuteNonQuery()
    'crashes here probably

    'Needless open and closes
    con.Open()
    con.Close()
    frmMDIMain.Show()
    Me.Hide()
    Else
    MsgBox("INVALID LOGIN")
    End If

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

    End Sub`

    Here is a cleaned up version that probably still crashes but would tickle my old programming teacher to no end

    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click 
    'Define your ConnectionString Outside of this function, so you can always refer back to it

    Dim errored As Boolean = false

    Using MyCon As New SqlConnection(connectionString)
    query = "select * from USERLOGIN where USERID='" & txtUsername.Text & "'and UPASSWORD='" & txtPasswd.Text & "'"
    Try
    da = New SqlDataAdapter(query, MyCon)
    ds = New DataSet
    da.Fill(ds)
    Catch ex As Exception
    MessageBox.Show("HAha, failure." & vbLfCr & ex.Message)
    errored = True
    End Try
    End Using

    If Not Errored Then
    If ds.Tables(0).Rows.Count > 0 Then
    USERNAME = txtUsername.Text
    USERTYPE = ds.Tables(0).Rows(0).Item(3)
    logintime = Now
    DELETETIME()
    Using MyCon as New SqlConnection(connectionString)
    query = "INSERT INTO USERLOG VALUES('" & txtUsername.Text & "','" & Today & "','" & Now.TimeOfDay.ToString & "')"
    Try
    CMD = New SqlCommand(query, con)
    CMD.ExecuteNonQuery()
    Catch ex As Exception
    MessageBox.Show("HAha, failure." & vbLfCr & ex.Message)
    errored = True
    End Try
    End Using
    If Not errored then
    frmMDIMain.Show()
    Me.Hide()
    Else
    MessageBox.Show("LOGIN ERROR")
    End If
    Else
    MsgBox("INVALID LOGIN")
    End If

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

    End Sub`
Sign In or Register to comment.