Updating a Recordset (contains Binary)

[SamHale]

Good afternoon,

Ok, I have a form that adds a record to my database. This record contains an image that gets entered as Binary Data. The code for this is:

Dim rs
    Set rs = Server.CreateObject("ADODB.Recordset")

    rs.Open "tbl_news", connStr, 2, 2

    ' Adding data
    rs.AddNew
        rs("File Name") = fileName
        rs("File Size") = fileSize
        rs("File Data").AppendChunk fileData
        rs("Content Type") = contentType
        rs("name") = nameInput
        rs("details") = detailsInput
        rs("date") = dateInput
    rs.Update

rs.Close
Set rs = Nothing

Obviously shortened to keep this post down. However, what I cannot work out is how to update the recordset. I have tried a number of attempts, i.e. changing rs.add to rs.edit but I receive:

Object doesn't support this property or method: 'edit'

I also tried changing the rs.Open to include 'where ID=1' to try and edit the first recordset. But this still just adds a new record.

If anyone has any ideas they would be greatly apprechiated.

Kind regards
Sam

 

[steven290]

i believe you just remove the rs.addnew, but there is no reason to open a recordset in the first place when updating or even inserting, try using the execute method (you save performance) example:

sql="UPDATE tbl_news set " &_
"File Name = '" & fileName & _"', "&_
"File Size ='" &  fileSize & "', "&_
"File Data = '" & fileData & "', "&_
"Content Type= '" & contentType & "', "&_
"name= '" & nameInput & "', "&_
"details= '" & detailsInput & "', "&_
"date= '" & dateInput & "' "&_
"where id=" & somenumhere

connStr.execute sql

 

[SamHale]

Hi Steven,

Many thanks for your help, I attempted both options but with no success.

Your code provided the following error -

Microsoft VBScript runtime error '800a01a8' 

Object required: 'Provider=Microsoft.J' 

/Insert2.asp, line 113

Line 113 is the

connStr.execute sql

command.

So with the Object required error. This is telling me there is a problem with the 'connStr' string.

My connection code is:

connStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("..\private\news.mdb")

Which works correctly on other pages.

Any ideas?

Thanks
Sam

 

[steven290]

use connstr after you open it.

set connStr= CreateObject("ADODB.Connection")
connStr.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("..\private\news.mdb")
connStr.Open()

sql="UPDATE tbl_news set " &_
"File Name = '" & fileName & _"', "&_
"File Size ='" &  fileSize & "', "&_
"File Data = '" & fileData & "', "&_
"Content Type= '" & contentType & "', "&_
"name= '" & nameInput & "', "&_
"details= '" & detailsInput & "', "&_
"date= '" & dateInput & "' "&_
"where id=" & somenumhere

connStr.execute sql

 

[SamHale]

Hi Steven,

Thanks for your help so far. I'm now receiving the following error:

Microsoft JET Database Engine error '80040e14' 

Syntax error in UPDATE statement. 

/Insert2.asp, line 112

line 112 is

connStr.execute sql

Any ideas?

Regards
Sam

 

[SamHale]

Update -

I played around a little with the coding, and managed to update both the Name field and Details field. However, the above error occurs when trying to update either the data field, or anything to do with the image upload.

The below code appears at the top of my page to obtain the details from the file upload. I'm guessing this may be what is causing the upload error. Any ideas?

<%

	' load object
	Dim load
		Set load = new Loader
		
		' calling initialize method
		load.initialize
		
	' File binary data
	Dim fileData
		fileData = load.getFileData("file")
	' File name
	Dim fileName
		fileName = LCase(load.getFileName("file"))
	' File path
	Dim filePath
		filePath = load.getFilePath("file")
	' File path complete
	Dim filePathComplete
		filePathComplete = load.getFilePathComplete("file")
	' File size
	Dim fileSize
		fileSize = load.getFileSize("file")
	' File size translated
	Dim fileSizeTranslated
		fileSizeTranslated = load.getFileSizeTranslated("file")
	' Content Type
	Dim contentType
		contentType = load.getContentType("file")
	' No. of Form elements
	Dim countElements
		countElements = load.Count
	' Value of text input field "fname"
	Dim nameInput
		nameInput = load.getValue("name")
	' Value of text input field "lname"
	Dim detailsInput
		detailsInput = load.getValue("details")
	' Value of text input field "profession"
	Dim dateInput
		dateInput = load.getValue("date")	
		
	' destroying load object
	Set load = Nothing
%>

Thanks,
Sam

 

[SamHale]

Any ideas?

Thanks,
Sam

 

[steven290]

when using the file field it messes with the binaryread of the code so all your request.whatever no longer work, you need to use an upload class which have the fix for your problem;

solution

http://www.pscode.com/vb/scripts/ShowCode.asp?txtCodeId=7361&lngWId=4