asked on

How can I use longer variables in my sql procedures?

I have a stored procedure which operates with an @parameter which I call using the attached code. The variable nameTable is is the name of the current database table that I want the procedure to use. When I run the code I get an error saying "Conversion from string "Table1" to type 'Integer' is not valid."

I tried editing the procedure from "@parameter1 int" to "@parameter1 str" but it causes an error.

Dim nameTable As Integer = Table1
cmdProcedure.Parameters.Add(New SqlParameter("@parameter1", nameTable))

Open in new window

Darren

Hi,

Dim nameTable As Integer = Table1

If Table1 variable is a string then you will get the error above...

Darren

BIGZIPZ1

ASKER

I don't understand, you just stated one of the lines of code I said caused an error and told me it would cause an error.

ASKER CERTIFIED SOLUTION

Yveau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BIGZIPZ1

ASKER

Hey, the above code gives the following error:

Error converting data type nvarchar to int.

Yveau

on line 1 or 2 ?
Is @parameter1 an integer ? Should be a varchar(128) ...

Hope this helps ...

BIGZIPZ1

ASKER

Hey, I changed to vchar128 like you said and I now get this error:

Conversion from string "Table1" to type 'Integer' is not valid.

I have attached the full code below for you to see. I appreciate you trying to help!

P.S in the procedure editor, the data type is "@parameter1 varchar(128)," as you told me to put.

Public Class RateBox
    Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
        Dim scoreRating As Integer = ListBox1.SelectedItem 'this is the rating score out of 5'
        Dim cnPubs As New SqlConnection("Server=MARK-HOME\SQLEXPRESS;Database=Library;Integrated Security=SSPI;")
        Dim nameTable As Integer = "Table1"
        cnPubs.Open()
        Dim cmdProcedure As New SqlCommand("dbo.StoredProcedure1", cnPubs)
        cmdProcedure.CommandType = CommandType.StoredProcedure
        cmdProcedure.Parameters.Add(New SqlParameter("@parameter2", scoreRating))
        cmdProcedure.Parameters.Add(New SqlParameter("@parameter1", nameTable))
        cmdProcedure.ExecuteNonQuery()
        Form1.JackBennyTableAdapter.Fill(Form1.LibraryDataSet.JackBenny)
        Me.Close()
    End Sub

Open in new window

Yveau

Try switching lines 9 and 10 ...
What does the header of the stored procedure look like ?
My guess would be:
create procedure dbo.StoredProcedure1
@parameter1 varchar(128)
, @parameter2 int
as
...

Hope this helps ...