INSERT help 2

[JontyMC]

I am creating code for a forum. I have a table of topic titles with columns TopicID, TopicTitle and Author. The TopicID is auto-increment. For the author to start a topic, they have to submit a post and topic title. I have another table of posts with TopicID, Post and Author. How can I create a statement that inserts a new topic title into topic titles table and inserts the post into post with the same TopicID? I'm using Access.

Thanks,

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[DotNetGnat]

You can get the TopicID from the topic titles table using

SELECT @@IDENTITY FROM Topic

then using this ID you can update Posts table...

-DNG

 

[JontyMC]

How do you combine this with the insert?

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[JontyMC]

Anyone help?

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[kavin]

hi jon

In MS-access @@identity does not work, for that, u have to query the tables as follows:

step 1 : Insert record in Title table
step 2: get the max(titleID) from table
step 3: insert record in Post table using that max(titleID)

this all should be in a transaction.

But in case of SQL, u can use @@identity variable which contains the last created Identity.

kavin

 

[JontyMC]

Thanks, what happens if two records get inserted at the same time by different users though?

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[JontyMC]

OK, this all has to be done in a transaction. I'm quite new to this. Could you provide some code? I'm using C# ASP.net

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[vbkris]

use a stored procedure. i am assuming that u know some basic details regarding a stored proc.

create procedure InsertIt [PASS parameters]
declare @NewId int --This should be the same as auto increment field type.
begin transaction
insert into first table
set @NewId=@@IDENTITY
insert into next table using @NewID
commit transaction



in C# .net the simplest way is to open a SqlDataReader that has the following stmt:
"exec InsertIt PARAMETERS"


Known is handfull, Unknown is worldfull

 

[JontyMC]

Thanks, thats great, but I need it to work in MS Access.

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[vbkris]

oops jumped the gun. i will see if that is possible in access and get back...

Known is handfull, Unknown is worldfull

 

[vbkris]

This is in VB.NET
Dim sql as string="insert query"
'Execute It

sql="select @@IDENTITY"
This will return the ID of the last inserted record...

Known is handfull, Unknown is worldfull

 

[JontyMC]

OK, this works, but I have another problem. I'm using the OleDbTransaction object in .net to create a transaction to do this. It works, but in my posts table it inserts about 30-40 new rows (all identical) instead of just the one. Any ideas why?

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[vbkris]

nope, 30 to 40 rows? i guess if i can have a look at ur code i could say something...

Known is handfull, Unknown is worldfull

 

[JontyMC]

OleDbConnection con = new OleDbConnection(@"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=""C:\Inetpub\[URL unfurl="true"]wwwroot\ArdleyUnited\db.mdb"";Jet[/URL] OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1");
OleDbCommand cmd = con.CreateCommand(); 
con.Open(); 
OleDbTransaction myTrans;
myTrans = con.BeginTransaction();
cmd.Connection = con;
cmd.Transaction = myTrans;
cmd.CommandText = "INSERT INTO Threads (Thread, [Date], Username) VALUES ('" + this.topic.Text + "', '" + DateTime.Now.ToString() + "', '" + user + "')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Posts (ThreadID, Username, Post) SELECT @@IDENTITY As ID, 'User' As UserName, 'Post' As Post FROM Threads";
cmd.ExecuteNonQuery();
myTrans.Commit();

Jon

"Asteroids do not concern me, Admiral. I want that ship, not excuses.

 

[vbkris]

seems ok, is there a chance of the file or the sub being called more that once?

Known is handfull, Unknown is worldfull

 

[kavin]

Hi Jon

You dont worry about the max() while using transaction.
If you have transaction open, your application will take care about it. but only thing need to know that u must have getting max() + 1 just above the insert statement.

kavin