Hey, I am currently having some problems with VB.net and MSSQL. I'm trying to create an application to learn more about VB.net, I decided to create a simple login system for the application (like the ones they used in distributed applications which have a centeral SQL server). I have come across a problem however.
When I run the program the login box pops up with two text boxes and a login button, one box is the user alias (username) and the other is the password. When the user enters the username and password and clicks login he program checks to see if the user exists using the following SQL statement:
"SELECT * FROM tUsers WHERE uAlias = @varAlias AND uPass = @varPass" I then insert the values entered by the user into the SQL query using the following bit of code:
SQLCommand.Parameters.AddWithValue("@varAlias", Trim(txAlias.Text)) SQLCommand.Parameters.AddWithValue("@varPass", Trim(txPassword.Text))
As that's what I've been taught to use, however if the logging in fails the user obviously tries again but on the second go it gives me the following error:
System.Data.SqlClient.SqlException was unhandled
Message="The variable name '@varAlias' has already been declared. Variable names must be unique within a query batch or stored procedure."
Source=".Net SqlClient Data Provider"
(Minus the Strack trace which is huge)
I understand what it is saying, the program is redeclaring the @varAlias variable (and the @varPass if it ever got round to it) but I have no idea how to fix it or where I am going wrong.
I have provided the code for both the application and the external SQL module file I have created. If anyone has any ammendements as well please do say as it's hard learning withotu knowing how proper applications are built.
(My password in the code has been intentionally hid ;) )
'Import some important things including the database 'wrapper' Imports Rain.fSQL, System, System.Security.Cryptography, System.Text Public Class Login Dim tmpSuccess As Boolean = False Dim tmpNoOfTries As Integer Private Function Login() As Object If txAlias.Text = "" Or txPassword.Text = "" Then MsgBox("You left a required field empty!", MsgBoxStyle.Exclamation) Else 'Create the SQL string Dim sqlCheckData As String = "SELECT * FROM tUsers WHERE uAlias = @varAlias AND uPass = @varPass" 'Encode the password 'Add the username and password into the sql string SQLCommand.Parameters.AddWithValue("@varAlias", Trim(txAlias.Text)) SQLCommand.Parameters.AddWithValue("@varPass", Trim(txPassword.Text)) 'Create the SQL connection fSQL.SQLConnectionCreate() SqlCommand.Connection = SqlConnection 'Add the SQL SqlCommand.CommandText = sqlCheckData 'Execute the query SqlCommand.ExecuteNonQuery() 'Create a data reader SQLData = SqlCommand.ExecuteReader 'If any data is found then the username exists If SQLData.Read() Then tmpSuccess = True Else tmpSuccess = False End If fSQL.SQLConnectionClose() End If Return tmpSuccess End Function Private Sub btClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btClose.Click Me.Close() End Sub Private Sub btLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btLogin.Click Login() If tmpSuccess = True Then btLogin.Enabled = False MainApp.Show() Me.Close() End If If tmpSuccess = False Then tmpNoOfTries += 1 MsgBox("Logging in failed, please try again. You have had " & tmpNoOfTries & " of 5 goes", MsgBoxStyle.Exclamation) End If If tmpNoOfTries >= 5 Then btLogin.Enabled = False lbWarning.Text = "Too many failed attempts!" End If End Sub End Class
Imports System.Data, System.Data.SqlClient Module fSQL 'SQL connection variables Public SQLConnection As New SqlConnection() Public SQLCommand As New SqlCommand() Public SQLData As SqlDataReader 'SQL connection functions 'Function used for creating the database connection Public Function SQLConnectionCreate() As Object If SQLConnection.State = ConnectionState.Closed Then 'Create the Connection String Dim SQLConnectionString As String = "Data Source=LAMBDA\SQLEXPRESS;Initial Catalog=Rain;User ID=Skyrail;Password=*******" 'Set the connection string into the SQL connection SQLConnection.ConnectionString = SQLConnectionString 'Open up the connection SQLConnection.Open() End If 'Return the connection status Return SQLConnection End Function 'Function used for closing the database connection Public Function SQLConnectionClose() As Object If SQLConnection.State = ConnectionState.Open Then 'Close the connection SQLConnection.Close() End If Return SQLConnection End Function End Module