INSERT INTO tblTargetSpendPerHead from flxgrid 1

[Stripes1283]

I would like to insert the data in my flexgrid into a table in my database, using a recordset in vb6. My server name is TDFDEV, the database name is : FRSSV. and the table name is : tblTargetSpendPerHead, and the columns in the database is: FltNo, LegNo, EffTargetDate, EndTargetDate, and SpendPerHead.

The data in my flexgrid comes from an excel sheet allready imported to the flexgrid now I just need to save it to database. Please assist.

str = "INSERT INTO tblTargetSpendPerHead (nolock)"
rsTest.Open sSqlCmd, cnDB, adOpenStatic, adLockReadOnly
cnDB.Execute str

While rsTest.EOF = False
rsTest.Open
'now insert records or update records from this recordset
str = "INSERT INTO tblTargetSpendPerHead"
cnDB.Execute (str)
rsTest.MoveNext
End With

thanx.

 

[ArtieChoke]

You can use the TextArray propery of the flexgrid to get the values and either build an insert command or fill a recordset and add it to the table.

What you are doing here doesn't make any sense to me. Why are you reading thru a recordset, then using a commmand to do an insert? Do you know how to use ADO to insert data into a table?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Stripes1283]

no I am pretty new at this and still struggling with all the possibilities with what to do. If you could please give me a lead of what to use, and how to do it!
Thanx

 

[Andrzejek]

Your basic INSERT statement is:

INSERT INTO TableName (Field1, Field2, Field3, ...) VALUES (Val1, val2, val3, ...)

I guess you have first part of it like this:

INSERT INTO tblTargetSpendPerHead (FltNo, LegNo, EffTargetDate, EndTargetDate, SpendPerHead) VALUES ()

Now you have to figure out how to get values from your flexgrid, which you already should now how to do it, I help you recenly with it.

You will end up with something like:

With grdMyGrid
   For intCounter = 0 to .Rows - 1
      .Row = intCounter
      strInsert = "INSERT INTO tblTargetSpendPerHead (FltNo, LegNo, EffTargetDate, EndTargetDate, SpendPerHead) VALUES (" & .TextMatrix(intCounter, 0) & ", "  & .TextMatrix(intCounter, 1) & ", TO_DATE('"  & .TextMatrix(intCounter, 2) & "',  'MM/DD/YYYY'), TO_DATE('"  & .TextMatrix(intCounter, 3) & " 'MM/DD/YYYY'), " & .TextMatrix(intCounter, 4) & ")"

   cnDB.Execute strInsert
   Next intCounter
End With

That's with FltNo, LegNo as numbers, and EffTargetDate, EndTargetDate as Dates


Have fun.

---- Andy