Updating a database table using vb

[kodingkarthik]

Hi all,

I am new to Asp.net programming and i have a requirement to retrieve the values from the database and allow the user to edit the values and then save back thechanged values.
I have written the code for saving the form fields values to a database but now i need to retrieve the saved values and allow the user to change the data according to the Name of the employee and then display the respective values of that particular employee for editing.

Thanks in advance,
Karthik.

[Aneesh_Argent]

One Way of doing it might be like this. Since you havnt mentioned your DB I am using an SqlConnection

Imports System.Data.SqlClient
Dim DBCon As New SqlConnection("MyConnectionString")
DBCon.Open()
Dim Cmd As New SqlCommand("Select FirstName, LastName from tblUsers Where UserID = 1 ", DBCon)
Dim dataReader As SqlDataReader
dataReader = Cmd.ExecuteReader()
If (dataReader.Read()) Then
TextBoxFirstName.Text = dataReader("FirstName").ToString()
TextBoxLastName.Text = dataReader("LastName").ToString()
End If
DBCon.Close()
dataReader.Close()

You can also use SqlDataAdapter or SqlDataReader depending on the context

[kodingkarthik]

Hi Aneesh,

Thanks for your reply but we are able to get only the data for ID value 1 but we require according to the name. I have used a drop down list for Name and if the user selects a name then accordingly the values should be displayed.

Thanks in advance,
karthik.

[SheSaidImaPregy]

A simple way would be to retrieve the information for the dropdown. Then when the user selects from the drop down, trigger an event to call the database to retrieve the values based on the dropdown selected value. Then place those values into textboxes, and allow the user to edit them. Once they are done editing, have them click a button that will call a save method on the server, and save the data. It's relatively easy. Follow the example below:

Sub Page_Load()
    Dim conn As New SqlConnection("connectionString");
    Dim cmd As New SqlCommand("SELECT Name, ID FROM Users", conn);
    Dim rdr As SqlDataReader

    Try
        conn.Open();

        rdr = conn.ExecuteReader()

        If rdr.HasRows Then
            dropdownlist1.DataSource = rdr
            dropdownlist1.DataBind()
        Else
            dropdownlist1.Items.Add(New ListItem("-- No values found --", "-- No values found --"))
        End If
    Catch ex As SqlException
        Response.Write(ex.ToString())
    Finally
        conn.Close();
    End Try
End Sub

Sub dropdownlist1_SelectedIndexChanged(s As Object, e As EventArgs) Handles dropdownlist1.SelectedIndexChanged
    Dim conn As New SqlConnection("connectionString");
    Dim cmd As New SqlCommand("SELECT Name, Address FROM Users WHERE ID=@id", conn);
    Dim rdr As SqlDataReader

    cmd.Parameters.AddWithValue("@id", dropdownlist1.SelectedValue)

    Try
        conn.Open();

        rdr = conn.ExecuteReader()

        If rdr.HasRows Then
            While rdr.Read()
                textbox1.Text = rdr("Name")
                textbox2.Text = rdr("Address")
            End While
        Else
            Response.Write("No users found with the given id: " & dropdownlist1.SelectedValue)
        End If
    Catch ex As SqlException
        Response.Write(ex.ToString())
    Finally
        conn.Close();
    End Try
End Sub

Sub button1_Click(s As Object, e As EventArgs)
    Dim conn As New SqlConnection("connectionString");
    Dim cmd As New SqlCommand("UPDATE Users SET Name=@name, Address=@address WHERE ID=@id", conn);
    Dim successfulRows As Integer = 0

    cmd.Parameters.AddWithValue("@name", Trim(textbox1.Text))
    cmd.Parameters.AddWithValue("@address", Trim(textbox2.Text))
    cmd.Parameters.AddWithValue("@id", dropdownlist1.SelectedValue)

    Try
        conn.Open();

        successfulRows = cmd.ExecuteNonQuery()

        If successfulRows > 0 Then
            Response.Write("Updated " & successfulRows & " successfully.")
        Else
            Response.Write("Could not find row to update with id: " & dropdownlist1.SelectedValue)
        End If
    Catch ex As SqlException
        Response.Write(ex.ToString())
    Finally
        conn.Close();
    End Try
End Sub

[kodingkarthik]

Hi,

Thanks for your reply but i am getting an error at
rdr=conn.ExecuteReader() and the error says that ExecuteReader() is not a member of System.Data.Sqlclient.SqlConnection
can any one please help me out with this problem?

Thanks in advance,
karthik.

[SheSaidImaPregy]

That's because it is not a member of System.Data.SqlClient.SqlConnection. That is the class of the actual connection. You need one right before that:

System.Data.SqlClient