Solved

Incrementing the contents of a record

Posted on 2004-10-19
9
170 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
[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
  • 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 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