Fill FlexGrid with data, add data and update records 1

[rtshort]

I have a MSHFlexGrid that I fill with data from a database, kind of like an invoice. I need to add data to the FlexGrid and add the new lines to the database, while updating the existing records. How would I do this.

I write the FlexGrid data to the database as follows:

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Driver=SQL Server;uid=;pwd=;Server=RobOffice;DAtabase=Test"
cn.ConnectionString = 30
cn.Open
sql = "Select * from DAinfo Where claimNo = '" & txtDANo.Text & "'"
rs.Open sql, cn, adOpenKeyset, adLockOptimistic

With flexDA
For xRow = 2 To .Rows - 1
Item = "'" & txtDANo.Text & "'"
For xCol = 0 To .Cols - 1
Item = Item & IIf(Len(Item) > 0, ", ", "&quot & "'" & .TextMatrix(xRow, xCol) & "'"
Next xCol
sql = "Insert into DAInfo (ClaimNo, Operation, PartType, Part#, PartName, Quantity, Hours, Materials, PartPrice, ExtPrice, Vendor#, Group#, Req#, Ref#) Values (" & Item & &quot"
MsgBox (sql)
cn.Execute sql
Next xRow
End With

I'm using the same code to write the data back to the database but it adds all of the information again, including what is already there. (selected to fill the grid)

How would I write the new information to the database while just updating the existing rows if the information changes. (the information on the existing rows is subject to change.)

TIA
Rob

Rob
Just my $.02.

 

[WHM]

Rob,
I am a bit confused with what you are trying to achieve. First off is the MSFlexGrid Bound or unbound? It would appear from your code that it is unbound, and that is a good thing. I must assume that you are altering the data in some way, or adding records to the underlying table. All you really need to do is check the recordset that you have opened before you define your sql statement to see if the Ref# already exists (I am assuming that the Ref# is the Primary Key Field). If the Record already exists then you do not need to INSERT it, you need to UPDATE it. I would also reccomend that you add a hidden field to your FlexGrid called "Changed" and whose initial values are Set to FALSE. When you handle your data you can then Set the hidden field to "TRUE", and when you Add a new record you can Set the value to "NEW". Then you simply use a Select Case to determine which type of action needs to occur (INSERT, UPDATE, or No Action). If the application is for a single user then you can handle all of your updates and additions in a batch mode like it appears that you are doing. If you think that you might have multiple users then you should probably handle UpDates on a field by Field Basis, and check to see if the value of the field has been altered by another user since you populated the grid. If you use the Recordset Object to do your updates and additions, the system will handle Record Locks, and you can avoid dealing with record locking.

Good Luck!

Hunter

 

[rtshort]

Hunter,

Thanks for the help. I know it's a little late, but I wasn't on the old computer all weekend. Kind of took off.

The Ref# is not a Primary Key field it is just a reference no. for the associated photo. The primary keys are Claim No., and UserID. The only person with the capability to open the record set is the one with the same UserID. It is a program to write estimates with and after the user is finished, all of the data entered in the FlexGrid is inserted into the database. If a suppliment to the estimate is needed, all of the prior information is loaded back into the flexgrid and the supplimental items then added.

I'll give what you said a try I think it should work.

Again, thanks

Rob Rob
Just my $.02.