Retrieve Id Of Newly Added Row In Stored Procedure
Jan 5, 2006
Hi,
I am trying to return the id of the newly added row after the insert
statement in my stored procedure. I was told to "RETURN
SCOPE_IDENTITY()", which i did. The problem is i do not know how to get
this value in asp.net?
I added this code below in my business layer. myDAL refers to an object
of my DAL layer. In my DAL layer, i have a addPara() method which will
help me dynamically add as many parameters. Someone please advise me on
what to do. Thanks
Howdie y'all, I'm quit new to SQL server and I'm getting there finally, but it's quit hard to find some good info on how to create stored procedures... But I've got the following one.... CREATE PROCEDURE [dbo].[spAddUser]@UserEmail VARCHAR(255),@UserPassword VARCHAR(16),@UserName VARCHAR(32)ASINSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)VALUES (@UserEmail, @UserPassword, @UserName)GO I actually would like to get value of the UserId column for the newly added record. Can anyone of you folks help me with this? Cheers, Wes
Can anyone tell me how an identity column decides what number to give which row.
I always thought that it was in order of the clustered index.
But i have found the following:
If you have Table A with a clustered index(not unique) say on the first 3 columns.
If the table is empty and you add an identity column, then afterward load it with data the value of the identity column seems to match the clustered index.
But if the table has no identity column and it is filled with data, and then you add an identity column the number for the identity column seems to be all over the place.
So basically how does a newly added identity column know what number to give each row, what if the clustered index is not unique? Is it random ?
Please help me somebody solve my problem with my first :o trigger: ALTER TRIGGER partner_update ON dbo.partner FOR UPDATE AS INSERT INTO partner (name) SELECT name FROM deleted UPDATE invoice SET id_partner= *** WHERE id_partner = (SELECT id_partner FROM deleted) *** - here I want to add a "reference" to the newly added record's automatically generated primary key (not to the updated!) Is it possible?
Hi.. I created a publication on my server and I just clicked the box to include all of the stored procedures. If I add a new procedure, will that procedure also be included? Or will I have to modify the publication for each SP I create? Thanks! -Greg
I need some help retrieving the stored procedure listed below. I would like to use a drop list to select the variable "UserName" and textboxes to send the variables "StartingDate" and "EndingDate". How do I pass these variables and then have the results show up in a gridview? Any advice would be greatly appreciated. CREATE PROCEDURE [dbo].[aspnet_starterkits_GetTimeEntryUserReportByUserNameAndDates] @UserName NVARCHAR(256), @StartingDate datetime, @EndDate datetimeASDECLARE @UserId AS UNIQUEIDENTIFIERSET NOCOUNT ONSELECT @UserId=UserId FROM aspnet_users WHERE UserName=@UserNameSELECT @UserName as UserName, SUM (timeentryDuration) AS TotalDuration,SUM (timeentryOvertime) AS TotalOvertimeHoursFROM aspnet_starterkits_TimeEntryWHERE aspnet_starterkits_TimeEntry.TimeEntryUserId=@UserId AND TimeEntryDate between @StartingDate and @EndDate
Hello Group I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can’t retrieve the data. Here is my code. CREATE PROCEDURE stpMyAuthentication ( @fldUsername varchar( 50 ), @fldPassword Char( 25 )--, --@fldFirstName char( 30 ) OUTPUT, --@fldLastName char( 30 ) OUTPUT ) As DECLARE @actualPassword Char( 25 ) SELECT @actualPassword = fldPassword FROM tbMembUsers Where fldUsername = @fldUsername IF @actualPassword IS NOT NULL IF @fldPassword = @actualPassword RETURN 1 ELSE RETURN -2 ELSE RETURN -1
SELECT fldFirstName, fldLastName, fldPassword FROM tbMembUsers Where fldUsername = @fldUsername GO ############### login page ################ Sub Login_Click(ByVal s As Object, ByVal e As EventArgs) If IsValid Then If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False) End If End If End Sub
Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer Dim myConn As SqlConnection Dim myCmd As SqlCommand Dim myReturn As SqlParameter Dim intResult As Integer Dim sqlConn As String Dim strFirstName, strLastName As String
sqlConn = ConfigurationSettings.AppSettings("sqlConnStr") myConn = New SqlConnection(sqlConn)
myCmd = New SqlCommand("stpMyAuthentication", myConn) myCmd.CommandType = CommandType.StoredProcedure
If intResult < 0 Then If intResult = -1 Then lblMessage.Text = "Username Not Registered!<br><br>" Else lblMessage.Text = "Invalid Password!<br><br>" End If End If
Return intResult End FunctionAt this time I am not getting any errors. How can I retrieve the data that I am after? Michael
Hi, Can anyone please help me solve this problem. My functions works well with this stored procedure: CREATE PROCEDURE proc_curCourseID@studentID int ASSELECT * FROM StudentCourse WHERE mark IS NULL AND studentID = @studentID AND archived IS NULLGO But when I applied the same function to the following stored procedure CREATE PROCEDURE proc_memberDetails@memberID int ASSELECT * FROM member WHERE id = @memberIDGO I received this message Exception Details: System.Data.SqlClient.SqlException: Procedure or function proc_memberDetails has too many arguments specified.Source Error:
Line 33: SqlDataAdapter sqlDA = new SqlDataAdapter(); Line 34: sqlDA.SelectCommand = sqlComm; Line 35: sqlDA.Fill(dataSet); Line 36: Line 37: return dataSet; The function I am using is returning a DataSet as below: public DataSet ExecuteStoredProcSelect (string sqlProcedure, ArrayList paramName, ArrayList paramValue) { DataSet dataSet = new DataSet(); SqlConnection sqlConnect = new SqlConnection(GetDBConnectionString()); SqlCommand sqlComm = new SqlCommand (sqlProcedure, sqlConnect); sqlComm.CommandType = CommandType.StoredProcedure;
for (int n=0; n<paramName.Count; n++) { sqlComm.Parameters.Add(paramName[n].ToString(),Convert.ToInt32(paramValue[n])); }
SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = sqlComm; sqlDA.Fill(dataSet); return dataSet; } If this is not the correct way, is there any other way to write a function to return a dataset as the result of the stored procedure? Thanks.
I have a stored procedure that return either 1 or -1 : IF EXISTS( ) BEGIN return 1 ENDELSE BEGIN return -1 ENDAnd then I try to retrieve the returned value by doing this:int value = Convert.ToInt32(command.ExecuteScalar());But the value I get is always 0. Anyone see what is wrong?
Hi to all! Is there a system stored procedure that retrieve info or name about an object? I must know if a database contains a specific table and i must know if there is a specific DTS.. before execute it.. someone could help me??
Hi, I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows: int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close(); my stored procedure: CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted) VALUES (@publicationid,@title,@categoryid,0) SELECT @slideshowid = SCOPE_IDENTITY()GO
Im a bit confused on my programming skills. I know how to add a user and password using the Membership.CreateUser class. ' Define database connection Dim conn As New SqlConnection("Server=localhostSqlExpress;" & _ "Database=lnp;Integrated Security=True") ' Create command Dim comm As New SqlCommand( _ "SELECT oldLogId, oldPassword FROM userDetails WHERE (oldLogId = N'chuck') OR (oldLogId = N'top_dawg')", conn) ' Open connection conn.Open() ' Execute the command Dim reader As SqlDataReader = comm.ExecuteReader() ' Do something with the data While reader.Read() Dim partPassword = reader.Item("oldPassword") ' Adds underscore character and first 3 letters of password for distinct USER ID Dim newUserID = reader.Item("oldLogId") & "_" & partPassword.SubString(0, 3) ' Create User if Valid Try Membership.CreateUser(newUserID, partPassword) Catch ex As MembershipCreateUserException ' Code that is executed when an exception is generated ' The exception's details are accessible through the ex object usersLabel.Text &= "<p><strong>Exception: " & newUserID & "</strong><br />" & ex.ToString() & "<br /><strong>Message: </strong><br />" & ex.Message & "</p>" End Try End While usersLabel.Text &= "<p><strong>END OF RECORDS</strong></p>" ' Close the reader and the connection reader.Close() conn.Close() But now I want to do something in a Stored Procedure USE [lnp]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_createPortingDetailsWithUsers]-- Add the parameters for the stored procedure here @passUserId NVarChar(50), @passPassword NVarChar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;Membership.CreateUser(@passUserId, @passPassword)Select SCOPE_IDENTITY()ENDBut when I try to execute this in SQL SERVER MANAGEMENT STUDIO EXPRESS, I get an error near the line MEMBERSHIP.CREATEUSER(@passUserId,@passPassword)Msg 102, Level 15, State 1, Procedure sp_createPortingDetailsWithUsers, Line 11Incorrect syntax near 'Membership'. What I want to do is create a user with a UserID and Password that I am passing from a table and then retrieve the USERID (unique identifier) when I create the new user. Can anyone suggest or show a tutorial on what I am doing wrong? I appreciate any assistance in advance,Chuck
Development - Visual Studio 2005 Database - MS SQL 2005
I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.
This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.
So... Does anyone here can help me out? Thanks in advance.
Hi Guys, I have a sql procedure that returns the following result when I execute it in query builder: CountE ProjStatus 6 In Progress 3 Complete 4 On Hold The stored procedure is as follow: SELECT COUNT(*) AS countE, ProjStatusFROM PROJ_ProjectsGROUP BY ProjStatus This is the result I want but when I try to output the result on my asp.net page I get the following error: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count. 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.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.Source Error:
Line 271: </asp:TemplateColumn> Line 272: <asp:TemplateColumn> Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate> Line 274: </asp:TemplateColumn> Line 275: </columns> My asp.net page is as follows: <script runat="server"> Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")
' Mark the Command as a SPROC myCommandPS.CommandType = CommandType.StoredProcedure Dim num as integer num = CInt(myCommand.ExecuteScalar) 'Set the datagrid's datasource to the DataSet and databind Dim myAdapterPS As New SqlDataAdapter(myCommandPS) Dim dsPS As New DataSet() myAdapter.Fill(dsPS)
I'm writing VB 6.0 codes to retrieve data from SQL Server 7.0. if the sql code is embedded in VB, everything works fine. But when I tried to use stored procedure, one error called "invalid object name 'author'" occurs. author is a table in my database and obviously is in the database. what's wrong?
Hi All, Does anyone have a Stored Procedure that works perfectly to retrieve all zipcodes within a specified zipcode and distance radius - a zipcode and radius is passed and the Store Procedure result shows all zipcodes that falls within that range.
In my application on click of Delete button, actually I am not deleting the record. I am just updating the flag. But before updating the record I just want to know the dependency of the record ie. if the record I am deleting(internally updating) exists any where in the dependent tables, it should warn the user with message that it is available in the child table, so it can not be deleted. I want to check the dependency dynamically. For that I have written the following procedure. But it is updating in both cases.
CREATE proc SProc_DeleteRecord @TableName varchar(50), @DeleteCondition nVarchar(200) As Begin Begin Transaction Declare @DelString nvarchar(4000) set @DelString = 'Delete from ' + @TableName + ' where ' + @DeleteCondition execute sp_executeSql @DelString RollBack Transaction --because i donot want to delete the record in any case If @@Error <> 0 Begin select '1' End Else Begin Declare @SQLString nvarchar(4000) set @SQLString = 'Update ' + @TableName + ' set DeletedFlag=1 where ' + @DeleteCondition execute sp_executeSql @SQLString select '0' End
We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).
I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?
I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.
Thanks in advance ripern
-- Retrieves the value of column @columnName for credential id @credID ALTER PROCEDURE [dbo].[PMC_User_GetUserValue] @credID int, @columnName nvarchar(50) AS
DECLARE @userDataTable nvarchar(50) DECLARE @userDataID int DECLARE @statement nvarchar(500) SET @statement = ' '
SET @userDataID = (SELECT PMC_UserMapping.fk_userDataID FROM PMC_UserMapping INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id WHERE PMC_User.fk_credentials_id = @credID)
SET @userDataTable = (SELECT PMC_UserType.userDataTable FROM PMC_UserType INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id WHERE PMC_User.fk_credentials_id = @credID)
SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)
-- Checks whether the given column name exists in the user data table for the given credential id. IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName ) BEGIN EXEC (@statement) END
hi, i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do: i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview: [<<Prev] [Selected Month] [Next>>] the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads). i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year. i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing. i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd] GO /****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening. The item is added into the db but the output param is not modified. Here is the code: SP CREATE procedure dbo.AddItem(@Desc nvarchar(100),@intItemID int output)as insert into RR_Item ( desc ) values ( @Desc ) select @intItemID = SCOPE_IDENTITY()GO I have tried in the last line of the SP select @intItemID = SCOPE_IDENTITY() select @intItemID = @@IDENTITY select @intItemID = max(itemid) from RR_Item but nothing seems to work. I'm calling the store procedure as follows from asp.net: Dim intItemID As New SqlParameter("@intItemID", SqlDbType.Int) intItemID.Direction = ParameterDirection.Output SqlHelper.ExecuteDataset(objConn.ConnectionString, "AddItem", desc, intItemID) MsgBox(intItemID.Value.ToString)
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
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],
Hi there I'm using VS2005 (VB.net) and SQL 2005. We've uploaded files from the web application to the SQL database. The next thing I want to be able to do is to retrieve this uploaded file(s) from the database and attach it in the email when the user click on the Submit button on the web form. How can this be done ? Any help would be greatly appreciated. TIA
