How to Save from datagridview to Remote- sqlserver 2005 table 1

[Mahey]

Hi,

With My datagridview

Allowuser ToAddrows=true
Allowuser ToDeleterows=true

dim dsf_52 As New DataSet, dtbf_52 As New DataTable,daf52 As SqlDataAdapter

Dim Comd_52 As String = "select sec_erb_id.emp_id,emp_master.fir_name,sec_erb_id.sec_id_no from sec_erb_id,emp_master where emp_master.emp_id=sec_erb_id.emp_id"

con.ConnectionString = ConStr
con.Open()
daf52 = New SqlDataAdapter(Comd_52, con)
daf52.Fill(dsf_52)
dtbf_52 = dsf_52.Tables(0)
con.Close()
MyDataGrid1.DataSource = dsf_52.Tables(0).DefaultView

Now the user is added & deleted the rows in datagridview. Is there any easier way to save to Remote sql server 2005 ?
------------------------------------------------------------
Now Iam using

con.Open()
For Rjim As Integer = 0 To MyDataGrid1.RowCount - 1

Dim line_1 As String = "update sec_erb_id set emp_id='" & MyDataGrid1.Rows(Rjim).Cells(0).Value & "', sec_id_no= '" & MyDataGrid1.Rows(Rjim).Cells(2).Value & "',sec_id_expiry='" & MyDataGrid1.Rows(Rjim).Cells(3).Value & "',sec_project='" & MyDataGrid1.Rows(Rjim).Cells(5).Value & "',sec_division='" & MyDataGrid1.Rows(Rjim).Cells(6).Value & "',sec_remarks='" & MyDataGrid1.Rows(Rjim).Cells(7).Value & "' where sec_erb_id.emp_id='" & MyDataGrid1.Rows(Rjim).Cells(0).Value & "'"

Dim line_2 As String = "insert into sec_erb_id (emp_id,sec_id_no,[sec_id_expiry],sec_project,sec_division,sec_remarks) values ('" & MyDataGrid1.Rows(Rjim).Cells(0).Value & "','" & MyDataGrid1.Rows(Rjim).Cells(2).Value & "','" & MyDataGrid1.Rows(Rjim).Cells(3).Value & "','" & MyDataGrid1.Rows(Rjim).Cells(5).Value & "','" & MyDataGrid1.Rows(Rjim).Cells(6).Value & "','" & MyDataGrid1.Rows(Rjim).Cells(7).Value & "' )"

Dim sv_cmd As New SqlCommand(line_1, con)
sv_cmd.ExecuteNonQuery()

Dim se_cmd As New SqlCommand(line_2, con)
se_cmd.ExecuteNonQuery()

Next
con.Close()
---------------------------------------
Here Update is working fine. But Insert is just inserting all records.
But I want to insert only EMP_ID Not exist.

And "Emp_id" is not a primary key field. Also, If I set as Emp_Id [Varchar 10] as primary key then system simply hang-up.

Any Better ideas for me?

THANKS AGAIN

 

[jebenson]

You can use the dataadapter's Update method to do this in one pass.

Dim line_1 As String = "update sec_erb_id set sec_id_no=@sec_id_no, sec_id_expiry=@sec_id_expiry, sec_project=@sec_project, sec_division=@sec_division, sec_remarks=@sec_remarks where sec_erb_id.emp_id=@emp_id"

Dim line_2 As String = "insert into sec_erb_id (emp_id, sec_id_no, sec_id_expiry, sec_project, sec_division, sec_remarks) values (@emp_id, @sec_id_no, @sec_id_expiry, @sec_project, @sec_division, @sec_remarks)"

Dim line_3 As String = "Delete from sec_erb_id where emp_id=@emp_id"

daf52.InsertCommand = New SqlCommand(line_2)

daf52.UpdateCommand = New SqlCommand(line_1)

daf52.DeleteCommand = New SqlCommand(line_3)

[green]'note: change the SqlDbType and the Size parameter (the third one in the .Add method) to appropriate values
'The last parameter in the .Add method is the field in the datatable that is used for the parameter's value[/green]
With daf52.InsertCommand.Parameters
    .Add("@emp_id", SqlDbType.Int, 4, "emp_id")
    .Add("@sec_id_no", SqlDbType.Int, 4, "sec_id_no")
    .Add("@sec_id_expiry", SqlDbType.Int, 4, "sec_id_expiry")
    .Add("@sec_project", SqlDbType.Int, 4, "sec_project")
    .Add("@sec_division", SqlDbType.Int, 4, "sec_division")
    .Add("@sec_remarks", SqlDbType.Int, 4, "sec_remarks")
End With

With daf52.UpdateCommand.Parameters
    .Add("@sec_id_no", SqlDbType.Int, 4, "sec_id_no")
    .Add("@sec_id_expiry", SqlDbType.Int, 4, "sec_id_expiry")
    .Add("@sec_project", SqlDbType.Int, 4, "sec_project")
    .Add("@sec_division", SqlDbType.Int, 4, "sec_division")
    .Add("@sec_remarks", SqlDbType.Int, 4, "sec_remarks")
    .Add("@emp_id", SqlDbType.Int, 4, "emp_id")
End With

With daf52.DeleteCommand.Parameters
    .Add("@emp_id", SqlDbType.Int, 4, "emp_id")
End With

Dim Trans As SqlTransaction

Trans = con.BeginTransaction

daf52.InsertCommand.Transaction = Trans
daf52.UpdateCommand.Transaction = Trans
daf52.DeleteCommand.Transaction = Trans

Try
    daf52.Update(dsf_52.Tables(0))
    Trans.Commit()
Catch ex As Exception
    If Not Trans Is Nothing Then
        Trans.Rollback()
    End If
    MsgBox(ex.Message)
End Try

Post back if you have any questions.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Mahey]

Thank You Jebenson