trying to copy the autonumber from one table to the next

  • Thread starter Thread starter sparks
  • Start date Start date
S

sparks

So far I have tried the microsoft version of this, and lost the table
LOl
so I tried this way to write to two tables carrying over the autoid
but so far I can not get the autonumber and put it in the other table.

thanks big time for any help
maybe I tried to do it the dumb way but it looks to me like this
should work
probably not :)

oh the passed values for person and member are are in the sql strings
at least I knew how to check that


sparks

==============================
the tblperson creates the autoid PersonID
but when I try to create it in tblmember I get an error on the first
ExecuteNonQuery.

==============================

public void SavePerson (Person person,Member member)
{
Connection.Open (); //ToShortDateString()
try
{
String sqlString = "insert into
tblPerson (ContractNumber,Name, Address, PhoneNumber, barcode)"+
"values (" +
person.getContractNumber() + ",'" +
person.getName() +"','" +
person.getAddress() +"','" +
person.getPhoneNum() +"','" +
person.getBarcodeNum() + "')";
OleDbCommand command = new
OleDbCommand (sqlString, Connection);

command.ExecuteNonQuery();
OleDbCommand cmd = new
OleDbCommand("SELECT @@IDENTITY", Connection);
int nId = (int)cmd.ExecuteScalar();
---------------------------------------------------------------------------------------------------------
String sqlStr = "insert into tblMember (personID,MonthlyFee,paidup)"+
"values (" + nId +
member.getFee() + "," + member.getMemValidation() + ")";
OleDbCommand comma = new OleDbCommand (sqlStr, Connection);

command.ExecuteNonQuery();
Connection.Close();
}
finally
{
if (Connection!= null)
{
if (Connection.State ==
ConnectionState.Open)
Connection.Close();
}
}
 
Sparks,

You cannot call just SELECT @@IDENTITY. Sql has no way of knowing what
record you want to look for. Would it be possible for you to write a stored
proc and send it your parameters? Then you could use a return statement in
the stored proc to return the ID of the inserted record and then grab it
using ExecuteScalar(). This is a lot more efficient and reliable all the way
around.

Good Luck!
 
Hi,

Actually you can say just SELECT @@IDENTITY.
To prove this just run the line in query analyser. Running it on it's own
you get an unnamed column with the value NULL.

The problem with the original post is more likely that the original poster
is not running the INSERT and the @@IDENTITY
select in the same command. Therefore @@IDENTITY will be null, if you ran it
within the command as the INSERT you would
get a number back.

Bye,
Pete
 
this is what I found at microsoft

// Event Handler for RowUpdated Event
private static void HandleRowUpdated(object sender, OleDbRowUpdatedEventArgs e)
{
if (e.Status == UpdateStatus.Continue && e.StatementType ==
StatementType.Insert)
{
// Get the Identity column value
e.Row["PersonID"]=Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString());
System.Diagnostics.Debug.WriteLine(e.Row["personID"]);
e.Row.AcceptChanges();
}
}


====================
cmdGetIdentity = new OleDbCommand();
cmdGetIdentity.CommandText = "SELECT @@IDENTITY";
cmdGetIdentity.Connection = Connection;


I could not make this work I guess I am stupid.
but as you can see they used the SELECT as well.
that is where I got the idea of how I did it.
 
Please forgive me if I was not clear. You can use just SELECT @@IDENTITY on
Sql Server but It needs to be in the same batch as the Insert statement
(hence the suggestion of the stored proc). If it comes after the batch Sql
has no idea what table or row to select the identity from. What Pete says is
correct you run the SELECT @@IDENTITY in the same command which will keep it
within the same batch and Sql will know what IDENTITY you are looking for.

Sparks - If my guess is correct the link below is the link that you are
using to get the Identity from your database. This method is will only work
if you are using a Jet Database (e.g. MS Access) because Jet databases do not
support multi statement batch commands (which is different from SQL Server).
If you are using Access the method described in this URL should work but if
you are trying to apply it to SQL Server it will not. If you are still
having trouble please reply to this post with what database you are trying to
get the IDENTITY from and I will try to help you further.
 
Ok I think I understand...and yes this is an access 2000 database

but when I try to do 2 commands on one connection it errors out
String sqlString = "insert into tblPerson
(ContractNumber,Name, Address, PhoneNumber, barcode)"+
"values (" + person.getContractNumber()
+ ",'" +
person.getName() +"','" +
person.getAddress() +"','" +
person.getPhoneNum() +"','" +
person.getBarcodeNum() + "')";
OleDbCommand command = new OleDbCommand (sqlString, Connection);

==============================================
==============================================
this is where I get confused...I need to write the person out and get the
personID(autonumber) to use in creating the new tblmember personID
==============================================
do I need 2 command strings and how can you pass 2 strings to the
command.ExectureNonQuery
==============================================
OleDbCommand cmd = new OleDbCommand("SELECT @@IDENTITY", Connection);
int nId = (int)cmd.ExecuteScalar();
==============================================
==============================================
==============================================
command.ExecuteNonQuery();

String sqlStr = "insert into tblMember (personID,MonthlyFee,paidup)"+
"values (" + nId + member.getFee() + "," + member.getMemValidation() + ")";
OleDbCommand comma = new OleDbCommand (sqlStr, Connection);
command.ExecuteNonQuery();
Connection.Close();
 
Sparks,

I think that what is happening is that you are not executing the first batch
before you try to execute the second batch. You cannot do multiple batch
statements in MS Access. In other words you should execute the first command
(the one that you have named “commandâ€) before you try to execute the next
command (the one named “cmdâ€). I have written a sample console app below to
demonstrate what I am talking about. You should be able to modify the app to
work with your database with little effort if you would like to step through
the code. Don't forget that this is not production code. A good deal of
exception handling should be placed in this code first. Please let me know
if this helps.

Good Luck!
------------------------------------------------------------------------------

using System;
using System.Data;
using System.Data.OleDb;

namespace ConsoleTestApp
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
try
{
string sqlString_one = "insert into person (Name) VALUES ('John Doe')";

OleDbConnection conn = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\MyJetTest.mdb");
conn.Open();
OleDbCommand cmd_one = new OleDbCommand (sqlString_one, conn);
cmd_one.ExecuteNonQuery();

string sqlString_two = "SELECT @@IDENTITY";
OleDbCommand cmd_two = new OleDbCommand(sqlString_two, conn);
int id = Convert.ToInt16(cmd_two.ExecuteScalar());

Console.WriteLine(id.ToString());

conn.Close();
conn.Dispose();
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}
}
 
Well I ALMOST have it
when I run this
I end up with 2 records in tblperson...exactly the same and nothing is written
to tblmember is this because of the connection still being open thru the entire
process?
=================================================

public void SavePerson (Person person,Member member)
{
Connection.Open (); //ToShortDateString()
try
{
String sqlString = "insert into tblPerson (ContractNumber,Name, Address,
PhoneNumber, barcode)"+
"values (" + person.getContractNumber() + ",'" +
person.getName() +"','" +
person.getAddress() +"','" +
person.getPhoneNum() +"','" +
person.getBarcodeNum() + "')";
OleDbCommand command = new OleDbCommand (sqlString, Connection);
command.ExecuteNonQuery();
string sqlString_two = "SELECT @@IDENTITY";
OleDbCommand cmd_two = new OleDbCommand(sqlString_two, Connection);
int nId = Convert.ToInt16(cmd_two.ExecuteScalar());
String sqlStr = "insert into tblMember
(personID,MonthlyFee,paidup)"+
"values (" + nId + "," + member.getFee() + "," + member.getMemValidation() +
")";
OleDbCommand comma = new OleDbCommand (sqlStr, Connection);
command.ExecuteNonQuery();
Connection.Close();


Sparks,

I think that what is happening is that you are not executing the first batch
before you try to execute the second batch. You cannot do multiple batch
statements in MS Access. In other words you should execute the first command
(the one that you have named “command”) before you try to execute the next
command (the one named “cmd”). I have written a sample console app below to
demonstrate what I am talking about. You should be able to modify the app to
work with your database with little effort if you would like to step through
the code. Don't forget that this is not production code. A good deal of
exception handling should be placed in this code first. Please let me know
if this helps.

Good Luck!
------------------------------------------------------------------------------

using System;
using System.Data;
using System.Data.OleDb;

namespace ConsoleTestApp
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
try
{
string sqlString_one = "insert into person (Name) VALUES ('John Doe')";

OleDbConnection conn = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\MyJetTest.mdb");
conn.Open();
OleDbCommand cmd_one = new OleDbCommand (sqlString_one, conn);
cmd_one.ExecuteNonQuery();

string sqlString_two = "SELECT @@IDENTITY";
OleDbCommand cmd_two = new OleDbCommand(sqlString_two, conn);
int id = Convert.ToInt16(cmd_two.ExecuteScalar());

Console.WriteLine(id.ToString());

conn.Close();
conn.Dispose();
}
catch(Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}
}
Click to expand...
 
I see the problem. It is because right before you close the connection you
are using command.ExecuteNonQuery() not comma.ExecuteNonQuery(). In other
words you just ran the first command again. Change this and it should work
ok.

Good Luck.
 
THAT was IT YES CHEER CHEER
thank you VERY MUCH for the help

I hate to do it but I must ask one more thing

string sCommand = "Select * From tblPerson Where (ContractNumber =
ContractNumber)"+" AND "+"(Name = Name)";

I pass the correct number and name but it always reads the first file in the
table
is my syntax messed up?

ps can you tell me a good book to start reading on ado.net
this is my first try at this and it gets a little over my head quick

thanks again for all your help

sparks
 
You are welcome! Glad I could help.

Try...

string sCommand = "Select * From tblPerson Where (ContractNumber = '" +
strContractNumber + "') " + "AND (Name = '" + strName + "' )";

The two that are prefaced with "str" are variables. That should work for
you.

My favorite ADO.net book is the core reference by David Sceppa (I have
posted the link for you below). Don't let the name fool you it is an easy
ready and has good techniques. Again, I am glad I could help!
 
Back
Top