looping through flexgrid while inserting records to a table

• • Reply

  12 years ago

  by depash

  Patric

  , France

  Joined 12 years ago
  I wanted to insert several records into a table while looping throuth the records in a msflexgrid with the code below: 1. Dim q As Integer 2. With Me.MSFlexPOS 3. For q = 1 To (.Rows - 1) 4. 5. Cn.Execute "INSERT INTO POS_TBL(INVNO,INVDATE,BARCODE,DESCRIPTION,CATEGORY ,UNITS,COST,QTY,VATPER,STOTAL,VATAMT,LINETOTAL)VAL UES(" & _ 6. Me.TXTINVNUMBER.Caption & ",#" & _ 7. Me.TXTINVDATE.Caption & "#,'" & _ 8. .TextMatrix(q, 1) & "','" & _ 9. .TextMatrix(q, 2) & "','" & _ 10. .TextMatrix(q, 3) & "','" & _ 11. .TextMatrix(q, 4) & "'," & _ 12. .TextMatrix(q, 5) & "," & _ 13. .TextMatrix(q, 6) & "," & _ 14. .TextMatrix(q, 7) & "," & _ 15. Val(.TextMatrix(q, 8)) & "," & _ 16. Val(.TextMatrix(q, 9)) & "," & _ 17. Val(.TextMatrix(q, 10)) & ")" 18. Next q 19. End With but the program generates a syntax error in the insert into statement. The table has a primaly key which is autonumber. Please sort me out coz am seriously stuck. Consider an elaborate answer please. Thanks in advance
  Report abuse
• • Reply

  12 years ago

  by konikula

  Matej Kasper

  Trutnov, Czech Republic

  Joined 15 years ago
  If there is some double value in string, consider to use correct formatting (dot-comma) and also try to enclose it in apostrophes...
  Report abuse
• • Reply

  12 years ago

  by James Crowley

  James Crowley

  London, United Kingdom

  Joined 20 years ago
  Hi depash, konikula is right - it's likely to be because you're not escaping the strings. It's actually quite a dangerous way to build up your SQL like that - you'd be much better off using a parameterized insert query on your connection object - that way you define your query with values like @paramName, and then pass the actual values in seperately - this will ensure all your inputs are encoded correctly and prevents any SQL injection attacks. Hope that helps
  Report abuse
• • Reply

  12 years ago

  by depash

  Patric

  , France

  Joined 12 years ago
  Thanks for your help. Am not so good in sql and vb, could you please send me an example based on my code above that implements DOUBLE VALUE IN STRING and how to use DOT-COMMA in my code. Have never used parametrized insert query, how should implement one? If possible please edit my code and post it back or send an example for your suggestion. Much gratitude.
  Report abuse
• • Reply

  12 years ago

  by konikula

  Matej Kasper

  Trutnov, Czech Republic

  Joined 15 years ago
  :) what I suggested can be shown at example:
  "Insert Into dbl_pile Values('" & Val(TextMatrix(1,2).ToString & "')"
  
  What James suggested:
  cmd = New YourDbCommand("Insert Into dict(name,value) Values(?,?)", con)
  If cmd.Parameters.Count>0 then cmd.Parameters.Clear
  cmd.Parameters.Add("name", String)
  cmd.Parameters.Add("value", String)
  you should order "Add" sequence same as question marks go by (as you can see there is no other option to determine which ? is which parameter... thats why microsoft does it by additions order in code). Also remember to clear parameters if you reuse some commands... it can cause errors and take a while to find a source.
  Report abuse