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

Stored Procedure, scope_identity, expects parameter

I'm not sure if this problem is in my stored procedure or my code.  I have the following stored procedure:

CREATE procedure dbo.adtAddCustomer
      @customerid varchar(50),
      @name varchar(50),
      @housenum int,
      @housesuffix varchar(10),
      @directionpreffix varchar(2),
      @streetname varchar(50),
      @directionsuffix varchar(2),
      @pscode varchar(10),
      @location varchar(10),
      @city varchar(30),
      @state varchar(2),
      @zip varchar(11),
      @country varchar(3),
      @custype varchar(20),
      @companynum numeric,
      @ID numeric output
as

Insert into Customer(customerAccountIDVar, customerName, houseNum, houseNumSuffix, directionalPreffix, streetName, directionalSuffix, locationPSCode, location, city, state, zipCode, country, customerType, companyNumber)
values(@customerid, @name, @housenum, @housesuffix, @directionpreffix, @streetname, @directionsuffix, @pscode, @location, @city, @state, @zip, @country, @custype, @companynum)
select @ID = scope_identity()
GO

When I run it :

        Me.OdbcAddCustomer.Parameters.Item("@customerid").Value = Me.txtcustomerid.Text
        Me.OdbcAddCustomer.Parameters.Item("@name").Value = Me.txtName.Text
        Me.OdbcAddCustomer.Parameters.Item("@housenum").Value = Me.txtHouseNum.Text
        Me.OdbcAddCustomer.Parameters.Item("@housesuffix").Value = Me.txtHouseNumSuffix.Text
        Me.OdbcAddCustomer.Parameters.Item("@directionpreffix").Value = Me.ddlDirPrefix.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@streetname").Value = Me.txtStreet.Text
        Me.OdbcAddCustomer.Parameters.Item("@directionsuffix").Value = Me.ddlDirSuffix.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@pscode").Value = Me.ddlLocation.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@location").Value = Me.txtLocation.Text
        Me.OdbcAddCustomer.Parameters.Item("@city").Value = Me.txtCity.Text
        Me.OdbcAddCustomer.Parameters.Item("@state").Value = Me.ddlState.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@zip").Value = Me.txtZip.Text
        Me.OdbcAddCustomer.Parameters.Item("@country").Value = Me.ddlCountry.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@custype").Value = Me.rblCutomerType.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@companynum").Value = Me.Session.Item("company")
        Me.OdbcAddCustomer.Parameters.Item("@ID").SourceColumn = "primaryServiceID"
        Me.OdbcAddCustomer.Parameters.Item("@ID").Direction = ParameterDirection.Output
        'Me.Session.Add("ID", Me.OdbcAddCustomer.Parameters("@ID").Value)
        Me.OdbcLocalConnection.Open()
        Try
            Me.OdbcAddCustomer.ExecuteNonQuery()
            Me.OdbcLocalConnection.Close()

I recieve an error:

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'adtAddCustomer' expects parameter '@ID', which was not supplied.AddCustomer

What do I need to change to fix this?
0
m_evergreen
Asked:
m_evergreen
  • 5
  • 4
  • 2
  • +3
1 Solution
 
Kevin3NFCommented:
It would appear your parameters are not getting to the SP.  
0
 
TimCotteeCommented:
Hi m_evergreen,
>      @ID numeric output

Needs to have a default applied to it so that it doesn't need to be supplied:

     @ID numeric = -1 output

For example, this will allow the sp to be called without supplying a value for the ID parameter but it will still return the correct value from the sp


Tim Cottee
0
 
Kevin3NFCommented:
My bad...I did not notice teh distinction between ID and customerID....sorry about that
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!

 
m_evergreenAuthor Commented:
Tim Cottee,

Thank you,

@ID numeric = -1 output

eliminates my error, but now I seem to recieve a value of dbnull.

I'm using

? Me.OdbcAddCustomer.Parameters("@ID").Value

to look at the result, do you know if my method is incorrect, or if I need to change something else to get the true value.

Thank you for the help.  I will open a different question for this if you don't know.

-M
0
 
Anthony PerkinsCommented:
If IDENTITY column is an integer than change this:
     @ID numeric output

To:
     @ID integer output

The same applies to companynum

If they are in fact numeric than you may need to define the precion and scale.

Also, can you post all the relevant code.  Where you define the parameters (data type, length, etc) and where you read the value of Me.OdbcAddCustomer.Parameters("@ID").Value
0
 
Anthony PerkinsCommented:
Tim,

>>Needs to have a default applied to it so that it doesn't need to be supplied:<<
I beg to differ with you on this one.  If it is an output (only) parameter you do not need to declare a default value.  What would be the point?
0
 
m_evergreenAuthor Commented:
Well, I tried consulting a senior programmer at my company and the problem continues.  He wasn't able to resolve it either.  After all of our changes here is the conclusion.  If we provide a default in the stored procedure we get back dbnull.  If we do not we receive an error:

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'adtAddCustomer' expects parameter '@ID', which was not supplied.AddCustomer

In the Table we have:

primaryServiceID    numeric    9

In the Stored Procedure:

CREATE procedure dbo.adtAddCustomer
      @customerid varchar(50),
      @name varchar(50),
      @housenum int,
      @housesuffix varchar(10),
      @directionpreffix varchar(2),
      @streetname varchar(50),
      @directionsuffix varchar(2),
      @pscode varchar(10),
      @location varchar(10),
      @city varchar(30),
      @state varchar(2),
      @zip varchar(11),
      @country varchar(3),
      @custype varchar(20),
      @companynum numeric,
      @id numeric = -1 output
as

Insert into customer(customerAccountIDVar, customerName, houseNum, houseNumSuffix, directionalPreffix, streetName, directionalSuffix, locationPSCode, location, city, state, zipCode, country, customerType, companyNumber)
values(@customerid, @name, @housenum, @housesuffix, @directionpreffix, @streetname, @directionsuffix, @pscode, @location, @city, @state, @zip, @country, @custype, @companynum)
--select @ID = scope_identity()
Set @id = @@Identity
GO

Defining the command:

        'OdbcAddCustomer
        '
        Me.OdbcAddCustomer.CommandText = "{? = CALL dbo.[adtAddCustomer]( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )}"
        Me.OdbcAddCustomer.CommandType = System.Data.CommandType.StoredProcedure
        Me.OdbcAddCustomer.Connection = Me.OdbcLocalConnection
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@RETURN_VALUE", System.Data.Odbc.OdbcType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@customerid", System.Data.Odbc.OdbcType.VarChar, 50))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@name", System.Data.Odbc.OdbcType.VarChar, 50))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@housenum", System.Data.Odbc.OdbcType.Int, 4))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@housesuffix", System.Data.Odbc.OdbcType.VarChar, 10))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@directionpreffix", System.Data.Odbc.OdbcType.VarChar, 2))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@streetname", System.Data.Odbc.OdbcType.VarChar, 50))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@directionsuffix", System.Data.Odbc.OdbcType.VarChar, 2))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@pscode", System.Data.Odbc.OdbcType.VarChar, 10))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@location", System.Data.Odbc.OdbcType.VarChar, 10))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@city", System.Data.Odbc.OdbcType.VarChar, 30))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@state", System.Data.Odbc.OdbcType.VarChar, 2))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@zip", System.Data.Odbc.OdbcType.VarChar, 11))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@country", System.Data.Odbc.OdbcType.VarChar, 3))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@custype", System.Data.Odbc.OdbcType.VarChar, 20))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@companynum", System.Data.Odbc.OdbcType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(18, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        Me.OdbcAddCustomer.Parameters.Add(New System.Data.Odbc.OdbcParameter("@id", System.Data.Odbc.OdbcType.Numeric, 9, System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

and running the command

    Private Sub btnContinue_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnContinue.Click
        Me.OdbcAddCustomer.Parameters.Item("@customerid").Value = Me.txtcustomerid.Text
        Me.OdbcAddCustomer.Parameters.Item("@name").Value = Me.txtName.Text
        Me.OdbcAddCustomer.Parameters.Item("@housenum").Value = CInt(Me.txtHouseNum.Text)
        Me.OdbcAddCustomer.Parameters.Item("@housesuffix").Value = Me.txtHouseNumSuffix.Text
        Me.OdbcAddCustomer.Parameters.Item("@directionpreffix").Value = Me.ddlDirPrefix.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@streetname").Value = Me.txtStreet.Text
        Me.OdbcAddCustomer.Parameters.Item("@directionsuffix").Value = Me.ddlDirSuffix.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@pscode").Value = Me.ddlLocation.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@location").Value = Me.txtLocation.Text
        Me.OdbcAddCustomer.Parameters.Item("@city").Value = Me.txtCity.Text
        Me.OdbcAddCustomer.Parameters.Item("@state").Value = Me.ddlState.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@zip").Value = Me.txtZip.Text
        Me.OdbcAddCustomer.Parameters.Item("@country").Value = Me.ddlCountry.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@custype").Value = Me.rblCutomerType.SelectedValue
        Me.OdbcAddCustomer.Parameters.Item("@companynum").Value = CDec(Me.Session.Item("company"))
        Me.OdbcAddCustomer.Parameters.Item("@id").Value = -1


        'Me.OdbcAddCustomer.Parameters.Item("@ID").SourceColumn = "primaryServiceID"
        'Me.OdbcAddCustomer.Parameters.Item("@ID").Direction = ParameterDirection.Output
        Me.OdbcLocalConnection.Open()

        'Dim dtID As System.Data.DataTable
        Try
            Me.OdbcAddCustomer.ExecuteNonQuery()
            'Me.Session.Add("ID", Me.OdbcAddCustomer.Parameters("@ID").Value)
        Catch ex As Exception
            lblError.Text = ex.Message & "AddCustomer"
            onmyerror()
        Finally
            Me.OdbcLocalConnection.Close()
        End Try

All of your help and comments are very appreciated, I hope you can help me resolve this.  When I have trouble it is often a simple mistake, but this time we just can't find it...

Thank you!!!
-M
0
 
Anthony PerkinsCommented:
I believe I see what the problem is:  In a nutshell your CommandText contradicts CommandType, but more on that later.  First we need to make sure your stored procedure is correct.

Modify your stored procedure as follows:
CREATE procedure dbo.adtAddCustomer
     @customerid varchar(50),
     @name varchar(50),
     @housenum int,
     @housesuffix varchar(10),
     @directionpreffix varchar(2),
     @streetname varchar(50),
     @directionsuffix varchar(2),
     @pscode varchar(10),
     @location varchar(10),
     @city varchar(30),
     @state varchar(2),
     @zip varchar(11),
     @country varchar(3),
     @custype varchar(20),
     @companynum integer,
     @id integer output

As

SET NOCOUNT ON

Insert customer(      customerAccountIDVar,
            customerName,
            houseNum,
            houseNumSuffix,
            directionalPreffix,
            streetName,
            directionalSuffix,
            locationPSCode,
            location,
            city,
            state,
            zipCode,
            country,
            customerType,
            companyNumber)
Values      (@customerid,
            @name,
            @housenum,
            @housesuffix,
            @directionpreffix,
            @streetname,
            @directionsuffix,
            @pscode,
            @location,
            @city,
            @state,
            @zip,
            @country,
            @custype,
            @companynum)

Set @id = Scope_Identity()
GO
These are the changes I made:
1. Changed the numeric data types from numeric to integer
2. Removed the default column for the output parameter
3. Replaced @@IDENTITY with Scope_Identity()

Now executed from SQL Query Analyzer as follows:
Declare @ID integer
Exec dbo.adtAddCustomer 'insert value of customerid here',
                                    'insert value of name here',
                                    insert value of housenum here,
                                    'insert value of housesuffix here',
                                    'insert value of directionpreffix here',
                                    'insert value of streetname here',
                                    'insert value of directionsuffix here',
                                    'insert value of pscode here',
                                    'insert value of location here',
                                    'insert value of city here',
                                    'insert value of state here',
                                    'insert value of zip here',
                                    'insert value of country here',
                                    'insert value of custype here',
                                    insert value of companynum here,
                                    @ID OUTPUT
Select @ID

Do you get a value back for @ID?
0
 
m_evergreenAuthor Commented:
Yes! I recieved a value!  This works.  I still don't get anything in my program, but I do in Query Analyzer, so now there must be a problem in my code.  Should I open a new question for the coding problem and if I do would you be willing/able to help me with it?

-M
0
 
Anthony PerkinsCommented:
>>so now there must be a problem in my code. <<
I know there is, I alluded to it earlier on.  I just wanted you to feel confident that the Stored Procedure was correct, so that you could focus on the VB.NET code.

>>Should I open a new question for the coding problem and if I do would you be willing/able to help me with it?<<
That is entirely up to you.  Since it is evidently a VB.NET problem, you may get more response in that Topic Area.  If you do than:
1. Include a reference to the new question here.
2. Get ready to explain unrelated questions as to why you are using ODBC and not SQLClient.
3. And (less unrelated) for you to post where you are declaring OdbcAddCustomer.

If on the other hand you wish to pursue it here, just let us know.
0
 
nmcdermaidCommented:
I have alawys used an inputoutput type as opposed to just output, and supplied a value of zero when you calling it.

This was in VB6.
0
 
Anthony PerkinsCommented:
nmcdermaid,

If the parameter is not required as input than there is no need to give it a value.
0
 
rafranciscoCommented:
Another option for you is, instead of using an OUTPUT parameter, why not just return the SCOPE_IDENTITY() as a result set.  So your stored procedure will be like this (using acperkins' code above):

CREATE procedure dbo.adtAddCustomer
     @customerid varchar(50),
     @name varchar(50),
     @housenum int,
     @housesuffix varchar(10),
     @directionpreffix varchar(2),
     @streetname varchar(50),
     @directionsuffix varchar(2),
     @pscode varchar(10),
     @location varchar(10),
     @city varchar(30),
     @state varchar(2),
     @zip varchar(11),
     @country varchar(3),
     @custype varchar(20),
     @companynum integer

As

SET NOCOUNT ON

Insert customer(     customerAccountIDVar,
          customerName,
          houseNum,
          houseNumSuffix,
          directionalPreffix,
          streetName,
          directionalSuffix,
          locationPSCode,
          location,
          city,
          state,
          zipCode,
          country,
          customerType,
          companyNumber)
Values     (@customerid,
          @name,
          @housenum,
          @housesuffix,
          @directionpreffix,
          @streetname,
          @directionsuffix,
          @pscode,
          @location,
          @city,
          @state,
          @zip,
          @country,
          @custype,
          @companynum)

SELECT Scope_Identity() AS ID
GO

Then in your VB.NET code, you can use the ExecuteScalar() method which will return the ID as a result set and you can assign it to wherever you want (I am not really a VB.NET person but a C#/ASP.NET guy and I am assuming that there is an ExecuteScalar() method, which exists in C#/ASP.NET/ADO.NET.
0
 
m_evergreenAuthor Commented:
Thank you much for all of your help and comments.  In the end the revised stored procedure from acperkins and a long argument here at work over odbc vs. sql connections resolved this issue.  I was given the go ahead to switch over to sql which I'm used to working with, and when I ran with the changes everything worked!

-M
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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