• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1199
  • Last Modified:

Writing to BLOBs in mySQL and ASP

I have transferred to a mySQL database and have run up against a problem.

I use the below code to create a new record but it cannot create the record due to an error caused by incorrect code when writing to a BLOB.

Response.Buffer = true
set rst = Server.CreateObject("ADODB.RecordSet")
sqltext = "SELECT * FROM TableName"

rst.Open sqltext,objaConn,3,3
rst.AddNew
rst("name") = Request.form("name")' varchar in DB
rst("email") = Request.form("email") ' varchar in DB
rst("information") = Request.form("information") 'blob in DB
rst.update
rst.close
0
BeakerTrail
Asked:
BeakerTrail
  • 8
  • 6
1 Solution
 
SlimshaneeyCommented:
Change the code to

rst.Open sqltext,objaConn,3,3
rst.AddNew
rst("name") = Request.form("name")' varchar in DB
rst("email") = Request.form("email") ' varchar in DB
rst("information").AppendChunk = Request.form("information") 'blob in DB
rst.update
rst.close
0
 
BeakerTrailAuthor Commented:
That returns an error:

ADODB.Field error '800a0c93'

Operation is not allowed in this context.

line 95 <-- line with appendchunk on.
0
 
alorentzCommented:
Not sure about this: ($)

Response.Buffer = true
sqltext = "INSERT INTOP TableName (name, email, information) VALUES " & _
    "('" & request("email") & "', '" & request("email") & "', '$" & request("information") & "')"

objaConn.execute sqltext
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
alorentzCommented:
Whoops...think that was PHP, didn't read enough ;-)
0
 
BeakerTrailAuthor Commented:
Yeah. What I am trying to do is in ASP - i've zero knowledge of PHP
0
 
alorentzCommented:
However, I would like to recommend not storing the image in the DB, rather store the path to the uploaded fileonthe server, then use this to display:

<img src="<%=rs("information")%>">
0
 
alorentzCommented:
Try this:

rst.Open sqltext,objaConn,3,3
rst.AddNew
rst("name") = Request.form("name")' varchar in DB
rst("email") = Request.form("email") ' varchar in DB
rst("information").AppendChunk Request.form("information") 'no = sign
rst.update
rst.close
0
 
BeakerTrailAuthor Commented:
It isn't an image, it is a very big chunk of text.

The error i get occurs when writing to a Blob or a Text field. This is ASP and mySQL

I will post all the code below with the mySQL field types, maybe it will help:

Dim rst, objaConn, objaRS
Set objaConn = Server.CreateObject("ADODB.Connection")
objaConn.Open "Driver={MySQL ODBC 3.51 Driver};Server=xxx;Database=xxx;UID=xxx;PWD=xxx;"
Response.Buffer = true
set rst = Server.CreateObject("ADODB.RecordSet")
sqltext = "SELECT * FROM TableName"
rst.Open sqltext,objaConn,3,3
rst.AddNew
rst("name") = Request.form("name") 'varchar
rst("email") = Request.form("email") 'varchar
rst("datejoined") = Year(Date) & "-" & Month(Date) & "-" & Day(Date) & " " & time 'datetime
rst("information") = Request.form("information") 'text
rst("interests") = Request.form("interests") 'text
rst("occupation") = Request.form("occupation") 'text
rst.update 'line 114
rst.close

which gives the error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Invalid string or buffer length

line 114
0
 
BeakerTrailAuthor Commented:
For the line:
rst("information").AppendChunk Request.form("information")

It gives:

ADODB.Field error '800a0c93'

Operation is not allowed in this context.
0
 
alorentzCommented:
May need to put this in there:


Set objaConn = Server.CreateObject("ADODB.Connection")
objaConn.Open "Driver={MySQL ODBC 3.51 Driver};Server=xxx;Database=xxx;UID=xxx;PWD=xxx;"

objaConn.CursorLocation = adUseClient  '<---------ADD THIS

0
 
BeakerTrailAuthor Commented:
That returns:

ADODB.Connection error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
0
 
alorentzCommented:
This then:

Set objaConn = Server.CreateObject("ADODB.Connection")
objaConn.CursorLocation = 3                           '<-----move line here and change to 3
objaConn.Open "Driver={MySQL ODBC 3.51 Driver};Server=xxx;Database=xxx;UID=xxx;PWD=xxx;"

Also, make sure you put back to this:

rst("information") = Request("information") '<-----should be fine for TEXT field


0
 
alorentzCommented:
Also, check the request object for data at top of page:

Response.write "TEXT: " & request("information")
Response.end
0
 
BeakerTrailAuthor Commented:
Excellent! that did the trick, i think.... mySQL server keeps going down - argh!

Thanks :)
0
 
alorentzCommented:
MySQL is sketchy like that....good luck!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now