Probleme concatenation requete SQL trop longueRésolu

Question

Bonjour à tous, voilà ma question je viens de finir d'écrire ma requête sous Access en VBA le problème c'est que je ne sais pa ou mettre les caractères & _ à la fin des lignes ni comment reprendre la ligne suivante voici mon code : 

sql = "INSERT INTO table1 ([Facturation K&N du],[Année],[Type de facture],[Order Release Nr],[OR_DELIVERY_NOTE_NUMBER],[Center Code],[SAP_ACCOUNT],[SAP_PROFIT_CENTER],[SAP_COST_CENTER],[# RFTs],[Original Location code],[VAT Type],[ReturnYes / No],[Invoice Receiver],[Raflatac Order Type],[OR_Source_Location Name],[OR Source City],[OR Source Country Code],[OR Source Postal Code],[Pickup Seq Nr],[Planned Pickup date],[Planned Pickup time],[Actual Pickup date],[Actual Pickup time],[OR_DESTINATION_CUSTOMER_CODE],[Final Destination Name],[Final Dest City],[OR Final Destination Country],[OR Destination Postal Code],[Drop Seq Nr],[Planned Delivery date],[Planned Delivery time],[EAN Code],[# Shipped Unit],[# Unit],[Weight],[Rounded Weight],[Total Net Weight],[Total m3],[Total m2],[Rounded m2],[Carrier],[Shipment Group Nr],[Shipment Nr],[Source Code for the cost],[Source Name for the cost],[Source City for the cost]" & _
"[Destination Code for the cost],[Destination Name for the cost],[Destination City],[Destination Post code],[Shipment Destination Country], [Trpt Mode], [Equipment], [GC3 Invoice Nr], [GC3 Voucher Nr],[Voucher creation date],[Voucher creation time],[Rate calculation zones],[Type of Cost],[Accessorial Code],[Accessorial Description],[Adjustment Reason Code],[Adjustment Reason Description],[Cost Amount],[VAT Rate],[VAT Amount],[iTotal Amount],[Currency],[MAIN CLIENT],[ORDER_TYPE]) VALUES ('"& Année & "','"& TypeFacture &"','"& iOrder_Release_Nr &"','"& iOR_DELIVERY_NOTE_NUMBER &"','"& iCenter_Code &"', '"& iSAP_ACCOUNT &"','"& iSAP_PROFIT_CENTER"','"& iSAP_COST_CENTER'","'& i_RFTs &'", "'& iOriginal_Location_code &'", "'& iVAT_Type &'",'"& iReturn_Yes_No &"','"& iInvoice_Receiver &"','"& iRaflatac_Order_Type &"','"& iOR_Source_Location_Name &"','"& iOR_Source_City &"'",& _
'"& iOR_Source_Country_Code &"','"& iOR_Source_Postal_Code &"','"& iPickup_Seq_Nr &"' ,'"& iPlanned_Pickup_date &"','"& iPlanned_Pickup_time &"','"& iActual_Pickup_date &"','"& iActual_Pickup_time &"','"& iOR_DESTINATION_CUSTOMER_CODE &"','"& iFinal_Destination_Name &"','"& iFinal_Dest_City &"','"& iOR_Final_Destination_Country &"','"& iOR_Destination_Postal_Code &"','"& iDrop_Seq_Nr &"','"& iPlanned_Delivery_date &"','"& iPlanned_Delivery_time &"','"& iEAN_Code &"','"& i_Shipped_Unit &"','"& i_Unit &"','"& iWeight &"','"& iRounded_Weight &"','"& iTotal_Net_Weight &"','"& iTotal_m3 &"',& _
'"& iTotal_m2 &"','"& iRounded_m2 &"','"& iCarrier &"','"& iShipment_Group_Nr &"', '"& iShipment_Nr &"','"& iSource_Code_for_the_cost &"','"& iSource_Name_for_the_cost &"','"& iSource_City_for_the_cost &"','"& iDestination_Code_for_the_cost &"','"& iDestination_Name_for_the_cost &"','"& iDestination_City &"','"& iDestination_Post_code &"','"& iShipment_Destination_Country &"','"& iTrpt_Mode &"','"& iEquipment &"','"& iGC3_Invoice_Nr &"','"& iGC3_Voucher_Nr &"',& _
'"& iVoucher_creation_date &"','"& iVoucher_creation_time &"','"& iRate_calculation_zones &"' ,'"& iType_of_Cost &"','"& iAccessorial_Code &"','"& iAccessorial_Description &"','"& iAdjustment_Reason_Code &"','"& iAdjustment_Reason_Description &"','"& iCost_Amount &"','"& iVAT_Rate &"' ,'"& iVAT_Amount &"','"& iTotal_Amount &"','"& iCurrency &"' ,'"& iMAIN_CLIENT &"','"& iORDER_TYPE &"');"

Bon je sais c'est pas très clair mais pour se rendre compte quand il y a le caractère & _ c'est la fin d'une ligne. Problème : tout la requête est en rouge ce qui signifie que j'ai du me tromper dans l'écriture de celle ci. Je vous remercie d'avance pour votre aide.

                                 Cordialement,

Renfield · Accepted Answer

ne colle pas les &  aux "
une ligne commence et se termine par " 

... pas très lisible tout cela, hein ^^

en plus, tes champs sont décallés... 
ex:  
INSERT INTO table1 ([Facturation K&N du],[Année],[Type de facture],...
VALUES ('"& Année & "','"& TypeFacture &"',

ou est passé le champ ([Facturation K&N du] ?

Renfield
Admin CodeS-SourceS- MVP Visual Basic

Renfield · Answer

Pourquoi ne pas manipuler un Recordset, c'est plus simple et lisible...

oRS.AddNew
oRS.Fields("Année") = Année
oRS.Fields("Type de facture") = TypeFacture 
...
oRS.Update

Renfield
Admin CodeS-SourceS- MVP Visual Basic

Renfield · Answer

Si tu tiens a passer par une requète SQL, tu peux faire un compromis:

Dim xsFields(70) As String
Dim xsValues(70) As String   xsFields(0) "Facturation K&N du":             xsValues(0) "''"   xsFields(1) "Année":                          xsValues(1) "'" & Année & "'"   xsFields(2) "Type de facture":                xsValues(2) "'" & TypeFacture & "'"   xsFields(3) "Order Release Nr":               xsValues(3) "'" & iOrder_Release_Nr & "'"   xsFields(4) "OR_DELIVERY_NOTE_NUMBER":        xsValues(4) "'" & iOR_DELIVERY_NOTE_NUMBER & "'"   xsFields(5) "Center Code":                    xsValues(5) "'" & iCenter_Code & "'"   xsFields(6) "SAP_ACCOUNT":                    xsValues(6) "'" & iSAP_ACCOUNT & "'"   xsFields(7) "SAP_PROFIT_CENTER":              xsValues(7) "'" & iSAP_PROFIT_CENTER & "'"   xsFields(8) "SAP_COST_CENTER":                xsValues(8) "'" & iSAP_COST_CENTER & "'"   xsFields(9) "# RFTs":                         xsValues(9) "'" & i_RFTs & "'"   xsFields(10) "Original Location code":        xsValues(10) "'" & iOriginal_Location_code & "'"   xsFields(11) "VAT Type":                      xsValues(11) "'" & iVAT_Type & "'"   xsFields(12) "ReturnYes / No":                xsValues(12) "'" & iReturn_Yes_No & "'"   xsFields(13) "Invoice Receiver":              xsValues(13) "'" & iInvoice_Receiver & "'"   xsFields(14) "Raflatac Order Type":           xsValues(14) "'" & iRaflatac_Order_Type & "'"   xsFields(15) "OR_Source_Location Name":       xsValues(15) "'" & iOR_Source_Location_Name & "'"   xsFields(16) "OR Source City":                xsValues(16) "'" & iOR_Source_City & "'"   xsFields(17) "OR Source Country Code":        xsValues(17) "'" & iOR_Source_Country_Code & "'"   xsFields(18) "OR Source Postal Code":         xsValues(18) "'" & iOR_Source_Postal_Code & "'"   xsFields(19) "Pickup Seq Nr":                 xsValues(19) "'" & iPickup_Seq_Nr & "'"   xsFields(20) "Planned Pickup date":           xsValues(20) "'" & iPlanned_Pickup_date & "'"   xsFields(21) "Planned Pickup time":           xsValues(21) "'" & iPlanned_Pickup_time & "'"   xsFields(22) "Actual Pickup date":            xsValues(22) "'" & iActual_Pickup_date & "'"   xsFields(23) "Actual Pickup time":            xsValues(23) "'" & iActual_Pickup_time & "'"   xsFields(24) "OR_DESTINATION_CUSTOMER_CODE":  xsValues(24) "'" & iOR_DESTINATION_CUSTOMER_CODE & "'"   xsFields(25) "Final Destination Name":        xsValues(25) "'" & iFinal_Destination_Name & "'"   xsFields(26) "Final Dest City":               xsValues(26) "'" & iFinal_Dest_City & "'"   xsFields(27) "OR Final Destination Country":  xsValues(27) "'" & iOR_Final_Destination_Country & "'"   xsFields(28) "OR Destination Postal Code":    xsValues(28) "'" & iOR_Destination_Postal_Code & "'"   xsFields(29) "Drop Seq Nr":                   xsValues(29) "'" & iDrop_Seq_Nr & "'"   xsFields(30) "Planned Delivery date":         xsValues(30) "'" & iPlanned_Delivery_date & "'"   xsFields(31) "Planned Delivery time":         xsValues(31) "'" & iPlanned_Delivery_time & "'"   xsFields(32) "EAN Code":                      xsValues(32) "'" & iEAN_Code & "'"   xsFields(33) "# Shipped Unit":                xsValues(33) "'" & i_Shipped_Unit & "'"   xsFields(34) "# Unit":                        xsValues(34) "'" & i_Unit & "'"   xsFields(35) "Weight":                        xsValues(35) "'" & iWeight & "'"   xsFields(36) "Rounded Weight":                xsValues(36) "'" & iRounded_Weight & "'"   xsFields(37) "Total Net Weight":              xsValues(37) "'" & iTotal_Net_Weight & "'"   xsFields(38) "Total m3":                      xsValues(38) "'" & iTotal_m3 & "'"   xsFields(39) "Total m2":                      xsValues(39) "'" & iTotal_m2 & "'"   xsFields(40) "Rounded m2":                    xsValues(40) "'" & iRounded_m2 & "'"   xsFields(41) "Carrier":                       xsValues(41) "'" & iCarrier & "'"   xsFields(42) "Shipment Group Nr":             xsValues(42) "'" & iShipment_Group_Nr & "'"   xsFields(43) "Shipment Nr":                   xsValues(43) "'" & iShipment_Nr & "'"   xsFields(44) "Source Code for the cost":      xsValues(44) "'" & iSource_Code_for_the_cost & "'"   xsFields(45) "Source Name for the cost":      xsValues(45) "'" & iSource_Name_for_the_cost & "'"   xsFields(46) "Source City for the cost":      xsValues(46) "'" & iSource_City_for_the_cost & "'"   xsFields(47) "Destination Code for the cost": xsValues(47) "'" & iDestination_Code_for_the_cost & "'"   xsFields(48) "Destination Name for the cost": xsValues(48) "'" & iDestination_Name_for_the_cost & "'"   xsFields(49) "Destination City":              xsValues(49) "'" & iDestination_City & "'"   xsFields(50) "Destination Post code":         xsValues(50) "'" & iDestination_Post_code & "'"   xsFields(51) "Shipment Destination Country":  xsValues(51) "'" & iShipment_Destination_Country & "'"   xsFields(52) "Trpt Mode":                     xsValues(52) "'" & iTrpt_Mode & "'"   xsFields(53) "Equipment":                     xsValues(53) "'" & iEquipment & "'"   xsFields(54) "GC3 Invoice Nr":                xsValues(54) "'" & iGC3_Invoice_Nr & "'"   xsFields(55) "GC3 Voucher Nr":                xsValues(55) "'" & iGC3_Voucher_Nr & "'"   xsFields(56) "Voucher creation date":         xsValues(56) "'" & iVoucher_creation_date & "'"   xsFields(57) "Voucher creation time":         xsValues(57) "'" & iVoucher_creation_time & "'"   xsFields(58) "Rate calculation zones":        xsValues(58) "'" & iRate_calculation_zones & "'"   xsFields(59) "Type of Cost":                  xsValues(59) "'" & iType_of_Cost & "'"   xsFields(60) "Accessorial Code":              xsValues(60) "'" & iAccessorial_Code & "'"   xsFields(61) "Accessorial Description":       xsValues(61) "'" & iAccessorial_Description & "'"   xsFields(62) "Adjustment Reason Code":        xsValues(62) "'" & iAdjustment_Reason_Code & "'"   xsFields(63) "Adjustment Reason Description": xsValues(63) "'" & iAdjustment_Reason_Description & "'"   xsFields(64) "Cost Amount":                   xsValues(64) "'" & iCost_Amount & "'"   xsFields(65) "VAT Rate":                      xsValues(65) "'" & iVAT_Rate & "'"   xsFields(66) "VAT Amount":                    xsValues(66) "'" & iVAT_Amount & "'"   xsFields(67) "iTotal Amount":                 xsValues(67) "'" & iTotal_Amount & "'"   xsFields(68) "Currency":                      xsValues(68) "'" & iCurrency & "'"   xsFields(69) "MAIN CLIENT":                   xsValues(69) "'" & iMAIN_CLIENT & "'"   xsFields(70) "ORDER_TYPE":                    xsValues(70) "'" & iORDER_TYPE & "'"
   
   sql = "INSERT INTO table1([" & Join(xsFields, "], [") & "]) VALUES (" & Join(xsValues, ",") & ")", ----
By Renfield

cependant, gaffe au formatage... les ' ne sont necessaires que pour les champs de type String
pas de séparateur, donc, pour les numériques
les dates, sont a formater en MM/DD/YYYY avec des # comme séparateur

Renfield
Admin CodeS-SourceS- MVP Visual Basic

cs_bluntz54 · Answer

et pr les ( ) elles se positionnent ainsi : INSERT INTO ... (..) VALUES (...)?
ou bien INSERT INTO ... ((...) VALUES (...)) ?

pr la concaténation cela se fait comme ceci? : 

sql = "INSERT INTO ... (... .. ... ..." & _
" .. .... ... ......) " & _
"VALUES (... ..... .....);"

c'est bien ça? 
ça pose pa de probleme qu'en début de ligne j'ai par exemple : 

'"& nomagent &"'   Dois-je r ajouter " '"& nomagent &"' étant donné que c'est un retour à la ligne? merci d'avance Renfield

Renfield · Answer

INSERT INTO ... (..) VALUES (...)

sql = "INSERT INTO ... (... .. ... ..." & _
        " .. .... ... ......) " & _
        "VALUES (... ..... .....);"

exact...

les lignes commencent et finissent toujours par une guillemet (si c'est une chaine, pas de soucis si tu commence par un numérique ou une variable ex:

"............." & _
12 & ", ........" & _
Text1.Text & ", "

Renfield
Admin CodeS-SourceS- MVP Visual Basic

Probleme concatenation requete SQL trop longue

5 réponses

Votre réponse

Discussions similaires