Search code examples
asp-classic

Result is always zero

When perform this piece of code on my webserver the result is always 0 (ZERO)

Can anyone give a hunch on this problem (solution even better :-D )

Dim MyTotalPages
Dim Recordset
Dim Connection
Dim aspDBcount
Dim ShowRowCount

ShowRowCount = 3 ' Fixed size

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

ConnString = "DRIVER={MySQL ODBC 5.3 Unicode Driver}; SERVER=server_address; UID=a_username;PASSWORD=a_password; OPTION=3; Port=a_port"

Connection.Open ConnString

SQL = "SELECT COUNT(*) AS MyRowCounts FROM `a_database`.`a_table`;"

Set Recordset = Connection.Execute(SQL)

aspDBcount = (Recordset("MyRowCounts") * 1) 

Recordset.Close

MyTotalPages = (aspDBcount * 1) / (ShowRowCount * 1))

Response.Write aspDBcount & "HTML_NEW_LINE" & ShowRowCount & "HTML_NEW_LINE" & MyTotalPages
  • Output is 10, 3, 0
  • Should be 10, 3, 3.33333... (with current data available in database)
Solution

  • Use: 

    aspDBcount = cInt(Recordset("MyRowCounts")) * 1

    In MySQL, SELECT COUNT() returns a BIGINT integer type and needs converting before you can manipulate it and use it properly in VBScript.

    CInt can only convert numbers between -32,767 and 32,767

    If your SELECT COUNT() exceeds this range you'll need to use CLng instead, which has a range of -2,147,483,648 to 2,147,483,647

    You can also use CDbl, which is a bit of a weird one. It can convert HUGE numbers and doesn't seem to have an overflow. It will just eventually return an Invalid number error if you try to pass a ridiculously large integer.