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! :confused:

 

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! :cool: