Insert/update/delete Transaction

Hi,

I have an unbound DataGridView and I have load it with a set of records from a Data base.

I modify existing rows, delete rows and add new rows to DataGridView control. I have to send a new modified dataset back to the data base.

Please any suggestions how to solve the problem?

Thanks in advance

George

Hi George,

I think you'll have more success posting your question on the Visual Studio forums - this is the T-SQL forum which is primarily used for back-end SQL questions, rather than user interface coding problems like DataGridViews.

Hope that helps :)

Menthos
|||Thanks :)sql

Insert transaction batch size

I have insert statements that inserts 1.7 million rows from one server to
another. Half way through the insert, the connection gets lost. I am thinking
that because of the batch size the connection gets cut-off. How can I
implement a commit in T-SQL after say every 500 rows inserted ?
Thanks.
DXC,
You will have to manage this yourself in a stored procedure or sql batch.
Have you considered DTS - you can set the commit batch size there.
-- Bill
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:B8409B51-1A56-4CB5-946B-234102EDFA27@.microsoft.com...
>I have insert statements that inserts 1.7 million rows from one server to
> another. Half way through the insert, the connection gets lost. I am
> thinking
> that because of the batch size the connection gets cut-off. How can I
> implement a commit in T-SQL after say every 500 rows inserted ?
> Thanks.

Insert transaction batch size

I have insert statements that inserts 1.7 million rows from one server to
another. Half way through the insert, the connection gets lost. I am thinkin
g
that because of the batch size the connection gets cut-off. How can I
implement a commit in T-SQL after say every 500 rows inserted ?
Thanks.DXC,
You will have to manage this yourself in a stored procedure or sql batch.
Have you considered DTS - you can set the commit batch size there.
-- Bill
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:B8409B51-1A56-4CB5-946B-234102EDFA27@.microsoft.com...
>I have insert statements that inserts 1.7 million rows from one server to
> another. Half way through the insert, the connection gets lost. I am
> thinking
> that because of the batch size the connection gets cut-off. How can I
> implement a commit in T-SQL after say every 500 rows inserted ?
> Thanks.

insert stored procedure with error check and transaction function

Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?

thanks

My contract table DDL:
************************************************** ***

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

My insert stored procedure is:
************************************************** *****

create proc sp_insert_new_contract
( @.contractDate [smalldatetime],
@.tuition [money],
@.studentId [char](4),
@.contactId [int])
as

if not exists (select studentid
from student
where studentid = @.studentId)
begin
print 'studentid is not a valid id'
return -1
end

if not exists (select contactId
from contact
where contactId = @.contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction

insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@.contractDate,
@.tuition,
@.studentId,
@.contactId)

/*Error Check */
if @.@.error !=0 or @.@.rowcount !=1
begin
rollback transaction
print Insert is failed
return -1
end
print New contract has been added

commit transaction
return 0
goI recreated your environment including tables, DRI, and stored procedure in question. This is how I call it which successfully executes:

exec sp_insert_new_contract
@.contractDate = '01/01/2004',
@.tuition = 3000,
@.studentId = 'ABCD',
@.contactId = 1