Last post Aug 05, 2009 05:39 PM by limno
Aug 04, 2009 03:39 PM|march11|LINK
I am having a very difficult time returning the Primary Key value using a Stored Procedure. Please see the post below...
My code connects to the DB and I return a response, but not the Primary Key value.
Any help would be greatly appreciated.
Populating Session Var in STORED PROC or POSTBACK - HELP please!!
Aug 04, 2009 04:53 PM|moises.dl|LINK
sorry mine is in C# but thats because C# == DA BOMB hahaha like this?
this is my C# code calling the sproc that inserts a record and returns the new id to be used as reference in another insert or update....
SqlCommand cmd = new SqlCommand("InsertRequestNote", ProjectsConnection);
SqlParameter paramRequestId = new SqlParameter("@RequestId", requestId);
SqlParameter paramNote = new SqlParameter("@Note", note.Note);
SqlParameter paramDate = new SqlParameter("@DateAdded", note.DateAdded);
SqlParameter paramAddBy = new SqlParameter("@AddedBy", note.AddedBy);
cmd.Parameters.Add(new SqlParameter("@newid", SqlDbType.Int)).Direction = ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;
returnvalue = cmd.Parameters["@newid"].Value;
if (returnvalue == DBNull.Value)
throw new Exception("Project failed to commit to server at this time.");
catch (Exception ex)
HERE IS THE SQL
ALTER PROCEDURE [dbo].[InsertRequestNote]
@newid INT OUTPUT)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO projectRequestNotes
(RequestId, Note, DateAdded, AddedBy)
VALUES(@RequestId, @note, @DateAdded, @addedBy)
SET @newid = SCOPE_IDENTITY()
NOTICE the ouput param that sql knows its going to return you just have to set the value... now if the value is null you know nothing happened.... word?
Aug 04, 2009 04:58 PM|march11|LINK
I appreciate your repsonse, but I had no choice in the matter so it is VB.net that I need help with.
Populating Session Var in STORED PROC or POSTBACK
Aug 04, 2009 07:20 PM|moises.dl|LINK
well you should do your best to take the knowledge and apply it to your problem. what your looking for is to implement a out parameter from your sql call.... look it up
Aug 05, 2009 07:58 AM|march11|LINK
I understand that, if you look at the code from the link that opened this post you will see that this were I am having difficulties.
I think there is a problem with VB.net using the out parameter. Which is why I am looking for help and maybe a snippet of code where someone has it working. I have been on this days and am looking at contacting MS directly if I can't get any further.
So, if any one else could help with this issue I would be very thankful.
Aug 05, 2009 03:37 PM|moises.dl|LINK
so from a re-read im understanding that you have most of it working you just cant get the newid to work because its saying its not supplied anymore, what does your declaration of your parameters look like to sql... is it set to out (IN SQL), another thing
i thought might help is setting a default value when you set it in your aspx? im sorry this may be frustrating you so good luck
i see two more suggestions online... one is to set up select parameters ... this sounds like it may be logicaly right too cuz its not really an insert param. check out this link
Aug 05, 2009 03:51 PM|march11|LINK
I have a SqlDataSource Insert and need to return the Primary Key using VISUAL BASIC!
The Stored Procedure works find and I have tried both SELECT Scope_Identityt() and SET newID = SCOPE_INDENTITY() see my SP in the code section.
I have been able to change the Type of the variable in the code behind and return a value of 2, which I think is a bit count of the prim key. As a string the return value is the word "output". But I can not seem to get the actual value of for the PK of the
record that was just inserted.
Dim schoolsDataSource As New SqlDataSource
schoolsDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionStringPSR").ToString()
schoolsDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
schoolsDataSource.InsertCommand = "insertSchools"
newID = ParameterDirection.Output
Session("institutionID") = newID
Dim rowsAffected As Integer = 0
rowsAffected = schoolsDataSource.Insert()
Catch ex As Exception
'/ Post to windows error log, or send an email if this line fires.
schoolsDataSource = Nothing
If rowsAffected <> 1 Then
AND MY STORED PROCEDURE IS HERE....
ALTER PROCEDURE dbo.insertParents
@newID int output
INSERT INTO [parents] ([parent2Zip], [parent2Email])
VALUES (@parent2Zip, @parent2Email);
SELECT @newID = SCOPE_IDENTITY()
Aug 05, 2009 05:14 PM|Naom|LINK
1. Why do you need to set InsertCommand and InsertParameters in code - would it be much simpler to set them in ASPX instead?
The way you're doing it makes the whole SQLDataSource sort of useless - you can do the same with direct ADO.NET code (as shown in the
Aug 05, 2009 05:39 PM|limno|LINK
Here is a sample in VB.NET:
<asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:myConn %>" ID="SqlDataSource1"runat="server" SelectCommand="SELECT ContactID, ContactName from Contacts" InsertCommand="INSERT INTO Contacts (ContactName) VALUES (@contactName);SELECT @contactID = Scope_Identity()"
<asp:Parameter Name="contactName" Type="String" />
<asp:Parameter Direction="Output" Name="contactID" Type="Int32" />
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
Response.Write("Record Inserted: " + Server.HtmlEncode(e.Command.Parameters("@ContactID").Value.ToString()) + "<br/>")