Classic ASP adding records to MySQL

Question

I have had to move on of my old classic ASP projects to a new host, and I'm having problems connecting to their MySQL server.

I have attached below the script I used with the old host which now errors

Data source name not found and no default driver specified

After a bit of digging it seems I have to change the driver to {MySQL ODBC 5.3 Unicode Driver} but it still errors. It seems to point to the cursor/lock type but I have used all option with no success.

ODBC driver does not support the requested properties.

<%
Dim Conn
Dim Rs
Dim sql

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

Conn.Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=xxx; PORT=xxx; DATABASE=xxx; UID=xxx; PASSWORD=xxx; OPTION=3"

sql= "SELECT * FROM table;" 

Rs.CursorType = 2 
Rs.LockType = 3 

Rs.Open sql, Conn    

Rs.AddNew 

Rs.Fields("database") = Request.Form("form")

Rs.Update   
Rs.Close
Set Rs = Nothing
Set Conn = Nothing
%>

Why are you using AddNew()? Just execute a SQL INSERT statement, its simpler and less error prone. — user692942
– user692942, Commented Oct 11, 2016 at 23:55
What was the original connection string (minus any real server, user info) that caused the Data source name not found and no default driver specified before you changed it? — user692942
– user692942, Commented Oct 12, 2016 at 11:05
@Lankymart well, for one, it's a simple way to protect against SQL Injection attacks. — user447356
– user447356, Commented Oct 13, 2016 at 7:01
@ShadowWizard while I agree I doubt very much that is why this method was used. Plus having to run a SELECT then have the ADODB .Update() to do the INSERT which is two db calls instead of one maybe simple but inefficient. — user692942
– user692942, Commented Oct 13, 2016 at 8:20
@Lankymart true, and yes, classic ASP wasn't really designed to be efficient, just simple to write and use. (That code is part of the basic classic ASP "features", I remember it from books and tutorials. :)) — user447356
– user447356, Commented Oct 13, 2016 at 8:22

user447356 · Accepted Answer · 2016-10-13 07:12:52Z

2

You don't need this to insert a record. Instead, use plain SQL which should be supported by all database drivers:

Dim oCommand
Const adInteger = 3
Const adDate = 7
Const adVarChar = 200
sql = "Insert Into table (database) Values (?)" 
Set oCommand = Server.Createobject("ADODB.Command")
Set oCommand.ActiveConnection = Conn
oCommand.CommandText = sql
oCommand.Parameters.Append(oCommand.CreateParameter("database", adVarChar, , 512, Request.Form("form")) )
oCommand.Execute

This is indeed bit more to write, but should preserve all the benefits of the other way (e.g. SQL Injections attack protection) and not being dependant on specific drivers.

answered Oct 13, 2016 at 7:12

user447356

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Classic ASP adding records to MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related