How to insert rows into tables with Autonumber PKey by using DataSet and DataAdapter?

[A.M-SG]

Hi,



I have a strongly typed DataTable that represent a table with database with
a primary key.



I am trying to use the DataTable to add a new row to the database table by
using a DataAdapter. Since the primary key is autonumber, I have to leave
the primary key column blank, so my insert stored procedure can return the
key to the DataAdapter and DataAdapter assign the key value back to the
DataTable column through the output parameter.



I assume that above process is standard way to insert rows by using typed
DataTables.



The problem is that I cannot insert a new row with blank primary key into
the typed DataTable. I am sure that I am missing a small point. How can I
add a new row without primary key value into the column?



Any help would be appreciated,

Alan

 

[Kevin Yu [MSFT]]

Hi Alan,

I think there might be something wrong with the DataAdapter.InsertCommand
property. Could you post your insert command here? I assume you have
specified the primary key value as null in the insert command, while it has
to be left untouched, and let the SQL server add value itself.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[A.M]

Hi Kevin,

I think there might be something wrong with the DataAdapter.InsertCommand
property.

The problem happens before I use DataAdapter

I need to create a new DataRow with NULL primary key and add it to DataTable
then ask the Adapter.Update to persist it.
The problem is that I cannot add such DataRow to the DataTable because the
in the DataTable's schema, the Key column is "Allow Null=False"

In other words, you must have the PK value to be able to create the DataRow
and add it to the DataTable. I don't have the PK value untill I call the
stored procedure.

Thanks for help
Alan

 

[Bart Mermuys]

Hi,

Hi Kevin,

The problem happens before I use DataAdapter

I need to create a new DataRow with NULL primary key and add it to
DataTable then ask the Adapter.Update to persist it.
The problem is that I cannot add such DataRow to the DataTable because the
in the DataTable's schema, the Key column is "Allow Null=False"

In other words, you must have the PK value to be able to create the
DataRow and add it to the DataTable. I don't have the PK value untill I
call the stored procedure.

Ussually if the PK is a numeric identity column (eg. int) then you use a
temporary key by setting the PK DataColumn to AutoIncrement and
AutoIncrementStep to -1.

Then when DataAdapter.Update is called, the temporary keys are replaced with
the real one.

Offcourse for this to work, you must not include the pk column name inside
the "INSERT INTO" statement.

It's commonly done this way.

HTH,
Greetings

 

[A.M-SG]

Thanks Bart, Your post solved my problem!



Thanks alot

 

[Guest]

Hi, I have de same problem,I think, can you help me?
I have a store procedure to insert rows, nad I use a tableadapter to execute
the store procedure, but when I run my application I see the row added but
when I return to my database the row don't exist. My code to execute the
store procedure is:

dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter prensa = new
Prensas.dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter();
int i = prensa.Update(ID, this.txtNombre.Text, int.Parse(txtNumero.Text));

My store rpocedure code is :
ALTER PROCEDURE spu_Prensa

(
@ID int=0,
@sNombre varchar(15),
@bNumero int =0
)

AS
if @ID=0
begin
insert into tPrensa (sNombre,bNumero)
values (@sNombre,@bNumero)
end
else
begin
update tPrensa
set sNombre=@sNombre, bNumero=@bNumero
where ID=@ID
end
RETURN

And I use Id as int autoincrement too.
I hope you can help me!
Regards.

 

[A.M-SG]

Hi Diana,

The 1st thing that I see in you SP code is it doesn't return the new PK
value to the DataAdapter

Have a look at this article. It explains everything except that you must set
AutoNumberIncrementStep -1

http://www.codeproject.com/cs/database/relationaladonet.asp



HTH

 

[Bart Mermuys]

Hi,

Hi, I have de same problem,I think, can you help me?
I have a store procedure to insert rows, nad I use a tableadapter to
execute
the store procedure, but when I run my application I see the row added but
when I return to my database the row don't exist. My code to execute the
store procedure is:

dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter prensa = new
Prensas.dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter();
int i = prensa.Update(ID, this.txtNombre.Text, int.Parse(txtNumero.Text));

What's i ? If it is 1 then the update succeeded and the row is stored inside
the DB. The reason you don't see it might be caused by a new feature in
VS2005: storing your DB file inside your project ( Solution Explorer ). The
simpliest workaround is not to choose "copy the DB into project" when you
configure a DataSource or DataConnection. You can still read/modify the DB
using Database Explorer window. (see also http://tinyurl.com/deuzx )

My store rpocedure code is :
ALTER PROCEDURE spu_Prensa

(
@ID int=0,
@sNombre varchar(15),
@bNumero int =0
)

AS
if @ID=0
begin
insert into tPrensa (sNombre,bNumero)
values (@sNombre,@bNumero)
end
else
begin
update tPrensa
set sNombre=@sNombre, bNumero=@bNumero
where ID=@ID
end
RETURN

I'm not entirely sure, but don't you need to use different sp's for insert,
update and delete commands so that the TableAdapter correctly works when
updating DataTable too...

ALTER PROCEDURE spu_PrensaInsert
( @sNombre varchar(15),
@bNumero int
)
AS
ÏNSERT INTO tPrensa (sNombre,bNumero)
VALUES (@sNombre,@bNumero);
SELECT * FROM tPrense WHERE ID=SCOPE_IDENTITY();
RETURN

Use this sp for the insert command on the TableAdapter and then use:

dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter prensa = new
Prensas.dbPrensas2006DataSetTableAdapters.spq_PrensaTableAdapter();

' direct insert
int i = prensa.Insert(this.txtNombre.Text, int.Parse(txtNumero.Text));


HTH,
Greetings

 

[Guest]

Sorry, but I try it and I can´t , now I change using commands, but when I
close the application and reun again the rows aren´t there, in the data base,
now my code is:
SqlConnection sqlConnection1 = new SqlConnection(@"Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbPrensas2006.mdf;Integrated Security=True;User Instance=True");
SqlCommand cmd = new SqlCommand();
Object returnValue;

cmd.CommandText = "spu_Prensa";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;
cmd.Parameters.Add("@ID",SqlDbType.Int).Value=ID;
cmd.Parameters.Add("@sNombre",SqlDbType.VarChar ,15).Value =
txtNombre.Text;
cmd.Parameters.Add("@bNumero",SqlDbType.Int).Value =
int.Parse(txtNumero.Text);
sqlConnection1.Open();
cmd.ExecuteNonQuery();

sqlConnection1.Close();
this.Close();

Help with some idea please!!
Thanks a lot
Regards!!

 

[Bart Mermuys]

Hi,

Sorry, but I try it and I can´t , now I change using commands, but when I
close the application and reun again the rows aren´t there, in the data
base,
now my code is:
SqlConnection sqlConnection1 = new SqlConnection(@"Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbPrensas2006.mdf;Integrated
Security=True;User Instance=True");

The "|DataDirectory|" alias is what's causing the problem, you should not
use it, it doesn't work well within an IDE env, use an absolute path to the
DB file instead. The result of using "|DataDirectory|" and a DB file in
your vs project is that each time your app starts it will copy the DB from
your project directory into the \bin\debug folder and use that one, next
time your app starts it gets overwritten and all changes are lost.

Can you make a simple fresh project ? Create a new project and add a new
Data Source (Data Source window), when you create the Data Source, the
wizard will ask if you want to copy the DB inside your project then it's
important to say "No". Next drag a DataTable from the Data Sources window
to the Form and you end up with a complete data entry Form. Now run it and
see if updates work.

If you have chosen to copy the DB into your project, then the
connectionstring will use "|DataDirectory|" inside .app.config, if you
have chosen NOT to copy the DB into your project then the connectionstring
within app.config will use an absolute path (which works better).

I hope this makes it clear.

HTH,
Greetings

 

[A.M-SG]

Hi Diana,

Have you used profiler to track what is the SQL command that database
receives?

If you issue the same command within query analyzer, do you have rows in the
table?

The reason that I asked is that I think it is not your c# code issue and it
might be database side issue.

Alan

 

[Guest]

Hi, thanks a lot!! I found the error, Bart your idea was excellent, the
problem was that my dataset make a copy in debug directory and allways when I
run my application this overwrite, I don´t need to make a new dataset only I
look the mdf file properties inside VS2005 and it have a Copy to output
directory property, and I had copy always and now I change it to Copy if
newer and it solve my problem.

Realy, thanks a lot for your help!!!
I love you!!! jejeje
See you.
Regards