update query not working when i attach the content of text box using data reader on page load

Question

Code works fine when i remove the page load content. this is a form which will allow user to edit the data already present in database. i just want to let a user edit a form which he have already submitted. This is the code:

Dim con As New SqlConnection("Data Source=ENCODER-PC\SQLEXPRESS;Integrated Security=True")
Dim cmd, com As New SqlCommand
Dim dr As SqlDataReader
Dim n, d, a As Integer
Dim returnValue As Object
Dim str As String


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


    str = "select * from School where RollNo=11"
    com = New SqlCommand(str, con)
    dr = com.ExecuteReader()
    con.Open()        
    If (dr.Read()) Then

        Enroll.Text = dr("RollNo").ToString()
        Name.Text = dr("Name").ToString()
        Class.Text = dr("Class").ToString()
    End If
    con.Close()
    dr.Close()

End Sub



Protected Sub Next_Click(sender As Object, e As EventArgs) Handles [Next].Click
    Try
        cmd.CommandText = "Update School SET RollNo='" & Enroll.Text & "', Name='" & Name.Text & "', Class='" & Class.Text & "'  where RollNo=11 "


        cmd.Connection = con


        con.Open()

        MsgBox("Connection is Open ! ")
        n = cmd.ExecuteNonQuery
        If n > 0 Then
            MsgBox("data inserted successfully")
        Else
            MsgBox("data insertion failed")
        End If
    Catch ex As Exception
        MsgBox(ex.ToString())
    Finally
        con.Close()
    End Try
End Sub

AND THIS CODE ALSO WORKS FINE. IT DOESN'T GIVE ANY ERROR – s_anzer Mar 17 '15 at 13:35 — s_anzer, Mar 17 '15 at 13:35

Steve · Accepted Answer · 2017-05-17T07:01:47.490

1

You have tagged your question with MySql but in code you use the classes for Sql Server and a connection string specific to Sql Server.
So you should clarify this point. However, in the meantime I wish to give an answer to some errors in your Page_Load event handler:

First you need to check if the call to Page_Load is a postback from other controls and avoid to reload the data from the database in that case. See ASP.NET Page Life Cycle
Second, open the connection before executing the reader

Dim constring  = "Data Source=ENCODER-PC\SQLEXPRESS;Integrated Security=True"

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim str = "select * from School where RollNo=11"

    If Not IsPostBack Then
        Using con = new SqlConnection(conString)
        Using cmd = new SqlCommand(str, con)
            con.Open()
            Using dr = com.ExecuteReader()
                If (dr.Read()) Then
                    Enroll.Text = dr("RollNo").ToString()
                    Name.Text = dr("Name").ToString()
                    Class.Text = dr("Class").ToString()
                End If
            End Using
        End Using
        End Using
    End If

End Sub

As you can see there are other improvements: No more global variables for connection, command and reader and Using Statement around the disposable objects.

If this code is really intended to run against a MySql database then you need to use the appropriate classes like MySqlConnection, MySqlCommand, MySqlDataReader etc.. and fix the connectionstring

edited May 17 '17 at 07:01

answered Mar 17 '15 at 13:28

Steve

213,761
22
232
286

thanks...what i want to ask is why the database is not being UPDATED with that sql query. i tagged sql because the sql query is not working and i dont know why.. – s_anzer Mar 17 '15 at 13:34
1

@AbuzerBCA Steve mentioned it in his answer that you have to check `if not IsPostBack then` otherelse, everytime (even if you click *next*) this code will be executed - and therefore any inputs made, will be reseted! – Pilgerstorfer Franz Mar 17 '15 at 13:55
@AbuzerBCA the ASP.NET Life Cycle explains well what happens when you click a button. The Page_Load event is recalled BEFORE the click event of your button. If you don't protect the code in Page_Load that reloads the textboxes then, when you reach the click event, your textboxes are reverted to the original values. – Steve Mar 17 '15 at 14:12
@AbuzerBCA I am looking at your UPDATE code. There is a big problem there. Please look at this link [Sql Injection explained](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work) – Steve Mar 17 '15 at 14:19
@Steve Thanks. so should i use parameterized queries instead? – s_anzer Mar 17 '15 at 15:48
Absolutely yes, there is no better way – Steve Mar 17 '15 at 15:55

update query not working when i attach the content of text box using data reader on page load

1 Answers1