声明错误结束 [英] End of statement error

问题描述

我想组装多换行的INSERT语句。插入包括数值以及文本数据。我不断收到一个错误，我无法找到我的语法错误。任何人都可以看到我的错误？

  CurrentDb.ExecuteINSERT INTO tblCustParts（CustPartNum，客户ID，合金，状态，完成，FormType，压力表，GaugeAimPlus，GaugeAimMinus，＆放大器; _
        "GaugeGuarPlus,GaugeGuarMinus,Width,WidthAimPlus,WidthAimMinus,WidthGuarPlus,WidthGuarMinus,Length,LengthAimPlus,LengthAimMinus," ＆放大器; _
        "LengthGuarPlus,LengthGuarMinus,Diameter,DiameterAimPlus,DiameterAimMinus,DiameterGuarPlus,DiameterGuarMinus,CircleShear,IDMin," ＆放大器; _
        "ODMin,ODMax,LabelAs,ProduceAs,ShopPaperNotes,EyeOrientation,CoreType,PackingNotes,AQ,PaperInterleave,HeatTreatedSkids,HasRecipeFlag," ＆放大器; _
        "QRRExists,TensionLevel,MSKCoreSpec,ChemCertsReq,PhysCertsReq,PhysAndChemCertsReq,AAStandard,ASTM_B209_10)" ＆放大器; _
        VALUES（'＆放大器;第二部分和放大器;，＆放大器; Me.Customer＆安培;，'＆放大器;合金和放大器;，＆放大器;回火和放大器;，＆放大器;完成＆安培; '，'与＆amp;＆FormType安培;'，＆安培; Me.Gauge＆安培;，与＆amp; _
        与＆amp; Me.Gauge＆安培;，＆安培; Me.GaugeAimPlus＆安培;，＆安培; Me.GaugeAimMinus＆安培;，＆安培; Me.GaugeGuarPlus＆安培;，＆安培; Me.GaugeGuarMinus＆安培;， 与＆amp; _
        与＆amp; Me.WidthEntry＆安培;，＆安培; Me.WidthAimPlus＆安培;，＆安培; Me.WidthAimMinus＆安培;，＆安培; Me.WidthGuarPlus＆安培;，＆安培; Me.WidthGuarMinus＆安培;， 与＆amp; _
        与＆amp; Me.LengthEntry＆安培;，＆安培; Me.LengthAimPlus＆安培;，＆安培; Me.LengthAimMinus＆安培;，＆安培; Me.LengthGuarPlus＆安培;，＆安培; Me.LengthGuarMinus＆安培;， 与＆amp; _
        与＆amp;＆Me.Diameter安培;，＆安培; Me.DiameterAimPlus＆安培;，＆安培; Me.DiameterAimMinus＆安培;，＆安培; Me.DiameterGuarPlus＆安培;，与＆amp; _
        与＆amp; Me.DiameterGuarMinus＆安培;，＆安培; Me.CS＆安培;，＆安培; Me.IDMin＆安培;，＆安培; Me.ODMin＆安培;，＆安培; Me.ODMax＆安培;， '与＆amp;＆Me.LabelAs安培;'，'与＆amp; _
        与＆amp; Me.ProduceAs＆安培;'，'与＆amp; Me.ShopPaperNotes＆安培;'，'与＆amp; Me.EyeOrientation＆安培;'，'与＆amp; Me.CoreType＆安培;'，'与＆amp ; Me.PackingNotes＆安培;'，与＆amp; _
        与＆amp; Me.AQ＆安培;，＆安培; Me.PaperInterleave＆安培;，＆安培; Me.HeatTreatedSkids＆安培;，＆安培; Me.HasRecipeFlag＆安培;，＆安培; Me.QRRExists＆安培;， 与＆amp; _
        与＆amp; Me.TensionLevel＆安培;，＆安培; Me.MSKCoreSpec＆安培;，＆安培; Me.ChemCertsReq＆安培;，＆安培; Me.PhysCertsReq＆安培;，＆安培; Me.PhysAndChemCertsReq＆安培;， 与＆amp; _
        与＆amp;＆Me.AAStandard安培;，＆安培; Me.ASTM_B209_10＆安培;）
 

解决方案

我不知道有什么额外的报价和＆符号是，但他们所造成的问题。它们发生在各个值行的开始和结束。最好是把你的字符串在一个单独的变量，然后执行该，更容易发现问题。你可能也想考虑的参数，它们会让你的生活更容易在这种情况下。

  ssql =INSERT INTO tblCustParts（CustPartNum，客户ID，合金，状态，完成，FormType，压力表，GaugeAimPlus，GaugeAimMinus，＆放大器; _
        "GaugeGuarPlus,GaugeGuarMinus,Width,WidthAimPlus,WidthAimMinus,WidthGuarPlus,WidthGuarMinus,Length,LengthAimPlus,LengthAimMinus," ＆放大器; _
        "LengthGuarPlus,LengthGuarMinus,Diameter,DiameterAimPlus,DiameterAimMinus,DiameterGuarPlus,DiameterGuarMinus,CircleShear,IDMin," ＆放大器; _
        "ODMin,ODMax,LabelAs,ProduceAs,ShopPaperNotes,EyeOrientation,CoreType,PackingNotes,AQ,PaperInterleave,HeatTreatedSkids,HasRecipeFlag," ＆放大器; _
        "QRRExists,TensionLevel,MSKCoreSpec,ChemCertsReq,PhysCertsReq,PhysAndChemCertsReq,AAStandard,ASTM_B209_10)" ＆放大器; _
        VALUES（'＆放大器;第二部分和放大器;，＆放大器; Me.Customer＆安培;，'＆放大器;合金和放大器;，＆放大器;回火和放大器;，＆放大器;完成＆安培; '，'与＆amp;＆FormType安培;'，＆安培; Me.Gauge＆安培;，＆安培; _
        Me.Gauge和放大器; ，＆安培; Me.GaugeAimPlus和放大器; ，＆安培; Me.GaugeAimMinus和放大器; ，＆安培; Me.GaugeGuarPlus和放大器; ，＆安培; Me.GaugeGuarMinus和放大器; ，＆安培; _
        Me.WidthEntry和放大器; ，＆安培; Me.WidthAimPlus和放大器; ，＆安培; Me.WidthAimMinus和放大器; ，＆安培; Me.WidthGuarPlus和放大器; ，＆安培; Me.WidthGuarMinus和放大器; ，＆安培; _
        Me.LengthEntry和放大器; ，＆安培; Me.LengthAimPlus和放大器; ，＆安培; Me.LengthAimMinus和放大器; ，＆安培; Me.LengthGuarPlus和放大器; ，＆安培; Me.LengthGuarMinus和放大器; ，＆安培; _
        Me.Diameter和放大器; ，＆安培; Me.DiameterAimPlus和放大器; ，＆安培; Me.DiameterAimMinus和放大器; ，＆安培; Me.DiameterGuarPlus和放大器; ，＆安培; _
        Me.DiameterGuarMinus和放大器; ，＆安培; Me.CS和放大器; ，＆安培; Me.IDMin和放大器; ，＆安培; Me.ODMin和放大器; ，＆安培; Me.ODMax和放大器; ，＆放大器; Me.LabelAs和放大器; '，'与＆amp; _
        Me.ProduceAs和放大器; '，'与＆amp; Me.ShopPaperNotes和放大器; '，'与＆amp; Me.EyeOrientation和放大器; '，'与＆amp; Me.CoreType和放大器; '，'与＆amp; Me.PackingNotes和放大器; '，＆安培; _
        Me.AQ和放大器; ，＆安培; Me.PaperInterleave和放大器; ，＆安培; Me.HeatTreatedSkids和放大器; ，＆安培; Me.HasRecipeFlag和放大器; ，＆安培; Me.QRRExists和放大器; ，＆安培; _
        Me.TensionLevel和放大器; ，＆安培; Me.MSKCoreSpec和放大器; ，＆安培; Me.ChemCertsReq和放大器; ，＆安培; Me.PhysCertsReq和放大器; ，＆安培; Me.PhysAndChemCertsReq和放大器; ，＆安培; _
        Me.AAStandard和放大器; ，＆安培; Me.ASTM_B209_10和放大器; ）


昏暗的数据库作为数据库
设置DB = CurrentDB
db.Execute ssql dbFailOnError
 

最后一个记录插入ID

 设置RS = db.OpenRecordset（选择@@身份ID）
LastID = RS（ID）
 

参数例如：

  ssql =INSERT INTO表1（Atext，aNumber的）值（@ AText，@ aNumber的）
DIM QDF作为的QueryDef
设置QDF = CurrentDb.CreateQueryDef（，ssql）
qdf.Parameters（@ atext）=abc的
qdf.Parameters（@ aNumber的）= 1
qdf.Execute dbFailOnError
 

最后一个记录插入ID

  qdf.SQL =SELECT @@身份ID
设置RS = qdf.OpenRecordset
LastID = RS（ID）
 

I am trying to assemble an insert statement with multiple line breaks. The insertion includes both numerical as well as textual data. I keep getting an error, and I cannot find the where I am syntactically wrong. Can anyone see me error?

CurrentDb.Execute "INSERT INTO tblCustParts (CustPartNum,CustomerID,Alloy,Temper,Finish,FormType,Gauge,GaugeAimPlus,GaugeAimMinus," & _
        "GaugeGuarPlus,GaugeGuarMinus,Width,WidthAimPlus,WidthAimMinus,WidthGuarPlus,WidthGuarMinus,Length,LengthAimPlus,LengthAimMinus," & _
        "LengthGuarPlus,LengthGuarMinus,Diameter,DiameterAimPlus,DiameterAimMinus,DiameterGuarPlus,DiameterGuarMinus,CircleShear,IDMin," & _
        "ODMin,ODMax,LabelAs,ProduceAs,ShopPaperNotes,EyeOrientation,CoreType,PackingNotes,AQ,PaperInterleave,HeatTreatedSkids,HasRecipeFlag," & _
        "QRRExists,TensionLevel,MSKCoreSpec,ChemCertsReq,PhysCertsReq,PhysAndChemCertsReq,AAStandard,ASTM_B209_10)" & _
        "VALUES('" & Part & "', " & Me.Customer & ", '" & Alloy & "', '" & Temper & "', '" & Finish & "', '" & FormType & "', " & Me.Gauge & ", "" & _
        " & Me.Gauge & ", " & Me.GaugeAimPlus & ", " & Me.GaugeAimMinus & ", " & Me.GaugeGuarPlus & ", " & Me.GaugeGuarMinus & ", "" & _
        " & Me.WidthEntry & ", " & Me.WidthAimPlus & ", " & Me.WidthAimMinus & ", " & Me.WidthGuarPlus & ", " & Me.WidthGuarMinus & ", "" & _
        " & Me.LengthEntry & ", " & Me.LengthAimPlus & ", " & Me.LengthAimMinus & ", " & Me.LengthGuarPlus & ", " & Me.LengthGuarMinus & ", "" & _
        " & Me.Diameter & ", " & Me.DiameterAimPlus & ", " & Me.DiameterAimMinus & ", " & Me.DiameterGuarPlus & ", "" & _
        " & Me.DiameterGuarMinus & ", " & Me.CS & ", " & Me.IDMin & ", " & Me.ODMin & ", " & Me.ODMax & ", '" & Me.LabelAs & "', '"" & _
        " & Me.ProduceAs & "', '" & Me.ShopPaperNotes & "', '" & Me.EyeOrientation & "', '" & Me.CoreType & "', '" & Me.PackingNotes & "', "" & _
        " & Me.AQ & ", " & Me.PaperInterleave & ", " & Me.HeatTreatedSkids & ", " & Me.HasRecipeFlag & ", " & Me.QRRExists & ", "" & _
        " & Me.TensionLevel & ", " & Me.MSKCoreSpec & ", " & Me.ChemCertsReq & ", " & Me.PhysCertsReq & ", " & Me.PhysAndChemCertsReq & ", "" & _
        " & Me.AAStandard & ", " & Me.ASTM_B209_10 & ")"

解决方案

I am not sure what the extra quotes and ampersands are for, but they are causing the problem. They occur at the start and end of each Value line. It is best to put your string in a separate variable, and then execute that, it is easier to spot problems. You might also like to consider parameters, they will make your life easier in this case.

ssql = "INSERT INTO tblCustParts (CustPartNum,CustomerID,Alloy,Temper,Finish,FormType,Gauge,GaugeAimPlus,GaugeAimMinus," & _
        "GaugeGuarPlus,GaugeGuarMinus,Width,WidthAimPlus,WidthAimMinus,WidthGuarPlus,WidthGuarMinus,Length,LengthAimPlus,LengthAimMinus," & _
        "LengthGuarPlus,LengthGuarMinus,Diameter,DiameterAimPlus,DiameterAimMinus,DiameterGuarPlus,DiameterGuarMinus,CircleShear,IDMin," & _
        "ODMin,ODMax,LabelAs,ProduceAs,ShopPaperNotes,EyeOrientation,CoreType,PackingNotes,AQ,PaperInterleave,HeatTreatedSkids,HasRecipeFlag," & _
        "QRRExists,TensionLevel,MSKCoreSpec,ChemCertsReq,PhysCertsReq,PhysAndChemCertsReq,AAStandard,ASTM_B209_10)" & _
        "VALUES('" & Part & "', " & Me.Customer & ", '" & Alloy & "', '" & Temper & "', '" & Finish & "', '" & FormType & "', " & Me.Gauge & ", " & _
        Me.Gauge & ", " & Me.GaugeAimPlus & ", " & Me.GaugeAimMinus & ", " & Me.GaugeGuarPlus & ", " & Me.GaugeGuarMinus & ", " & _
        Me.WidthEntry & ", " & Me.WidthAimPlus & ", " & Me.WidthAimMinus & ", " & Me.WidthGuarPlus & ", " & Me.WidthGuarMinus & ", " & _
        Me.LengthEntry & ", " & Me.LengthAimPlus & ", " & Me.LengthAimMinus & ", " & Me.LengthGuarPlus & ", " & Me.LengthGuarMinus & ", " & _
        Me.Diameter & ", " & Me.DiameterAimPlus & ", " & Me.DiameterAimMinus & ", " & Me.DiameterGuarPlus & ", " & _
        Me.DiameterGuarMinus & ", " & Me.CS & ", " & Me.IDMin & ", " & Me.ODMin & ", " & Me.ODMax & ", '" & Me.LabelAs & "', '" & _
        Me.ProduceAs & "', '" & Me.ShopPaperNotes & "', '" & Me.EyeOrientation & "', '" & Me.CoreType & "', '" & Me.PackingNotes & "', " & _
        Me.AQ & ", " & Me.PaperInterleave & ", " & Me.HeatTreatedSkids & ", " & Me.HasRecipeFlag & ", " & Me.QRRExists & ", " & _
        Me.TensionLevel & ", " & Me.MSKCoreSpec & ", " & Me.ChemCertsReq & ", " & Me.PhysCertsReq & ", " & Me.PhysAndChemCertsReq & ", " & _
        Me.AAStandard & ", " & Me.ASTM_B209_10 & ")"


Dim db As database
Set db = CurrentDB
db.Execute ssql dbFailOnError

Last record inserted ID

Set rs = db.OpenRecordset("select @@identity as id")
LastID = rs("id")

Parameter example:

ssql = "INSERT INTO Table1 (Atext,Anumber) Values (@AText,@Anumber)"
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("", ssql)
qdf.Parameters("@atext") = "abc"
qdf.Parameters("@Anumber") = 1
qdf.Execute dbFailOnError

Last record inserted ID

qdf.SQL = "select @@identity as id"
Set rs = qdf.OpenRecordset
LastID = rs("id")

这篇关于声明错误结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！