Solved

code help, vb.net

Posted on 2014-10-26
6
312 Views
Last Modified: 2014-12-19
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?
0
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 100 total points
ID: 40404794
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 ...
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 100 total points
ID: 40404812
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)
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 300 total points
ID: 40404962
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40404973
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

0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40404997
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
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40405035
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
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question