datagridview

[prancode]

Hi, i'm working on a grid, where the data can be saved, modified and deleted from the grid and effects in the database. I have a problem,i.e after I enter data in one row and goto the next row, the next row automatically gets filled by the same data as in the previous row. Please somebody help me, I have posted the patch below.. Thanks in advance.

Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

    If (con.State = ConnectionState.Open) Then
        con.Close()

    End If
    con.Open()


    Dim v_SelectRow As Integer
    v_SelectRow = Me.DataGridView1.CurrentRow.Index
    Dim CMD As New SqlCommand("fetch_dtls", con)
    CMD.CommandType = CommandType.StoredProcedure
    Dim ad As New SqlDataAdapter(CMD)
    Dim value1 As String = DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString()
    'CMD.Parameters.Add("@emp_no", value1)
    CMD.Parameters.Add("@emp_no", SqlDbType.VarChar).Value = value1
    CMD.ExecuteScalar()

    Dim dt As New DataTable
    CMD.Connection = con
    If (con.State = ConnectionState.Open) Then
        con.Close()
    End If

    Me.Refresh()

    con.Open()
    ad.Fill(dt)
    If dt.Rows.Count > 0 Then

        txtComboBox1.Text = dt.Rows(0)("Loan_code")
        txtdisb_date.Text = dt.Rows(0)("Disb_date")
        txtsanc_date.Text = dt.Rows(0)("Sanc_date")
        txtno_of_inst.Text = dt.Rows(0)("No_of_inst")
        txtsanc_amt.Text = dt.Rows(0)("Sanc_amt")
    End If

End Sub



Private Sub DataGridView1_CellLeave(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellLeave

    If con.State = ConnectionState.Open Then
        con.Close()
    End If
    con.Open()

    'Dim v_SelectRow As Integer
    'v_SelectRow = Me.DataGridView1.CurrentRow.Index
    Dim value1 As String = DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString()

    Dim CMD As New SqlCommand("select H01_emp_num from H01_identfcatn", con)
    Dim ad As New SqlDataAdapter(CMD)
    CMD.Parameters.Add("@emp_no", SqlDbType.VarChar).Value = value1
    CMD.Parameters("@emp_no").Value = value1


    Dim dt As New DataTable
    CMD.Connection = con
    value1 = Me.DataGridView1.SelectedCells.Item(0).Value.ToString & ""
    con.Close()

End Sub

Private Sub DataGridView1_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEnter

    If con.State = ConnectionState.Open Then
        con.Close()
    End If
    con.Open()

    'Dim v_SelectRow As Integer
    'v_SelectRow = Me.DataGridView1.CurrentRow.Index
    Dim CMD As New SqlCommand("val_name", con)
    CMD.CommandType = CommandType.StoredProcedure
    Dim ad As New SqlDataAdapter(CMD)

    Dim value1 As String
    Me.Refresh()

    value1 = DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString()
    CMD.Parameters.Add("@emp_no", SqlDbType.VarChar, 50, value1)
    CMD.Parameters("@emp_no").Value = value1
    ad.Fill(dt)
    CMD.Connection = con

    For I As Integer = 0 To dt.Rows.Count - 1

        If dt.Rows.Count > 0 Then
            'DataGridView1.Rows(I).Cells(I).Value = dt.Rows(0)("H01_First_Name")
            DataGridView1.CurrentRow.Cells(1).Value = dt.Rows(0)("H01_First_Name")
            DataGridView1.CurrentRow.Cells(2).Value = txtsanc_amt.Text
            Me.Refresh()

        End If
    Next
    con.Close()



End Sub

[M.Waqas Aslam]

well , there is not that much time to read all your code , i have a better suggestion for you , you can just edit all the records in your grid , after that save all your records , it is simple fast and it will reduce your work ,

Regards

[prancode]

Can somebody please help me with grid??... If can't be solved in the program i wrote, then please suggest alternative codes...

[M.Waqas Aslam]

well you can do like this , by default the grid is editable you can edit its cell by double clicking on it , after adding data or inserting your information in the grid. just use loop and insert your data into your db , here is an example code.

' i create this sub because it will easy to use and it will also easy to chane , 
'i am passing two variables name and age , these variables are used to get the values form the controls.
sub myinsert(byval name as string , byval age as string)

dim con as new sqlconnection("your connection string")
con.open()
dim cmd as new sqlcommand
cmd.connection = con
cmd.commandtext = "insert into table1 (name,age) values (@name,@age)"
cmd.parameters.addwithvalue("@name",name)
cmd.parameters.addwithvalue("@age",age)
cmd.executenonquery()
con.close()

End Sub
' now after creating this sub you can call it on the save button like this.
'i am using loop to insert all the rows into the database , 

for i = 0 to datagridview1.rows.count -1 
myinsert(datagridview1.item(0,i).value.tostring,datagridview1.item(1,i).value.tostring)
Next

this code will insert all the records into the database , you can also use update command to update all your records , hope this time you can better understand .

Regards

[prancode]

K, the code you have mentioned does it, but then what if i need to modify any row and then save??... it'll be creating another row then...

[M.Waqas Aslam]

well you just need to add a hidden column , when you insert the new row insert -1 to that column , and when you are saving all data in grid , just check that is that column value is -1 , if yes then call insert function and otherwise just call update function ,

for i = 0 to datagridview1.rows.count-1
if datagridview1.item(2,i).value = -1 then
'here you can call insert code.
else
'here you can call update code.
end if

next

regards

[prancode]

K, I had to mention this before... I actually want no operations from the front end, I want to update,add and delete via a storedprocedure.....so all I will able to do in the front end is exec the procedure... In that case, what will i have to do??

[prancode]

Can somebody please, help me with this...??

[prancode]

Hello...somebody there who could help me with this problem??... I'll summarize it, I have a grid, where in I enter values and it saves in the db, but when I move to the new row, the data from the previous row gets copied...

[prancode]

I solved my problem... what I did is, replaced the following
Private Sub DataGridView1_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEnter

by the below code....

  Private Sub DataGridView1_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        con.Open()
        Me.Refresh()
        dt.Rows.Clear()

        DataGridView1.Rows(DataGridView1.Rows.Count - 1).Selected = True


        Dim CMD As New SqlCommand("val_name", con)
        CMD.CommandType = CommandType.StoredProcedure
        Dim ad As New SqlDataAdapter(CMD)
        Dim value1 As String
        'value2 = DataGridView1.Rows(i).Cells(1).Value

        'Me.Refresh()
        'value1 = DataGridView1.NewRowIndex

        value1 = DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString()
        value2 = DataGridView1.Rows(e.RowIndex).Cells(1).Value.ToString()
        CMD.Parameters.Add("@emp_no", SqlDbType.VarChar, 50, value1)
        CMD.Parameters("@emp_no").Value = value1
        ad.Fill(dt)
        CMD.Connection = con

        For I As Integer = 0 To dt.Rows.Count - 1

            If dt.Rows.Count > 0 Then
                'DataGridView1.Rows(I).Cells(I).Value = dt.Rows(0)("H01_First_Name")
                DataGridView1.CurrentRow.Cells(1).Value = dt.Rows(0)("H01_First_Name")
                DataGridView1.CurrentRow.Cells(2).Value = txtsanc_amt.Text
                Me.Refresh()

            End If
        Next

        DataGridView1.ClearSelection()


        con.Close()
    End Sub