Solved

code help, vb.net

Posted on 2014-10-26
6
305 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 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

829 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