Re: trying to copy the autonumber from one table to the next
sparks_at_somewhere.net
Date: 12/06/04
- Next message: Bradley1234: "Re: Cant Connect to Data base"
- Previous message: JS: "Thread/architecture question"
- In reply to: Brian Brown: "Re: trying to copy the autonumber from one table to the next"
- Next in thread: Brian Brown: "Re: trying to copy the autonumber from one table to the next"
- Reply: Brian Brown: "Re: trying to copy the autonumber from one table to the next"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 06 Dec 2004 16:40:56 GMT
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();
On Mon, 6 Dec 2004 07:37:03 -0800, "Brian Brown"
<BrianBrown@discussions.microsoft.com> wrote:
>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.
>
>---------------------------------------
>http://support.microsoft.com/default.aspx?scid=kb;en-us;816112
- Next message: Bradley1234: "Re: Cant Connect to Data base"
- Previous message: JS: "Thread/architecture question"
- In reply to: Brian Brown: "Re: trying to copy the autonumber from one table to the next"
- Next in thread: Brian Brown: "Re: trying to copy the autonumber from one table to the next"
- Reply: Brian Brown: "Re: trying to copy the autonumber from one table to the next"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
