billb59

 

Hey all...

I have a DGV that is contains rows I'd like to be able to insert back into the same table with a few paramater changes. Or I can just change the TSQL since it's button driven and insert the data from there but I'm having some trouble with SQL CE. Not sure which would be easier and thought I'd post and get some opinions... code... etc..

Here's my TSQL:

SELECT     SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM,
                      PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id,
                      create_date
FROM         ProposalDetail 
INSERT INTO temp
(
SQLKey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList,
PropDCost, PropDGMDollar, PropDGM, PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus,
create_timestamp, update_timestamp,update_originator_id, create_date
)
VALUES
(
'1', '3/20/2007 8:13:10 AM', 'SomeUser', 'b1', '4', '0761046', 'APP', 'Miscellaneous Options',
'54.000', '27.00', '27.00', '50.00', '54.00', '54.00', '54.00', '1', 'Active', '3/20/2007 8:13:10 AM', '3/20/2007 8:13:10 AM', '0',
 '3/20/2007 8:13:10 AM'
)
WHERE     (ProposalDetail.PropDNumb = 'b1') AND (ProposalDetail.PropDRevNumb = '1')

The parameters going in will be '1' (Searches for a record #) and then it will be set to '4' (from user input) for the Insert.

I have all the data coming back from a different tsql insert into statement and can get one row inserted but thats it using a For / Next block. Any ideas welcomed.

Thanks a ton for looking.

Bill




Re: Windows Forms Data Controls and Databinding copy datagridview rows question...

billb59

figured it out and oddly enough this is solid and works great...

'SQL STATEMENT......

Dim conGridInsert As New SqlCeCommand

conGridInsert.CommandText = "INSERT INTO ProposalDetail" & _

"(SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, " & _

"PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id, " & _

"create_date)" & _

"SELECT SQLkey, CreateDateTime, Alias, PropDNumb, '" & ComboRevision.Text & "' as PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, " & _

"PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id, " & _

"create_date" & _

" " & _

" FROM ProposalDetail ProposalDetail_1 " & _

" " & _

" WHERE (PropDNumb = '" & txtProposalNum.Text & "') AND (PropDRevNumb = '" & OldRevNumber & "')"

conGridInsert.Connection = CnGridInsert

CnGridInsert.Open()

conGridInsert.ExecuteNonQuery()

CnGridInsert.Close()