• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

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

0
BIGZIPZ1
Asked:
BIGZIPZ1
  • 3
  • 3
1 Solution
 
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
 
YveauCommented:
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
Industry Leaders: 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!

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now