looping through flexgrid while inserting records to a table

insert , sql , retrieve , msflexgrid Saint-Denis, France
  • 9 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
  • 9 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...
  • 9 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
  • 9 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.
  • 9 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.

Post a reply

Enter your message below

Sign in or Join us (it's free).


Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“You can stand on the shoulders of giants OR a big enough pile of dwarfs, works either way.”