Return value from Stored Procedure #3

Hi

The codes below run smoothly on my Windows Based Application BUT when I apply it on my Web Application error hit - "Operand type clash: text is incompatible with int"

Dim tempRV As String

Dim retValParam As New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int)

retValParam.Direction = ParameterDirection.ReturnValue

With Conn.Command

.CommandText = "usp_FProcessStdTestResult"

.CommandType = CommandType.StoredProcedure

.Connection = oConn.Connection

.CommandTimeout = 0

.Parameters.Add(retValParam)

oConn.addParam("@pUserID", UserID)

oConn.addParam("@pPassword", UserPass)

oConn.addParam("@pSerialNbr", tempID)

End With

Conn.Open()

Conn.Command.ExecuteNonQuery()

tempRV = CStr(retValParam.Value)

Conn.Close

 

So anything wrong with the above coding?

 

Thanks in advance. 

Henry Han

0
HenryHan
2/17/2009 9:18:42 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

4 Replies
502 Views

Similar Articles

[PageSpeed] 45

tempRV = CStr(retValParam.Value)

cast as string or varchar
Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

0
AvanthaSiriwardana
2/17/2009 11:04:47 AM

tempRV = CStr(retValParam.Value.ToString())


Mark as Answer if it helped you

Gayathri
0
gayathri
2/17/2009 11:43:58 AM

Hi both,

Thanks for the suggestion. But the result still the same after adding .ToString. Any other way to do it?

Again thanks.

Henry Han 

 

0
HenryHan
2/18/2009 5:37:22 AM

 Hi,HenryHan

I have tested with below code and it is running succesfully 

U have not specified datatypes 

If u have further  queries then u should also post  ur database entities also that u r i=using,show the script of ur proc

 

 

        Dim UserID As String
        Dim UserPass As String
        Dim tempID As String


        UserID = ""
        UserPass = ""
        tempID = ""



        Dim Conn As SqlConnection
        Dim cmd As New SqlCommand
        Conn = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("TestConn").ConnectionString)


        Dim tempRV As String
        Dim retValParam As New SqlClient.SqlParameter("@RETURN_VALUE", SqlDbType.Int)

        retValParam.Direction = ParameterDirection.ReturnValue


        Dim SqlParameter1 As New SqlParameter("@pUserID", SqlDbType.VarChar, 20, ParameterDirection.Input)
        Dim SqlParameter2 As New SqlParameter("@pPassword", SqlDbType.VarChar, 20, ParameterDirection.Input)
        Dim SqlParameter3 As New SqlParameter("@pSerialNbr", SqlDbType.VarChar, 20, ParameterDirection.Input)




        With cmd

            .CommandText = "usp_FProcessStdTestResult"

            .CommandType = CommandType.StoredProcedure

            .Connection = Conn

            .CommandTimeout = 0


            .Parameters.Add(SqlParameter1)
            .Parameters.Add(SqlParameter2)
            .Parameters.Add(SqlParameter3)

            .Parameters.Add(retValParam)

            .Parameters.Item("@pUserID").Value = UserID
            .Parameters.Item("@pPassword").Value = UserPass
            .Parameters.Item("@pSerialNbr").Value = tempID



        End With

        Conn.Open()

        cmd.ExecuteNonQuery()

        tempRV = CStr(retValParam.Value)

        Conn.Close()
  
RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
2/18/2009 6:40:44 AM
Reply:

Similar Artilces:

How to Retrive the store procedure return value in Ado.Net by using Dot Net?
Hi i am thiru. i try to create the small search engine. so that i have wrote the store procedure for it. thats given below.Create procedure FindtestThree @Name varchar(25), @Location varchar(25), @EmpId varchar(25) as select * from TbInfo where Name like + '%' + @Name + '%' select * from TbInfo where Location like + '%' + @Location + '%' select * from hi where ComName like + '%' + @EmpId + '%' Exec FindtestThree 'aa','a','d' ----------------------------------------- The above procedure return t...

Return Value from SQL Stored Procedure is Wrong
Hi,I've got two very similar stored procedures that are supposed to provide a Return Value. The procedure "procCheckForDuplicateName" returns the correct value, while "procCheckForDuplicateEmail" always returns "2", which is incorrect. There are 6 records in the database that share the same data for FirstName, LastName, and Email. Thus, both procedures should be returning "6", but only "procCheckForDuplicateName" returns the correct value.Can anyone explain why this is happening?Here are the stored procedures: ALTER PROCEDURE procChe...

accessing stored procedure return values #3
I am trying get a return value from a stored procedure. I see a result when I use SQL Advantage, but trying to access the return value from a PD template returns nothing: query = connection.CreateQuery( "declare @next_id char(14) execute sp_msg_mgr_next_id @next_id output SELECT @next_id go" ); while ( query.MoveNext() ) { msg_id = query.GetValue( "@next_id" ); } document.WriteLn( "Message Id: " + msg_id ); Prints out 0 in PD, but in SQL Advantage I get the correct value -- Ernesto Gonzalez Web Developer Sybase Inc....

How do I use the return value from a SQL stored procedure?
I want to be able to use the returned number to set a Label accordingly.Something like this:if the return value == 1 then Label1.Text = "Correct" else Label1.Text = "Wrong" What is the correct syntax for this?  I couldn't find this answer. If you've just got one return parameter, I usually stick with ExecuteScalar. This will execute a stored procedure and return an object, which is taken from the first row and first column of whatever the stored procedure returns.So at the end of the stored procedure, after doing what you need to do, add a select stat...

Value that is returned if an insert is not performed from a SQL stored procedure.
I am using the following stored procedure to insert a value into the database. I am new to stored procedures so I need help. Basically this stored procedurewill only insert a value for "CustomerName" if it is not a duplicate value. So I noticed in My C# when I call this stored procedure, a negative one "-1"is returned if the insert is not performed because of a duplicate value. Is that correct? Should I be getting back a negative one "-1" ? You seeI thought that a Zero "0" would be returned if the insert was not performed.Not a negative one? SET ANS...

Calling SQL 2005 stored procedure that returns a value
I have a stored procedure on a SQL server, which is workign correctly to check some date/time parameters and then insert a row into a log table. I am calling it from an ASP page.   Initially I just called it and didn't worry about the return value.  However the Stored procedure now will return a value to determine if it made the change or not and if not why (ie log entry was at incorrect time etc).  I woudl liek to capture this returned value in my code to display a message to the user but am havign problems finding the right way to get the value. I am calling the SP a...

help with stored procedure returning a value (and then retrieving it in .NET)
Hi guys,  The methods for connecting to the db, various functions that use (executescalar() etc), were not written by me.  This will be my first time dabbling with these functions... So I am in the process of writing a stored procedure.  All it does is scan tbl_inv_finalized to see if a user has finalized (there could be multiple records, i.e. employeeX | 7/8/2008 10:12:47 AM | 2009 (school year) | 330 (building number) employeeX | 5/30/2008 10:00:00 AM | 2008 | 330 The goal here is that... there is another table that will define the 'finalize periods' throughout t...

DBD:ODBC and SQL Server, return value from stored procedure
Hi, I need the return value from a stored procedure. Usually I do like this: $sth = $dbh->prepare('Declare @RETURN_VALUE int; exec @RETURN_VALUE=some_procedure @param0=?, @param1=?; select @RETURN_VALUE') but that will result in multiple result sets and in this special case I can't handle that. I would like to use something like this (output parameter): $sth = $dbh->prepare('exec ?=some_procedure @param0=?, @param1=?') but that won't work. Does DBD:ODBC and SQL Server support output parameters now? And if so from what version? Thanks ...

how do I return a single value from stored procedure to a function in c#.net
I have this stored procedure  ALTER PROCEDURE [dbo].[GetOrderID] (@username VARCHAR(25)) ASSELECT MAX(order_id) FROM Orders WHERE username = @username; ******************************************************* and this functionpublic static int GetOrderId(CartProspect cs) {int order_id = -1; SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myconnectionstring"].ConnectionString);SqlCommand cmd = new SqlCommand("GetOrderID", conn); cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add("@u...

Capturing return values from MS SQL 7.0 Store Procedure
Hi, I have used DATAWINDOW (in DATAWINDOW PAINTER) to get the result sets from my Store Procedure. How can I get the return value from my store procedure? Below is the codes for my store procedure, CREATE PROCEDURE spEDIExport_tblDetailTaking @FromDate datetime, @ToDate datetime AS Select Distinct DET.FilmCode, FM.FilmTitle From tblDetailTaking DET, tblFilmMaster FM Where DET.FilmCode Not In (Select Distinct FilmCode from tblEDIFilmReference) AND DET.FilmCode = FM.FilmCode If @@RowCount = 0 Begin Select ECR.CompanyCode As CompanyCode, ECR.EDI...

SQL 2005 stored procedure not returning output value to web app
I am having probelms trying to get a stored proedure to return an output value. The outline of the specific request is that I supply a varchar which is unique within a set of tables (tables named in another table). I want to search each of the tables until I find the one that has the value and when found return the GUID of the row and the table name. I have not been successful in getting the value of the GUID (an int) to be returned. If I run in debug and stop after the SQL call I can see the value in the SQL output parameter but it does not appear in the variable I specified in the S...

Capturing return values from MS SQL 7.0 Store Procedure
Hi, I have used DATAWINDOW (in DATAWINDOW PAINTER) to get the result sets from my Store Procedure. How can I get the return value from my store procedure? Below is the codes for my store procedure, CREATE PROCEDURE spEDIExport_tblDetailTaking @FromDate datetime, @ToDate datetime AS Select Distinct DET.FilmCode, FM.FilmTitle From tblDetailTaking DET, tblFilmMaster FM Where DET.FilmCode Not In (Select Distinct FilmCode from tblEDIFilmReference) AND DET.FilmCode = FM.FilmCode If @@RowCount = 0 Begin Select ECR.CompanyCode As CompanyCode, ECR.EDI...

ADO.NET Entity Framework: How to handle both a return value AND entities from a stored procedure
Hi, I'm switching from LINQ 2 SQL to Entity Framework and I've encountered the following scenario.  I have a stored procedure that returns a return value (integer) and a recordset.  In LINQ2SQL I was able to access both the recordset and the result item.  However using EF i'm not able to see a clear way to do this. I get back an ObjectResult<T> which clearly containts the records... but where can I access the return value? Any pointers to a posting or sample would be appreciated! Brian...

MS SQl Server 7 stored procedures returning a record set and a value
I have a stored procedure that returns a record set and a value, it looks like this Create procedure sp_test as * * * * Select * from table Return @mvalue What should I do in PB ( version 6.5) to retrieve the recotd set and the value? Thanks Enrique Hey, I suppose you want to call this stored procedure in a script... Take a look at "Declare" and "Fetch" in pb help Greets, Davy "Enrique Perez" <eperezch@sympatico.ca> wrote in message news:Qug6aNF3BHA.298@forums.sybase.com... > I have a stored procedure that returns a ...

Web resources about - Return value from Stored Procedure #3 - asp.net.sql-datasource

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure
'Daily Show' Host Trevor Noah Hospitalized for Emergency Procedure

Resources last updated: 12/24/2015 10:59:09 PM