Solved

Incrementing the contents of a record

Posted on 2004-10-19
9
165 Views
Last Modified: 2013-12-24
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
Comment
Question by:spiderbuzz
  • 4
  • 4
9 Comments
 
LVL 35

Accepted Solution

by:
mrichmon earned 500 total points
ID: 12352303
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
 

Author Comment

by:spiderbuzz
ID: 12352438
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 12352640
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
 

Author Comment

by:spiderbuzz
ID: 12352859
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 35

Expert Comment

by:mrichmon
ID: 12352885
Oh whoops try this:

UPDATE AbsolutRegCode SET RegCode = RegCode + <cfqueryparam cfsqltype="cf_sql_varchar" value="#i#">
0
 

Author Comment

by:spiderbuzz
ID: 12352978
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 12353209
Can you post the actual code you are using?

Also what kind of SQL databse?  MySQL? MS SQL?
0
 

Author Comment

by:spiderbuzz
ID: 12353896
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
 
LVL 14

Expert Comment

by:Renante Entera
ID: 12356714
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

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Join & Write a Comment

Suggested Solutions

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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.

708 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

11 Experts available now in Live!

Get 1:1 Help Now