Solved

Create an incrementing field in VB ASP to insert into SQL

Posted on 2009-07-16
8
245 Views
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!
0
Comment
Question by:chipsterva69
  • 5
  • 3
8 Comments
 
LVL 7

Expert Comment

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

0
 

Author Comment

by:chipsterva69
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?
0
 
LVL 7

Expert Comment

by:alphaau
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
begin
 
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)
 
end
 
 
end

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:chipsterva69
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.  
0
 

Author Comment

by:chipsterva69
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.
0
 
LVL 7

Accepted Solution

by:
alphaau earned 500 total points
ID: 24888037
how about this?

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

Author Comment

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

Author Closing Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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