468,483 Members | 2,619 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,483 developers. It's quick & easy.

Is there a way to change my primary key in Access using ASP?

Is there a way to change my primary key in Access using ASP?

My primary key looks like this:
1101001CXR001MS01-01 thru 1101001CXR001MS01-24

I want to change MS01 to MS04.

Expand|Select|Wrap|Line Numbers
  1. <%
  2.  
  3.  
  4. locvar = Request.Form("loc")
  5.  
  6. newlocvar = Request.Form("loc") 
  7.  
  8. locCount = 1
  9.  
  10. locSize = Request.Form("ps")
  11.  
  12. locSize = Cint(locSize)
  13.  
  14. Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
  15. MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Paneldata\Databases\FDPIN.mdb;Jet OLEDB:Database Password=niemtel;"
  16.  
  17. Set rsUpdateComments = Server.CreateObject("ADODB.Recordset")
  18.  
  19.  
  20. 'Getting locbay info for locvar string
  21.  
  22. locBay = Request.Form("bay")
  23.  
  24. locBay = Ucase(locBay)
  25.  
  26. locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  27.  
  28. strSql = "select *  from panel where PlantLoc like '" &  locvar & "%'"    'Set up a SQL command in a string variable
  29.  
  30.  
  31. 'Getting newlocbay info for newlocvar string
  32.  
  33. newlocBay = Request.Form("newbay")
  34.  
  35. newlocBay = Ucase(locBay)
  36.  
  37. newlocvar = Request.Form ("loc") & Request.Form("rem") & newlocBay & "MS" & Request.Form("newms") & "-0" & locCount
  38.  
  39.  
  40. rsUpdateComments.CursorType = 2
  41.  
  42. rsUpdateComments.LockType = 3
  43.  
  44. rsUpdateComments.Open strSQL, MyConn
  45.  
  46.  
  47. 'Update records
  48.  
  49. do until locCount > locSize
  50.  
  51. rsUpdateComments.Fields("PlantLoc")= newlocvar
  52.  
  53. rsUpdateComments.Update
  54.  
  55.  
  56. 'incrament to next record
  57.  
  58. locCount = (locCount + 1)
  59.  
  60. LocSplit = Split(locvar, "-")
  61.  
  62. LocSplit(1) = CInt(LocSplit(1))
  63.  
  64. LocSplit(1) = (LocSplit(1) + 1)
  65.  
  66. LocSplit(1) = CStr(LocSplit(1))
  67.  
  68. if LocSplit(1) < 10 then
  69.     locvar = Join(LocSplit, "-0")
  70.   else 
  71.     locvar = Join(LocSplit, "-")
  72. end if
  73.  
  74. LocSplit = Split(newlocvar, "-")
  75.  
  76. LocSplit(1) = CInt(LocSplit(1))
  77.  
  78. LocSplit(1) = (LocSplit(1) + 1)
  79.  
  80. LocSplit(1) = CStr(LocSplit(1))
  81.  
  82. if LocSplit(1) < 10 then
  83.     newlocvar = Join(LocSplit, "-0")
  84.   else 
  85.     newlocvar = Join(LocSplit, "-")
  86. end if
  87.  
  88. loop
  89.  
  90. rsUpdateComments.Close
  91.  
  92.  
  93. Set rsUpdateComments = Nothing
  94.  
  95. MyConn.close  ' close the database connection
  96.  
  97. response.write  newlocvar
  98. response.write  locvar
  99.  
  100. %>
  101.  
Aug 24 '10 #1

✓ answered by Oralloy

There were two bugs in the code as I published it. I just ran a compile test and fixed them.

Did you put the code in a module, and not a class module?

Expand|Select|Wrap|Line Numbers
  1. Public Function MakeNewLoc(ByVal loc As Variant) As String
  2. ''
  3. ''  Makes a new location based on the old one
  4. ''
  5.   ''--local variables
  6.   Dim newLoc As String
  7.  
  8.   ''--process the input loc
  9.   If (IsEmpty(loc)) Then
  10.     '' either raise an exception here or return a reasonable value
  11.     newLoc = "Empty"
  12.   ElseIf (IsNull(loc)) Then
  13.     '' either raise an exception here or return a reasonable value
  14.     newLoc = "NULL"
  15.   ElseIf (IsObject(loc)) Then
  16.     Err.Raise (0 + VBA.Constants.vbObjectError), moduleName, "Error: loc is an Object."
  17.   Else
  18.     ''=======================================
  19.     ''=======================================
  20.     ''  Compute the newLoc value here in any way you want
  21.     ''=======================================
  22.     newLoc = CStr(loc)
  23.     ''=======================================
  24.     ''=======================================
  25.   End If
  26.  
  27.   ''--error check(s)
  28.   ''If ("" = result) Then
  29.   ''  Err.Raise (0 + VBA.Constants.vbObjectError), moduleName, "Error: EMPTY newLoc"
  30.   ''End If
  31.  
  32.   ''--result assignment
  33.   MakeNewLoc = newLoc
  34. End Function
And, here is the function I used to test the code.

Expand|Select|Wrap|Line Numbers
  1. Public Sub testMakeNewLoc()
  2.   Dim db As DAO.database
  3.   Dim sqlUPDATE As String
  4.  
  5.   sqlUPDATE = "UPDATE [Persistent State]" & ASCII.NL & _
  6.               "  SET [key] = MakeNewLoc([key]);"
  7.  
  8.   Set db = Application.CurrentDb
  9.  
  10.   db.Execute (sqlUPDATE)
  11. End Sub
NOTE: ASCII is my ascii value data structure. The ASCII.NL simply inserts a line break into the SQL. You can write Chr$(10), if you prefer. You can also omit it from your test, if you want.

19 1906
Oralloy
983 Expert 512MB
Is there any reason why you can't just create an update statement and execute it?
Expand|Select|Wrap|Line Numbers
  1. UPDATE panel
  2.   SET key=newValue
  3.   WHERE key=oldValue;
  4.  
]
Aug 24 '10 #2
Getting a new error:

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/paneldatatest/indiana/fdp/movepaneldefault.asp, line 65, column 18

Expand|Select|Wrap|Line Numbers
  1. 'UPDATE panel 
  2.   SET key = newlocvar
  3.   WHERE key = locvar;
  4.  
Aug 26 '10 #3
Oralloy
983 Expert 512MB
@Jimqunkel,

My bad. I was thinking embedded SQL, like
Expand|Select|Wrap|Line Numbers
  1. EXEC SQL UPDATE panel SET key = :newlocvar WHERE key = :locvar
Which is definitely not ASP.

Try something like this:
Expand|Select|Wrap|Line Numbers
  1. sqlUPDATE = "UPDATE panel SET key = " & newValue & " WHERE key = " & oldValue
  2. MyConn.Execute sqlUPDATE
  3.  
Aug 26 '10 #4
Getting a new error:

Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/paneldatatest/indiana/fdp/movepaneldefault.asp, line 65

Expand|Select|Wrap|Line Numbers
  1. sqlUPDATE = "UPDATE panel SET key = " & newlocvar & " WHERE key = " & locvar 
  2. MyConn.Execute sqlUPDATE 
  3.  
Line 65 is "MyConn.Execute sqlUPDATE"
Sep 1 '10 #5
JKing
1,206 Expert 1GB
Is key the name of your primary key? Also I think you want to use a replace function to alter your key.

Expand|Select|Wrap|Line Numbers
  1. sqlUPDATE ="UPDATE panel SET primary_key = REPLACE(primary_key, 'MS01', 'MS04')"
This would change MS01 in all primary keys to MS04.

Make sure to swap out primary_key for the name of your key in table panel.
Sep 1 '10 #6
Oralloy
983 Expert 512MB
If the key is a string, you'll need to add delimiters to the variables.

Something like this...

Expand|Select|Wrap|Line Numbers
  1. sqlUPDATE = "UPDATE panel SET key = '" & newlocvar & "' WHERE key = '" & locvar & "'"
Sep 1 '10 #7
Same error

Expand|Select|Wrap|Line Numbers
  1. locvar = Request.Form("loc")
  2.  
  3. newlocvar = Request.Form("loc") 
  4.  
  5. locCount = 1
  6.  
  7. locSize = Request.Form("ps")
  8.  
  9. locSize = Cint(locSize)
  10.  
  11. Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
  12. MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Paneldata\Databases\FDPIN.mdb;Jet OLEDB:Database Password=niemtel;"
  13.  
  14. Set rsUpdateComments = Server.CreateObject("ADODB.Recordset")
  15.  
  16.  
  17. 'Getting locbay info for locvar string
  18.  
  19. locBay = Request.Form("bay")
  20.  
  21. locBay = Ucase(locBay)
  22.  
  23. locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  24.  
  25. strSql = "select *  from panel where PlantLoc like '" &  locvar & "%'"    'Set up a SQL command in a string variable
  26.  
  27.  
  28. 'Getting newlocbay info for newlocvar string
  29.  
  30. newlocBay = Request.Form("newbay")
  31.  
  32. newlocBay = Ucase(locBay)
  33.  
  34. newlocvar = Request.Form ("loc") & Request.Form("rem") & newlocBay & "MS" & Request.Form("newms") & "-0" & locCount
  35.  
  36.  
  37. rsUpdateComments.CursorType = 2
  38.  
  39. rsUpdateComments.LockType = 3
  40.  
  41. rsUpdateComments.Open strSQL, MyConn
  42.  
  43.  
  44. 'Update records
  45.  
  46. do until locCount > locSize
  47.  
  48. 'rsUpdateComments.Fields("PlantLoc")= newlocvar
  49.  
  50. 'rsUpdateComments.Update
  51.  
  52. response.write  newlocvar
  53.  
  54. 'UPDATE panel
  55. sqlUPDATE = "UPDATE panel SET key = '" & newlocvar & "' WHERE key = '" & locvar & "'"
  56. MyConn.Execute sqlUPDATE
  57.  
Sep 1 '10 #8
Oralloy
983 Expert 512MB
Ok, I'm confused.

I think you needed to change 'key' in my string to 'PlantLoc', like this:

Expand|Select|Wrap|Line Numbers
  1. sqlUPDATE = "UPDATE panel SET PlantLoc = '" & newlocvar & "' WHERE PlantLoc = '" & locvar & "'"
  2.  
  3. or
  4.  
  5. sqlUPDATE = "UPDATE panel SET PlantLoc="
  6. sqlUpdate = sqlUPDATE & REPLACE(PlantLoc, '" & locvar & "', '" & newlocvar & "')"
  7. sqlUpdate = sqlUpdate & " WHERE PlantLoc LIKE ' & locvar & "%'"
Although I must say that it looks like your loop should be doing the the same thing, without the subsequent UPDATE statement.
Sep 1 '10 #9
I'm not getting an error but the database is not being updated with the new key data "PlantLoc".

Expand|Select|Wrap|Line Numbers
  1. <% @language="vbscript" %>
  2. <% Response.buffer = true %>
  3.  
  4. <%
  5.  
  6. '************************************************************
  7. 'ADO is the keyword for google searches on help with DB stuff
  8. '************************************************************
  9.  
  10. locvar = Request.Form("loc")
  11.  
  12. newlocvar = Request.Form("loc") 
  13.  
  14. locCount = 1
  15.  
  16. locSize = Request.Form("ps")
  17.  
  18. locSize = Cint(locSize)
  19.  
  20. Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
  21. MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Paneldata\Databases\FDPIN.mdb;Jet OLEDB:Database Password=niemtel;"
  22.  
  23. Set SQLupdate = Server.CreateObject("ADODB.Recordset")
  24.  
  25.  
  26. 'Getting locbay info for locvar string
  27.  
  28. locBay = Request.Form("bay")
  29.  
  30. locBay = Ucase(locBay)
  31.  
  32. locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
  33.  
  34. strSql = "select *  from panel where PlantLoc like '" &  locvar & "%'"    'Set up a SQL command in a string variable
  35.  
  36.  
  37. 'Getting newlocbay info for newlocvar string
  38.  
  39. newlocBay = Request.Form("newbay")
  40.  
  41. newlocBay = Ucase(locBay)
  42.  
  43. newlocvar = Request.Form ("loc") & Request.Form("rem") & newlocBay & "MS" & Request.Form("newms") & "-0" & locCount
  44.  
  45.  
  46. sqlupdate.CursorType = 2
  47.  
  48. sqlupdate.LockType = 3
  49.  
  50. sqlupdate.Open strSQL, MyConn
  51.  
  52.  
  53. 'Update records
  54.  
  55. do until locCount > locSize
  56.  
  57. 'response.write  newlocvar
  58.  
  59. 'UPDATE panel
  60.  
  61. sqlUPDATE = "UPDATE panel SET PlantLoc = '" & newlocvar & "' WHERE PlantLoc = '" & locvar & "'"
  62.  
  63.  
  64. locCount = (locCount + 1)
  65.  
  66. LocSplit = Split(locvar, "-")
  67.  
  68. LocSplit(1) = CInt(LocSplit(1))
  69.  
  70. LocSplit(1) = (LocSplit(1) + 1)
  71.  
  72. LocSplit(1) = CStr(LocSplit(1))
  73.  
  74. if LocSplit(1) < 10 then
  75.     locvar = Join(LocSplit, "-0")
  76.   else 
  77.     locvar = Join(LocSplit, "-")
  78. end if
  79.  
  80. LocSplit = Split(newlocvar, "-")
  81.  
  82. LocSplit(1) = CInt(LocSplit(1))
  83.  
  84. LocSplit(1) = (LocSplit(1) + 1)
  85.  
  86. LocSplit(1) = CStr(LocSplit(1))
  87.  
  88. if LocSplit(1) < 10 then
  89.     newlocvar = Join(LocSplit, "-0")
  90.   else 
  91.     newlocvar = Join(LocSplit, "-")
  92. end if
  93.  
  94. loop
  95.  
  96.  
  97. MyConn.close  ' close the database connection
  98.  
  99. %>
  100.  
Sep 8 '10 #10
Oralloy
983 Expert 512MB
Jim,

I don't see where you are executing the update in your code.

You build it in line 61.

But I just don't see the DB.Execute(sqlUPDATE)

Am I blind?

=============================

As an aside, we may be a bit disconnected. Are you opening the recordset as modifyable, with the goal of writing back to it as you visit each record?

If so, the UPDATE query is pointless, and we need to simplify the code considerably.

Also, we can be really slick and implement a function that computes the new key value based on its parameters and then just execute a single update that references that function to do the update in a single stroke.

======================================

Cheers!
Sep 8 '10 #11
Sorry - I was tring a few things and forgot to add it back in to the code.

Expand|Select|Wrap|Line Numbers
  1. 'UPDATE panel
  2.  
  3. sqlUPDATE = "UPDATE panel SET PlantLoc = '" & newlocvar & "' WHERE PlantLoc = '" & locvar & "'"
  4. MyConn.Execute sqlUPDATE
  5.  
  6.  
I'm game to try a different way.
Sep 8 '10 #12
Oralloy
983 Expert 512MB
Another tack you might take is to create a "support" module with a function like this:

Expand|Select|Wrap|Line Numbers
  1. Public Function MakeNewLoc(ByVal loc As Variant) AS String
  2. ''
  3. ''  Makes a new location based on the old one
  4. ''
  5.   ''--local variables
  6.   Dim newLoc As String
  7.  
  8.   ''--process the input loc
  9.   If (IsEmpty(loc)) Then
  10.     '' either raise an exception here or return a reasonable value
  11.     newLoc = "Empty"
  12.   ElseIf (IsNull(loc)) Then
  13.     '' either raise an exception here or return a reasonable value
  14.     first = "NULL"
  15.   ElseIf (IsObject(loc)) Then
  16.     Err.Raise (0 + VBA.Constants.vbObjectError), moduleName, "Error: loc is an Object."
  17.   Else
  18.     ''=======================================
  19.     ''=======================================
  20.     ''  Compute the newLoc value here in any way you want
  21.     ''=======================================
  22.     newLoc = loc
  23.     ''=======================================
  24.     ''=======================================
  25.   End If
  26.  
  27.   ''--error check(s)
  28.   ''If ("" = result) Then
  29.   ''  Err.Raise (0 + VBA.Constants.vbObjectError), moduleName, "Error: EMPTY newLoc"
  30.   ''End If
  31.  
  32.   ''--result assignment
  33.   MakeNewLoc = result
  34. End Function
And then, a single UPDATE should suffice:
Expand|Select|Wrap|Line Numbers
  1. UPDATE panel
  2.   SET PlantLoc = MakeNewLoc(PlantLoc);
Proviso: I do know that the trick of writing functions works for SELECT statements. I haven't actually tried it on an UPDATE.
Sep 8 '10 #13
Getting new error:

Microsoft VBScript compilation (0x800A03EA)
Syntax error
/paneldatatest/indiana/fdp/movepaneldefault.asp, line 61, column 7

Expand|Select|Wrap|Line Numbers
  1. Public Function MakeNewLoc(ByVal loc As Variant) AS String
  2.  
Sep 8 '10 #14
JKing
1,206 Expert 1GB
This seems to have gotten very complicated...

It is my understanding that you have a primary key named PlantLoc.

PlantLoc looks like this: 1101001CXR001MS01-01 thru 1101001CXR001MS01-24

But you wanted it changed to: 1101001CXR001MS04-01 thru 1101001CXR001MS04-24

Instead of looping and executing multiple SQL statements why not just do one SQL statement that updates all 24 records?

Expand|Select|Wrap|Line Numbers
  1. sqlUPDATE ="UPDATE panel SET PlantLoc = REPLACE(PlantLoc, 'MS01', 'MS04')"
  2.  
Sep 8 '10 #15
Oralloy
983 Expert 512MB
There were two bugs in the code as I published it. I just ran a compile test and fixed them.

Did you put the code in a module, and not a class module?

Expand|Select|Wrap|Line Numbers
  1. Public Function MakeNewLoc(ByVal loc As Variant) As String
  2. ''
  3. ''  Makes a new location based on the old one
  4. ''
  5.   ''--local variables
  6.   Dim newLoc As String
  7.  
  8.   ''--process the input loc
  9.   If (IsEmpty(loc)) Then
  10.     '' either raise an exception here or return a reasonable value
  11.     newLoc = "Empty"
  12.   ElseIf (IsNull(loc)) Then
  13.     '' either raise an exception here or return a reasonable value
  14.     newLoc = "NULL"
  15.   ElseIf (IsObject(loc)) Then
  16.     Err.Raise (0 + VBA.Constants.vbObjectError), moduleName, "Error: loc is an Object."
  17.   Else
  18.     ''=======================================
  19.     ''=======================================
  20.     ''  Compute the newLoc value here in any way you want
  21.     ''=======================================
  22.     newLoc = CStr(loc)
  23.     ''=======================================
  24.     ''=======================================
  25.   End If
  26.  
  27.   ''--error check(s)
  28.   ''If ("" = result) Then
  29.   ''  Err.Raise (0 + VBA.Constants.vbObjectError), moduleName, "Error: EMPTY newLoc"
  30.   ''End If
  31.  
  32.   ''--result assignment
  33.   MakeNewLoc = newLoc
  34. End Function
And, here is the function I used to test the code.

Expand|Select|Wrap|Line Numbers
  1. Public Sub testMakeNewLoc()
  2.   Dim db As DAO.database
  3.   Dim sqlUPDATE As String
  4.  
  5.   sqlUPDATE = "UPDATE [Persistent State]" & ASCII.NL & _
  6.               "  SET [key] = MakeNewLoc([key]);"
  7.  
  8.   Set db = Application.CurrentDb
  9.  
  10.   db.Execute (sqlUPDATE)
  11. End Sub
NOTE: ASCII is my ascii value data structure. The ASCII.NL simply inserts a line break into the SQL. You can write Chr$(10), if you prefer. You can also omit it from your test, if you want.
Sep 8 '10 #16
Oralloy
983 Expert 512MB
@JKing,

You have the right of it.

My form is a little more general, because I'm used to my simplisitic ways.

What's important is that the replacement value computation is correctly done.

Thanks! I learned something.
Sep 8 '10 #17
JKing - The MS value is not a constant I used it as an example other info may need to change like 1099009FDP009MS01 to 1099009FDP008MS03 as an example. So a simple way may not work. I'm all for simple if possable but I need the flexability to change more than one peramiter. Sorry if I was to vague.
Sep 8 '10 #18
Oralloy
983 Expert 512MB
Jim,

Sounds like you're looking at the module with transformation method.

Give it a try and let's see how it works out.

Cheers!
Sep 8 '10 #19
Thanks this works
Sep 10 '10 #20

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by x taol | last post: by
4 posts views Thread by macca | last post: by
1 post views Thread by kmladenovski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.