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

**Oralloy** · Aug 24 '10, 07:16 PM

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

Code:

UPDATE panel
  SET key=newValue
  WHERE key=oldValue;

]

**Jimgunkel** · Aug 26 '10, 01:41 PM

Getting a new error:

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

Code:

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

**Oralloy** · Aug 26 '10, 02:22 PM

@Jimqunkel,

My bad. I was thinking embedded SQL, like

Code:

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

Which is definitely not ASP.

Try something like this:

Code:

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

**Jimgunkel** · Sep 1 '10, 03:47 PM

Getting a new error:

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

Code:

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

Line 65 is "MyConn.Exe cute sqlUPDATE"

**JKing** · Sep 1 '10, 04:07 PM

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

Code:

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** · Sep 1 '10, 04:29 PM

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

Something like this...

Code:

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

**Jimgunkel** · Sep 1 '10, 06:18 PM

Same error

Code:

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** · Sep 1 '10, 06:31 PM

Ok, I'm confused.

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

Code:

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** · Sep 8 '10, 02:03 PM

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

Code:

<% @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** · Sep 8 '10, 04:52 PM

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(sqlU PDATE)

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** · Sep 8 '10, 05:15 PM

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

Code:

'UPDATE panel

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

I'm game to try a different way.

**Oralloy** · Sep 8 '10, 05:23 PM

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

Code:

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:

Code:

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** · Sep 8 '10, 05:54 PM

Getting new error:

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

Code:

Public Function MakeNewLoc(ByVal loc As Variant) AS String

**JKing** · Sep 8 '10, 06:22 PM

This seems to have gotten very complicated...

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

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

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

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

Code:

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