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

BIGZIPZ1Asked:
Who is Participating?
 
YveauConnect With a Mentor Commented:
you are trying to put the value 'Table1' into a integer parameter nameTable ... that won't work.
you have to declare the parameter nameTable as a string.

... and I think you need to put quotes around Table1, but I'm not sure ... ("Table1" in stead of Table1)

Hope this helps ...

Dim nameTable As String = Table1   ' or "Table1" ?
cmdProcedure.Parameters.Add(New SqlParameter("@parameter1", nameTable))

Open in new window

0
 
DarrenDCommented:
Hi,

Dim nameTable As Integer = Table1

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

Darren
0
 
BIGZIPZ1Author Commented:
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.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
BIGZIPZ1Author Commented:
Hey, the above code gives the following error:

Error converting data type nvarchar to int.
0
 
YveauCommented:
on line 1 or 2 ?
Is @parameter1 an integer ? Should be a varchar(128) ...

Hope this helps ...
0
 
BIGZIPZ1Author Commented:
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

0
 
YveauCommented:
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 ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.