SQLParameterCollection Question

[monkeynote]

hello guys!

i am new to VB.net and i would like to ask if how can i pass sqlparameters in my custom function

i have a class name obj and has a function called search

    Public Function Search(ByVal sqlStatement As String, ByVal cparam As SqlParameterCollection, Optional ByRef e As String = "") As String
        Try
            Dim cm As New SqlCommand
            cm.Connection = cn                  'Set a Connection
            cm.CommandText = sqlStatement       'Execute SQL Statement
            cm.Parameters.Add(cparam)
            cm.CommandType = CommandType.Text   'Refers that the command is SQL and not Stored Proc
            Search = cm.ExecuteScalar()         'Execute the SQL Statement
        Catch ex As Exception
            e = ex.ToString
            Search = "ER"
        End Try
    End Function

i am passing the parameter values but it seems that it my username is only the condition that it satisfies (any password will return a value!) am i doing right in passing my parameter collection? username and password must match in the users table.

    Private Sub buttLogIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttLogIn.Click
        Dim sqlStatement As String, UserID As String, err As String = ""
        Dim paramcollection As New SqlClient.SqlParameterCollection
        Dim param As New SqlClient.SqlParameter
        sqlStatement = "SELECT UserID FROM username WHERE username = @username AND password = @password"

        param.ParameterName = "@username"
        param.Value = boxUsername.Text
        paramcollection.Add(param)
        param.ParameterName = "@password"
        param.Value = boxPassword.Text
        paramcollection.Add(param)

        UserID = obj.Search(sqlStatement, paramcollection, err)
        MsgBox(UserID & " " & err)

        If UserID <> "ER" Then
            Me.Close()
        End If
    End Sub

please help me on how can i fix this bug!

 

[PlausiblyDamp]

What version of .Net / Visual Studio are you using? When I cut and paste your code it fails to compile on the line

Dim paramcollection As New SqlClient.SqlParameterCollection

because SqlParameterCollection doesn't have a public constructor.

 

[monkeynote]

i am using VS2008.

i just click continue when the error appears that paramcollection has no constructor.

i resolve some part of my problem by using this code

paramcollection.Add("@username", SqlDbType.Text).Value = boxUsername.Text
        paramcollection.Add("@password", SqlDbType.Text).Value = boxPassword.Text

i remove param variable and put it directly in the paramcollection and it was OK but... my main problem now is paramcollection has no constructor

how can i solve this issue? is there anything wrong with my function? how can i put public constructor in SqlParameterCollection ? coding in .net is very much frustrating!

Please help me with my problem

 

[PlausiblyDamp]

You could replace the parametercollection with a generic list of parameters like

   Public Function Search(ByVal sqlStatement As String, ByVal cparam As List(Of SqlParameter), Optional ByRef e As String = "") As String
        Try
            Dim cm As New SqlCommand
            cm.Connection = cn                  'Set a Connection
            cm.CommandText = sqlStatement       'Execute SQL Statement
            cm.Parameters.AddRange(cparam.ToArray())
            cm.CommandType = CommandType.Text   'Refers that the command is SQL and not Stored Proc
            Search = cm.ExecuteScalar()         'Execute the SQL Statement
        Catch ex As Exception
            e = ex.ToString
            Search = "ER"
        End Try
    End Function

and call it like

    Private Sub buttLogIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttLogIn.Click
        Dim sqlStatement As String, UserID As String, err As String = ""
        Dim paramcollection As New List(Of SqlParameter)

        sqlStatement = "SELECT UserID FROM username WHERE username = @username AND password = @password"

        paramcollection.Add(New SqlParameter("@username", boxUserName.Text))
        paramcollection.Add(New SqlParameter("@password", boxPassword.Text))

        UserID = Search(sqlStatement, paramcollection, err)
        MsgBox(UserID & " " & err)

        If UserID <> "ER" Then
            Me.Close()
        End If
    End Sub

The problem you are having with your original code is down to reusing the same parameter object for both parameters - the second set of property assignments are over writing the first set.

As an aside I would also recommend against catching the exception and returning a string - it is easier to let the calling routine catch the error in this case i.e. your search routine could be similar to

    Public Function Search(ByVal sqlStatement As String, ByVal cparam As List(Of SqlParameter)) As String

        Dim cm As New SqlCommand
        ' cm.Connection = cn                  'Set a Connection
        cm.CommandText = sqlStatement       'Execute SQL Statement
        cm.Parameters.AddRange(cparam.ToArray)
        cm.CommandType = CommandType.Text   'Refers that the command is SQL and not Stored Proc
        Search = cm.ExecuteScalar()         'Execute the SQL Statement

    End Function

and the calling code similar to

    Private Sub buttLogIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttLogIn.Click
        Dim sqlStatement As String, UserID As String
        Dim paramcollection As New List(Of SqlParameter)

        sqlStatement = "SELECT UserID FROM username WHERE username = @username AND password = @password"

        paramcollection.Add(New SqlParameter("@username", boxUserName.Text))
        paramcollection.Add(New SqlParameter("@password", boxPassword.Text))

        Try
            UserID = Search(sqlStatement, paramcollection)
        Catch ex As Exception
            'error occurred so deal with it however...
        End Try

        If Not UserID Is Nothing Then
            Me.Close()
        End If

    End Sub

or an alternate way could be

    Private Sub buttLogIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttLogIn.Click
        Dim sqlStatement As String, UserID As String
        Dim paramcollection As New List(Of SqlParameter)

        sqlStatement = "SELECT UserID FROM username WHERE username = @username AND password = @password"

        paramcollection.Add(New SqlParameter("@username", boxUserName.Text))
        paramcollection.Add(New SqlParameter("@password", boxPassword.Text))


        UserID = Search(sqlStatement, paramcollection)
        

        If Not UserID Is Nothing Then
            Me.Close()
        End If

    End Sub

    Public Function Search(ByVal sqlStatement As String, ByVal cparam As List(Of SqlParameter)) As String

        Try
            Dim cm As New SqlCommand
            ' cm.Connection = cn                  'Set a Connection
            cm.CommandText = sqlStatement       'Execute SQL Statement
            cm.Parameters.AddRange(cparam.ToArray)
            cm.CommandType = CommandType.Text   'Refers that the command is SQL and not Stored Proc
            Return cm.ExecuteScalar()         'Execute the SQL Statement
        Catch
            Return Nothing
        End Try

    End Function

 

[monkeynote]

Wowowow! :d

Wow! that was awesome!!!!

i adapt the alternative code below that handles error within the search function!

the search function is working now! weeeeeee!

thank you for guiding me what to do and i get the whole picture of it! as a sign of my appreciation... i put your UserID (PlausiblyDamp) in Search() Function

thank you so much! you rock dude!