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

SQL output Parameter in Visual basic Winforms App

Hi,
I have a stored proc that inserts Items in a master table. I then get the ScopeID from that insert and try to insert that in the detail table. I get an error saying :
Procedure 'CupMaster' expects parameter '@CupTableID' which was not supplied.
Looking at my code I think I have what I need, but I still get the error message. Here is my code:
Stored Proc:
ALTER PROCEDURE [dbo].[CupMaster]
@CustomerId int
, @CupTotal money
, @CupTableID INT OUTPUT
AS
Begin
INSERT INTO CupTable
(
[CustomerID]
,[CupTotal])
     VALUES
(@CustomerId
,@CupTotal
)
Set @CupTableID = SCOPE_IDENTITY()
End
My Code:
Public Shared Function AddMaster(ByVal item As CupMaster) As Integer
        Dim objSql As New SQL()
        Dim CupDetailsParam(2) As SqlParameter
        CupDetailsParam(0) = New SqlParameter("@CustomerID", item.CustomerID)
        CupDetailsParam(1) = New SqlParameter("@CupTotal", item.CupTotal)
        CupDetailsParam(2) = New SqlParameter("@CupTableID", item.CupTableID, 4, ParameterDirection.Output)
        objSql.ExecuteNonQuery("CupMaster", CupDetailsParam)
        Return CupDetailsParam("@CupTableID").Value
    End Function
Button Click calls:
Private Function TestingOutput()
Dim New_ID As Integer
Dim Item As New CupMaster
Item.CustomerID = ACBox.SelectedValue
Item.CupTotal = ctTBox.Text
Item.CupTableID = New_ID
New_ID = Cup.AddMaster(Item)
Return New_ID
End Function
0
ALawrence007
Asked:
ALawrence007
  • 4
  • 4
1 Solution
 
Daniel WilsonCommented:
You're not actually setting the value.  Putting the value in the SqlParameter constructor doesn't do what those of us w/ VB6 ADO experience would expect.

try this:

Public Shared Function AddMaster(ByVal item As CupMaster) As Integer
        Dim objSql As New SQL()
        Dim CupDetailsParam(2) As SqlParameter
        CupDetailsParam(0) = New SqlParameter("@CustomerID", SqlDbType.int)
         CupDetailsParam(0) .Value = item.CustomerID
        CupDetailsParam(1) = New SqlParameter("@CupTotal", SqlDbType.Int)
          CupDetailsParam(1).value = item.CupTotal
        CupDetailsParam(2) = New SqlParameter("@CupTableID", item.CupTableID, 4, ParameterDirection.Output)
        objSql.ExecuteNonQuery("CupMaster", CupDetailsParam)
        Return CupDetailsParam("@CupTableID").Value
    End Function
0
 
Daniel WilsonCommented:
Ooops, failed to correct the (2) item:


Public Shared Function AddMaster(ByVal item As CupMaster) As Integer
        Dim objSql As New SQL()
        Dim CupDetailsParam(2) As SqlParameter
        CupDetailsParam(0) = New SqlParameter("@CustomerID", SqlDbType.int)
         CupDetailsParam(0) .Value = item.CustomerID
        CupDetailsParam(1) = New SqlParameter("@CupTotal", SqlDbType.Int)
          CupDetailsParam(1).value = item.CupTotal
        CupDetailsParam(2) = New SqlParameter("@CupTableID", SqlDbType.Int, 4, ParameterDirection.Output)
          CupDetailsParam(2).Value = item.CupTableID
        objSql.ExecuteNonQuery("CupMaster", CupDetailsParam)
        Return CupDetailsParam("@CupTableID").Value
    End Function
back to top
0
 
ALawrence007Author Commented:
DanielWilson,

Finally I got beond my error. I have been trying to do this for a while now. I do have another error now saying :
Conversion from string "@CupTableID" to type 'Integer' is not valid

Any Ideas?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Daniel WilsonCommented:
Can you show me the code as it now stands?
0
 
ALawrence007Author Commented:
My button Click :
Button1_click
Dim ScopeID As Integer
ScopeID = TestingOutput()
End sub

Private Function TestingOutput()
Dim New_ID As Integer
Dim Item As New CupMaster
Item.CustomerID = ACBox.SelectedValue
Item.CupTotal = ctTBox.Text
Item.CupTableID = New_ID
New_ID = Cup.AddMaster(Item)
Return New_ID
End Function


Public Shared Function AddMaster(ByVal item As CupMaster) As Integer
Dim objSql As New SQL()
Dim CupDetailsParam(2) As SqlParameter
CupDetailsParam(0) = New SqlParameter("@CustomerID", SqlDbType.int)
CupDetailsParam(0).Value = item.CustomerID
CupDetailsParam(1) = New SqlParameter("@CupTotal", SqlDbType.Int)
CupDetailsParam(1).value = item.CupTotal
CupDetailsParam(2) = New SqlParameter("@CupTableID", SqlDbType.Int, 4, ParameterDirection.Output)
CupDetailsParam(2).Value = item.CupTableID
objSql.ExecuteNonQuery("CupMaster", CupDetailsParam)
Return CupDetailsParam("@CupTableID").Value
End Function

Let me know if you need more.



0
 
Daniel WilsonCommented:
http://msdn2.microsoft.com/en-us/library/aa326287(vs.71).aspx

If I'm seeing this right ... the only constructor that allows the provision of an parameter direction requires a bunch more parameters.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter_members(VS.71).aspx



suppose you  try:

CupDetailsParam(2) = New SqlParameter("@CupTableID", SqlDbType.Int)
cupDetailsParam(2).Direction = ParameterDirection.Output
CupDetailsParam(2).Value = item.CupTableID
0
 
ALawrence007Author Commented:
Daniel,

I tried everything last night, but I keep on getting the same error: Conversion from string "@CupTableID" to type 'Integer' is not valid.

Here is my Cup Class as well.
Private IntCupTableID As Integer
Public Property CupTableID() As Integer
Get
Return IntCupTableID
End Get
Set(ByVal value As Integer)
IntCupTableID = value
End Set
End Property

Can you please let me know your thoughts?
Thanks
0
 
ALawrence007Author Commented:
Daniel,

I found a solution. I had to add my New_ID in my AddMaster function and return that New_ID.

Thanks for the help.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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