code help, vb.net

In a method in a class, there is:
  ' ADD SP PARAMETERS
    Public Sub AddSpPram(name As String, value As Object, int As Int32)
        Dim newPram As New SqlParameter(name, value, int)
        params.Add(newPram)
    End Sub

The calling code is:

With SQL
            .AddSpPram("@FirstName", SqlDbType.VarChar, 30)
            .AddSpPramValue("@FirstName", FirstNameTextBox.Text)
            .
End With

Open in new window


When code adds parameter values, I get an error: "Conversion from string "@FirstName" to type 'Integer' is not valid."

ParamError
As you can see in the image, the value for @FirstName is "w". Why the message is writing about integer conversion?

Question: How can I fix this error?
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
The parameter is OK. Your AddSpPram function is probably OK.

Something else is wrong in the SQL statement.  It is doing something with or near the @FirstName parameter that lacks a cast.

What is the SQL statement? e.g. Select * from MyTable ...
Éric MoreauSenior .Net ConsultantCommented:
how is your params variable declared? It looks like it is declared as integer.

Is it declared as a dictionnary (which you can access by key) or is defined as an array which can only be accessed by index (and in this case, name is not an index)
Jacques Bourgeois (James Burger)PresidentCommented:
You can use de AddWithValue method if you want to easily assign a value at the same time that you declare it. It prevents you from having to go through 2 methods to create and assign a parameter, and thus removes the need of creating a sub to do so.

params.AddWithValue("@FirstName",FirstNameTextBox.Text)

It works in most situations, and the little overhead required by the system to define the type of parameter is usually not noticeable.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
re:> What is the SQL statement? e.g. Select * from MyTable ...

SQL.ExecQuery("spAddCustomer")

There is no sql script.

re:> how is your params variable declared? It looks like it is declared as integer.

   ' QUERY PARAMETERS
    Public params As New List(Of SqlParameter)

  ' SqlControl CLASS TO HANDLE QUERY EXCUTION THROUGHOUT THE PROJECT
Public Class SqlControl

    ' DECLRATIONS
#Region "DECLRATIONS"

    ' CONNECTION STRING
    Dim xmlReader As New XmlTextReader("csWSCGSoftware.xml")
    Dim strCnn As String = xmlReader.ReadElementString("connection")

    ' CONNECTION 
    Public cnn As SqlConnection = New SqlConnection(strCnn)

    ' COMMAND
    Private cmd As SqlCommand = New SqlCommand

    ' DATA ADAPTER
    Public da As SqlDataAdapter

    ' DATASET
    Public ds As DataSet

    ' QUERY PARAMETERS
    Public params As New List(Of SqlParameter)

    ' QUERY STAT
    Public RecordCount As Integer

    ' TO CALL DATA CONTROL EXCEPTIONS ANYWHERE
    Public my_exception As String

#End Region

 ' EXECUTE THE QUERY
    Public Sub ExecQuery(query As String)

        RecordCount = 0

        Try

            cnn.Open()

            ' CREATE QUERY
            cmd = New SqlCommand(query, cnn)

            ' LOAD PARAMETERS TO SQLCOMMAND
            params.ForEach(Sub(x) cmd.Parameters.Add(x))

            ' CLEAR PARAMETERS
            params.Clear()

            ' EXECUTE COMMAND AND FILL DATASET
            ds = New DataSet
            da = New SqlDataAdapter(cmd)

            'MessageBox.Show(cmd.CommandText.Substring(0, 6))
            If UCase(cmd.CommandText.Substring(0, 6)) = "SELECT" Then

                RecordCount = da.Fill(ds)

            ElseIf InStr(cmd.CommandText, " ") = 0 Then

                cmd.CommandType = Data.CommandType.StoredProcedure
                RecordCount = cmd.ExecuteNonQuery()

            Else


                RecordCount = cmd.ExecuteNonQuery()

            End If

            cnn.Close()
        Catch ex As Exception
            ' CAPTURE ERROR
            my_exception = ex.Message
        Finally

        End Try

        ' MAKE SURE THE CONNECTION IS CLOSED
        If cnn.State = ConnectionState.Open Then cnn.Close()

    End Sub

 'ADD QUERY PARAMETERS
    Public Sub AddPram(name As String, value As Object)

        Dim newPram As New SqlParameter(name, value)

        params.Add(newPram)

    End Sub

    'ADD SP PARAMETERS
    Public Sub AddSpPram(name As String, value As Object, int As Int32)

        Dim newPram As New SqlParameter(name, value, int)

        params.Add(newPram)

    End Sub

    ' ADD SP PARAMETERS
    Public Sub AddSpIntPram(name As String, value As Object, int As Int32)

        Dim newPram As New SqlParameter(name, value, int)

        params.Add(newPram)

    End Sub

     ADD SP PARAMETER VALUES
    Public Sub AddSpPramValue(name As String, value As String)

        params(name).Value = value '.ToString

    End Sub

End Class

Open in new window

Mike EghtebasDatabase and Application DeveloperAuthor Commented:
After the comment and question from Eric, I see that params is public; so I can use it in my code. The other methods like "AddPram" and "AddSpIntPram" are not for my use (they are used by lambda expressions at line 47 above code).

And I think I should make this public like "Private Sub AddPram" and even dele both "AddSpIntPram" and "AddSpPramValue".

brb
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Well, I made these changes:
 ' QUERY PARAMETERS
    [b]Private [/b]params As New List(Of SqlParameter)

  'ADD QUERY PARAMETERS
    Public Sub AddPram(name As String, value As Object)
        Dim newPram As New SqlParameter(name, value)
        params.Add(newPram)
    End Sub

Open in new window

This works calling:   SQL.AddPram("@FirstName", FirstNameTextBox.Text)    

The above is for Select, Update, Insert, and Delete statements. Now, for Stored Procs, I need help to come up with other methods to work with:

With SQL
            .AddSpPram("@FirstName", SqlDbType.VarChar, 30)
            .AddSpPramValue("@FirstName", FirstNameTextBox.Text)
            .
End With

Or only AddSpPram that would take four parameters:  

"@FirstName", SqlDbType.VarChar, 30, FirstNameTextBox.Text
            1                               2                  3                  4

Please see my other question at: http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_28544689.html

Is this doable?

Thanks,

Mike
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.