mysql via asp

Hi all,

I am having a problem with mysql via asp that I was hoping you could help me with. I have my database set up through asp so i can perform inserts and queries--I know it's not a severe connection problem. But when i evaluate the RecordCount property it yields -1 and .EOF and .BOF always yield FALSE. I tried using a DSN with the "Return matching rows" box checked and without a dsn using 'Option=0' in the connection string. Neither of these seem to work. I am getting results from the selects (the fields have values), I just dont know how many records im getting back. Anyone have any ideas? Thanks in advance for any help you could give.

Regards,
Jeremy

That's a common problem with ASP. You need to set the lockType and cursorLocation properties of your recordset. Here's a quick example:

<%
' change these for your site
connectme="DSN=Student;uid=student;pwd=magic"
sqltemp="select * from publishers where state='NY'"
set rstemp=Server.CreateObject("adodb.Recordset")
rstemp.open sqltemp, connectme, adopenstatic
howmanyrecs=rstemp.recordcount
response.write howmanyrecs & " records in<br>" & sqltemp
rstemp.close
set rstemp=nothing
%>

I have tried the same thing. Is there something we are missing maybe in mysql when setting up the connection through the drivers instead of code?

Sometimes an recordset will return one empty row and thus no information is available at that time. A possible solution to this is the use of the GetRows statement and fill a dynamic array. Then simply count the number of rows that are in the dynamic array.

A short example.

<%
database connection
connection.open
SQL statement.

recordset.Open
IF NOT recordset.Eof THEN
dynamic_array = recordset.Getrows
dynamic_array_rows = Ubound(dynamic_array, 2)
ELSE
dynamic_array_rows = -1
END IF
recordset.Close
connection.Close

SET recordset = Nothing
SET connection = Nothing


IF dynamic_array_rows >= 0 THEN
Count = 0
DO WHILE dynamic_array_rows <= Count
response.write(dynamic_array(0,Count))
Count = Count + 1
Loop
ELSE
response.write("No records Found")
END IF

%>


The example will simply fill the dynamic array with all the records found and then count the number of rows. (that is the ubound,2 statement) then close all connections and use the dynamic array to display the information.

WARNING: the first row starts at 0 thus if the eof statement is true then the rows should start at -1

NOTE: this example is vb / asp.

Hope it will help,

Werner.

Man, ASP seems 50 times more complex than PHP, it's just so unnecessary.

Hi all

Its not difficult to create a connection of ASP with MySQL

Remember all the constants like adOpenDynamic, etc. and all the lock type Optimistic etc. constants will not work if you dont include a file called as 'adovbs.inc' in your asp page.

adovbs.inc is located in <Drive Name>\Program Files\Common Files\System\ADO\

To get the RecordCount property right remember to open recordset with CursorType = adOpenStatic

Here is a sample code to make a connection with MySQL

<%
On Error Resume Next

Dim cn
Dim rs

Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

'connection using System DSN make sure MyODBC is installed on the system with web server

cn.ConnectionString = "DSN=mysql_dsn_name;uid=username;pwd=password"


'Now remember to execute Insert, Update, Delete query you dont have to make a recordset
'just type this text -- cn.Execute "SQL query" -- and then cn.Close
'for Select queries use this

rs.ActiveConnection = cn
rs.CursorType = 2 'equivalent to Dynamic recordset
rs.LockType = 2 'equivalent to Optimistic lock

rs.Open "Select * from table_name"

'after this use can use rs recordset for different purposes
'and finally

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing
%>

Hope this helps.

if it isn't so hard then why do I still get a -1 when i use the following script


set buyerconn = Server.CreateObject("adodb.connection")
buyerconn.mode = 3
buyerconn.connectionstring = "dsn=cddata"
buyerconn.open

Set buyeror = Server.CreateObject("ADODB.Recordset")
buyeror.open "Select * from Buyer_property where agent_number = '" & session("username") & "'",buyerconn,2,2


My query is correct. I did the gentlemen in one of these post idea and it works. The only thing I am having a real problem with is my VB code. It errors on me when I try to use this code.

I think you missed the closing quote for the sql statement..

Perhaps fill a variable with the sql query first and then use the open statement..

for example.

var_sql = "Select * "
var_sql = var_sql & "from table "
var_sql = var_sql & "where customer_id = ' " & session("cust_id") & " ';"

Then open the var_sql statement



As for the -1.. this indicates that there are 0 lines returned. which explanes the bof or eof statement..

Then answer me this why does this code work:

set oConn = Server.CreateObject("adodb.connection")
oconn.mode = 3
oconn.connectionstring = "dsn=cddata"
oconn.open

set ors = server.createobject("adodb.recordset")
ors.open "Select * from Agents where code = '" & session("username") & "'",oconn,2,2

What error message do you get.

Perhaps it is related to the variable you use.. If this session variable is empty you'll get a strange error message like not acceptible sql query bla bla..

try doing a movelast and movefirst, and then evaluate the recordcount. e.g.

rs.movelast
rs.movefirst
i = rs.recordcount

this should be a fool-proof way of getting the recordcount. i know in earlier versions of ado/dao, the recordcount would not be set correctly unless you did this. if you still get -1 after doing this, then you are either not getting any results or there must be some problem with the mysql odbc driver you are using...

what versions of the following are you using:
ADO
ASP/IIS
MySQL
MySQL ODBC Driver

??

A note about ADO in ASP / VBScript...

To use the various ad... constants, such as adLockOptimistic, adChar, adCmdProc, adCmdTable, and adOpenKeyset, without having to use the numerical values, you need to include the adovbs.inc file. Just search your C drive for it and put it in your includes folder. It saves you the trouble of learning (or more than likely, looking up) the constants and their actual values =)

Set rs = Server.CreateObject("ADODB.RecordSet")

'--------------------------------------
rs.CursorLocation=adUseClient
rs.CursorType=adOpenStatic
rs.LockType=adLockBatchOptimistic
'--------------------------------------

rs.Open strSQL, conn


This one method for work fine.
The CursorLocation = adUseClient is the key.

Greetings,

Manbos
URL