?
Solved

Unable to return Output parameters from Stored Proc in Dot Net   ---specified cast is not valid ---

Posted on 2003-03-28
18
Medium Priority
?
443 Views
Last Modified: 2010-05-18
I had noticed this topic being posted before but no real solution. What I and others are trying to do is...
from an ASPX page call upon a component's function that hits a database using a stored procedure, and return the output parameter.

My specifics are adding a new user to a DB, then return the @@Identity for the use of the next page after the user is added. The stored proc works, I can run from a VB6, Query Analyzer, or ASP page and get the expected results. So the problem is something I and others are doing in Dot Net. I receive the error "specified cast is not valid " when attempting to do execute the procedure and return the output parameter. I've tried using different data types, but doesn't change the results.

Code for NewUser Function...

Imports System.Data

Imports System.Data.SqlClient

 

 

Public Class cLogin

    Inherits cConnection       'Connection Object
 

   
Public Function NewRefPortalUser(ByVal UserName As String, ByVal Password As String, ByVal FirstName As String, ByVal LastName As String) As VariantType

 

        Dim objCrypto As EncryptIt      'Declare Encryption object

 

        Dim varID As VariantType        'Declare variable for ID of new record

 

        objCrypto = New EncryptIt()     'Instantiate Encryption object

 

        Password = objCrypto.EncryptString(Password)    'Encrypt the password in DB

 

        objCrypto = Nothing             'Destroy Encryption object

 

        Dim MyCommand As SqlCommand = New SqlCommand("usp_NewRefPortalUser_INS")

 

        MyCommand.CommandType = CommandType.StoredProcedure

 

        Dim workParam As SqlParameter = Nothing

 

        MyCommand.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.VarChar, 30))

        MyCommand.Parameters("@FirstName").Value = FirstName

 

        MyCommand.Parameters.Add(New SqlParameter("@LastName", SqlDbType.VarChar, 30))

        MyCommand.Parameters("@LastName").Value = LastName

 

        MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.VarChar, 50))

        MyCommand.Parameters("@UserName").Value = UserName

 

        MyCommand.Parameters.Add(New SqlParameter("@Password", SqlDbType.VarChar, 50))

        MyCommand.Parameters("@Password").Value = Password


        workParam = MyCommand.Parameters.Add(New SqlParameter("@Identity", SqlDbType.Variant, 4))

        workParam.Direction = ParameterDirection.Output


        Connect(True)                   'Connect to DB

        MyCommand.Connection = CNx

        MyCommand.ExecuteScalar()

        varID = MyCommand.Parameters("@Identity").Value

        MyCommand.Dispose()

        MyCommand = Nothing  
 
        Connect(False)

        Return varID

End Function



Here is the code from the ASPX.vb page

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim objportal As clsRefPortalLogin.cLogin

        objportal = New clsRefPortalLogin.cLogin()

        Dim varID As VariantType

        varID = objportal.NewRefPortalUser("Jeremy", "powerfull", "Jeremy", "Pettit")  

        objportal = Nothing

        Response.Write(varID)
       

    End Sub

0
Comment
Question by:jeremypettit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
  • +2
18 Comments
 
LVL 6

Expert Comment

by:CarlosMu
ID: 8226198
can you post the stored procedure?
0
 
LVL 6

Expert Comment

by:thomasdodds
ID: 8226565
seems that the examples I have show using the .ExecuteNonQuery() and not using the .ExecuteScalar() ...
0
 
LVL 6

Expert Comment

by:thomasdodds
ID: 8226595
Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database.

--------------------------------------------------------

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

Although the ExecuteNonQuery does not return any rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

0
Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

 
LVL 1

Author Comment

by:jeremypettit
ID: 8226607
Stored Proc here...

CREATE PROCEDURE usp_NewRefPortalUser_INS

(

            @UserName varchar(50),

            @Password varchar(50),

            @FirstName varchar(30),

            @LastName varchar(30),

            @Identity int out

)

 

AS

 

 

INSERT INTO

            tblRefPortalUsers

            (

            Email,

            PortalPassword,

            FirstName,

            LastName

            )

VALUES

            (

            @UserName,

            @Password,

            @FirstName,

            @LastName

            )

 

SELECT  @Identity = @@Identity

RETURN
0
 
LVL 1

Author Comment

by:jeremypettit
ID: 8226670
ExecuteNonQuery  Produces same results

Here's a detailed error...

Specified cast is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Specified cast is not valid.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
 


Stack Trace:

 
[InvalidCastException: Specified cast is not valid.]
   clsRefPortalLogin.cLogin.NewRefPortalUser(String UserName, String Password, String FirstName, String LastName) +584
   PortalComponentTest.WebForm1.Button1_Click(Object sender, EventArgs e) +58
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1263
 

 
0
 
LVL 6

Expert Comment

by:thomasdodds
ID: 8226722
      workParam = MyCommand.Parameters.Add(New SqlParameter("@Identity", SqlDbType.Variant, 4))

       workParam.Direction = ParameterDirection.Output

and

           @Identity int out

don't match!!  thus: Specified cast is not valid.
0
 
LVL 6

Expert Comment

by:thomasdodds
ID: 8226727
also keep the .ExecuteNonQuery method of your command object
0
 
LVL 1

Author Comment

by:jeremypettit
ID: 8226774
ExecuteNonQuery  Produces same results

Here's a detailed error...

Specified cast is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Specified cast is not valid.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
 


Stack Trace:

 
[InvalidCastException: Specified cast is not valid.]
   clsRefPortalLogin.cLogin.NewRefPortalUser(String UserName, String Password, String FirstName, String LastName) +584
   PortalComponentTest.WebForm1.Button1_Click(Object sender, EventArgs e) +58
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1263
 

 
0
 
LVL 1

Author Comment

by:jeremypettit
ID: 8226793
workParam = MyCommand.Parameters.Add(New SqlParameter("@Identity", SqlDbType.Variant, 4))
 Was changed after while trying different variable types, same error with the type of int
0
 
LVL 6

Expert Comment

by:thomasdodds
ID: 8226808
then use a datatype that you know is compatible from ADO.NET and SQL Server ... I know that Numeric (in Oracle) must be Decimal in ADO.NET ... but if you don't know those types of conversions then pass it out as a string and receive it as a string then convert is to the numeric or what ever other datatype you need to work with it ... just a suggestion, as I don't have a working .NET project with SQL Server on the go right now ...
0
 
LVL 1

Author Comment

by:jeremypettit
ID: 8226819
workParam = MyCommand.Parameters.Add(New SqlParameter("@Identity", SqlDbType.Variant, 4))
 Was changed after while trying different variable types, same error with the type of int
0
 
LVL 1

Author Comment

by:jeremypettit
ID: 8227199
I was able to fix. I changed the Parameter direction from workParam.Direction = ParameterDirection.Output
to
workParam.Direction = ParameterDirection.InputOutput

and sent it a value of 0

then to get the return value...
varID = MyCommand.Parameters("@Identity").Value.ToString
0
 
LVL 6

Expert Comment

by:thomasdodds
ID: 8227226
good fix ...
0
 
LVL 12

Expert Comment

by:farsight
ID: 8227332
I see you've got "a" fix, but I've already written this up, so here it is.  By the way, in sample 2 I've successfully tested:
  SqlDbType.Int, Integer, and int ... in place of ...
  SqlDbType.Variant, VariantType, and sql_variant.

I have no problems with ParameterDirection.Output.  I think your fix was just using ToString.

(Guess: Sounds like you're a VBScript // ASP programmer extending your skills.  In regard to type-conversion issues, I highly recommend using "Option Strict On" at the top of ALL your VB.NET code.  It requires just a tiny bit more work up-front, but will save you lots of headaches later.)

----- ----- -----

[Using Win2000 SP3, SQL Server 2000, Northwind database sample]

I'm not sure why you're using variant so early.  I think the only place you need to use VariantType is the return value of NewRefPortalUser()

Here's a couple of examples that work.  Of course, if your stored procedure uses some other data type, you'll need to use it's data type for the parameter and then use CType() to convert it to almost whatever you want.


SAMPLE 1: Just uses the return value.  No parameter needed.


VB.NET:

Dim cmd1 As New SqlCommand("Sample1_INS")
cmd1.CommandType = CommandType.StoredProcedure
cmd1.Connection = conn
Dim varID1 As VariantType
varID1 = CType(cmd1.ExecuteScalar(), VariantType)
cmd1.Dispose()
cmd1 = Nothing

T-SQL:

CREATE PROCEDURE Sample1_INS
AS
INSERT INTO [Northwind].[dbo].[Employees]([LastName], [FirstName])
VALUES('Smith', 'Bill' )
SELECT @@IDENTITY

----- ----- -----

SAMPLE 2: Uses an output parameter of type SqlDbType.Variant

VB.NET:

Dim cmd2 As New SqlCommand("Sample2_INS")
cmd2.CommandType = CommandType.StoredProcedure
cmd2.Connection = conn
Dim workParam2 As SqlParameter = Nothing
workParam2 = cmd2.Parameters.Add(New SqlParameter("@Identity", SqlDbType.Variant))
workParam2.Direction = ParameterDirection.Output
cmd2.ExecuteScalar()
Dim varID2 As VariantType
varID2 = CType(cmd2.Parameters("@Identity").Value, VariantType)
cmd2.Dispose()
cmd2 = Nothing

T-SQL:

CREATE PROCEDURE Sample2_INS (@Identity sql_variant output)
AS
INSERT INTO [Northwind].[dbo].[Employees]([LastName], [FirstName])
VALUES('Smith', 'Bill' )
SET @Identity = (SELECT @@IDENTITY AS 'Identity')

0
 
LVL 1

Author Comment

by:jeremypettit
ID: 8227336
I was able to fix. I changed the Parameter direction from workParam.Direction = ParameterDirection.Output
to
workParam.Direction = ParameterDirection.InputOutput

and sent it a value of 0

then to get the return value...
varID = MyCommand.Parameters("@Identity").Value.ToString
0
 
LVL 1

Author Comment

by:jeremypettit
ID: 8227362
I did replace the variants back to integers, was originally using them, but tried variants to get around the type conversion thing. Thanks for your help and feedback
0
 

Expert Comment

by:modulo
ID: 8430239
Dear expert(s),

A request has been made to close this Q in CS:
http://www.experts-exchange.com/Community_Support/Q_20601596.html

Without a response in 72 hrs, a moderator will finalize this question by:

 - Saving this Q as a PAQ and refunding the points to the questionner

When you agree or disagree, please add a comment here.

Thank you.

modulo

Community Support Moderator
Experts Exchange
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 8449400
Saving this Q as a PAQ and refunding the 75 points to the questionner

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Suggested Courses

743 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