SQL Server :: Parameters From Codebehind For Returning Scope_Identity
Nov 17, 2010
I've used a sql data source to execute an insert stored procedure into my sql database. This time though, I want to execute the sp from my code behind. BUT, I'm stuck on the parameters and how to pass in the ones for the insert, and return one parameter, which is the identity of the row just inserted. The error I'm getting is. I'm missing something, but I don't see what... I think it's with the input/output parameters...
Procedure or function 'sp_CloneIT' expects parameter '@ClonedMatterID', which was not supplied. My stored proc
am new to entity frame work and having a hell of time building a stored procedure that insertsa value into a table using a Scalar stored procedure and returning the new ID which is a Unique Identifier. I am trying
Iam inserting ,updating in one DBfunction .i want to retrieve identityvalue when in insert.for that am adding select SCOPE_IDENTITY() in insert statement.how i can i get this value??
public int funAddEdit_rtblIncidentTypesDB(clsCM_Inc_IncidentTypesData objData, int actiontype) { string qry = ""; if (actiontype == 1) qry = "INSERT INTO _rtblIncidentType (cDescription,iEscGroupID,bAllowOverride,bRequireContract,iIncidentTypeGroupID,iWorkflowID,bAllowOverrideIncidentType,bPOIncidentType,cDefaultOutline) VALUES ('" + objData.cDescription + "'," + objData.iEscGroupID + ",'" + objData.bAllowOverride + "','" + objData.bRequireContract + "'," + objData.iIncidentTypeGroupID + "," + objData.iWorkflowID + ",'" + objData.bAllowOverrideIncidentType + "','" + objData.bPOIncidentType + "','" + objData.cDefaultOutline + "'); select SCOPE_IDENTITY() "; else if (actiontype == 2) qry = "UPDATE _rtblIncidentType SET cDescription='" + objData.cDescription + "' WHERE idIncidentType=" + objData.idIncidentType; int result = SqlHelper.ExecuteNonQuery(clsHelper.ConnectionString.ToString(), CommandType.Text, qry); return result; }
if i exceute the above query individually,it returns iddentity value,,bt unable to retireve the value in codebehind.
Is there a way of selecting returned values from a call to an sql database in the code behind? For example assume that I used the following to select values from a table called Products;
SELECT Price, Qty, Numbersold FROM Products
How can I now get the returned values e.g. Price in the c# codebehind and display it in a textbox? I know that in the asp markup that I can use EVAL and Bind statements to do this for example I can do Eval("Price") but is there a way of getting any of the selected values (Price, Qty or Numbersold) in the codebehind?
I know that I can use three separate select statements and make three separate calls to return each value and then do something like the following
I guess it is too late and I'm too tired to see what I'm doing wrong. Here is what I'm trying:
int imageId = imageDal.AddImage(new SqlParameter[] { new SqlParameter("@IMAGE_ID", SqlDbType.Int, Int32.MaxValue, ParameterDirection.Output, true, 0, 0,"IMAGE_ID", DataRowVersion.Current,DBNull.Value), new SqlParameter("@IMAGE", SqlDbType.Image, 11, ParameterDirection.Input, true, 0, 0,"IMAGE", DataRowVersion.Current,image) }); public int AddImage(SqlParameter[] spParams) { SqlHelper.ExecuteNonQuery(BaseDAL.ConnectionStringImages, INSERT_IMAGE_SQL, spParams); return Convert.ToInt32(spParams[0].Value); } Stored Procedure: [dbo].[sp_insert_image] -- Add the parameters for the stored procedure here @IMAGE_ID int OUT, @IMAGE image AS BEGIN INSERT INTO images (IMAGE) VALUES (@IMAGE) SELECT @IMAGE_ID = SCOPE_IDENTITY(); END GO
I get DBNull as spParams[0].Value. I've tried setting value of @IMAGE_ID to a constant in my stored procedure yet it didn't change anything so the problem isn't with my stored procedure (that is what I think). When I execute the procedure from sql management studio, I see the inserted_id returning..
I am trying to return the identity value of a inserted record for the first time in ASP.NET but it always returns 0, I can see in the database that a record is being inserted and the identity value is not 0.
I am using DetailsView attached to a ObjectDataSource to add a new record to a database. When it is complete I want to display the ID of the row that was added. I have a column in my database called ID that is set as the primary key and has Identity Specification enabled. My Insert statement is:
i have the following stored proc that inserts data into a database based on a stauts field. the first stored proc inserts into the first table and i am trying to get the identity field out as i need this value for the next stored proc. the first stored proc works fine and inserts the data but the secnd one doesnt, even though there is data there.
I have a Stored Proceedure which DID return the Scope_Identity when this was executed in SQLServer 2000.
Now I am porting this over to SQLServer 2008, and this always returns 0, and not the Scope_Identity which it should.
Here is the Stored Proceedure which will pass null for the parameter @pProjId for an insert:
[Code]....
Here is the script which creates the hip_Project Table:
[Code]....
What do I need to change for SQLServer 2008?
Again, this executed fine in SQLServer 2000 and always returned the Scope_Identity as expected. This problem has come up with other stored proceedures as well for which I need the Scope_Identity returned.
I have a page called Page2.aspx and it is called by another page called Page1.aspx and Page1.aspx passes two parameters namely: Name and Category to Page2.aspx. Does anyone know why the following code from Page2.aspx is not working and it keeps giving an error message as follows: " You have declare the parameter @Username".
When I want to get the output values its okay but I also want returning a table as a result data.But Datareader has no rows.is it possible if I want a returning query result and multiple output values togather ?I wrote a test above.I can get output values as sqlparameters. But Datareader attached to a Gridview is empty.can you detect whats wrong here and it doesnt return a query result.So stored procedure is not standart or ı am doing something wrong.this doesnt raise any exception.but not returning any data.
We have a web project that contain its business methods in a class library project called "Bll.dll" some methods of Bll.dll return List<> ... from a source - that i don't remember now - told that returning Collection<> is better than returning List<> Is it a valid ? Note that i don't make any process on values returned from BLL methods .. just view it in a web page
select NotificationID, (select u.UserName from aspnet_Users u, notifications n where u.UserId = n.FromID) as 'UserName', [Message], DateCreated, TypeID from Notifications n, aspnet_Users u
I have simple stored procedure to check if there is any email address in the database entered by user or not. My SP should return 1 when user enters a correct email. However, it is returning 0 or -1.
I have a procedure that is used on a search page.. im trying to provide a highlevel resultset which you can then click on a record to view all records associated with it.
My expectation is to return 5 columns.. the ID and Date is what i want to group on.. so that if there are 10 records for ID=5 and Date=10/25/2010 then in my results i would have 1 record. Once im done with the page, you can then click on that line in the gridview to view the 10 records for that id. Does that make sense?
I have the following query, but for the criteria i entered, im getting back 3 records instead of just 2 since there is a differnet reboot count, BUT i really still just need overall data. so it should be only 1 record and seen below is what is returned and what is expected..
I want send backup statement from my c# code to sql server then sql server return same back up file to my application in varbinary(max). what code is necessary for that?
The layout of my web app is C:inetpubwwwroot<sln folder><web app folder>. So the image folder in my web app would be the previous path images.
When I try to save to my images folder from a FileUpload control and use Server.MapPath, it drops my <sln folder>, so the path is incorrect. How do I fix this? I figured Server.MapPath just moved up the folder until it hit a drive, no?
I then create a DirectoryInfo object as I want to find some files in the directory.
DirectoryInfo updateDirectory = new DirectoryInfo(mappedPath);
But then updateDirectory.Exists is false?? I can take the string from the mappedPath and copy into Start->Run to get to the path so I know it exists. I am authenticating to the webservice using integrated windows authentication and have permissions to the necessary folders.
Is there something obvious I'm missing in the code? Or is this purely set-up and configuration of IIS etc.?
I been trying to get a simple stored procedure called and get its returned value back to a parm. In this case I am trying the simplest one I can make to get it to work and the I can build form there. I have tried to do this about 100 times and I try try and say heck with it and do it with out using a stored procedure and move on but I would really like to figure this out. I created a stored procedure called ReqLineItemTotal. It looks like this
USE [OnBillPROD] GO /****** Object: StoredProcedure [dbo].[ReqLineItemTotal] Script Date: 11/09/2010 14:08:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Author: Bill Blair -- Create date: 11-9-2010 -- Description: ReqLineItemTotal -- ALTER PROCEDURE [dbo].[ReqLineItemTotal] -- Add the parameters for the stored procedure here @SessionIDNo nvarchar(50) = 0
AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select (select SUM(dbo.TempReqDetail.Amount) from dbo.TempReqDetail,dbo.tblReqAmountTypes where dbo.tblReqAmountTypes.AmountType = dbo.TempReqDetail.AmountType and dbo.tblReqAmountTypes.NumericOpperator = '+' and dbo.TempReqDetail.SessionID = @SessionIDNo) - (select SUM(dbo.TempReqDetail.Amount) from dbo.TempReqDetail,dbo.tblReqAmountTypes where dbo.tblReqAmountTypes.AmountType = dbo.TempReqDetail.AmountType and dbo.tblReqAmountTypes.NumericOpperator = '-' and dbo.TempReqDetail.SessionID = @SessionIDNo) AS LineItemTotal END
From what I understand I should be getting my total back as LineItemTotal. So I want to call it pass it my parm of @SessionIDNo and have its returned value set to my @LineItemTotal parm. Trying to use these stored proc's always seems like way more work than they are worth but perhaps if I ever get the hang of it would make since to me.
I have an entry from a database that has 500 characters. But when i display it i only want to return 200 charcters. How do I do that. I use Sql Server.
