Solved

Create an incrementing field in VB ASP to insert into SQL

Posted on 2009-07-16
8
244 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:chipsterva69
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:chipsterva69
Comment Utility
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
Comment Utility
how about this?

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

Author Comment

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

Author Closing Comment

by:chipsterva69
Comment Utility
awesome job - thanks a million as always!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now