[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Row change event in datagrid

Posted on 2005-05-13
10
Medium Priority
?
743 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:suran78
9 Comments
 
LVL 14

Expert Comment

by:puranik_p
ID: 14009322
You can keep a hidden column in the datagrid which idenetifies database row from user enetered row.
0
 

Author Comment

by:suran78
ID: 14009857
How will I do that?  Could you give me some code ?
0
 
LVL 10

Expert Comment

by:EBatista
ID: 14010700
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:suran78
ID: 14011000
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
 
LVL 9

Expert Comment

by:caball88
ID: 14015701
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
 

Author Comment

by:suran78
ID: 14019045
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
 
LVL 10

Accepted Solution

by:
EBatista earned 2000 total points
ID: 14036244
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
 

Author Comment

by:suran78
ID: 14121852
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
 

Author Comment

by:suran78
ID: 14171291
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Screencast - Getting to Know the Pipeline
Suggested Courses

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question