Solved

Create an incrementing field in VB ASP to insert into SQL

Posted on 2009-07-16
8
249 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
[X]
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
8 Comments
 
LVL 7

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.

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: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
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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:
Alpha Au 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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