Error on 2nd try of log-in

Question

0.00/5 (No votes)

See more:

Hi guys, there's a problem with my log-in codes. I can log-in smoothly for 1st time log-in when I run/debug the program, but when I log out then try to log-in again I get this error:

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

Additional information: The variable name '@username' has already been declared. Variable names must be unique within a query batch or stored procedure.

Can anyone help me fix this. Here's my code:

Public Class frmLogin

    Dim cn As New SqlConnection("Data Source=TANSENGCO\SQLEXPRESS;Initial Catalog=MonitoringSystem;Integrated Security=True")
    Dim cmd As New SqlCommand
    Dim dr As SqlDataReader
    Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
        frmMainMenu.Height = 720
        frmMainMenu.Width = 950
        Dim numID As Integer = 2
        cmd.Connection = cn
        cn.Open()
        cmd.CommandText = "SELECT ID, username, password FROM tblUsers WHERE username = @username and password = @password"
        cmd.Parameters.Add(New SqlClient.SqlParameter("@username", SqlDbType.VarChar, 20)).Value = txtUsername.Text
        cmd.Parameters.Add(New SqlClient.SqlParameter("@password", SqlDbType.VarChar, 20)).Value = txtPassword.Text
        dr = cmd.ExecuteReader
        If dr.HasRows Then
            dr.Read()
            If dr("ID") = numID Then
                Me.Hide()
                frmMainMenu.lblAccessLevel.Text = "Admin"
                frmMainMenu.Show()
            Else
                Me.Hide()
                frmMainMenu.lblAccessLevel.Text = "User"
                frmMainMenu.TSMOffertory.Enabled = False
                frmMainMenu.TSMOffertory.Visible = False
                frmMainMenu.Show()
            End If
        Else
            MsgBox("Invalid Credentials", MsgBoxStyle.Exclamation, "Invalid LogIn")
        End If
        txtPassword.Clear()
        txtUsername.Clear()
        cn.Close()
    End Sub

What I have tried:

I don't really know what's going on so I don't know what to try.

Posted 19-Sep-16 2:23am

Paolo Tansengco

Updated 19-Sep-16 3:33am

Add a Solution

Comments

[no name] 19-Sep-16 8:27am

The error message clearly tells you what the problem is.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2016-09-19T02:27:00

Solution 1

declare this line inside the button click event

Dim cmd As New SqlCommand

(or)
add

cmd.Parameters.Clear()

before this line

cmd.Parameters.Add(New SqlClient.SqlParameter("@username", SqlDbType.VarChar, 20)).Value = txtUsername.Text

Posted 19-Sep-16 2:27am

Karthik_Mahalingam

Updated 19-Sep-16 2:29am

v2

Comments

Paolo Tansengco 19-Sep-16 8:39am

Wow thanks. It's working now. Thank you very much!

Karthik_Mahalingam 19-Sep-16 8:47am

welcome paolo

pt1401 · Accepted Answer · 2016-09-19T02:46:00

Your cmd is a member variable of the form, so retains it's parameters after you've used it for the first time.
Then, when you try to add the @username parameter to *the same* cmd object again, it croaks.

Always dispose a sql Command object by wrapping it in a using block so that it is disposed correctly.

C#

using (cmd = new SqlCommand(...))
{
   cmd.Open();
   ....
}

Dave Kreskowiak · Accepted Answer · 2016-09-19T02:49:00

Solution 3

Yeah, the SqlConnection, SqlCommand, and SqlDataReaders should NOT be declared in class scope. They should be declared in the method scope and Disposed of when not needed by your query any more. By failing to call Dispose on the Connection you're leaking resources and holding on to connections much longer than needed.

Connect as late as possible, query as fast as possible, and disconnect and Dispose as soon as possible.

Posted 19-Sep-16 2:49am

Dave Kreskowiak

Updated 19-Sep-16 2:50am

v2

Comments

pt1401 19-Sep-16 9:37am

> Connect as late as possible, query as fast as possible, and disconnect and Dispose as soon as possible.
Excellent mantra. Developers should have this tattooed on their foreheads in mirror writing :-)

Wendelius · Accepted Answer · 2016-09-19T03:33:00

One more comment on your code. The password, never store the password in the database. No one, not even the administrators, should be able to see what are the passwords for individuals.

The only thing you need to do is to verify that the password matches the one user has originally defined, you don't need to know what it is. Based on this you should hash the password, store the has and only compare if the hash for given password matches the hash in the database.

Have a look at Password Storage: How to do it.[^]

Error on 2nd try of log-in

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0