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

[Jimgunkel]

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.

<%
 
 
locvar = Request.Form("loc")
 
newlocvar = Request.Form("loc") 
 
locCount = 1
 
locSize = Request.Form("ps")
 
locSize = Cint(locSize)
 
Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Paneldata\Databases\FDPIN.mdb;Jet OLEDB:Database Password=niemtel;"
 
Set rsUpdateComments = Server.CreateObject("ADODB.Recordset")
 
 
'Getting locbay info for locvar string
 
locBay = Request.Form("bay")
 
locBay = Ucase(locBay)
 
locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
 
strSql = "select *  from panel where PlantLoc like '" &  locvar & "%'"    'Set up a SQL command in a string variable
 
 
'Getting newlocbay info for newlocvar string
 
newlocBay = Request.Form("newbay")
 
newlocBay = Ucase(locBay)
 
newlocvar = Request.Form ("loc") & Request.Form("rem") & newlocBay & "MS" & Request.Form("newms") & "-0" & locCount
 
 
rsUpdateComments.CursorType = 2
 
rsUpdateComments.LockType = 3
 
rsUpdateComments.Open strSQL, MyConn
 
 
'Update records
 
do until locCount > locSize
 
rsUpdateComments.Fields("PlantLoc")= newlocvar
 
rsUpdateComments.Update
 
 
'incrament to next record
 
locCount = (locCount + 1)
 
LocSplit = Split(locvar, "-")
 
LocSplit(1) = CInt(LocSplit(1))
 
LocSplit(1) = (LocSplit(1) + 1)
 
LocSplit(1) = CStr(LocSplit(1))
 
if LocSplit(1) < 10 then
    locvar = Join(LocSplit, "-0")
  else 
    locvar = Join(LocSplit, "-")
end if
 
LocSplit = Split(newlocvar, "-")
 
LocSplit(1) = CInt(LocSplit(1))
 
LocSplit(1) = (LocSplit(1) + 1)
 
LocSplit(1) = CStr(LocSplit(1))
 
if LocSplit(1) < 10 then
    newlocvar = Join(LocSplit, "-0")
  else 
    newlocvar = Join(LocSplit, "-")
end if
 
loop
 
rsUpdateComments.Close
 
 
Set rsUpdateComments = Nothing
 
MyConn.close  ' close the database connection
 
response.write  newlocvar
response.write  locvar
 
%>
 

[Oralloy]

Is there any reason why you can't just create an update statement and execute it?

UPDATE panel
  SET key=newValue
  WHERE key=oldValue;
 

]

[Jimgunkel]

Getting a new error:

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

'UPDATE panel 
  SET key = newlocvar
  WHERE key = locvar;
 

[Oralloy]

@Jimqunkel,

My bad. I was thinking embedded SQL, like

EXEC SQL UPDATE panel SET key = :newlocvar WHERE key = :locvar

Which is definitely not ASP.

Try something like this:

sqlUPDATE = "UPDATE panel SET key = " & newValue & " WHERE key = " & oldValue
MyConn.Execute sqlUPDATE
 

[Jimgunkel]

Getting a new error:

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

sqlUPDATE = "UPDATE panel SET key = " & newlocvar & " WHERE key = " & locvar 
MyConn.Execute sqlUPDATE 
 

Line 65 is "MyConn.Execute sqlUPDATE"

[JKing]

Is key the name of your primary key? Also I think you want to use a replace function to alter your key.

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.

[Oralloy]

If the key is a string, you'll need to add delimiters to the variables.

Something like this...

sqlUPDATE = "UPDATE panel SET key = '" & newlocvar & "' WHERE key = '" & locvar & "'"

[Jimgunkel]

Same error

locvar = Request.Form("loc")
 
newlocvar = Request.Form("loc") 
 
locCount = 1
 
locSize = Request.Form("ps")
 
locSize = Cint(locSize)
 
Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Paneldata\Databases\FDPIN.mdb;Jet OLEDB:Database Password=niemtel;"
 
Set rsUpdateComments = Server.CreateObject("ADODB.Recordset")
 
 
'Getting locbay info for locvar string
 
locBay = Request.Form("bay")
 
locBay = Ucase(locBay)
 
locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
 
strSql = "select *  from panel where PlantLoc like '" &  locvar & "%'"    'Set up a SQL command in a string variable
 
 
'Getting newlocbay info for newlocvar string
 
newlocBay = Request.Form("newbay")
 
newlocBay = Ucase(locBay)
 
newlocvar = Request.Form ("loc") & Request.Form("rem") & newlocBay & "MS" & Request.Form("newms") & "-0" & locCount
 
 
rsUpdateComments.CursorType = 2
 
rsUpdateComments.LockType = 3
 
rsUpdateComments.Open strSQL, MyConn
 
 
'Update records
 
do until locCount > locSize
 
'rsUpdateComments.Fields("PlantLoc")= newlocvar
 
'rsUpdateComments.Update
 
response.write  newlocvar
 
'UPDATE panel
sqlUPDATE = "UPDATE panel SET key = '" & newlocvar & "' WHERE key = '" & locvar & "'"
MyConn.Execute sqlUPDATE
 

[Oralloy]

Ok, I'm confused.

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

sqlUPDATE = "UPDATE panel SET PlantLoc = '" & newlocvar & "' WHERE PlantLoc = '" & locvar & "'"
 
or
 
sqlUPDATE = "UPDATE panel SET PlantLoc="
sqlUpdate = sqlUPDATE & REPLACE(PlantLoc, '" & locvar & "', '" & newlocvar & "')"
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.

[Jimgunkel]

I'm not getting an error but the database is not being updated with the new key data "PlantLoc".

<% @language="vbscript" %>
<% Response.buffer = true %>
 
<%
 
'************************************************************
'ADO is the keyword for google searches on help with DB stuff
'************************************************************
 
locvar = Request.Form("loc")
 
newlocvar = Request.Form("loc") 
 
locCount = 1
 
locSize = Request.Form("ps")
 
locSize = Cint(locSize)
 
Set MyConn = Server.CreateObject("ADODB.Connection") ' Establish the connection object
MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Paneldata\Databases\FDPIN.mdb;Jet OLEDB:Database Password=niemtel;"
 
Set SQLupdate = Server.CreateObject("ADODB.Recordset")
 
 
'Getting locbay info for locvar string
 
locBay = Request.Form("bay")
 
locBay = Ucase(locBay)
 
locvar = Request.Form ("loc") & Request.Form("rem") & locBay & "MS" & Request.Form("ms") & "-0" & locCount
 
strSql = "select *  from panel where PlantLoc like '" &  locvar & "%'"    'Set up a SQL command in a string variable
 
 
'Getting newlocbay info for newlocvar string
 
newlocBay = Request.Form("newbay")
 
newlocBay = Ucase(locBay)
 
newlocvar = Request.Form ("loc") & Request.Form("rem") & newlocBay & "MS" & Request.Form("newms") & "-0" & locCount
 
 
sqlupdate.CursorType = 2
 
sqlupdate.LockType = 3
 
sqlupdate.Open strSQL, MyConn
 
 
'Update records
 
do until locCount > locSize
 
'response.write  newlocvar
 
'UPDATE panel
 
sqlUPDATE = "UPDATE panel SET PlantLoc = '" & newlocvar & "' WHERE PlantLoc = '" & locvar & "'"
 
 
locCount = (locCount + 1)
 
LocSplit = Split(locvar, "-")
 
LocSplit(1) = CInt(LocSplit(1))
 
LocSplit(1) = (LocSplit(1) + 1)
 
LocSplit(1) = CStr(LocSplit(1))
 
if LocSplit(1) < 10 then
    locvar = Join(LocSplit, "-0")
  else 
    locvar = Join(LocSplit, "-")
end if
 
LocSplit = Split(newlocvar, "-")
 
LocSplit(1) = CInt(LocSplit(1))
 
LocSplit(1) = (LocSplit(1) + 1)
 
LocSplit(1) = CStr(LocSplit(1))
 
if LocSplit(1) < 10 then
    newlocvar = Join(LocSplit, "-0")
  else 
    newlocvar = Join(LocSplit, "-")
end if
 
loop
 
 
MyConn.close  ' close the database connection
 
%>
 

[Oralloy]

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!

[Jimgunkel]

Sorry - I was tring a few things and forgot to add it back in to the code.

'UPDATE panel
 
sqlUPDATE = "UPDATE panel SET PlantLoc = '" & newlocvar & "' WHERE PlantLoc = '" & locvar & "'"
MyConn.Execute sqlUPDATE
 
 

I'm game to try a different way.

[Oralloy]

Another tack you might take is to create a "support" module with a function like this:

Public Function MakeNewLoc(ByVal loc As Variant) AS String
''
''  Makes a new location based on the old one
''
  ''--local variables
  Dim newLoc As String
 
  ''--process the input loc
  If (IsEmpty(loc)) Then
    '' either raise an exception here or return a reasonable value
    newLoc = "Empty"
  ElseIf (IsNull(loc)) Then
    '' either raise an exception here or return a reasonable value
    first = "NULL"
  ElseIf (IsObject(loc)) Then
    Err.Raise (0 + VBA.Constants.vbObjectError), moduleName, "Error: loc is an Object."
  Else
    ''=======================================
    ''=======================================
    ''  Compute the newLoc value here in any way you want
    ''=======================================
    newLoc = loc
    ''=======================================
    ''=======================================
  End If
 
  ''--error check(s)
  ''If ("" = result) Then
  ''  Err.Raise (0 + VBA.Constants.vbObjectError), moduleName, "Error: EMPTY newLoc"
  ''End If
 
  ''--result assignment
  MakeNewLoc = result
End Function

And then, a single UPDATE should suffice:

UPDATE panel
  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.

[Jimgunkel]

Getting new error:

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

Public Function MakeNewLoc(ByVal loc As Variant) AS String
 

[JKing]

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?

sqlUPDATE ="UPDATE panel SET PlantLoc = REPLACE(PlantLoc, 'MS01', 'MS04')"
 

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

Public Function MakeNewLoc(ByVal loc As Variant) As String
''
''  Makes a new location based on the old one
''
  ''--local variables
  Dim newLoc As String
 
  ''--process the input loc
  If (IsEmpty(loc)) Then
    '' either raise an exception here or return a reasonable value
    newLoc = "Empty"
  ElseIf (IsNull(loc)) Then
    '' either raise an exception here or return a reasonable value
    newLoc = "NULL"
  ElseIf (IsObject(loc)) Then
    Err.Raise (0 + VBA.Constants.vbObjectError), moduleName, "Error: loc is an Object."
  Else
    ''=======================================
    ''=======================================
    ''  Compute the newLoc value here in any way you want
    ''=======================================
    newLoc = CStr(loc)
    ''=======================================
    ''=======================================
  End If
 
  ''--error check(s)
  ''If ("" = result) Then
  ''  Err.Raise (0 + VBA.Constants.vbObjectError), moduleName, "Error: EMPTY newLoc"
  ''End If
 
  ''--result assignment
  MakeNewLoc = newLoc
End Function

And, here is the function I used to test the code.

Public Sub testMakeNewLoc()
  Dim db As DAO.database
  Dim sqlUPDATE As String
 
  sqlUPDATE = "UPDATE [Persistent State]" & ASCII.NL & _
              "  SET [key] = MakeNewLoc([key]);"
 
  Set db = Application.CurrentDb
 
  db.Execute (sqlUPDATE)
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.

[Oralloy]

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

[Jimgunkel]

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.

[Oralloy]

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!

[Jimgunkel]

Thanks this works