Ok, first of all, I write in VB, not ASP, so you may have to convert what I say to ASP.
Here is how I would do what you are trying to do in ASP:
Dim strConnect As String
Dim conn As ADODB.Connection
Dim rs as ADODB.Recordset
Dim strBannerName As String
Dim strBannerImage As String
Dim strBannerUrl As String
Dim number As Long
Dim number1 As Long
strBannerName = "verr"
strBannerImage = ",,rty.."
strBannerUrl = "heki"
number = 1234567890
number1 = 1234567890
strConnect = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=127.0.0.1;" _
& "DATABASE=test;" _
& "UID=testuser;" _
& "PWD=12345;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841
Set conn = New ADODB.Connection
Conn.CursorLocation = adUseClient
Conn.ConnectionString = StrConnect
Conn.Open
Set rs = new ADODB.Recordset
Rs.open "SELECT * FROM bann WHERE 1=0", conn, adOpenStatic, adLockOptimistic
Rs.AddNew
Rs.fields("bannerName").value = strBannerName
Rs.fields("bannerImage").value = strBannerImage
Rs.fields("bannerURL").value = strBannerUrl
Rs.fields("bannerImpressions").value = number
Rs.fields("bannerClickThrus").value = number1
Rs.update
Rs.close
Set rs=nothing
Conn.close
Set conn = nothing
.....................and so on
Main difference is my explicit declaration of client side cursor, optimistic lock, and my
opening of an empty recordset to add to as opposed to opening the entire table. Not only
can this cause potential problems, but you are slowing down your script by loading what
could eventually become a huge table unnecessarily.
Hope that helps,
Mike Hillyer
www.vbmysql.com
-----Original Message-----
From: Hekuran Vokshi [mailto:heki@stripped]
Sent: Sunday, May 18, 2003 3:33 PM
To: mysql@stripped
Subject: ASP and MySQL
Hi all,
I know it is bizarre using ASP and MySQL but that is the requirement that we
have. However I am having a bit of a problem. I have string variables and
numeric variables that need inserting into a record set. The problem is that
whenever I have a string longer then 4 characters (that is any characters
and has been tested many times) it does not inert it into the record set.
I'll include the script bellow and the table. If you think that I am missing
something please tell me so, otherwise I have to put it down as a ODBC
connectivity problem. By the way I am running MySQL 4.0.12-nt on win2K
platform and IIS; phpMyAdmin (which incidentally works fine) and
MyODBC-3.51.06.
TABLE:
Table comments : Banner rotation system
Field Type Null Default
bannerID int(4) No
bannerName varchar(255) Yes NULL
bannerImage varchar(255) Yes NULL
bannerURL varchar(255) Yes NULL
bannerImpressions int(8) Yes NULL
bannerClickThrus int(8) Yes NULL
Indexes : Keyname Type Cardinality Field
PRIMARY PRIMARY 7 bannerID
Space usage : Type Usage
Data 216 Bytes
Index 2,048 Bytes
Total 2,264 Bytes
Row Statistic : Statements Value
Format dynamic
Rows 7
Row length ø 30
Row size ø 323 Bytes
Next Autoindex 8
Creation May 17, 2003 at 09:44 AM
Last update May 18, 2003 at 06:27 PM
The script is:
<%
option Explicit
Dim strConnect
On Error Resume next
strConnect = "Driver={mySQL}; Server=teneqe; Port=3306; Option=; Socket=;
Stmt=; Database=banners; Uid=root; Pwd=;"
%>
<!-- METADATA Type="TypeLib" File="c:\program files\common
files\system\ado\msado15.dll" -->
<html><body>
<%
Dim objRS, mySql, number, number1, strBannerName, strBannerUrl,
strBannerImage, temp1
Set objRS = Server.CreateObject("ADODB.Recordset")
temp1 = "test"
strBannerName = "verr"
strBannerImage = ",,rty.."
strBannerUrl = "heki"
number = 1234567890
number1 = 1234567890
mySql = "bann"
objRS.Open mySql, strConnect, adOpenStatic, adLockPessimistic, adCmdTable
objRS.AddNew
objRS("bannerName") = CStr(strBannerName)
objRS("bannerImage") = CStr(strBannerImage)
objRS("bannerURL") = CStr(strBannerUrl)
objRS("bannerImpressions") = number
objRS("bannerClickThrus") = number1
objRS.Update
objRS.Close
Set objRS = nothing
objConn.Close
Set objConn = nothing
Response.write strBannerName & "<br>"
Response.write strBannerImage & "<br>"
Response.write strBannerUrl & "<br>"
Response.write number & "<br>"
Response.write number1 & "<br>"
If err.number>0 then
response.write "VBScript Errors Occured:" & "<br>"
response.write "Error Number=" & err.number & "<br>"
response.write "Error Descr.=" & err.description & "<br>"
response.write "Help Context=" & err.helpcontext & "<br>"
response.write "Help Path=" & err.helppath & "<br>"
response.write "Native Error=" & err.nativeerror & "<br>"
response.write "Source=" & err.source & "<br>"
response.write "SQLState=" & err.sqlstate & "<br>"
End if
%>
</body></html>
Thanks in advance for your help.
Regards
Heki
