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(paramRequestId);
cmd.Parameters.Add(paramNote);
cmd.Parameters.Add(paramDate);
cmd.Parameters.Add(paramAddBy);
cmd.Parameters.Add(new SqlParameter("@newid", SqlDbType.Int)).Direction = ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;
object returnvalue;
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
returnvalue = cmd.Parameters["@newid"].Value;
if (returnvalue == DBNull.Value)
{
throw new Exception("Project failed to commit to server at this time.");
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Connection.Close();
cmd.Dispose();
}
return int.Parse(returnvalue.ToString());
HERE IS THE SQL
ALTER PROCEDURE [dbo].[InsertRequestNote]
(
@RequestId INT,
@Note NTEXT,
@DateAdded DATETIME,
@AddedBy VARCHAR(100),
@newid INT OUTPUT)
AS
BEGIN
-- 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()
END
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?
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
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.
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
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.
march11
Contributor
3017 Points
1367 Posts
Populating Session Var in STORED PROC or POSTBACK - HELP please!!
Aug 04, 2009 07:39 PM|LINK
I am having a very difficult time returning the Primary Key value using a Stored Procedure. Please see the post below...
http://forums.asp.net/t/1454603.aspx?PageIndex=2
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!!
moises.dl
Contributor
2046 Points
628 Posts
Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!
Aug 04, 2009 08:53 PM|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(paramRequestId); cmd.Parameters.Add(paramNote); cmd.Parameters.Add(paramDate); cmd.Parameters.Add(paramAddBy); cmd.Parameters.Add(new SqlParameter("@newid", SqlDbType.Int)).Direction = ParameterDirection.Output; cmd.CommandType = CommandType.StoredProcedure; object returnvalue; try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); returnvalue = cmd.Parameters["@newid"].Value; if (returnvalue == DBNull.Value) { throw new Exception("Project failed to commit to server at this time."); } } catch (Exception ex) { throw ex; } finally { cmd.Connection.Close(); cmd.Dispose(); } return int.Parse(returnvalue.ToString());HERE IS THE SQL
ALTER PROCEDURE [dbo].[InsertRequestNote] ( @RequestId INT, @Note NTEXT, @DateAdded DATETIME, @AddedBy VARCHAR(100), @newid INT OUTPUT) AS BEGIN -- 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() ENDNOTICE 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?
MOIhawk
march11
Contributor
3017 Points
1367 Posts
Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!
Aug 04, 2009 08:58 PM|LINK
I appreciate your repsonse, but I had no choice in the matter so it is VB.net that I need help with.
Thanks though.
Populating Session Var in STORED PROC or POSTBACK
moises.dl
Contributor
2046 Points
628 Posts
Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!
Aug 04, 2009 11:20 PM|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
MOIhawk
march11
Contributor
3017 Points
1367 Posts
Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!
Aug 05, 2009 11:58 AM|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.
moises.dl
Contributor
2046 Points
628 Posts
Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!
Aug 05, 2009 07:37 PM|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
http://www.velocityreviews.com/forums/t86158-re-how-to-retrieve-an-output-parameter-using-sqldatasource-control.html
MOIhawk
march11
Contributor
3017 Points
1367 Posts
Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!
Aug 05, 2009 07:51 PM|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.
Any help would be greatly appreciated.
Dim schoolsDataSource As New SqlDataSource
schoolsDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionStringPSR").ToString()
schoolsDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
schoolsDataSource.InsertCommand = "insertSchools"
parentsDataSource.InsertParameters.Add("parent2Email", parent2EmailTextBox.Text.Trim())
parentsDataSource.InsertParameters.Add("parent2Zip", parent2ZipTextBox.Text.Trim())
parentsDataSource.InsertParameters.Add("newID", SqlDbType.Int)
newID = ParameterDirection.Output
Session("institutionID") = newID
Dim rowsAffected As Integer = 0
rowsAffected = schoolsDataSource.Insert()
Try
Catch ex As Exception
'/ Post to windows error log, or send an email if this line fires.
Server.Transfer("problem_uploadpage.aspx")
Finally
schoolsDataSource = Nothing
End Try
If rowsAffected <> 1 Then
Server.Transfer("problem_dbuploadpage.aspx")
Else
Server.Transfer("accountcreated.aspx")
End If
AND MY STORED PROCEDURE IS HERE....
ALTER PROCEDURE dbo.insertParents
@parent2Zip nchar(10),
@parent2Email varchar(50),
@newID int output
AS
INSERT INTO [parents] ([parent2Zip], [parent2Email])
VALUES (@parent2Zip, @parent2Email);
SELECT @newID = SCOPE_IDENTITY()
RETURN
Naom
All-Star
36004 Points
7901 Posts
Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!
Aug 05, 2009 09:14 PM|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
http://www.aspsnippets.com/post/2009/05/27/Get-ID-of-the-newly-inserted-record-in-SQL-Server-using-ADONet.aspx )
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
limno
All-Star
117340 Points
8005 Posts
Moderator
MVP
Re: Populating Session Var in STORED PROC or POSTBACK - HELP please!!
Aug 05, 2009 09:39 PM|LINK
Here is a sample in VB.NET:
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/>") End SubFormat your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm