Dear friends,

My requirement is : I need to add one record to a table. This will generate a new primary key. I have to get the new value and then insert a number of records into another table. Here is how I have done it in PHP. I want to do the same in ASP.

I know it is wrong to post PHP codes in this forum. But I want to explain a situation and the code is not that difficult also.

if(mysql_query("LOCK TABLES tbl_basic_submission WRITE",$cid_mine))
    {
        }
    else{
        echo(mysql_error());
    }

    $qry_insert_basic_submission = "insert into tbl_basic_submission (".$str_fields.") values(".$str_values.")";
    if(mysql_query($qry_insert_basic_submission,$cid_m ine))
    {
    }
    else{
        echo(mysql_error());
    }

    $i_order_id = func_getfield($str_connection,"select max(order_id) from tbl_basic_submission");
    //echo($i_order_id);
    if(mysql_query("UNLOCK TABLES",$cid_mine))
    {
    }
    else{
        echo(mysql_error());
    }

This will first lock the table. Then insert one record. Then get the primary key of the newly inserted record.

However the same thing could not be done in ASP. Because, I could not find a way to lock the table for write operation. So the primary key value returned may not be correct as in between another record may have got inserted.

Is there any similar method in ASP & SQL server ?

Hi Madhu,

But this has been answered by Imar in the other post I believe. He was too fast to reply your other post;).

Check out here. Creating a log file for multiple users

Cheers!

_________________________
-Vijay G
Strive for Perfection

Sure, no problem. But is there something to discuss further?

Didn't my previous post answer your question?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: 18 by Moby (Track 12 from the album: 18) What's This?

Well Madhu,

what database are you using with ASP ..

If you are using ACCESS then there is a locktype enumeration about which you can find out easily from MSDN ... bcoz i dont have any such code snippet just now ...

but if you are using some other database see how it can locked by using come command ... when you find that use that lock and i think that would end your troubles ...

Sudhan.

Hello Imar,

I have tried all the methods except the GUID method suggested by you. I could not find out the the method of generating GUID.

I have decided to use the first method. I am making the primary key field a random string. Then I will generate a random string and append it with date and time (including microtime). Then use that as the primary key as well as the key. Is there anything wrong with it ?

Hi madhukp,

Your method looks fine, but it may depend on your specific scenario and the random algorithm. If you have a low-volume application, then chances are minimal that you end up with a duplicate key.
The Guid method is pretty similar to this, but Guids are guaranteed to be unique, even if you're inserting your data on multiple machines and / or databases.

If you're using SQL Server, creating a new Guid (which stands for Globally Unique IDentifier) is as simple as calling NewID()
Alternatively, you can use API calls to create your own Guid. If you search Google for guid vb api I am sure you'll find useful links. .NET has built-in support for Guids, but that requires you to have .NET, of course.

One thing I haven't mentioned before is the use of a natural key. You cannot use this in all scenarios, but when it is applicable, it can be very useful. For example, Members of your Web site can be identified uniquely by an e-mail address or member name, instead of generating a (meaningless) unique ID in the form of a number or random string for them.

Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Let Me Down by Limp Bizkit (Track 10 from the album: Results May Vary) What's This?