Ad:

ASP.NET Discussion Thread
Unsolved
Views: 1753

Jan 1st, 2009

Updating a database table using vb

Expand Post »

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.

Similar Threads

Formatting text area in PHP
Using php and html forms to update a mysql database in PHP
Using php and html forms to update a mysql database in Community Introductions
code is nopt updating the database as given below in Java
Help editing table in Java

kodingkarthik

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

37 posts
since Dec 2008

Permalink

Jan 1st, 2009

Re: Updating a database table using vb

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

Last edited by Aneesh_Argent; Jan 1st, 2009 at 8:04 am.

Aneesh_Argent

Reputation Points: 16

Solved Threads: 18

Junior Poster

Offline

104 posts
since Dec 2008

Permalink

Jan 1st, 2009

Re: Updating a database table using vb

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.

kodingkarthik

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

37 posts
since Dec 2008

Permalink

Jan 1st, 2009

Re: Updating a database table using vb

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:

vb Syntax (Toggle Plain Text)
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
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

SheSaidImaPregy

Reputation Points: 43

Solved Threads: 68

Veteran Poster

Offline

1,080 posts
since Sep 2007

Permalink

Jan 6th, 2009

Re: Updating a database table using vb

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.

kodingkarthik

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

37 posts
since Dec 2008

Permalink

Jan 6th, 2009

Re: Updating a database table using vb

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

SheSaidImaPregy

Reputation Points: 43

Solved Threads: 68

Veteran Poster

Offline

1,080 posts
since Sep 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in ASP.NET Forum Timeline: Using login form control, how to redirect users to different pages?

Next Thread in ASP.NET Forum Timeline: Reporting Services Button problem

DaniWeb Message

Cancel Changes

User Name
Password