• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1203
  • 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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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