Problems with mySQL Backend

S

spectrolab

Registered User.
Local time
Today, 09:17
Joined
Feb 9, 2005
Messages
119
Hi All,

I have recently been converiting my former access backend to mySQl, all seems to be going well, but, after about 3 days of pulling my hair out, I can't figure out why the following script is not working!

Code: 
Line Input #1, buffer
While Not EOF(1)
   Select Case Mid(buffer, 1, 2)
      Case "Sa"
        SampleName = RTrim(Mid(buffer, 24, 25))
      
        
      Case "Ap"
        MeasureOriginName = RTrim(Mid(buffer, 24, 20))
        Set rst = dbs.OpenRecordset("tblNewXRFResults")
        rst.AddNew
        rst!SampleName = SampleName
        rst!ResultDate = Date
        rst!Time = Time()
        rst!MeasureOriginName = MeasureOriginName
        [COLOR="Red"]ResultID = rst!ResultID[/COLOR]
        rst.Update
        rst.Close
      Case "Fe"
        Fe = RTrim(Mid(buffer, 17, 9))
      Case "Si"
        SiO2 = RTrim(Mid(buffer, 17, 9))
      Case "Ca"
        CaO = RTrim(Mid(buffer, 17, 9))
      Case "Mn"
        Mn = RTrim(Mid(buffer, 17, 9))
      Case "Al"
        Al2O3 = RTrim(Mid(buffer, 17, 9))
      Case "Ti"
        TiO2 = RTrim(Mid(buffer, 17, 9))
      Case "Mg"
        MgO = RTrim(Mid(buffer, 17, 9))
      Case "P "
        P = RTrim(Mid(buffer, 17, 9))
      Case "S "
        Sul = RTrim(Mid(buffer, 17, 9))
      Case "K2"
        K2O = RTrim(Mid(buffer, 17, 9))
      Case "V "
        V = RTrim(Mid(buffer, 17, 9))
      Case "Cr"
        Cr = RTrim(Mid(buffer, 17, 9))
      Case "Co"
        Co = RTrim(Mid(buffer, 17, 9))
      Case "Ni"
        Ni = RTrim(Mid(buffer, 17, 9))
      Case "Cu"
        Cu = RTrim(Mid(buffer, 17, 9))
      Case "Zn"
        Zn = RTrim(Mid(buffer, 17, 9))
      Case "As"
        Ars = RTrim(Mid(buffer, 17, 9))
      Case "Pb"
        Pb = RTrim(Mid(buffer, 17, 9))
      Case "Ba"
        Ba = RTrim(Mid(buffer, 17, 9))
        Case "Na"
        Na = RTrim(Mid(buffer, 17, 9))
        Case "Cl"
        Cl = RTrim(Mid(buffer, 17, 9))
        
        
        sql = "INSERT INTO [tblNewXRFResultsConc] (ResultID, Fe, SiO2, CaO, Mn, Al2O3, TiO2, MgO, P, Sul, K2O, V, Cr, Co, Ni, Cu, Zn, Ars, Pb, Ba, Na, Cl)" & _
                     "Values(" & ResultID & ", " & _
                                 Fe & ", " & _
                                 SiO2 & ", " & _
                                 CaO & ", " & _
                                 Mn & ", " & _
                                 Al2O3 & ", " & _
                                 TiO2 & ", " & _
                                 MgO & ", " & _
                                 P & ", " & _
                                 Sul & ", " & _
                                 K2O & ", " & _
                                 V & ", " & _
                                 Cr & ", " & _
                                 Co & ", " & _
                                 Ni & ", " & _
                                 Cu & ", " & _
                                 Zn & ", " & _
                                 Ars & ", " & _
                                 Pb & ", " & _
                                 Ba & ", " & _
                                 Na & ", " & _
                                 Cl & ");"
                                 
         
        DoCmd.RunSQL sql
        
      End Select
   Line Input #1, buffer
   
   Wend

It fails at the line in red, saying incorrect use of null. The Result ID is auto_increment in mySQL and will work if you add a record using something else (i.e Navicat).It used to work fine in access, but now not with the table in mySQL. Any ideas? If you remove that line it works okay, but then there is nothing to tie the two tables together.
 
Last edited:
spectrolab said:
Hi All,

I have recently been converiting my former access backend to mySQl, all seems to be going well, but, after about 3 days of pulling my hair out, I can't figure out why the following script is not working!

Code: 
Line Input #1, buffer
While Not EOF(1)
   Select Case Mid(buffer, 1, 2)
      Case "Sa"
        SampleName = RTrim(Mid(buffer, 24, 25))
      
        
      Case "Ap"
        MeasureOriginName = RTrim(Mid(buffer, 24, 20))
        Set rst = dbs.OpenRecordset("tblNewXRFResults")
        rst.AddNew
        rst!SampleName = SampleName
        rst!ResultDate = Date
        rst!Time = Time()
        rst!MeasureOriginName = MeasureOriginName
        [COLOR="Red"]ResultID = rst!ResultID[/COLOR]
        rst.Update
        rst.Close
      Case "Fe"
        Fe = RTrim(Mid(buffer, 17, 9))
      Case "Si"
        SiO2 = RTrim(Mid(buffer, 17, 9))
      Case "Ca"
        CaO = RTrim(Mid(buffer, 17, 9))
      Case "Mn"
        Mn = RTrim(Mid(buffer, 17, 9))
      Case "Al"
        Al2O3 = RTrim(Mid(buffer, 17, 9))
      Case "Ti"
        TiO2 = RTrim(Mid(buffer, 17, 9))
      Case "Mg"
        MgO = RTrim(Mid(buffer, 17, 9))
      Case "P "
        P = RTrim(Mid(buffer, 17, 9))
      Case "S "
        Sul = RTrim(Mid(buffer, 17, 9))
      Case "K2"
        K2O = RTrim(Mid(buffer, 17, 9))
      Case "V "
        V = RTrim(Mid(buffer, 17, 9))
      Case "Cr"
        Cr = RTrim(Mid(buffer, 17, 9))
      Case "Co"
        Co = RTrim(Mid(buffer, 17, 9))
      Case "Ni"
        Ni = RTrim(Mid(buffer, 17, 9))
      Case "Cu"
        Cu = RTrim(Mid(buffer, 17, 9))
      Case "Zn"
        Zn = RTrim(Mid(buffer, 17, 9))
      Case "As"
        Ars = RTrim(Mid(buffer, 17, 9))
      Case "Pb"
        Pb = RTrim(Mid(buffer, 17, 9))
      Case "Ba"
        Ba = RTrim(Mid(buffer, 17, 9))
        Case "Na"
        Na = RTrim(Mid(buffer, 17, 9))
        Case "Cl"
        Cl = RTrim(Mid(buffer, 17, 9))
        
        
        sql = "INSERT INTO [tblNewXRFResultsConc] (ResultID, Fe, SiO2, CaO, Mn, Al2O3, TiO2, MgO, P, Sul, K2O, V, Cr, Co, Ni, Cu, Zn, Ars, Pb, Ba, Na, Cl)" & _
                     "Values(" & ResultID & ", " & _
                                 Fe & ", " & _
                                 SiO2 & ", " & _
                                 CaO & ", " & _
                                 Mn & ", " & _
                                 Al2O3 & ", " & _
                                 TiO2 & ", " & _
                                 MgO & ", " & _
                                 P & ", " & _
                                 Sul & ", " & _
                                 K2O & ", " & _
                                 V & ", " & _
                                 Cr & ", " & _
                                 Co & ", " & _
                                 Ni & ", " & _
                                 Cu & ", " & _
                                 Zn & ", " & _
                                 Ars & ", " & _
                                 Pb & ", " & _
                                 Ba & ", " & _
                                 Na & ", " & _
                                 Cl & ");"
                                 
         
        DoCmd.RunSQL sql
        
      End Select
   Line Input #1, buffer
   
   Wend

It fails at the line in red, saying incorrect use of null. The Result ID is auto_increment in mySQL and will work if you add a record using something else (i.e Navicat).It used to work fine in access, but now not with the table in mySQL. Any ideas? If you remove that line it works okay, but then there is nothing to tie the two tables together.
Click to expand...

First let me say I am not a MySQL user, I have used it but in a minor way a few years ago. However, it seems to me your auto increment won't have a value until you do the Update -- so it is unknown (Null) in your usage.

Is there a way to get the next value for ResultId before you do the addnew?

Suppose you added an empty record with only the resultId (assigned via the auto increment), then changed your SQL from INSERT to an UPDate, using the "new" resultId as your Key?
 
Thanks jDraw,

Got it to work, please see code

Code: 
Line Input #1, buffer
While Not EOF(1)
   Select Case Mid(buffer, 1, 2)
      Case "Sa"
        SampleName = RTrim(Mid(buffer, 24, 25))
      
        
      Case "Ap"
        MeasureOriginName = RTrim(Mid(buffer, 24, 20))
        Set rst = dbs.OpenRecordset("tblNewXRFResults")
        rst.AddNew
        rst!SampleName = SampleName
        rst!ResultDate = Date
        rst!Time = Time()
        rst!MeasureOriginName = MeasureOriginName
        rst.Update
        rst.Close
        Set rst = dbs.OpenRecordset("tblNewXRFResults")
        rst.MoveLast
        ResultID = rst!ResultID
        rst.Close

Not sure why it worked in Access but not mySQL, but it works well. Now I just have to iron out the other 20 or so bugs.
 
You must log in or register to reply here.

Similar threads

S
Serious Problem with Data Import
Replies
7
Views
1,483
WayneRyan
W

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom