Create an incrementing field in VB ASP to insert into SQL

Posted on 2009-07-16
Medium Priority
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: http://www.healthcaredistribution.org/membership/award_diana-50th-cat8-13-nom.asp

I am going to post the second challenge in another posting, to spread around the points.  Thanks in advance!
Question by:chipsterva69
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

719 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