adding record to existing DB; id field blank; needs to increment 1

[artguy]

I must not be searching for the right keywords because I can't find a direct answer for this:

I'm trying to add a record to an existing MS SQL database with ASP and it works but the ID field ends up blank. I'm assuming the person that setup the DB set that field to auto increment but I don't know how to check it (No enterprise manager) or fix it if it isn't set up that way. So, I'm trying to work around it by attempting to use something like:

strSQL = "SELECT MAX(id) as IDNum FROM detailtable;"

and then incrementing IDNum by 1 for the new record id. That doesn't seem to give me the maximum record value though.

I'm new to database interaction so any guidance would be appreciated.

Bob

 

[amrita418]

If you are able to insert a value into a column and sql does not throw an error then the field is not set up as identity.

If you have query analyzer then try
sp_columns 'tablename'
-- the column type_name should tell you if the column is identity.

What is the data type of the column?

Regards,
AA

 

[Catadmin]

Getting client tools, like EM, would be the best way to check it. You need to know if the field is an identity field (which is the auto-increment feature you're talking about).

I doubt it is, if your insert is working and it's leaving the ID blank. The way Identity works is that it increments anytime something is inserted on the table level, no matter what the source.

There are 2 possibilities. 1, the designer has a table keeping track of IDs for various tables (CustID, ReceiptID, etc.) that some code increments and replaces or 2, the ID insert is happening in the application, but you're not finding it.

BTW, your SQL String above will only take the last number and duplicate it into the next ID. What you need is to set a variable in your code to equal the Max# plus one then do your insert statement with that value. I advise only doing this AFTER you have verified whether or not one of the other two methods is being used. Changing the way your process IDs in midstream will cause data issues further down the line.

Does that make sense?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[vongrunt]

> I'm assuming the person that setup the DB set that field to auto increment but I don't know how to check it (No enterprise manager)

To check whether table has identity column or not, use (from ASP):

SELECT IDENT_CURRENT('tablename')

Returned NULL value means 'no identity column'.


> I'm trying to add a record to an existing MS SQL database with ASP and it works but the ID field ends up blank

I guess detailtable initially has no rows, in which case MAX() will return NULL. Is that correct?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[artguy]

I don't think I have query analyzer, or if I did I know nothing about it. It sounds handy from what I read.

Catadmin, yup. That was the plan to increment by one once I did get the right number back. There are 2 tables. One that lists dealers and the other that keeps a list of counties and their corresponding zip codes.

vongrunt, the table I'm dealing with has 70 rows and I'm able query them all and display them. I'm afraid I'm not sure how to implement that "SELECT IDENT_CURRENT('tablename')" and be able to spit out a value. I did this and got nothing:

strSQL = "SELECT IDENT_CURRENT('detailtable') as IDNum;"
Set dealerdb = Server.CreateObject("ADODB.Recordset")
dealerdb.Open strSQL, CONN_STRING, adOpenKeyset, adLockOptimistic, adCmdText
response.write IDNUm

It sounds like I'm at least headed in the right direction once I can get the Select Max() to work. Which for that all I'm doing is:

strSQL = "SELECT MAX(id) as IDNum FROM detailtable;"
Set dealerdb = Server.CreateObject("ADODB.Recordset")
dealerdb.Open strSQL, CONN_STRING, adOpenKeyset, adLockOptimistic, adCmdText
response.write IDNUm

which spits out nothing as well.

Thanks for the help.

bob

 

[vongrunt]

> response.write IDNUm

Um... no. Try:

Response.Write dealerdb("IDNum")

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[artguy]

Um... no.

When I said I was new to this, I meant NEW!

Well, now that I did that it spits out nothing which means there isn't an identity column.

I went back to my original statement and fixed that little faux pa you pointed out and now it outputs the correct max number I expected. Now I'll just increment by one and I should be good to add my records, I hope.

Thanks for the help!