Return An Unique Identifier To An ASP.NET Page To Send It As A Parameter Into Another Stored Procedure
May 1, 2007
Hi !
I have a problem with the unique identifier and don't know how to solve it.
I have a stored procedure, called from my ASP.NET page, which inserts a new record into a table. I need to get the Id of the row just inserted in order to use it as a parameter of another stored procedure which inserts a new row with this value and other values.
I tried with SCOPE_IDENTITY but i don't know how to ask for this value to the first stored procedure and stored it into an ASP variable.
I developed a main report containing numerous subreports. But now I'm trying to page break on these subreports and because these subreports all have sub-sub reports, I get this error when I try to run this report..The value '22' is invalid. Valid values are between '0' and '1'. (rrRenderingError)..I am grouping on uniqueidentifers and I do not get errors on subreports that have sub-subreports. How can I avoid this error and get these subreports to page break? (I don't get any errors if I remove the page breaks).
As you can I have a uniqueidentifier (UniqueId) column which I populate with NewID() I'm trying to return this as I need it for other functionality of the website but I can't figure out how I can get it after the insert has completed?
I would like to pass an unique identifier (UserID) to a Crystal Reportfrom a SQL stored procedure. I found an article from Business Objectsabout this issue, but I couldn't get my head around it's hard codedexample.Does anyone have an example of this? Would I need to also create aparameter in my Crystal Report?Help appreciated.Steve*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted. In my code behind file (VB) how would I capture the value "@myERROR" then display in the label I have that a duplicate entry was attempted. Stored ProcedureCREATE PROCEDURE dbo.usp_InsertNew @IDNumber nvarchar(25), @ID nvarchar(50), @LName varchar(50), @FName varchar(50) AS DECLARE @myERROR int -- local @@ERROR , @myRowCount int --local @@rowcountBEGIN -- See if a contact with the same name and zip code exists IF EXISTS (Select * FROM Info WHERE ID = @ID) BEGIN RETURN 1END ELSEBEGIN TRAN INSERT INTO Info(IDNumber, ID, LName, FName) VALUES (@IDNumber, @ID, @LName, @FName) SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT If @myERROR !=0 GOTO HANDLE_ERROR COMMIT TRAN RETURN 0 HANDLE_ERROR: ROLLBACK TRAN RETURN @myERROR ENDGO asp.net page<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>" SelectCommand="SELECT * FROM TABLE_One" UpdateCommand="UPDATE TABLE_One WHERE ID = @ID" InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource>
how do i return the identity from stored procedure to asp.net code behind page? CREATE PROCEDURE [dbo].[myInsert] ( @Name varchar(35), @LastIdentityNumber int output)AS insert into table1 (name) values (@name) DECLARE @IdentityNumber intSET @IdentityNumber = SCOPE_IDENTITY()SELECT @IdentityNumber as LastIdentityNumber code behind: public void _Insert( string _Name, { DbCommand dbCommand = db.GetStoredProcCommand("name_Insert");db.AddInParameter(dbCommand, "name", DbType.String, _userId); db.AddParameter(dbCommand, "@IdentityNumber", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Current, null); db.ExecuteNonQuery(dbCommand); int a = (int)db.GetParameterValue(dbCommand,"@IdentityNumber"); whats wrong with to the above code?
I'm attempting to return a value from my stored procedure. Here is my button click event that runs the stored procedure. protected void btn_Move_Click(object sender, EventArgs e) { /*variables need for the update of the old staff record and the creation of the new staff record. */ BMSUser bmsUser = (BMSUser)Session["bmsUser"]; int staffid = Convert.ToInt32(bmsUser.CmsStaffID); int oldStaffProfileID = Convert.ToInt32(Request.QueryString["profileid"]); string newManager = Convert.ToString(RadComboBox_MangersbyOrg.Text); int newMangerProfileID = Convert.ToInt32(RadComboBox_MangersbyOrg.Value);
SqlParameter sp = new SqlParameter("@OLDManagerReturn", SqlDbType.Int); sp.Direction = ParameterDirection.Output; cmd.Parameters.Add(sp);
lbl_ERROR.Text = Convert.ToString(sp);
conn.Open(); SqlTransaction trans = conn.BeginTransaction(); cmd.Transaction = trans; cmd.ExecuteNonQuery(); conn.Close(); My parameter sp is coming back null for some reason. Am I not setting the parameter correctly? I set a break point and it says the value is null. Here is the parts of my stored proc returning the value... DECLARE @OLDManagerReturn numeric(9) SELECT @OLDManagerReturn = ManagerProfileID FROM tblBMS_Staff_rel_Staff WHERE StaffProfileID = @OldStaffProfileID AND Active=1 AND BudgetYear = @BudgetYear RETURN @OLDManagerReturn
I am trying to retrieve the Output value from running a Stored Procedure. How can I retrieve the value into a variable? Below is the source code that I have:1 ' Insert question_text into Question table 2 Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 3 Dim cmd As New SqlCommand 4 Dim QuestionID As Integer 5 6 ' Set connection to DB 7 cmd.Connection = conn 8 9 cmd.CommandText = "usp_QuestionResponse_ins" 10 cmd.CommandType = Data.CommandType.StoredProcedure 11 12 Dim sp1 As SqlParameter = cmd.Parameters.Add(New SqlParameter("@question_id", SqlDbType.Int, 4)).Direction = ParameterDirection.Output 13 cmd.Parameters.Add(New SqlParameter("@assessment_id", AssessmentID)) 14 cmd.Parameters.Add(New SqlParameter("@domain_id", DomainID)) 15 cmd.Parameters.Add(New SqlParameter("@question_text_en", QuestionTextEn)) 16 cmd.Parameters.Add(New SqlParameter("@question_text_sp", QuestionTextSp)) 17 cmd.Parameters.Add(New SqlParameter("@category_en", CategoryEn)) 18 cmd.Parameters.Add(New SqlParameter("@display_order", DisplayOrder)) 19 cmd.Parameters.Add(New SqlParameter("@question_template_id", 3)) 20 QuestionID = sp1.Value 21 cmd.Connection.Open() 22 cmd.ExecuteNonQuery() 23 24 conn.Close() 25 cmd.Dispose() 26
Here is the Stored Procedure that I have: 1 ALTER PROCEDURE usp_QuestionResponse_ins 2 ( 3 @question_id int = null output, 4 @assessment_id int = null, 5 @domain_id int = null, 6 @question_text_en nvarchar(1000) = null, 7 @question_text_sp nvarchar(1000) = null, 8 @category_en nvarchar(500) = null, 9 @display_order smallint = null, 10 @question_template_id int = null 11 ) 12 As 13 BEGIN 14 DECLARE @Err Int 15 16 DECLARE @QuestionID INT 17 DECLARE @ReturnValue INT 18 SET @ReturnValue = 0 19 20 DECLARE @CategoryId int 21 SELECT @CategoryId = NULL 22 23 -- Check to see if an answer has already been inserted (i.e. revisiting) 24 SELECT @CategoryId = category_id FROM category WHERE (category_name = @category_en) AND (active_f = 1) AND (delete_f = 0) 25 IF ( @CategoryId IS NOT NULL ) 26 BEGIN 27 EXEC @ReturnValue = usp_question_ins @question_id OUTPUT, @assessment_id, @domain_id, 2, 1, 'Right', @display_order, 1, 8, @CategoryID, @question_text_en, Null, Null, 'Horizontal', 0, Null, Null, 0, 1, 0 28 SET @QuestionID = @ReturnValue 29 END 30 31 IF ( @QuestionID IS NOT NULL ) 32 BEGIN 33 DECLARE @question_locale_id int 34 EXEC usp_question_locale_ins @QuestionID, 1, @question_text_en, 1, 0 35 EXEC usp_question_locale_ins @QuestionID, 2, @question_text_sp, 1, 0 36 37 END 38 39 RETURN @QuestionID 40 End
What am I doing wrong? How do I properly capture the QuestionID from the SP? Running the SP from command line works fine. Thanks
What is the best way to design this stored procedure for speed?
I want to pass in a list of IDs and have a return value of a table that contains the ID and its associated value. Is there a way to pass in as table and come out as table?
My stored proceddure "My Programs" includes a parameter @meid.How do I code an aspx file to run the procedure with a user ID, e.g. EmpID?I tried the following codes, but the error message indicated it can not findthe Stored Procedure. How do I pass the EmpID as a Stored Procedure parameter? <%meid = EmpIdcmd.CommandText = "MyPrograms meid"cmd.CommandType = CommandType.StoredProcedurecmd.Connection = sqlConnection2sqlConnection2.Open()reader3 = cmd.ExecuteReader(meid)While reader3.Read()sb.Append(reader3(i).ToString() + "......<BR> <BR /> ")End While%> TIA,Jeffrey
I am trying to access the Return Value provided by a stored procedure executed on SQL Server 2005. The stored procedure has already been tested and it returns the required value. However, I do not know how to access this value. I have tried appending a parameter to the command object using "adParamReturnValue" but that only returns an error. The code works fine without appending this parameter. I have tested it by grabbing the recordset and returning the first field.
To avoid any confusion, I'm not talking about adding an "output" parameter to the stored procedure. I just want to be able to access the return value provided when the procedure is executed. Below is some of the code I am using.
try{
pCmd.CreateInstance((__uuidof(Command)));
pCmd->ActiveConnection = m_pConnection;
pCmd->CommandType = adCmdStoredProc;
pCmd->CommandText = _bstr_t("dbo.GetFlightPlan");
............................ code here ........................................
I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:
Does anyone know how to call a SQL store procedure that return a value to the page? I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user. Are there a similar article somewhere?
I have a package that I have been attempting to return a error code after the stored procedure executes, otherwise the package works great.
I call the stored procedure from a Execute SQL Task (execute Marketing_extract_history_load_test ?, ? OUTPUT) The sql task rowset is set to NONE. It is a OLEB connection.
I have two parameters mapped:
tablename input varchar 0 (this variable is set earlier in a foreach loop) ADO. returnvalue output long 1
I set the breakpoint and see the values change, but I have a OnFailure conditon set if it returns a failure. The failure is ignored and the package completes. No quite what I wanted.
The first part of the sp is below and I set the value @i and return.
Why is it not capturing and setting the error and execute my OnFailure code? I have tried setting one of my parameter mappings to returnvalue with no success.
I'm trying to create a unique identifier number that meets the following criteria. The unique identifier needs to be a concatenation of two values submitted from a form and the identity value (primary key) for the new record that is inserted into the database.
So, if the first field is the year and the second field is a objnumber, the unique identifier number would have the format: ("YR" + "objnumber" + primary key value), where the year and object number are what the user selected in the form.
I have a stored procedure that I use to handle the insert, which also returns @@identity for the purpose of passing that value into another stored procedure that inserts child records.
So, within my stored procedure, is there a way I can create the unique identifier number and return that value back to the application? I'm not sure how to accomplish this?
I am working on some tables and would like to know which is best way to go when deciding what Type to use for Unique ID in my tables please. Int Or uniqueIdentifier? Or is it all the same??
Hi there,I'm new to sql server. I've created a table which can be updated through an aspx form. However coming from an access background I don't know how to generate an auto number. I've read through a number of the threads on here and keep coming across Identity or unique identifier. However I can't actually find out how to implement these.Any help would be greatCheersStu
Hello!I work with oracle, mySQL but im completely new to SQLserver.How can i set a auto-increment unique variable for userID ?After deleted user, the userID should be never used again on a new user.Thank you,
Does anyone know if there is a read-only unique identifier of a database in a given server instance? E.g. a value stored somewhere in the database meta data that is generated during CREATE DATABASE...
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
I need to develop a strong licensing solution based on the database accessed... Currently our solution is easily hackable because the the license information is kept in the database of your choice and is not dependant on some static information, the current encryption key is static and kept in the software so it can be hacked easily. What i want to do to change this is simple in nature but i don't know how to get that one particular info i need. I want to get some kind of unique identifier for a database (catalog) that sql server could generate. This info must be static and must not be movable. If for example, someone would backup and restore, this information would not be transfered with the backup. Thus, a user that backups his database and restores it on another database server or another database catalog even on the same server would corrupt his license since the Unique ID returned by the SQL Server would be different; the hashing code would change and thus the decryption would fail. Is there any such info i can query using SQL that will not require admin rights?
I'd like to know the current value of my uniqueID column before Icreate a new record.Is there a way to find out this value?It is numeric in my case, but I can't just look for the MAX value,since some records may have been deleted, and the value for theuniqueID still stays at the higher value.Is there a way to read this internally kept value?
Hi guysI'm having a nasty problem with bulk copying into a table that hasunique identifier column. I'm coding on C++, using ODBC driver.I'm coping from a file containing UID description like this:{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}(I also tried a non-bracket version)and in the DB table I get:4233347B-4235-4433-452D-353238302D34which seems random at first sight, but it is:[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.and my question is: what the hell?my code look like this:if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)ret = -1;if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,SQLUNIQUEID, colNo) == FAIL){ret = -1;}(I also tried a VARLEN version:)if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){ret = -1;}and then stuff like sendrow ans save:if (bcp_sendrow(m_hDbproc) == FAIL)return -1;if (bcp_batch (m_hDbproc) == -1)return -1;I also tried specyfiling the column type in the m_hDbproc handle asSQLUNIQUEID, but either I'm doing something wrong, or this just isn'tthe way of a bulk copy samurai:INT * pValue=new INT;INT *pLen=new INT;*pValue=0x24;bcp_setcolfmt(m_hDbproc,1,BCP_FMT_TYPE,pValue,4);So like, PLEASE help me on this. I need to get this working by lastmonday :]Thanx, M.
Using SQL Server 2014 i try to merge data from database [Susi] on server2 to database [Susi] on server1. Server2 is a linked server in server1. The PK of the table Core.tKontakte is uniqueidentifier with rowguidcol.
I wrote the following script and get error 206: "uniqueidentifier ist inkompatibel mit int".
INSERT Core.tKontakte (KontaktID, AnredeID, Titel, Nachname) SELECT KontaktID, AnredeID, Titel, Nachname FROM [Susi].MSCMS.Core.tKontakte AS Client WHERE NOT EXISTS (SELECT KontaktID FROM Core.tKontakte AS Host WHERE Host.KontaktID = Client.KontaktID);
Function GUID() As String GUID = System.GUID.NewGuid().ToString() End Function
GUID is always 36 characters, for example:
1737be72-fe96-4c3c-b455-3730b049bef9
What the best way to store this in a MS SQL database? I'm thinking of using a fixed length string (char). Is there a better way than to just store this 36 character field?
May I know what does the syntax of inserting data into a field of type Unique Identifier look like?
[code] INSERT INTO THAI_MK_MT_Log(GUID, Status) VALUES ('2331486348632', 'S') [/code]
The "2331486348632" is to be inserted into a unique identifier field. If i coded the insert statement as the above, I got an error saying that "Syntax error converting from a character string to uniqueidentifier".....
I have created some stored procedures that I wish to share with another user in another database. How can I extract this code, other than cut and paste, since I have quite a few? Is it possible to duplicate database object "stored procedures"? How about a script that would recreate them in the target database?
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
So for years I was using the int identity(1,1) primary_key for all the tables I created, and then in this project I decided, you know, I like the uniqueidentifier using newsequentialid() to ensure a distinctly unique primary key.
then, after working with the php sqlsrv driver, I realized huh, no matter what, i am unable to retrieve the scope_identity() of the insert
So of course I cruised back to the MSSMS and realized crap, I can't even make the uniqueidentifier an identity.
So now I'm wondering 2 things...
1: Can I short cut and pull the uniqueidentifier of a newly inserted record, even though the scope_identity() will return null or 2: do I now have to add a column to each table, keep the uniqueidentifier (as all my tables are unified by that relationship) and also add a pk field as an int identity(1,1) primary_key, in order to be able to pull the scope_identity() on insert...
