Solved

code help, vb.net

Posted on 2014-10-26
6
290 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
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 69

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 33

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 33

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 33

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now