Row change event in datagrid

Experts,

I have a datagrid for data entry with 25 rows and 5 columns.  2 column - textboxes and 2 dropdowns.  I have reached the point of entering the data in database table correctly.  The challenge that i am facing is with duplicate entry.  Thats because, when a user enters data in row 1 and saves it, it inserts correctly in daatbase table, but once again when data is entered in row 2, and is resubmited, the web form tries to save row 1 again with row2 - hence row 1 gives primary ket violation error.  My code loops thru all datagrid cols and identifies which one is not empty, if not empty it inserts.

Is there any event in ASP.net/vb.net that identifies that in the datagrid new data entry has been made in a row or rows and only inserts those new rows in database table ?  If yes please help me with the code.  I need help ASAP.  Giving 500 ready to give more.

Here is my code:

Try

            For counter = 0 To myDataGrid.Items.Count - 1

                dtg = myDataGrid.Items(counter)
                If (dtg.ItemType = ListItemType.Item Or dtg.ItemType = ListItemType.AlternatingItem) Then

                    CellNum = CType(dtg.FindControl("textbox1"), TextBox)

                    Units = CType(dtg.FindControl("Textbox2"), TextBox)
                    TypeDDL = CType(dtg.Cells(3).Controls(1), DropDownList)
                    ReasonDDL = CType(dtg.Cells(4).Controls(1), DropDownList)
                    Label5.Text = Label5.Text & ", " & CellNum.Text

                    If Not CellNum.Text = "" And Not Units.Text = "" Then
                        Dim cell_no As Integer = CType(CellNum.Text, Integer)
                        Dim Units_no As Integer = CType(Units.Text, Integer)

                       
                        'Label5.Text = Label5.Text & ", " & cell_no
                        If cell_no > 0 And Units_no > 0 And Erection <> "" Then

                            Dim treat_amt As Decimal = CType(2.5 * Units_no, Decimal)

                            Typeid = CType(TypeDDL.SelectedValue, Integer)
                            Reasonid = CType(ReasonDDL.SelectedValue, Integer)

                            'this routine inserts the record in the database
                            Dim cnn As New SqlConnection(connectionString)
                            'Try
                            cnn.Open()
                            Dim cmd As New SqlCommand("spCellTreatcopy", cnn)
                            cmd.CommandType = CommandType.StoredProcedure
                            Dim Parameter As SqlParameter

                            Dim prmBlock As New SqlParameter("@Block_no", SqlDbType.Int)
                            prmBlock.Value = sBlock
                            cmd.Parameters.Add(prmBlock)

                            Dim prmSeries As New SqlParameter("@Series_no", SqlDbType.Int)
                            prmSeries.Value = sSeries
                            cmd.Parameters.Add(prmSeries)

                            Dim prmErectionDate As New SqlParameter("@Erection_Date", SqlDbType.DateTime)
                            prmErectionDate.Value = Erection '-->XXX
                            cmd.Parameters.Add(prmErectionDate)

                            Dim prmTreatCode As New SqlParameter("@Treatment_code", SqlDbType.Int)
                            prmTreatCode.Value = Typeid
                            cmd.Parameters.Add(prmTreatCode)

                            Dim prmTreatStart As New SqlParameter("@Treatment_Start", SqlDbType.DateTime)
                            prmTreatStart.Value = sTStamp
                            cmd.Parameters.Add(prmTreatStart)

                            Dim prmCellNo As New SqlParameter("@cell_no", SqlDbType.Int)
                            prmCellNo.Value = cell_no
                            cmd.Parameters.Add(prmCellNo)

                            cmd.ExecuteNonQuery()
                            Label5.Text = "record inserted"
                            cnn.Close()
                        Else
                            Label5.Text = " Block_no = " & sBlock & " and series = " & sSeries & " does not have Erection date"

                        End If
                    End If

                End If
            Next counter

        Catch ex As Exception
            Label5.Text = ex.Message.ToString()
        End Try

Thanks
suran78Asked:
Who is Participating?
 
EBatistaConnect With a Mentor Commented:
hi suran, i was very very busy, well the easiest way i see to accomplish this is by controlling the error number in every attemp to insert data into the database table, if the error number is 2627 then save that datagrid item into a datatable, then at the end of the bucle verify if the datatable have rows, if it does, then save it to a session variable and redirect the user to another page you should create, with a datagrid which will be binded to the datatable containing the conflicting rows, and let the user decide what he/she want to do with each row, analize this code and let me know any comment


Dim dt as new DataTable() 'datatable to store conflicting data
Dim rowNew as DataRow    

            For counter = 0 To myDataGrid.Items.Count - 1

                dtg = myDataGrid.Items(counter)
                If (dtg.ItemType = ListItemType.Item Or dtg.ItemType = ListItemType.AlternatingItem) Then

                    CellNum = CType(dtg.FindControl("textbox1"), TextBox)

                    Units = CType(dtg.FindControl("Textbox2"), TextBox)
                    TypeDDL = CType(dtg.Cells(3).Controls(1), DropDownList)
                    ReasonDDL = CType(dtg.Cells(4).Controls(1), DropDownList)
                    Label5.Text = Label5.Text & ", " & CellNum.Text

                    If Not CellNum.Text = "" And Not Units.Text = "" Then
                        Dim cell_no As Integer = CType(CellNum.Text, Integer)
                        Dim Units_no As Integer = CType(Units.Text, Integer)

                       
                        'Label5.Text = Label5.Text & ", " & cell_no
                        If cell_no > 0 And Units_no > 0 And Erection <> "" Then

                            Dim treat_amt As Decimal = CType(2.5 * Units_no, Decimal)

                            Typeid = CType(TypeDDL.SelectedValue, Integer)
                            Reasonid = CType(ReasonDDL.SelectedValue, Integer)

                            'this routine inserts the record in the database
                            Dim cnn As New SqlConnection(connectionString)
                            Dim cmd As New SqlCommand("spCellTreatcopy", cnn)

                            cmd.CommandType = CommandType.StoredProcedure
                            Dim Parameter As SqlParameter

                            Dim prmBlock As New SqlParameter("@Block_no", SqlDbType.Int)
                            prmBlock.Value = sBlock
                            cmd.Parameters.Add(prmBlock)

                            Dim prmSeries As New SqlParameter("@Series_no", SqlDbType.Int)
                            prmSeries.Value = sSeries
                            cmd.Parameters.Add(prmSeries)

                            Dim prmErectionDate As New SqlParameter("@Erection_Date", SqlDbType.DateTime)
                            prmErectionDate.Value = Erection '-->XXX
                            cmd.Parameters.Add(prmErectionDate)

                            Dim prmTreatCode As New SqlParameter("@Treatment_code", SqlDbType.Int)
                            prmTreatCode.Value = Typeid
                            cmd.Parameters.Add(prmTreatCode)

                            Dim prmTreatStart As New SqlParameter("@Treatment_Start", SqlDbType.DateTime)
                            prmTreatStart.Value = sTStamp
                            cmd.Parameters.Add(prmTreatStart)

                            Dim prmCellNo As New SqlParameter("@cell_no", SqlDbType.Int)
                            prmCellNo.Value = cell_no
                            cmd.Parameters.Add(prmCellNo)
                           
                            'I HAVE MOVED THE TRY BLOCK TO HERE
                            Try
                                cnn.Open()
                                cmd.ExecuteNonQuery()
                                Label5.Text = "record inserted"
                            Catch ex As SqlException
                                If ex.Number=2627 Then ' 2627=primary key violation error code
                                    'save the item in a datatable row
                                    rowNew=dt.NewRow()
                                    rowNew("sBlock")=sBlock
                                    rowNew("sSeries")=sSeries
                                    rowNew("Erection")=Erection
                                    rowNew("Typeid")=Typeid
                                    rowNew("sTStamp")=sTStamp
                                    rowNew("cell_no")=cell_no
                                    dt.Rows.Add(rowNew)                                  
                                End If  
                                Label5.Text = ex.Message.ToString()
                            Finally
                                 cnn.Close()
                            End Try

                        Else
                            Label5.Text = " Block_no = " & sBlock & " and series = " & sSeries & " does not have Erection date"

                        End If
                    End If

                End If
            Next counter

If dt.Rows.Count>0 Then -->duplicated rows occurred
    Session("ExistingData")=dt 'saving the datatable to a session variable
    Server.Transfer("ConfirmAction.aspx")  'transfers the user to the page
End If

then in "ConfirmAction.aspx" page, you can place a datagrid and bind it to the dt datable. In the form_load of such page you have to cast the Session variable "ExistingData" to a datatable, coz Session variable are Object:

'Page_Load
Dim myDT as DataTable=CType(Session("ExistingData"),DataTable)

regards
0
 
puranik_pCommented:
You can keep a hidden column in the datagrid which idenetifies database row from user enetered row.
0
 
suran78Author Commented:
How will I do that?  Could you give me some code ?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
EBatistaCommented:
hi suran, let me ask you something, if you insert one row at a time into the database, why do you need to loop through the datagrid again?
0
 
suran78Author Commented:
Because, when the user enters row 1 for example, and clicks the submit button, the datagrid inserts it and the row 1 is still there.  Now when he enters row 2, the row 1 that is still present will be resubmitted with row 2.  One way I can avoid checking complicated row changing event, is by clearing rows that have been inserted already once the form is submitted.  So that if the user decides to enter a new row, he will see all empty 25 rows, with empty textboxes. which means I can just rebind the datagrid when the form is submitted. But what about this scenario:

User1 inserts 3 record today.  User2 does not know that user1 has entered 3 records and once againg he enters 2 same records but not 3.  I have to give a popup window message with OK button and notifying:

Record 1 and 2 already exists do you want to update them or ignore.  If he selects OK, record 1 and 2 will be updated instead of insert.  But if he selects cancel then record 1 and 2 will not be inserted or updated.

Hopefuly,  I have not confused you.  Let me know if you have questions.
0
 
caball88Commented:
what is the datasource for the datagrid? if you use a datatable you can get rowstate which will tell you if it has been added, deleted, modified, unchanged, or detached. if you want to continue using you function to travese through the data and save to the database. what i would suggest is changing you datasource for the datagrid to a datatable and then using the SQLDataAdapter to update your database. here is an example of how that would be done:

'create a datatable as the datasource for you datagrid
Dim dtYourTable as New DataTable

'add your columns to you datatable
Dim dc as New DataColumn("columnname") add as many columns as you need

dtYourTable.Columns.Add(dc)

'this sets the datatable as your datasource and now the grid is bound to your table
myDataGrid.DataSource = dtYourTable

'setup your SQLDataAdapter
Dim daSQL as New SQlDataAdpater

setup your select, update, delete and insert sql commands
Dim sqlcmd as New SQLCommand
sqlcmd.text = "spSelectStatement"
sqlcmd.commandtype = CommandType.StoredProcedure
sql.parameters.add(New SQlParameter("paramName", sqldbtype.int, 4, "sourceColumn")  'add as many as these as you want
'the "sourceColumn" is important as this is the column from  the datatable that the sqldataadapter is going to be bound to

'add the select statement to the adapter
daSQL.SelectCommand = sqlcmd

'do the same for the insert, update and delete commands and set their respective commands

now when you perform a database update all you have to do is call
daSQL.Update(dtYourTable)

it will automatically recognize which row in your table has been added, deleted, or updated and perform the respective sql command. the setup is a little more tedious up front but the function ality is well worth it. maintenance later on will be easier as you will not have a large loop with logic to decipher through.

if you must stick to the way you have posted then put your logic in sql instead. in your stored proc you can perform a check to see if the row already exists. if it does you can either perform an update or nothing at all. also as a side note do not create your sqlcommand over and over again in the loop as that is unnecessary repetitive statements. instead just setup your command outside the loop without setting the value. in your loop you would then set the value but accessing the parameter by name like this:

cmd.Parameters("paramName").value = yourValue

t-sql:

if exists (select * from YourTable where PrimaryKeyColumn = @Param)  'i am not sure which column is your primary key
Begin
     --perform update statement here or nothing at all
End
Else
Begin
     --perform your insert statement here
End
0
 
suran78Author Commented:
Thsi si how I am binding my datagrid.  Is there any way to clear the entry made in rows when the user clicks the browser's back button.  So that each time the user goes back to the form he will see all empty rows.

Sub BindDataGrid()
        Dim dtTable As New DataTable("datagridcols")
        Dim dv As New DataView

        Dim strSelect As String
        strSelect = "SELECT * FROM datagridcols order by Col_number"
        Dim cnn As New SqlConnection(connectionString)
        Dim da As New SqlDataAdapter(strSelect, cnn)
        Dim ds As New DataSet
        da.Fill(dtTable)

         dv = dtTable.DefaultView
     
        myDataGrid.DataSource = dv
        myDataGrid.DataBind()

    End Sub
0
 
suran78Author Commented:
Ebatista,

Thanks for taking the time to answer this question.  I have not tried this code yet.  I will get back soon to close it.

S

0
 
suran78Author Commented:
Finally I got the time to work on this problem.  Is it possible to do something like this:

If existing record is changed in datagrid  then <--------this is done
   show a javascript popup "do you want to update this cell " &num &"?"<------ please help me with the code for this part
 
   if popup is yes then update else ignore
else if record exist and nothing is changed then ignore

end if

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.