Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Rey
    Rey is offline
    New Coder
    Join Date
    Sep 2006
    Location
    Peoria, AZ
    Posts
    16
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Connection not executing SQL Insert

    Howdy.
    Back after some time.
    I've been assigned to work an company's ASP site.
    I'm somewhat new to ASP but been learning.
    I'm also new to MySQL.

    My problem is that I've tried to use the recordset AddNew method with an array of fieldnames and values.
    And it did not work - unfortunately, I have to use response.write to debug - I do not have any visual studio except the express versions.

    Additionally, the MySQL logs I enabled today that I thought would show the sql statements and errs, i.e. the general log while created do not show any statements.

    So I then changed from using the AddNew method to just doing an insert via the connection object.

    I get inside the class method - using a class that instantiates and terminates at end - again seeing this via response.write statements.

    Again, it gets to the execute line and then I get a 404 page but nothing else in IE8 nor even in Chrome.

    Using XP sp3 box logging in remotely - next room - to another XP box (older) that has IIS5/6, MySQL 5.1.

    One iteration before, the class was being closed but now we get inside the class CloseDB method but it does not write that connection is being closed.

    Below are the results thus far. Interestingly, the SQL statement works by itself within HeidiSQL and inserts row - no problem.

    I'm probably too close this to see my errors.
    Appreciate your comments and suggestions.
    Won't be able to test any comments until I get back to work on Monday but will review them.

    Thank you in advance,

    Rey

    Response.write statements
    Moving to class

    tblName = patientsglau_copy

    Getting values of the fields and values arrays
    fldNames = memberid,fname,lname,dob,sex,address,city,state,zip,phone,ethid

    Instantiating class

    Connected to MySQL database!
    Inside InsertRecord sub
    strSQL = INSERT INTO patientsglau_copy (memberid,fname,lname,dob,sex,address,city,state,zip,phone,ethid) VALUES('12345678','bob','bob','1967-3-12','M','1 london derry rd','peoria','NV','12345','1234567890',3);
    m_conn state = 1
    Connection is open
    Connection is open BEFORE call BeginTrans
    Beginning transaction
    Beginning connection execution
    Last insert ID = 0
    Last Inserted ID = 0
    MemberID = 12345678
    First name = REY
    Last name = BROWN
    m_conn state = 1
    m_conn is an object


    Code:
    
    'class method
    
    		Public Sub InsertRecord(tgtTable, arrTheFields, arrTheValues)
    			' simple now then move to stored proc
    			' purpose: provide tblname, array of values - may affect when larger tbl...
    						
    			dim rs
    			dim x
    			dim ubFields
    			dim ubValues
    			dim strSQL
    			dim fldNames
    			dim fldValues
    			dim ra		'records affected
    			
    			ra=0
    
    			response.write "Inside InsertRecord sub" & "<br />"
    			
    			fldNames = ""
    			fldValues = ""
    			
    			for x = 0 to ub
    			  if (not x = ub) then
    				fldNames = fldNames & arrFields(x) & ","
    			  else
    				fldNames = fldNames & arrFields(x)
    			  end if
    			next
    
    			for x = 0 to ub
    			  if (not x = ub) then
    				fldValues = fldValues & arrTheValues(x) & ","
    			  else
    				fldValues = fldValues & arrTheValues(x)
    			  end if
    			next
    			
    			strSQL = "INSERT INTO " & tgtTable & " ("
    			strSQL = strSQL & fldNames & ") "
    			strSQL = strSQL & "VALUES(" & fldValues & ");"			
    			
    			response.write "strSQL = " & strSQL & "<br />"
    					
    			response.write "m_conn state = " & m_conn.state & "<br />"
    			if (m_conn.state = adStateOpen) then
    				response.write "Connection is open" & "<br />"
    			end If
    			
    			if (m_conn.state = adStateOpen) then
    				 response.write "Connection is open BEFORE call BeginTrans" & "<br />"
    				 m_conn.BeginTrans
    				 response.write "Beginning transaction" & "<br />"
    				
    				 response.write "Beginning connection execution" & "<br />"
    				 m_conn.execute strSQL, ra, adExecuteNoRecords
    				
    				 response.write "Ended connection execution" & "<br />"
    				 if (ra > 0) then
    					 m_conn.CommitTrans
    					 response.write ra & "Record data inserted" & "<br />"
    				 else
    					 m_conn.RollBackTrans
    					 response.write "NO Record data inserted" & "<br />"
    				 end If
    				
    					' 'rs.open tblName, m_conn, adOpenKeySet, adLockOptimistic, adCmdTable
    					' 'rs.open tblName, m_conn, 1,3,2
    					' 'rs.open tgtTable, m_conn, adOpenDynamic, adLockOptimistic, adCmdTable
    					' 'rs.open tgtTable, m_conn, 2,3,2
    				' '	rs.open tgtTable,, 1,3,2
    				' 'response.write tgtTable & "opened " & "<br />"
    					' 'rs.AddNew arrTheFields, arrTheValues
    					' ' rs.AddNew
    					
    					' ' ' now loop thru
    					' ' for x = 0 to ub
    					  ' ' rs(arrFields(x)) = arrInsertValues(x)
    					  ' ' response.write arrFields(x) & " = " & arrInsertValues(x) & "<br />"
    					' ' next
    					' 'rs.Update
    					' 'response.write "Record data inserted" & "<br />"
    					
    					' 'response.write "Record data saved" & "<br />"
    				' '	rs.close
    				' '	set rs = nothing
    				' '	response.write "Recordset closed" & "<br />"
    				' 'if (err.number = 0) then
    					' 'm_conn.CommitTrans
    					' 'response.write "Data added/transaction committed!" & "<br />"
    				' 'else
    					' 'm_conn.RollBackTrans
    					' 'response.write "Error encountered adding patient information; data NOT added:" & "<br />"
    					' 'response.write "Error number: " & err.number & "<br />"
    					' 'response.write "Description: " & err.description & "<br />"
    				' 'end if
    			else
    				 response.write "DB Connection is NOT open" & "<br />"
    			End If
    			
    		End sub
    		
    		Public Sub CloseDB
    
    			if isObject( m_conn ) then
    				response.write "m_conn state = " & m_conn.state & "<br />"
    				response.write "m_conn is an object" & "<br />"
    				m_conn.close
    				response.write "Connection to MySQL database closed!" & "<br />"
    				set m_conn = nothing
    				response.write "connection var set to nothing!" & "<br />"
    			end if		
    		
    
    		End Sub
    
    
    code snippet of the action page we are sent to from the user input form
    <%
    dim strMemberID
    dim strDOS
    dim strFName
    dim strLName
    dim strDOB
    dim strSex
    dim strAddress
    dim strCity
    dim strState
    dim strZIP
    dim strPhone
    dim intRace
    	 
    ' dates in MySQL are inserted as string with ''	 
    strMemberID = chr(39) & CStr(objUpload.Form("txtPlanID")) & chr(39)
    strDOS = chr(39) & CStr(GetMySQLDate(objUpload.Form("txtDOS"))) & chr(39)
    strFName = chr(39) & CStr(objUpload.Form("txtFName")) & chr(39)
    strLName = chr(39) & CStr(objUpload.Form("txtLName")) & chr(39)
    strDOB = chr(39) & CStr(GetMySQLDate(objUpload.Form("txtDOB"))) & chr(39)
    strSex = chr(39) & CStr(objUpload.Form("rdoSex")) & chr(39)
    strAddress = chr(39) & CStr(objUpload.Form("txtAddress")) & chr(39)
    strCity = chr(39) & CStr(objUpload.Form("txtCity")) & chr(39)
    strState = chr(39) & objUpload.Form("txtState") & chr(39)
    strZIP = chr(39) & CStr(objUpload.Form("txtZip")) & chr(39)
    strPhone = chr(39) & CStr(objUpload.Form("txtPhone")) & chr(39)
    intRace = CInt(objUpload.Form("rdoRace"))
    	 
    
    
    	 
    'release resources
    Set objUpload = Nothing
    	
    	'response.redirect "http://localhost/secureeyespec/esgl001c.asp"
    	
    %>
    
    
    <%
    	
    	response.write "<p>" & "Moving to class" & "</p>"
    	
    	' now adding patient info via class
    	dim arrFields
    	dim arrInsertValues
    	dim lastID
    	dim oGlaucoma
    	dim tblName
    	
    	'tblName = "patientsGlau"
    	tblName = "patientsglau_copy"
    	
    	response.write "tblName = " & tblName & "<p></p>"
    	
    	arrFields = array("memberid", "fname","lname", "dob", "sex", "address", "city", "state", "zip", "phone", "ethid")
    	arrInsertValues = array(strMemberID, strFName, strLName, strDOB, strSex, strAddress, strCity, strState, strZIP, strPhone, intRace)
    
    	
    	dim x
    	dim ub
    	dim fldNames
    	
    	fldNames = ""
    	ub = uBound(arrFields)
    	
    	response.write "Getting values of the fields and values arrays" & "<br />"
    
    	for x = 0 to ub
    	  if (not x = ub) then
    		fldNames = fldNames & arrFields(x) & ","
    	  else
    		fldNames = fldNames & arrFields(x)
    	  end if
    	next
    	response.write "fldNames = " & fldNames & "<br />"
    	
    	'for x = 0 to ub
    	 'rs(arrFields(x)) = arrInsertValues(x)
    	 'response.write arrFields(x) & " = " & arrInsertValues(x) & "<br />"
    	'next
    	
    	
    	response.write "<p>" & "Instantiating class" & "</p>"	
    	' instantiate obj of type clsGlaucoma
    	set oGlaucoma = new clsGlaucomaScreen	
    	
    	' call connection method
    	oGlaucoma.OpenDB
    	
    	' insert record	
    	oGlaucoma.InsertRecord tblName, arrFields, arrInsertValues
    	
    	
    	lastID = oGlaucoma.GetLastInsertedID()
    	response.write "Last Inserted ID = " & lastID & "<br />"
    	oGlaucoma.TestSelect
    	
    	oGlaucoma.CloseDB
    	
    	' destroy obj
    	set oGlaucoma = nothing
    	
    	
    %>

  • #2
    Rey
    Rey is offline
    New Coder
    Join Date
    Sep 2006
    Location
    Peoria, AZ
    Posts
    16
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Just to update this post:
    Found that use of adLockBatchOptimistic does NOT work but no err message is displayed other than maybe Unspecified error.

    Then tried using a command object with a dsn-less connection string with and without the parens, i.e.
    Code:
    strConn = "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=glaucomadiabetes; User=glUser; Password=gluser_ESAN;; Option=3;"
    
    dbconn.CursorLocation = adUseClient
    			 dbconn.mode = adModeWrite
    			 dbconn.open strConn
    			dbconn.open("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=glaucomadiabetes;User=glUser;Password=gluser_ESAN;;Option=3;")
    This did not work either - regardless whether I used the strConn variable or dbconn.open and the connectionstring.

    Again, no error displayed just that during debug it dropped out of the method...

    In the same command sub, tried these lines:
    Code:
    cmd.execute ra,,adExecuteNoRecords
    cmd.execute
    Only the cmd.execute line works - attempting to put parens around also failed.

    The original sub now works. Now to try using parameters to see if it works.

    Also, the MS Script debugger does not seem to work and it is irritating using response.write and not being able to see where the code line fails.

    Now to see if mgmt will spring for VS2010...

    Rey


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •