Convert returned value to Integer

I have the following stored procedure for creating a transaction record and after inserting the record, the transaction id is returned.

-----------------------------------------------------
DECLARE @TransactionID int
    <------ INSERT statement (after inserting the record, select the identity ID) --------->
        Select @TransactionID = @@Identity
    RETURN
------------------------------------------------------

.
.
.
Dim transactionID As Integer
connection.Open()
Dim reader As SqlDataReader
reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Dim p3 As New SqlParameter("@TransactionID", SqlDbType.Int)
p3.Direction = ParameterDirection.Output
transactionID = Convert.ToInt16(p3.Value)
connection.Close()
.
.
.

I wanna retrieve the transactionID of the newly inserted record so that I can use it in my next step of inserting a new record to another table which has reference to the transactionID as a foreign key. However, I encountered error and suspect that it is due to the conversion of the output to Integer as it worked when I tested using dummy Integers.

I tried many different ways of conversion but couldn't resolve the error. Could anyone help?
0
saggitarian
4/3/2006 6:30:41 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
716 Views

Similar Articles

[PageSpeed] 22
Get it on Google Play
Get it on Apple App Store

Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?
 
You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.
check if this article helps.
 

 

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
4/3/2006 11:19:59 PM
ndinakar:
Are you returning the TransactionIf through an OUTPUt parameter in the stored proc?
 
You need to use ExecuteReader if your stored proc/TSQL is returning a result set and you are trying to retrieve it through .NET. If you are retrieving values through OUTPUT parameters you would use ExecueNonQuery since ExecuteReader returns a reader which you are not using anyway. So why do you want to the overhead of the result set being passed around.
check if this article helps.
 

 


hmm.. pardon me as this is my first time with asp.net

In the article:

myCommand.Parameters.Add(New SqlParameter("@ProductId",SqlDbType.Int))

myCommand.Parameters.Direction = ParameterDirection.Output

The line in red doesn't work. I guess I'm using asp.net 2.0, that's why... so I changed to something like

command.Parameters("@ProductID").Direction = ParameterDirection.Output

Yupz.. I'm retrieving through output parameters, I tried ExecuteReader but still couldn't work.

Actually I have these two tables for a shopping cart project - OrderTransaction and OrderDetails

OrderTransaction stores general information of TransactionID, Status, ShippingAddress etc of an order. OrderDetails stores details of a particular transaction which has foreign keys references to the Ids of OrderTransaction, Customers and Products table.
Hence, the reason for retrieving the TransactionID is so that I can create records for the items in the shopping cart in the OrderDetails for the particular transaction.

Code:

Dim transactionID As Integer
Dim connection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString"))
            Dim command As SqlCommand = New SqlCommand("AddOrders", connection)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.AddWithValue("@CustomerID", Context.Session("myID"))
            command.Parameters.AddWithValue("@DateCreated", DateTime.Now.ToString())
            command.Parameters.AddWithValue("@Verified", 0)
            command.Parameters.AddWithValue("@Cancelled", 0)
            command.Parameters.AddWithValue("@CustomerName", nameTB.Text)
            command.Parameters.AddWithValue("@ShippingAddress", shippingaddressTB.Text)
            command.Parameters.AddWithValue("@CustomerEmail", emailTB.Text)
            command.Parameters.Add("@TransactionID", SqlDbType.SmallInt)
            command.Parameters("@TransactionID").Direction = ParameterDirection.Output
            connection.Open()
            command.ExecuteNonQuery()
            transactionID = Convert.ToInt16(command.Parameters("@TransactionID").Value)
            connection.Close()

Stored Procedure:

ALTER PROCEDURE AddOrders
    (
        @CustomerID int,
        @DateCreated smalldatetime,
        @Verified bit,
        @Cancelled bit,
        @CustomerName varchar(50),
        @ShippingAddress varchar(200),
        @CustomerEmail varchar(50)
    )

AS

DECLARE @TransactionID int
   
    INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
    VALUES     (@CustomerID, Convert(smalldatetime,@DateCreated), @Verified, @Cancelled, @CustomerName, @ShippingAddress, @CustomerEmail)
       
        Select @TransactionID = @@Identity
       
    RETURN


The error is
"Procedure or function AddOrders has too many arguments specified."

Line 161: command.ExecuteNonQuery()

I think that's because I did something wrong in the lines in
green. Appreciate if anyone helps. Thanks in advanced.
0
saggitarian
4/4/2006 6:23:27 AM
(1) The error is exactly what it says. You are trying to add @TransactionId as a parameter when you havent declared it as a parameter in your stored proc. modify your stored proc as follows:
(2) SCOPE_IDENTITY() is more efficient than @@IDENTITY. Check out books on line for more explanation.
(3) Since the value you are returning is through OUTPUT parameters, use ExecuteNonQuery.

ALTER PROCEDURE AddOrders
    (
        @CustomerID int,
        @DateCreated smalldatetime,
        @Verified bit,
        @Cancelled bit,
        @CustomerName varchar(50),
        @ShippingAddress varchar(200),
        @CustomerEmail varchar(50),
        @TransactionId INT OUTPUT
    )

AS

BEGIN
SET NOCOUNT ON
  
    INSERT INTO OrderTransaction (CustomerID, DateCreated, Verified, Cancelled, CustomerName, ShippingAddress, CustomerEmail)
    VALUES     (@CustomerID, Convert(smalldatetime,@DateCreated), @Verified, @Cancelled, @CustomerName, @ShippingAddress, @CustomerEmail)
        SELECT @TransactionID = SCOPE_IDENTITY()
       
SET NOCOUNT OFF
END

***********************
Dinakar Nethi
Life is short. Enjoy it.
***********************
0
ndinakar
4/4/2006 4:22:17 PM
Reply:

Similar Artilces:

How do I check to see if a value returned from a sql datasource equals a listitem value
I'm trying to see if this listitem equals a value returned in my sql data source, but I get keep getting a error saying  'Item' is not a member of 'System.Web.UI.WebControls.SqlDataSource'.    How do I check if the employeeID returned is equal to the li.value? For Each li As ListItem In BillingEmployeeID.Items If li.Value = getEmployeewithMostOpenClient.Item("employeeID") ThenxxxxxxxEnd If NextNever make important decisions on a Monday! Try Items.FindByValue() or Items.FindByText() method. Something like this... string szValueFromDataso...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

return a value from SQL server SP back to .net
I have a SP code: select 'nothing' from tableA where userID = '123' if @@rowcount = 0 return 0 else return 1 ..net code: Dim myConnection As New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes") Dim myCommand As New SqlCommand("StoreP", myConnection) myCommand.Connection.Open() Returnvalue = myCommand.ExecuteNonQuery() myCommand.Connection.Close() Returnvalue shows -1, it doesnt show the return value from SP. how do I fix this problem? thanksmother be Hi, The return value from the ExecuteNonQuery returns the ...

converting meaningful listitem value to sql value
Hi, I hope someone can help me with the below problem I have a dropdownlist that is used to narrow down the results of a gridviewIn the dropdownlist I have specified a couple of a listitem values; <asp:dropdownlist id="ptsearch" runat="server" autopostback="true"><asp:listitem>James</asp:listitem><asp:listitem>Michael</asp:listitem></asp:dropdownlist> In the sql connection part of the code behind I have set the dropdownlist selected value to be passed to a sql stored procedure variable called @user;command.Parameters.Add(&q...

ADO.NET returns different colum value when compared to View results in SQL 2005 Management Studio
I have a complex view in my sql 2005 database. The view returns a column that could be null (as the result of a left outer join). The coulmn that is returned is an integer. Everything works fine if I run the view from SQL 2005 Management Studio. My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable. Has anybody seen this behaviour before? Any help appreciated. Regards, Paul. Hi Paul, From you description, I understand that when you query data from the database view, the destination column in the DataSet is always null. This could be caused by a w...

Drop down list in Form view returns error when value in bound field (data binding
I get the following error when I click edit to edit the details in the web form:'ddlUserRestrictionType' has a SelectedValue which is invalid because it does not exist in the list of items.Parameter name: value  I would like it to accept any value which currently exists in the database. Is this easily achievable? With what you've posted, all I can tell is that you are trying to set the SelectedValue of a ddl to a value that does not exist in the list.  It would be helpful if you can post some code and the expected values of this control.~ Timwww.bucketo...

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...

SQL query returns data, .NET returns #42S22Unknown column binding to dataset
I have a webservice with a common function which calls MYSQL and returns a dataset. Today I encounterd a strange error when performing a Select with a count() in it which I cant figure out. From a command line, the SQL query works fine and returns 9 rows... but VB.NET bombs with an error when filling the data adapter with the returned data. Please see my sample code below, is there an additional flag I need to call to make this work with VB? SQL Command Line: ( Works Great )mysql> Select taction, count(*) from kiosklogs where packageid='NewHi-Def-Test' group by taction;...

SQL return value
I have an application that calls a sp. I am not able to capture the return value (I am forcing the code not to find any records). I am not sure at what point and where to get the value returned. What is it that causes a SQL exception? Thank you. Code:  EquentialCommand.Parameters.Add(New Data.SqlClient.SqlParameter("@RetValue", Data.SqlDbType.Char, 50)) EquentialCommand.Parameters("@RetValue").Direction = Data.ParameterDirection.Output EquentialCommand.CommandType = Data.CommandType.StoredProcedureEquentialCommand.CommandText = (&qu...

Returning a value From SQL
On   cmd.ExecuteNonQuery(); I am getting the following error "Procedure or function usp_Question_Count has too many arguments specified."Any Ideas as to how to fix this. Oh and I will include the SP down at the bottom        // Getting the Correct Answer from the Database.        int QuiziD = Convert.ToInt32(Session["QuizID"]);        int QuestionID = Convert.ToInt32(Session["QuestionID"]);        SqlConnection oConn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\quiz.mdf;In...

how can i caonvert positive value (in integer) to a negative value?
for example i have the number 4 i want to get the number -4 ? this wayint a = 1; a = -a;MAKMark as Answer if this reply helps youMVP ASP/ASP.NetASP.Net Hosting : Host DepotMy Site : ASPSnippets int a = 4 a= a - a*2 ie. a=4 a*2 = 8 4-8 = - 4  Now a becomes -4ByPT.SivakumarIndia...

Return Value from SQL
I am trying to get the following to work, here is the stored procedureSTORED PROCEDURE (@varA varchar(25),@varb varchar(25))AS IF EXISTS (SELECT column1 FROM table1 WHERE nameA=@NameA)RETURN 01IF EXISTS (SELECT column2 FROM table1 WHERE nameB=@nameB)RETURN 02ELSE     BEGIN              INSERT INTO table1 (nameA, NameB)              VALUES (@nameA, @nameB)          RETURN 03     EN...

SQL Server insert query
When I am inserting into a table, I have some values which I am finding are "N/A" which obviously come from a source which allows it ie nvarchar column)  I need to check the values before entering into the column, and if it is not an integer, converting it to the value '00000000' ie a similar thing would be isnull(columnname,00000000), but in this case, not checking for if it is null, but rather if it is an integer. Does anyone have any ideas? Take a look at http://berezniker.com/content/pages/sql/microsoft-sql-server/isnumericex-udf-data-type-awareBeware of ...

how to return return value?
imagine there was an sp beyond your control (i.e. you cannot use output parameters to obtain the value) but you had to catch the return value submitted by "return <value>" how would you do that? execute returns boolean executeUpdate returns int, but obviously not the return value but rows affected or so what the..? -- pat Hello Pat, For the return value you wills till need to register it as an output parameter, no matter how you execute the sproc. You would need to send the SQL as something like: {? = call sprocName(?, ?, ....)} The for the retu...

Web resources about - Convert returned value to Integer - asp.net.sql-datasource

Energy returned on energy invested - Wikipedia, the free encyclopedia
The natural or original sources of energy are not usually included in the calculation of energy invested, only the human-applied sources. For ...

How Facebook Returned A/B Testing To Mobile Apps
When Facebook rewrote its mobile applications and converted them from custom Web stacks to native development stacks, it lost the ability to ...

Facebook temporarily reveals ‘returned value’ metric for ads
Some Facebook advertisers temporarily saw a new metric called “returned value” in their Ads Manager dashboard. Facebook says the metric was accidentally ...

Baby sloth, rescued from captivity and to be returned to the wild - Flickr - Photo Sharing!
Paradise Garden, Boquete, Panama See where this picture was taken. [?]

Batista reveals why he returned to WWE - YouTube
The Animal talks about overcoming a broken back, his immersion in Jiu-Jitsu training, and his desire to capture the WWE World Heavyweight Championship. ...

Nearly 600 vehicles impounded by UAE authorities returned
The vehicles were returned last month after the impoundment period ended and all associated fines were paid.

Bender is back! Belair’s favourite robot returned - AdelaideNow Search Search
GOOD news, everyone! Bender the robot — possibly the coolest letterbox in the Adelaide Hills — has been returned just days after being stolen. ...

Returned foreign fighters should wear tracking devices: terrorism expert
Australians who return from known terrorist hot spots without good explanation should be made to wear tracking devices for two years, says security ...

NGA's retiring director Ron Radford glad stolen statue returned to India
Ron Radford has been responsible for around 47,000 acquisitions in a 43-year career, but there is one that he will always regret.

Family of sisters returned to Italy complains of police actions
THE family of four sisters, who were returned this week by Australia to Italy, say they will file a complaint to the Commonwealth Ombudsman over ...

Resources last updated: 11/25/2015 7:11:31 PM