data is not updating in database asp.net

Question

Did not find any error but data is not updating in database and displaying error on first load is

There is no row at position 0

and data displaying in gridview below.

But when load from menu data is displaying in textbox's.

Update Button Code

Protected Sub taxsubmit_Click(sender As Object, e As ImageClickEventArgs) Handles taxsubmit.Click
    Using con As SqlConnection = New SqlConnection(strConnString)
        Using cmd As SqlCommand = New SqlCommand
            cmd.Connection = con
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "UPDATE [Deduction] SET [IncomeTax] = @IncomeTax, [SalesTax] = @SalesTax, [ServiceTax] = @ServiceTax, [LabourCess] = @LabourCess, [SocityTax] = @SocityTax, [ESIC] = @ESIC, [EPF] = @EPF, [Security] = @Security, [FinYear] = @FinYear, [Condition1] = @Condition1, [Condition2] = @Condition2, [Condition3] = @Condition3, [CompID] = " + Session("Companydetl") + " WHERE [DedID] = " + dedid + ""
            cmd.Parameters.AddWithValue("@IncomeTax", Income_Tax.Text)
            cmd.Parameters.AddWithValue("@SalesTax", Sales.Text)
            cmd.Parameters.AddWithValue("@ServiceTax", Service.Text)
            cmd.Parameters.AddWithValue("@LabourCess", Labour_Cess.Text)
            cmd.Parameters.AddWithValue("@SocityTax", Society.Text)
            cmd.Parameters.AddWithValue("@ESIC", ESIC.Text)
            cmd.Parameters.AddWithValue("@EPF", EPF.Text)
            cmd.Parameters.AddWithValue("@Security", Security.Text)
            cmd.Parameters.AddWithValue("@FinYear", Fin_Year.SelectedValue)
            cmd.Parameters.AddWithValue("@Condition1", Cond1.Text)
            cmd.Parameters.AddWithValue("@Condition2", Cond2.Text)
            cmd.Parameters.AddWithValue("@Condition3", Cond3.Text)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using
    End Using
End Sub

Data Display Code

Using con As SqlConnection = New SqlConnection(strConnString)
            Using cmd As SqlCommand = New SqlCommand
                cmd.Connection = con
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "Select DedID, IncomeTax, SalesTax, ServiceTax, LabourCess, SocityTax, ESIC, EPF, 
                                    Security, FinYear, Condition1, Condition2, Condition3 from Deduction where CompID = '" + Session("Companydetl") + "'"
                Dim dt As New DataTable()
                con.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                Try
                    dt.Load(reader)
                    Income_Tax.Text = dt.Rows(0).Item("IncomeTax").ToString.Trim()
                    Labour_Cess.Text = dt.Rows(0).Item("LabourCess").ToString.Trim()
                    ESIC.Text = dt.Rows(0).Item("ESIC").ToString.Trim()
                    EPF.Text = dt.Rows(0).Item("EPF").ToString.Trim()
                    Society.Text = dt.Rows(0).Item("SocityTax").ToString.Trim()
                    Service.Text = dt.Rows(0).Item("ServiceTax").ToString.Trim()
                    Sales.Text = dt.Rows(0).Item("SalesTax").ToString.Trim()
                    Security.Text = dt.Rows(0).Item("Security").ToString.Trim()
                    Fin_Year.SelectedValue = dt.Rows(0).Item("FinYear").ToString.Trim()
                    Cond1.Text = dt.Rows(0).Item("Condition1").ToString.Trim()
                    Cond2.Text = dt.Rows(0).Item("Condition2").ToString.Trim()
                    Cond3.Text = dt.Rows(0).Item("Condition3").ToString.Trim()
                    dedid = dt.Rows(0).Item("DedID").ToString.Trim()
                    con.Close()
                Catch ex As Exception
                    MsgBox(ex.Message, vbOKOnly, "Error")
                End Try
            End Using
        End Using

Image on direct load of page after login.

Image After loading page from the above menu.

as am new to this please help.

In your Click event handler, where are you setting the value of dedid? That's the first place I'd look; if your query isn't returning any results, there's no record to update. Also, your SQL statement is vulnerable to an injection attack. You'll want to use query parameters for the entire statement, not just most of it. — InteXX
– InteXX, Commented Jan 29, 2017 at 9:27
I set the value on command for updating the data. And please help me for prevent from SQL injection. Suggest the way for that. Please — Atul Sharma
– Atul Sharma, Commented Jan 29, 2017 at 9:41
SQL Injection: You're almost there. Add parameters for the last two columns in your statement, [CompID] and [DedID]. Concatenating the string as you are now creates the vulnerability. Search: Prevent SQL Injection — InteXX
– InteXX, Commented Jan 29, 2017 at 13:08

InteXX · Accepted Answer · 2017-01-29 13:17:44Z

1

It appears you've created dedid as a field or a property in the class. Your problem is likely occurring because values for those don't persist between page loads.

Try storing it in a Session variable as you are with CompID.

answered Jan 29, 2017 at 13:17

InteXX

6,5578 gold badges56 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

InteXX Over a year ago

Set a break in your Click event handler, step through the code and make sure that dedid contains the value that you expect it to. Further, while debugging, build the SQL statement in a text editor and run it directly against the database (using SSMS or a similar tool). See whether your .NET code is sending the correct SQL statement to the database.

Atul Sharma Over a year ago

i tried it in SQL Query and query executed successfully with data and even on the GRIDVIEW on the page; Data is displaying.

InteXX Over a year ago

@AtulSharma — Did you manually construct your SQL update statement while in debugger break mode in your Click event handler? Can you verify that there is indeed a row in your database containing the value in the [DedID] column that equals the value in your dedid variable?

InteXX Over a year ago

@AtulSharma — Here's your update statement:

UPDATE [Deduction] SET [IncomeTax] = @IncomeTax, [SalesTax] = @SalesTax, [ServiceTax] = @ServiceTax, [LabourCess] = @LabourCess, [SocityTax] = @SocityTax, [ESIC] = @ESIC, [EPF] = @EPF, [Security] = @Security, [FinYear] = @FinYear, [Condition1] = @Condition1, [Condition2] = @Condition2, [Condition3] = @Condition3, [CompID] = @Companydetl WHERE [DedID] = @dedid

. Using your text editor, replace @IncomeTax with the value in Income_Tax.Text, @SalesTax with that of Sales.Text, and so on. Run that against your database and see what you get.

InteXX Over a year ago

@AtulSharma — Better yet, scrap the manual SQL and switch to Entity Framework. EF builds all your parameterized queries for you, enabling you to concentrate on solving the business problem at hand instead of mucking about with the plumbing as you are now.

|

InteXX · Accepted Answer · 2017-01-30 10:22:02Z

If you switch to Entity Framework, your code will look something like this:

Using oDb As New MyDbContext(ConnectionString)
  Dim oDeduction as Deduction

  oDeduction = oDb.Deductions.Where(Function(D) D.DedID = dedid).Single
  oDeduction.IncomeTax = Income_Tax.Text
  oDeduction.SalesTax = Sales.Text
  oDeduction.ServiceTax = Service.Text
  oDeduction.LabourCess = Labour_Cess.Text
  oDeduction.SocityTax = Society.Text
  oDeduction.ESIC = ESIC.Text
  oDeduction.EPF = EPF.Text
  oDeduction.Security = Security.Text
  oDeduction.FinYear = Fin_Year.SelectedValue
  oDeduction.Condition1 = Cond1.Text
  oDeduction.Condition2 = Cond2.Text
  oDeduction.Condition3 = Cond3.Text

  oDb.SaveChanges()
End Using

Note that I just hammered this together by memory in TextPad — it's untested and syntax may not be exactly correct. It's just a sample of the concept.

Community · Accepted Answer · 2017-05-23 12:09:03Z

Thank you all for your support but I found my point

If Session("login") = Nothing Then
        FormsAuthentication.SignOut()
        Session.Abandon()
        Session.RemoveAll()
        FormsAuthentication.RedirectToLoginPage()
    ElseIf Not IsPostBack Then
        Using con As SqlConnection = New SqlConnection(strConnString)
            Using cmd As SqlCommand = New SqlCommand
                cmd.Connection = con
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "Select * from Deduction where CompID = '" + Session("Companydetl") + "'"
                Dim dt As New DataTable()
                con.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                Try
                    dt.Load(reader)
                    Income_Tax.Text = dt.Rows(0).Item("IncomeTax").ToString.Trim()
                    Labour_Cess.Text = dt.Rows(0).Item("LabourCess").ToString.Trim()
                    ESIC.Text = dt.Rows(0).Item("ESIC").ToString.Trim()
                    EPF.Text = dt.Rows(0).Item("EPF").ToString.Trim()
                    Society.Text = dt.Rows(0).Item("SocityTax").ToString.Trim()
                    Service.Text = dt.Rows(0).Item("ServiceTax").ToString.Trim()
                    Sales.Text = dt.Rows(0).Item("SalesTax").ToString.Trim()
                    Security.Text = dt.Rows(0).Item("Security").ToString.Trim()
                    Fin_Year.SelectedValue = dt.Rows(0).Item("FinYear").ToString.Trim()
                    Cond1.Text = dt.Rows(0).Item("Condition1").ToString.Trim()
                    Cond2.Text = dt.Rows(0).Item("Condition2").ToString.Trim()
                    Cond3.Text = dt.Rows(0).Item("Condition3").ToString.Trim()
                    'Session("deductionid") = dt.Rows(0).Item("DedID").ToString.Trim()
                    con.Close()
                Catch ex As Exception
                    MsgBox(ex.Message, vbOKOnly, "Error")
                End Try
            End Using
        End Using
    End If

when i add "Not IsPostBack" then all the data is updating and the working thanks all for the support found at Post Back Error Same

Thank You All

Update Button

Using con As SqlConnection = New SqlConnection(strConnString)
        con.Open()
        Using cmd As SqlCommand = New SqlCommand
            cmd.Connection = con
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "UPDATE Deduction SET IncomeTax=" + Income_Tax.Text + ", SalesTax=" + Sales.Text + ", ServiceTax=" + Service.Text + ", LabourCess=" + Labour_Cess.Text + ", 
              SocityTax=" + Society.Text + ", ESIC=" + ESIC.Text + ", EPF=" + EPF.Text + ", Security=" + Security.Text + ", FinYear=" + Fin_Year.SelectedValue + ", 
              Condition1='" + Cond1.Text + "', Condition2='" + Cond2.Text + "', Condition3='" + Cond3.Text + "' WHERE CompID = " + Session("Companydetl") + ""
            Try
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                MsgBox(ex.Message, vbOKOnly, "Error")
            End Try
        End Using
        con.Close()
    End Using

Collectives™ on Stack Overflow

data is not updating in database asp.net

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related