Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

How to Save from datagridview to Remote- sqlserver 2005 table

MAHEYMAHEY Member Posts: 58
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






Comments

  • seancampbellseancampbell Pennsylvania, USAMember Posts: 684 ✭✭✭
    Hey Mahey,

    Your statement:
    ......But I want to insert only EMP_ID Not exist.....

    My answer:
    Well, it sounds like you want to only call the DB Insert code when a CONDITION is met, knowing that you probably want to use an IF Statement inside of your For Loop

    [code]
    If MyDataGrid1.Rows(Rjim).Cells(0).Value = "" Then

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

    End If
    [/code]

    Your Statement:
    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.

    My Answer:
    That's weird. The Primary key has to be unique, and you definitely want to set the Primary key of a table BEFORE you start putting data in it. My guess is that you have a lot of data in that table already, and when you select Emp_ID as a primary key it is trying to index the table on that value. A Primary key cannot be Blank or Duplicated.

    I suggest remaking the table with a new name, setting up all of the Fields(columns) correctly, and correctly selecting Emp_ID as primary key, then dumping the data from the old table to the new one. Once that is done, you can delete the old table and rename the new one to whatever you prefer.

    Hope this helps,
    Sean Campbell - firesickle.com
Sign In or Register to comment.