• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

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!!!!!
0
spiderbuzz
Asked:
spiderbuzz
  • 4
  • 4
1 Solution
 
mrichmonCommented:
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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
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 EnteraCommented:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now