SQL AddWithValue problems

[BluePaper]

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
Class=15
ErrorCode=-2146232060
LineNumber=1
Message="The variable name '@varAlias' has already been declared. Variable names must be unique within a query batch or stored procedure."
Number=134
Procedure=""
Server="LAMBDA\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1

(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

[BluePaper]

Can anyone help as this is quite an annoying problem and it would be nice if it could be solved :)

[bcasp]

What version of .NET are you using?

If you have the System.Data.SqlClient (or even System.Data.OleDb) library available, it's actually really easy to do what you're trying to do. I can put up some sample code if you can get to that library.

[BluePaper]

I believe I'm working with .NET 3.5, maybe 3.0, it's definately >=3.0 thanks for your time :)

[bcasp]

The first piece of code you would need is a way to execute the queries. The following is a simple method that just takes a SqlCommand and returns a DataSet with the results.

Private Function ExecuteQuery(ByVal cmd As SqlCommand) As DataSet
        Dim dbConnection As New SqlConnection(connectionString)

        cmd.Connection = dbConnection

        Dim dbAdapter As New SqlDataAdapter()
        dbAdapter.SelectCommand = cmd

        Dim resultSet As New DataSet()

        dbConnection.Open()
        Try
            dbAdapter.Fill(resultSet)
        Finally
            dbConnection.Close()
        End Try

        Return resultSet
    End Function

For the actual command, it's pretty simple.

Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT column_name FROM table_name WHERE column_name = @inputParam;"

cmd.Parameters.AddWithValue("@inputParam", value)

Dim results As New DataSet
results = ExecuteQuery(cmd)

You can then go through the data set to get the information you need. I think this approach makes the code a little easier to read.

You need to import System.Data and System.Data.SqlClient for this to work.

[bcasp]

Let me know if you try that and anything is deprecated. I originally used it in .NET 2.0.

[BluePaper]

Ok so I've taken it, ripped it a part and glued it back together to fit my needs (see code below) now how can I go through the data/check that the row was found, is there a simple tutorial on datasets, adapters and such seeing as I'm used to coming from PHP & MySQL which is an entirely different ball game altogether.

Here's the code I've changed (will it still work? As I've said I don't know it too well and I'm learning on the go):

Dim sqlCmd As New SqlCommand()
            sqlCmd.CommandType = CommandType.Text
            sqlCmd.CommandText = "SELECT * FROM tUsers WHERE uAlias = @varAlias AND uPass = @varPass;"
            sqlCmd.Parameters.AddWithValue("@varAlias", txAlias.Text)
            sqlCmd.Parameters.AddWithValue("@varPass", varPassword)

            Dim sqlCmdResults As New DataSet
            sqlCmdResults = sqlQuery(sqlCmd)

Public Function sqlQuery(ByVal dbSqlCommand As SqlCommand) As DataSet

        'Set the connection string
        Dim dbConnectionString As String = "Data Source=LAMBDA\SQLEXPRESS;Initial Catalog=Rain;User ID=Skyrail;Password=*****"
        'Create a database connection function
        Dim dbConnection As New SqlConnection(dbConnectionString)
        'Set up the database adapter
        Dim dbAdapter As New SqlDataAdapter()
        'Create a dataset to put data into
        Dim dbResultData As New DataSet()

        'Put the connection data into the function
        dbSqlCommand.Connection = dbConnection
        'Something to do with the adapter
        dbAdapter.SelectCommand = dbSqlCommand
        'Open the connection
        dbConnection.Open()

        'Try and put data into the adapter
        Try
            dbAdapter.Fill(dbResultData)
        Finally
            dbConnection.Close()
        End Try

        Return dbResultData
    End Function

Thanks once again, it looks a lot cleaner now and I'm hoping it will work efficiently :)

[bcasp]

If you're simply trying to see if a row was returned, just take your result DataSet and do something like...

if result.Tables[0].Rows.Count = 0 then
'nothing was found
end if

or

if result.Tables[0].Rows.Count > 0 then
'something was found
end if

Just depends on how you need to use it. Hope this helps.