Incrementing the contents of a record

I have an SQL database which holds 1500 records. One of the fields in each record is a registration number - S1W8E7D9E1N. I need to write a query that will allow me to append and increment numbers 25-1525. So the field in the first record would be S1W8E7D9E1N25, second would be S1W8E7D9E1N26 all the way to 1525. I'm in a little hot water here. I need to accomplish this today. All HELP would be appreciated!!!!!
spiderbuzzAsked:
Who is Participating?
 
mrichmonConnect With a Mentor Commented:
Not quite enougn details, but here is a guess with some assumptions.

I assume that each record has some unique ID or all have S1W8E7D9E1N and are ordered in some way.

Let's assume they all have a unique ID and S1W8E7D9E1N and are ordered by date

<cfloop from="25" to="1525" index="i">
<cfquery datasource="yourdsn" name="getrecord">
SELECT TOP 1* FROM yourtable ORDER BY TheDate
WHERE regnumber = 'S1W8E7D9E1N'
</cfquery>
<cfquery datasource="yourdsn">
UPDATE yourtable SET regNumber = <cfqueryparam cfsqltype="cf_sql_varchar" value="#regNumber##i#">
WHERE UID = getrecord.UID
</cfquery>
0
 
spiderbuzzAuthor Commented:
There is no date. The other fields are blank and will be filled in by the person registering. The person will receive a postcard through the mail which will have a registration number(the base code, S1W8E7D9E1N with the number(25-1575) appended to the base. All 1500 users will have a unique number, S1W8E7D9E1N25 -S1W8E7D9E1N1525. The records are not created yet. I must create the records with the numbering scheme so the user will be able to fill in the other fields. I hope this is a little more clear?
0
 
mrichmonCommented:
Not really.

So you don't have these 1500 records?

Or  the 1500 records don't have any registration yet?

I am very confused on wht you want since you provide so little detail.

However, the above logic should work in most cases.



Loop over the 25 to 1525

In each iteration of the loop
 --> Select the next record - how you do so is based on what you store...
--> create the next registration number and send it to the user


That is exactly waht I did above, but I assumed they already had the deffualt number entered.

You can adjust based on the details of your situation.... whatever those details may be...
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
spiderbuzzAuthor Commented:
Thanks for your input so far. Believe me I'm not trying to be vague. I have tried the code below as a test based on you suggestion and I'm getting an error telling me 'Variable REGCODE is undefined.' The regCode is the one "value" is referencing. The table is AbsolutRegCode, the field is RegCode. There are various other field in name, phone etc.


<cfloop from="25" to="35" index="i">
<cfquery datasource="petweekorg" name="getrecord">
SELECT TOP 1* FROM AbsolutRegCode
WHERE AbsolutRegCode.RegCode = 'S1W8E7D9E1N'
ORDER BY AbsolutRegCode.ID
</cfquery>
<cfquery datasource="petweekorg">
UPDATE AbsolutRegCode SET RegCode = <cfqueryparam cfsqltype="cf_sql_varchar" value="#RegCode##i#">
WHERE UID = getrecord.UID
</cfquery> --->
</cfloop>
</cfloop>
0
 
mrichmonCommented:
Oh whoops try this:

UPDATE AbsolutRegCode SET RegCode = RegCode + <cfqueryparam cfsqltype="cf_sql_varchar" value="#i#">
0
 
spiderbuzzAuthor Commented:
I finally getting what you doing. But my new error code is:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]The column prefix 'rsgetRecord' does not match with a table name or alias name used in the query.

I don't understand why it can't see the first query.
0
 
mrichmonCommented:
Can you post the actual code you are using?

Also what kind of SQL databse?  MySQL? MS SQL?
0
 
spiderbuzzAuthor Commented:
MS SQL

<cfloop from="25" to="35" index="i">
<cfquery datasource="petweekorg" name="getrecord">
SELECT TOP 1* FROM AbsolutRegCode
WHERE AbsolutRegCode.RegCode = 'S1W8E7D9E1N'
ORDER BY AbsolutRegCode.ID
</cfquery>
<cfquery datasource="petweekorg">
UPDATE AbsolutRegCode SET RegCode =  RegCode + <cfqueryparam cfsqltype="cf_sql_varchar" value="#i#">
WHERE UID = getrecord.UID
</cfquery> --->
</cfloop>

At the moment I have gotten around it by inserting all new records. I'm going to have to update them in a couple of days though. Any and all help would be appreciated.

0
 
Renante EnteraSenior PHP DeveloperCommented:
Hello spiderbuzz!

I think you need to create a code that will autogenerate registration number with prefix "S1W8E7D9E1N" upon insertion.  Let's say, I insert new record so, expected registration number should be "S1W8E7D9E1N25" then next "S1W8E7D9E1N26" and so on and so forth ... as what you specified on your question.

Suggestion :

Create another table for storage of your counter.
-------------    
RegCounter
-------------
  prefix - varchar(20)
  counter - integer
  -----------------------------
  prefix                | counter
  -----------------------------
  S1W8E7D9E1N  | 25

Do some query like this to get value of the registration number for insertion :
----------------------
Auto-Generate.cfm
----------------------
<!--- Get current value for the counter --->
<cfquery name="GetCounter" datasource="petweekorg">
  SELECT counter, prefix FROM RegCounter
  WHERE prefix = 'S1W8E7D9E1N'
</cfquery>

<cfset currentRegNumber = GetCounter.prefix&GetCounter.counter>

<!--- Check registration number existence --->
<cfquery name="CheckRegNumber" datasource="petweekorg">
  SELECT RegCode FROM AbsolutRegCode
  WHERE RegCode = '#currentRegNumber#'
</cfquery>

<cfif CheckRegNumber.recordcount>
  <!--- If exists update counter --->
  <cfquery datasource="petweekorg">
    UPDATE RegCounter SET counter = counter + 1
    WHERE prefix = 'S1W8E7D9E1N'
  </cfquery>

  <cfinclude template="Auto-Generate.cfm"> <!--- Execute again until get an available registration number to be saved --->
<cfelse>
  <!--- INSERT NEW RECORD HERE since registration number doesn't exists --->
  <cfquery datasource="petweekorg">
    INSERT INTO AbsolutRegCode(RegCode,Col1,Col2,...)
    VALUES('#currentRegNumber#','#Col1Value#','#Col2Value#',...)
  </cfquery>
</cfif>

Hope this helps.  Just try this.


Goodluck!
eNTRANCE2002 :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.