Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Combine two working scripts to update database 1

Status
Not open for further replies.

hamish75

Technical User
Dec 18, 2009
39
GB
using a form with a browse to file button i am able to successfully upload an image to a sql database using the following script, this updates a table called images:

Code:
<%
set upload = server.createobject("persits.upload")

upload.setmaxsize 100000, true

count = upload.save

set file = upload.files("logo")

if not file is nothing then

set rs = server.createobject("adodb.recordset")

rs.open "select * from [images] where [image]='" & hash & "'", conn, 2, 3

rs.addnew
rs("image") = file.binary
rs.update

response.write("successful")
else
response.write("fail")
end if
%>

i have another table called shape which i can insert another record using the following script:

Code:
<%

sql = "insert into shape ([shape index], [shape description], [imageid])"
sql = sql & " values "
sql = sql & "('" & request.form("shapeindex ") & "', '" & request.form("shapedescription") & "', '" & request.form("imageid") & "')"

%>

what i’d like to be able to do combine the forms so i can have one form where i insert a record in both tables because an image is linked to the shape table on the field [imageid].

can anyone help me do this?


Ah dinnae ken...
 
What DBMS are you using? With MSSQL i can do an "INSERT INTO .....) SELECT @@IDENTIY" 9in 1 statement).

With MSACCESS i believe you must do someting like this:

Code:
Set rsNewID = comm.execute("SELECT @@IDENTITY") 
nImageID = rsNewID(0) 
rsNewID.Close



 
Perfect!

I'm not sure if this works:

Code:
cSQL = "INSERT INTO [Images] (image) VALUE(" &_
 file.binary & ") SELECT @@IDENTITY"
rs.open cSQL, conn
nImageId = rs(0)

(of course imageid is an identity field)


i never did such an image upload into SQL, so maybe this is not working. I would INSERT a new record first (requesting the IDENTITY), then to an UPDATE (put the image in), and then do the INSERT into [shape]


 
i have tried altering the script from my first post and i'm getting a type mismatch error which i think relates to the lines below because i'm trying to pass values for the shape record which is more than an just an image value:

Code:
set file = upload.files("logo")

Code:
file.binary & ") select @@identity"

is it possible to pass the information for the "shape" record as well as pass the image?

thanks.


Ah dinnae ken...
 
Field [Image] in table [images] is of type "blob"?

This snippet from ASP 101 is using "File.ToDatabase":
Code:
<HTML>  
<BODY>  
<%  
Set Upload = Server.CreateObject("Persits.Upload.1")  
Upload.Save "c:\upload"  
On Error Resume Next  
For Each File in Upload.Files  

File.ToDatabase "DSN=data;UID=sa;PWD=xxx;", "insert into Blobs(id, Path, BigBlob) values(12, '" & File.Path & "', ?)" 
if Err <> 0 Then  
Response.Write "Error saving the file: " & Err.Description 
Else  
File.Delete  
Response.Write "Success!" 
End If 

Next 
%>  
</BODY>  
</HTML>
 
thank you, much appreciated, but i'm not sure how i can adapt that so it will update the two tables?

i can update the two tables in the same script using the following:

Code:
rs.open "select * from [images] where [image]='" & hash & "'", gbl_conn, 2, 3
rs.addnew
rs("image") = file.binary
rs.update
rs.close

rs.open "select * from [shape] where [shape scheme]='" & hash & "'", gbl_conn, 2, 3

rs.addnew
rs("shape scheme") = upload.form("shapescheme")
rs("shape description ") = upload.form("shapedescription")
rs.update
rs.close

if i have images.image_index column that is automatically populated when the script is run is it possible to pass that to shape.imageindex?

thanks.




Ah dinnae ken...
 
Code:
rs.open "SELECT @@IDENTITY FROM [images]", conn
nImageId = rs(0)
rs.close

after the insert and then use nImageId in the shape insert?

 
thank you!

i added that and it is now adding the first record value for images.image_index.

i've had a play but can't get it to display the last record value.

is it easy to change this so it adds the last value, as it will always be the last value that is needed?

thanks again.

Ah dinnae ken...
 
mmmm, if you are dead sure you always need the last ID (aka the highest), than of course you can change it into

Code:
 rs.open "SELECT TOP 1 max(imageid) FROM [images]", conn
 nImageId = rs(0)
 rs.close

etc

 
yeah i'm not convinced by my method either! :)

my thinking is that the last record is always the one that i just added so that would mean i would always need the last record...not very elegant though.

i did add the following that worked:

Code:
rs.Movelast
nImageId = rs(0)

i'll have to think about it..

thank you for your help foxbox.

Ah dinnae ken...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top