Help with multiple recordsets and pulling an ID value (ASP)

Report · Oct 15, 2012

I started this discussion when first developing my project and posted on the General Tab. I have figured out that issue (trying to get my Insert to work in ASP) but now am having trouble retrieving an ID value. It should be simple. I want to insert a new record into a single table and want to create a unique ID field. I am using SQL Server and tried the Identity setting but won't be able to have admin privileges which are required to set the ID. So, I'd like to just take the ID value from the last record and just add '1' to it to get a new unique ID manually. My INSERT portion was working, so I just decided to query the records to retrieve the last CatalogID and add '1' to get my new ID. But it's not working! I tried opening both a command and a recordset, but get various errors on invalid command use.

Any help would be great! I'm new to all of this!

I've posted a code snippet of what I thought I should do:

<%LANGUAGE = “VBSCRIPT” CODEPAGE=”65001”%)

<%

If (Cstr(Request(“MM_insert”)) = “form1”) Then

                Dim MM_editCmd
                Dim MM_Lookup
                Dim NextCatalogID

                Set MM_Lookup = Server.CreateObject (“ADOB.Recordset”)
                MM_Lookup.ActiveConnection = MM_CapConnect_STRING
                MM_Liikup.Source = “Select CatalogID From dbo.tblCatalogItems ORDERBY CatalogID Ascending”
                MMLookup.Open
                MM_Lookup.MoveLast
                NextCatalogID = MM_Lookup.Fields(“CatalogID”) + 1   'this is supposed to be my new ID value

Set MM_Lookup = Nothing

                Set MM_editCmd = Server.CreateObject(“ADOB.Command”)
                MM_editCmd.ActiveConnection = MM_CapConnect_STRING
                MM_editcmd.CommandText = “INSET INTO dbo.tblCatalogItems (CatalogID, ModelName, Description, POCName, [POCPhone]) VALUES (NextCatalogID, ?,?,?,?)
                MM_editcmd.Prepared = True
                MM_edit.Parameters.Append MM_editcmd.CreateParameter(“param2”, 202, 1, 100, Request.Form(“modelname”) )
              MM_edit.Parameters.Append MM_editcmd.CreateParameter(“param3”, 202, 1, 300, Request.Form(“Description”) )

…..

[Subject line edited by moderator to add server model]

Report · Oct 15, 2012

Ok, you've got lots of problems. First, explain again why you are not able to modify the CatologID to be an identity column? What is the datatype of that column now?

Next, you are going about selecting your recordset incorrectly. If you are just looking for the last value inserted, there is no need to return all values to the recordset. Just select the max value:

MM_Liikup.Source = “Select MAX (CatalogID) From dbo.tblCatalogItems”

Next, you have a typo in your insert statement "“INSET INTO" should be "INSERT INTO"

Next, your CommandText is wrong. There is no ending quote, and NextCatalogID will be treated as a string literal, not a variable. Use a ? placeholder for it and use the CreateParameter method to populate it.

Next, your command text includes 4 placeholder, but you are only populating 2.

But even when you get this to work it will be problematic. What happens when 2 users execute this at the same time? They both read the same value for the max record and increment by one, but the first will succeed and the second will fail. That's why it is always better to use an auto increment (Identity) column.

Report · Oct 15, 2012

Thanks for the reply. I'm sorry for the typos (INSET and missing quote) but I had to re-type the code into the message (long story but computer is not available to the internet). I did have the INSERT working with the NextCatalogID and I'm sorry I didn't type in the remaining parameters (thought the ellipse at the end would help indicate more code not printed). I do see that my NextCatalogId will not populate correctly as it looks like a literal, I can fix that.

Was the syntax for assigning the value correct?

NextCatalogID = MM_Lookup.Fields(“CatalogID”) + 1

The error I got when trying to set my variable to an Identity with a seed of '0' and increment value of '1' was that the Identity_Insert has to be set to ON. I tried adding SET Identity_Insert dbo.tblCatalogItems ON within my INSERT command and got permission denied since you need to be the owner, sysadmin or be a member of db admin. I don't want users to be in those groups when the site is up and running. Also, you bring up a good point about 2 users inserting at the same time. There will be a limited number of users who can insert but it is possible. Can I lock the recordset?

Report · Oct 15, 2012

>The error I got when trying to set my variable to an Identity with a

>seed of '0' and increment value of '1' was that the Identity_Insert has to be set to ON.

No, I mean alter the column in the table to be an Identity column. I don't use SQL server much, but see if this helps:

http://decipherinfosys.wordpress.com/2007/09/14/altering-a-regular-column-to-be-an-identity-column-i...

>Can I lock the recordset?

I think so but it's been a while.

Report · Oct 15, 2012

Thanks, but I started out by setting the column in my table to a primary key and with the Identity Set to Yes, Increment of 1 and seed value of 0. That's when I got the error that Identity_Insert has to be set to ON, so I tried setting it in my code. Then I got the error that I had to be an admin to turn the property ON! So, I went back to just trying to increment a value on my own and I'm not sure how to access the value once it is retireived from the query.

What is returned with the statement (i.e. how do I access the value?):

MM_Lookup.Source = “Select MAX (CatalogID) From dbo.tblCatalogItems”