Inserting values and get the last ID recorded to use in another INSERT

I need to insert some values into a table and after that catch the ID inserted.

I set some input parameters in stored procedure and only one to get the @.id defined as output

SqlParameter paramIdPedido = new SqlParameter("@.APP_IDPEDIDO", SqlDbType.Int, 4);
paramIdPedido.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramIdPedido);

Do I have to run cmd.ExecuteNonQuery(); to record first what I need and after
run ExecuteReader: something like SqlDataReader dr = cmd.ExecuteReader();while (dr.Read()... to get the ID)

From stored procedure:

INSERT table(fields) VALUES(vars)
SELECT TOP 1 @.id = id FROM table ORDER BY id DESC

I must do all these steps or maybe is there anything less complex to do?

Thanks!

Hello Roberot,

Have a look at the following example:http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

Jeroen Molenaar.

Inserting to SQL DB from a form using parameters

I have a web form for new members to fill out and save their info in a SQL database to be retrieved later. There will be like 20 elements in the form mostly textboxes. Is there a good example of how to read all the elements and have them INSERTed in a SQL Database? I imagine parameters and a SP would be the way to go. So if someone could provide me with some sample code or a good link.

Thanks in advance,Here's a sample calling a stored procedure with parameters and with a variety of data types.

SqlCommand cm= new SqlCommand();
cm.Connection= Connection;
cm.CommandType= CommandType.StoredProcedure;
cm.CommandText= "StoredProcedureName";
SqlParameter sp1 = cm.Parameters.Add( new SqlParameter("@.Field1",SqlDbType.Int,4)); sp1.Value = salesID;
SqlParameter sp2 = cm.Parameters.Add( new SqlParameter("@.Field2",SqlDbType.Int, 4)); sp2.Value = someIntValue;
SqlParameter sp3 = cm.Parameters.Add( new SqlParameter("@.Field3",SqlDbType.Money, 8));sp3.Value = someDoubleValue;
SqlParameter sp4 = cm.Parameters.Add( new SqlParameter("@.Field4",SqlDbType.Float, 8 )); sp4.Value = anotherDoubleValue;
SqlParameter sp5 = cm.Parameters.Add( new SqlParameter("@.Field5",SqlDbType.DateTime, 8)); sp5.Value = someDateTimeValue
Connection.Open();
cm.ExecuteNonQuery();
Connection.Close();

INSERTING NULL VALUES VIA STORED PROCEDURES

Hi,

Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this:

errorParams[3].Value = (e.InnerException==null)?(object)DBNull.Value:(object)e.InnerException;

When I do it like this ( it is a part of preparing values for insert ):

errorParams[3].Value = e.InnerException

no row is added. Is there any way to pass over that check : (e.InnerException==null)?(

and do it easier?

This the sample exception message for that issue:

{System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@.InnerException', which was not supplied.

Thanks,

Pawe?

That depends on your Procedure declaration, you will have to allow a default parameter in orde to let it work.

CREATE PROCEDURE sp_addError
(
@.InnerException VARCHAR(1000) = NULL --Which declares the default if no value is passed through
)
(...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Cool :) Big thanks!

Pawe?

|||Thank you very much
It was solution of my Big problem

INSERTING NULL VALUES VIA STORED PROCEDURES

Hi,

Becouse some of my stored procedure parameters can be NULL, for every parameter with potential NULL value I have to do checking like this:

errorParams[3].Value = (e.InnerException==null)?(object)DBNull.Value:(object)e.InnerException;

When I do it like this ( it is a part of preparing values for insert ):

errorParams[3].Value = e.InnerException

no row is added. Is there any way to pass over that check : (e.InnerException==null)?(

and do it easier?

This the sample exception message for that issue:

{System.Data.SqlClient.SqlException: Procedure or Function 'sp_addError' expects parameter '@.InnerException', which was not supplied.

Thanks,

Pawe?

That depends on your Procedure declaration, you will have to allow a default parameter in orde to let it work.

CREATE PROCEDURE sp_addError
(
@.InnerException VARCHAR(1000) = NULL --Which declares the default if no value is passed through
)
(...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Cool :) Big thanks!

Pawe?

|||Thank you very much
It was solution of my Big problem