Create an incrementing field in VB ASP to insert into SQL

Posted on 2009-07-16
Last Modified: 2012-06-27
I have a two part challenge for a web form I am putting together, and I am stumped on how to accomplish what I need to.

Background:  I am putting a web form together in Dreamweaver CS3 that will handle a nomination process that will ask users to rate a number of catagories for a nominee company.  I have the form itself assembled, but having issues with how to create and format some information that ITS is requiring in the database.

One piece is the dbase calls for a nomination_ID value to be inserted.  I had hoped to create a dbase query that pulls the highest nomination_ID value currently in the dbase, add 1, then set that as the value to be inserted with the new nomination.  The reason we need to do this is because the nominator can submit several nominees for several catagories, so need to keep track of what information goes with which nomination.

I am fairly competent in using Dreamweaver, but a definite newbie when it comes to SQL.  I hope to be able to get some idea of what the query should look like.

If it helps, here is a link to the nominating page:

I am going to post the second challenge in another posting, to spread around the points.  Thanks in advance!
Question by:chipsterva69
  • 5
  • 3

Expert Comment

by:Alpha Au
ID: 24875380
what db are you using? if using mssql server, you can use the id field as identity and it will auto increment.


Author Comment

ID: 24875461
we are using mssql, and we already have an id field that is incrementing (thanks though - that took me a bit to figure out).  

i am not explaining myself well, but the gist of it is that the field can't auto increment, because the number in question (i'll call it nominationID) is not dependant on the record or row count, it is dependent on the individual nominating.  one individual can nominate 6 companies, each with 14 criteria.  nominationID needs to be unique to the individual nominating a particular company but will remain consistent as each criteria is fed into the database. that same individual could then nominate another company, so the nominateID would increment by 1, with the individual info remaining the same, and then criteria for the second company.  we are tracking 14 criteria, so each nomination will have 14 data rows.  nominationID needs to remain the same for the first 14 inserts, then when another company is nominated, increment by 1, then remain the same for the next 14 inserts.

my thought process was to have a query check the table for the last nominationID, take that value and add 1, then use a dim statement to set that variable on the client form to populate the data in the insert.

does that make more sense?

Expert Comment

by:Alpha Au
ID: 24875512
is that mean you have 14 tables for the 14 criteria?

in that case, i would suggest to use one master table to handle the increment issue (you may treat it as company_id)

and after the insert to master table (the company table)
you extract the identity value and use it as the nominationID and insert others value to others table.

the store proc will be like this

create procedure insert_data(
@companyname varchar(100),
@value1 varchar(100),
@value14 varchar(100)
) as
declare @ID int
insert into MASTER_TABLE values(@companyname)
select @ID = @@identity  --this line get the identity value generated after the master table insert
insert into table1 values (@ID, @value1)
insert into table14 values (@ID, @value14)

Open in new window

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.


Author Comment

ID: 24877557
i wish it was like that, but nope.  the ITS dept wants it to be one table, with 1 row capturing each criteria, for a total of 14 rows for each nomination.  not sure why it has to be this way, but that is the restriction i have.  

Author Comment

ID: 24881191
okay - i have built a query that pulls the highest number in the nominationID field:

Set Recordset1_cmd = Server.CreateObject ("ADODB.Command")
Recordset1_cmd.ActiveConnection = MM_Dianaconnection_STRING
Recordset1_cmd.CommandText = "SELECT max(Nomination_ID) FROM dbo.nominationsonline"
Recordset1_cmd.Prepared = true

and I have a field on the confirmation form (it will be hidden or disabled so that it can't be changed):

<input name="textfield" type="text" id="textfield" value="<%=(Recordset1.Fields.Item("").Value)%>">

in the database itself, the field has a bigint datatype.

i have tried sevaral variations of changing that field code to:
value="<%=(Recordset1.Fields.Item("").Value) + 1%>" but all I generate is an error on the page -
Microsoft VBScript runtime error '800a000d'

Type mismatch

i am getting close, but just can't seem to figure out how to write that field value statement to add 1.

Accepted Solution

Alpha Au earned 500 total points
ID: 24888037
how about this?

<%=cint((Recordset1.Fields.Item("").Value)) + 1%>

Author Comment

ID: 24891839
That did it - thanks alphaau!!  I was missing the conversion first, then add.

Author Closing Comment

ID: 31604425
awesome job - thanks a million as always!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Weighted Randomizing 6 30
Html fieldset fix its height and width 4 32
Designing forms 3 18
Reg Exp to extract Url from string asp 12 15
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question