Hi Everybody!
I have came with the solution, it works fine ,please comment if i m wrong here!
Anywys DataReader is also used to insert the values in db.As dataReader is
optimised way to do DML process. As stated in many articles it is read only
forward based row reader.But it also do above things too.Please do try this
out!
public void OnAddEmpRecords()
{
Response.Write("Tried successfully");
string strFirstName=txtFirstName.Text.ToString();
string strLastName=txtLastName.Text.ToString();
string strAssociateNo=txtAssociateNo.Text.ToString();
string strWorkstationNo=txtWstNo.Text.ToString();
string strEstimatedNo=txtWkDone.Text.ToString();
string strLocation=dbLocation.SelectedItem.Value;
string strBusiness=dbBusiness.SelectedItem.Value;
string strQueryInsertEmpDetails="INSERT INTO employee_details
values('"+strFirstName+"','"+strLastName+"','asbestos','advisor','"+strAssociateNo+"','"+strLocation+"','"+strBusiness+"','"+strWorkstationNo+"','1','"+strEstimatedNo+"')SELECT
@@IDENTITY";
SqlCommand objCmd=new SqlCommand(strQueryInsertEmpDetails,MyConnection);
SqlDataReader dr;
dr=objCmd.ExecuteReader();
dr.Read();
Response.Write("Reader"+dr[0]);
//Above Response will get u a inserted primary key-
}
Chris Hynes <[EMAIL PROTECTED]> wrote:
Also, just a small note -- it's probably better to use SCOPE_IDENTITY()
instead of @@IDENTITY.
Chris
-----Original Message-----
From: Dominique Plante [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 26, 2005 5:57 PM
To: [email protected]
Subject: Re: [AspNetAnyQuestionIsOk] Retrieving ID from a database after
INSERT
the short answer is that ExeuteNonQuery will not return the value you're
expecting, but ExecuteScalar does...
According to MS docs for ExecuteNonQuery, "For UPDATE, INSERT, and DELETE
statements, the return value is the number of rows affected by the command.
For all other types of statements, the return value is -1. If a rollback
occurs, the return value is also -1."
So I put together something really quick for you that works (with SQL Server
2000), and you can experiment with you need to. Below is some SQL code to
create a table and a sproc that inserts a value into the table and return
the ID of the just-inserted record. The NUnit 2.2 C# class after that
simply calls the sproc, and asserts the ID is > 0....
you can debug this code to be more comfortable with it if you like :)
Hope this helps!
Dominique
------------------------
use nhibernate -- pick a database
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FooTest1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FooTest1] GO
CREATE TABLE [dbo].[FooTest1] (
[DomID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (15)
)
GO
select * from FooTest1
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[InsertIntoFoo]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[InsertIntoFoo]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE InsertIntoFoo
@Name nvarchar(15)
AS
begin
insert into FooTest1 (Name) values(@Name)
select @@identity
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--- Testing 1.2.3....
--- exec InsertIntoFoo 'abc'
----------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using NUnit.Framework;
namespace ClassLibrary1
{
/// <summary>
/// Summary description for TestGetIdentityAfterInsert.
/// </summary>
[TestFixture] public class TestGetIdentityAfterInsert
{
public TestGetIdentityAfterInsert()
{
//
// TODO: Add constructor logic here
//
}
[Test] public void TestInsertNameUsingSproc()
{
SqlConnection conn = new SqlConnection("Initial
Catalog=nhibernate;Data Source=localhost;Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "InsertIntoFoo";
SqlParameter myParm = cmd.Parameters.Add("@Name",
SqlDbType.NVarChar, 15);
myParm.Value = "d123";
conn.Open();
int res = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
Assert.IsTrue(res > 0);
}
}
}
On Wed, 26 Jan 2005 10:15:35 -0000, msilver99 <[EMAIL PROTECTED]> wrote:
>
>
> I'm saving details of a Web Service to an (SQL Server 2000) database,
> whos PKF is an auto-incrementing number. The values insert OK but I'm
> having trouble getting the PKF back after the Insert has been
> executed.
>
> I'm using a commandbuilder to dynamically create SQL, dataset and
> datarow attributes to assign the values to the columns and then using
> the DataAdapter.Update(dataset) command to write the SQL and commit
> the changes.
>
> How exactly can I get the PKF which is assigned by the database back
> after doing this? I've read about using "@@IDENTITY" but it doesn't
> seem to work (or more likely, I'm using it wrong!).
>
> Am I wrong in thinking this is enough? If not, can anyone advise me
> on how this can be done.
>
> Dim getid As Odbc.OdbcCommand = New Odbc.OdbcCommand("SELECT
> WebServiceID from webservices where (WebServiceID = @@IDENTITY)",
> theconn)
>
> Dim idis As Integer = getid.ExecuteNonQuery
----------------------------------------------------------
dom.website = http://www.binaryshift.com
Yahoo! Groups Links
---------------------------------
Yahoo! Groups Links
To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
Yahoo! India Matrimony: Find your life partneronline.
[Non-text portions of this message have been removed]
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
