Solved

How can I use longer variables in my sql procedures?

Posted on 2007-11-23
7
158 Views
Last Modified: 2010-04-23
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
Comment
Question by:BIGZIPZ1
  • 3
  • 3
7 Comments
 
LVL 18

Expert Comment

by:DarrenD
ID: 20339916
Hi,

Dim nameTable As Integer = Table1

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

Darren
0
 

Author Comment

by:BIGZIPZ1
ID: 20340050
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
 
LVL 18

Accepted Solution

by:
Yveau earned 500 total points
ID: 20340158
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:BIGZIPZ1
ID: 20340204
Hey, the above code gives the following error:

Error converting data type nvarchar to int.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20340224
on line 1 or 2 ?
Is @parameter1 an integer ? Should be a varchar(128) ...

Hope this helps ...
0
 

Author Comment

by:BIGZIPZ1
ID: 20340238
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20346288
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

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.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

759 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

20 Experts available now in Live!

Get 1:1 Help Now