aaronseidman 0 Newbie Poster

I am having a problem porting an application written in ASP, using an MS SQL Server database, to a different ISP using MySQL. Code that seems to run fine on the current site generates errors on the new site. My connection to the MySQL db is:

set objConn = Server.CreateObject("ADODB.Connection")
  objConn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & "SERVER=nn.nn.nn.nn PORT=3306; DATABASE=dbname;" & "UID=username; PWD=password; OPTION=3"

and using that I can embed SQL SELECT statements that work fine. However, when I try to do an INSERT or UPDATE the system barfs

Specifically with sqlstr = UPDATE Emaillist SET Email='AAA@foo.com' WHERE ID = 5492 When I use the original ASP code: objConn.Execute sqlStr I get:

ADODB.Connection error '800a0e78'
Operation is not allowed when the object is closed.
/manage/manage.asp, line 61

If I substitute ors.open sqlstr, objConn.ConnectionString (which works fine with SQL SELECT statements)
the system returns:

Microsoft VBScript runtime  error '800a01a8'
Object required: ''
/manage/manage.asp, line 61

Included below is the module from which the above is taken

select case request("db")
	case "Deceased"
		fieldArr = Array("GradYear","FName","LName","BHSLName")
		goBack = "deceased.asp"
	case "Emaillist"
		fieldArr = Array("GradYear","FName","LName","BHSLName","Email")
		goBack = "emaillist.asp"
end select

if request("action") = "delete" then
	sqlStr = "DELETE FROM " & request("db") & " WHERE ID = " & request("record")
	objConn.Execute sqlStr
	response.redirect(goBack)
end if

if request("action") = "add" or request("action") = "edit" then
	'response.write "ADD"

	if request("go") = "go" then
		'response.write "GO"
		if request("action") = "add" then
			sqlStr = "INSERT INTO " & request("db") & "("
			for i = 0 to UBound(fieldArr)
				sqlStr = sqlStr & fieldArr(i)
				if i < UBound(fieldArr) then
					sqlStr = sqlStr & ","
				end if
			next
			sqlStr = sqlStr & ") VALUES ("
			for i = 0 to UBound(fieldArr)
				temp = request(fieldArr(i))
				temp = replace(temp,"'","''")
				sqlStr = sqlStr & "'" & temp & "'"
				if i < UBound(fieldArr) then
					sqlStr = sqlStr & ","
				end if
			next
			sqlStr = sqlStr & ")"
		else
			sqlStrUp = "UPDATE " & request("db") & " SET "
			for i = 0 to UBound(fieldArr)
				temp = request(fieldArr(i))
				temp = replace(temp,"'","''")
				sqlStr = sqlStr & fieldArr(i) & "='" & temp & "'"
				if i < UBound(fieldArr) then
					sqlStr = sqlStr & ","
				end if
			next
			sqlStr = sqlStr & " WHERE ID = " & CInt(request("record"))
		end if
		response.write "SQL: " & sqlStr
	// USE ONE OR THE OTHER OF THE NEXT TWO LINES
		'objConn.Execute sqlStr
		ors.open sqlstr, objConn.ConnectionString
		response.redirect(goBack)
	else
		if request("action") = "edit" then
			Set ors = Server.CreateObject("ADODB.Recordset")
			strQuery = "SELECT * FROM " & request("db") & " WHERE ID = " & Cint(request("record"))
			'response.write strQuery
			ors.Open strQuery, objConn.ConnectionString
			Dim fieldValues(4)
			for i = 0 to UBound(fieldArr)
				'response.write "<br>" & i & " "
				fieldValues(i) = ors(fieldArr(i))
				'response.write fieldValues(i)
			next
		end if
		response.write "<form action=manage.asp method=post>"
		response.write "<table border=0><tr><td></td><td><input type=hidden name=go value=go>"
		response.write "<input type=hidden name=action value=" & request("action") & ">"
		if request("action") = "edit" then response.write "<input type=hidden name=record value=" & request("record") & ">"
		response.write "<input type=hidden name=db value=" & request("db") & "></td></tr>"
		for i = 0 to CInt(UBound(fieldArr))
			if isArray(fieldValues) then temp = fieldValues(i)
			response.write "<tr><td>" & fieldArr(i) & "</td><td><input name=" & fieldArr(i) & " size=30 value=" & chr(34) & temp & chr(34) & "></td></tr>"
		next
		response.write "<tr><td></td><td><input type=submit value=Submit></td></tr></table></form>"
	end if
end if
%>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.