#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    San Francisco
    Posts
    2
    Rep Power
    0

    Weird Field Type / Empty Record Issues With ASP / MYSQL


    Hello,

    I am looking for some insight into what for me seems like a crazy anomaly but to a lot of you it might be obvious...

    I have been trying to do something today that I have been doing for about ten years and I have never seen the code/db results I was getting today. All I am doing is, using classic ASP, passing data from a form to a set of variables and adding the data to a database. I am also using those variables in an email that is generated by the page.

    The good news for me is that my code finally works, the bad news is I don't understand why it wasn't working in the first place and I want to know what was happening. Even though I have been doing this for a while I am sadly not very educated in the intricacies of database structure and field types.

    Ultimately what I did was make numerous field type changes to the MySQL DB. Eventually the combination below worked (the field types are commented to the left of the update statements).

    But I have used all text before on the same database, different table, and it worked fine. Until I changed the tkEmail field to be a VarChar(255), only the last field (tkComments) would be updated and I wasn't getting ANY VbScript or MySQL errors. I would just get a table entry with all blanks except for the last field.

    Anyway, since I don't know exactly what was going on I am not sure what question to ask but I guess I am just curious as to how, if the 12th field out of 13 fields in a table was a bad field type, this would prevent the previous 11 fields (not including the id field) from being populated. And has anyone ever seen anything like that and can give me some feedback.

    I included the code and although it is basic, it works.

    Thanks for your help/advice/insight.

    Code:
    'CONNECTION
    dim rsICFB
    dim sDo
    
    'DATA
    dim tkContactType, tkSubType, tkGroupName, tkContact, tkPhone, tkAddress, tkAddress2
    dim tkCity, tkState, tkCountry, tkPostal, chkNewsletter, tkEmail, tkComments
    
    tkContactType = Request.Form("tkContactType")
    tkSubType = Request.Form("tkSubType")
    tkGroupName = Request.Form("tkGroupName")
    tkContact = Request.Form("tkContact")
    tkPhone = Request.Form("tkPhone")
    tkAddress = Request.Form("tkAddress")
    tkAddress2 = Request.Form("tkAddress2")
    tkCity = Request.Form("tkCity")
    tkState = Request.Form("tkState")
    tkCountry = Request.Form("tkCountry")
    tkPostal = Request.Form("tkPostal")
    tkEmail = Request.Form("tkEmail")
    tkComments = Request.Form("tkComments")
    
    dim sOutput
    sDo = Request.QueryString("do")
    
    SELECT CASE(sDo)
    	CASE("request")
    		conn = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=xxxxxxxx; PORT=3306; DATABASE=xxxxxxxx; USER=xxxxxxxxxxx; PASSWORD=xxxxxxxxxxxx; OPTION=3;"
    		set rsICFB = Server.CreateObject("ADODB.Recordset")
    
    			rsICFB.Open "icfbList",conn, 2, 2
    			rsICFB.AddNew          
    			rsICFB.Fields("tkContactType") = tkContactType               'Text - ok
    			rsICFB.Fields("tkSubType") = tkSubType               'Text - ok
    			rsICFB.Fields("tkGroupName") = tkGroupName               'Text - ok
    			rsICFB.Fields("tkContact") = tkContact               'Text - ok
    			rsICFB.Fields("tkPhone") = tkPhone               'Text - ok
    			rsICFB.Fields("tkAddress") = tkAddress               'Text - ok
    			rsICFB.Fields("tkAddress2") = tkAddress2               'Text - ok
    			rsICFB.Fields("tkCity") = tkCity               'Text - ok
    			rsICFB.Fields("tkState") = tkState               'Text - ok
    			rsICFB.Fields("tkCountry") = tkCountry               'Text - ok
    			rsICFB.Fields("tkPostal") = tkPostal               'Text - ok
    			rsICFB.Fields("tkEmail") = tkEmail                'VarChar - ok
    			rsICFB.Fields("tkComments") = tkComments   'Text - ok
    			
    			if err.number <> 0 then
    				sOutput = err.number & "(" & err.description & ")" & "<BR>" & err.source & "<br>" & vbCrLf
    				sOutput = sOutput & "The command could not be processed."
    			else
    				rsICFB.Update
    				rsICFB.Close
    				set rsICFB = nothing
                            end if
  2. #2
  3. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,449
    Rep Power
    4539
    If you have a data type mismatch I wouldn't expect to retrieve a populated recordset, just an error message.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    San Francisco
    Posts
    2
    Rep Power
    0
    I was inserting, not retrieving but, yeah, I expected an error message and didn't get one. Just a bunch of empty fields in my table.


    Originally Posted by Doug G
    If you have a data type mismatch I wouldn't expect to retrieve a populated recordset, just an error message.
  6. #4
  7. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,449
    Rep Power
    4539
    Do you have any On Error Resume Next statement in your code somewhere above the snip you posted? If yes, you may be supressing errors. You can just remove the error trapping until you get the problem resolved.
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester

IMN logo majestic logo threadwatch logo seochat tools logo