Dream Weaver

Calling the ASP crew. How can I use a "where" clause in the following update statement.

' Recordset object
Dim rspic
Set rspic = Server.CreateObject("ADODB.Recordset")

rspic.Open "Files", connStr, 1, 3, 2
rspic("fileName") = fileName
rspic("fileData").AppendChunk fileData
rspic("contentType") = contentType
rspic("carid") = passid
rspic.Update

rspic.Close
Set rspic = Nothing

The page uses a Loader class to create a scripting dictionary which holds the binary data from the uploaded image. The variables have been declared at the top of the page.

This page is intended to update the image if the wrong one has been submitted, or if it needs changing. Therefore, the user clicks on "Change Picture" and can then re-select a new image to insert into the DB. The table "Files" holds all the images, and links to the "Cars" table via carid.

What I am trying to say in the statement is "Update the record in 'Files' where carid = passid" passid is the id of the car passed from the initial page. Currently it works for the first record, but then gives the following error for subsequent records:

Microsoft JET Database Engine error '80040e21'

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

/process_updateimage.asp, line 75

From this I presume it is trying to replace the carid value, and the DB wont let it. However I want it to replace the value with the same number (passid) so it should work. I have tried the update statement without the carid line, but it justs changes the picture of the first record.

Help please - doing my head in.

DW

chiark

also, add another "," to the open command to make sure that it's not thinking that "1" is the activeConnection

Fosters

looks too complicated. do this instead...

dim rs
dim sSQL
dim sCDT

set rs = Server.CreateObject("ADODB.command")

sSQL="Update table set field1='1',field2 = '2' where blah=blah and whatever=1"

rs.activeconnection = cn
rs.commandtype = &H0001
rs.commandtext = sSQL
rs.execute

set rs=nothing

cn.close
set cn=nothing

chiark

...apart from the fact that there's binary data in a field, so that would be "interesting" to write using a sql statement

I do agree tho - SQL statements are easier to get your head around and don't rely on you having the right type of recordset open.

Nick.

Dream Weaver

Thanks, but still cant get it to work.

Fosters - where do I need to put the connection to the DB? Also what do I do with the .AppendChunk bit - can it be added as a value into the field?

Hard work this ASP malarky.

Dream Weaver

OK Nearly there - yeehaa!!!

I have got it to work now, by adding rspic.Filter after rspic.Open. Hence it filters out the files by using rspic.Filter = "carid = passid".

Only problem is it only works when I type a number in for passid. So if I type 70 into the script it updates car 70, 69 car 69 etc.

Just cant get it to take the value from the variable passid - I will though, oh yes I will

DW

Fosters

Chiark's wise. his way is better.

what db are you using? if you can use stored procedures, they're the way to go on this sort of thing.

Being an update I would have thought that it wouldn't matter what you were writing, which makes me suspect the recordset open parms.

Try the open parms I've specified below (they might be the ones you already have - I just don't have the numbers!)

rs.Open "Files", connstr, adOpenForwardOnly, adLockOptimistic, adCmdTable

chiark

...if there's a difficult way of doing something, you'll find it, eh Si?

Dream Weaver

What do you mean difficult

Anyway, all sorted now - out of interest here is the solution I have found:

' Recordset object
Dim rspic
Set rspic = Server.CreateObject("ADODB.Recordset")

rspic.Open "Files", connStr, 1, 3, 2

rspic.Filter = "carid = " & passid

rspic("fileName") = fileName
rspic("fileData").AppendChunk fileData
rspic("contentType") = contentType

rspic.Update

rspic.Close
Set rspic = Nothing

Thanks for the help guys

DW

chiark

DW,

change the recordset to open with "SELECT * from FILES WHERE CARID=" & request("passid")
or whatever...

Cheers,

Nick.

[Edited by chiark - 3/6/2002 12:15:26 PM]