Can't update datarow

Discussion in 'ASP .Net' started by CaptRR, May 11, 2004.

  1. CaptRR

    CaptRR Guest

    I think this is the right group to post to, so here goes.

    My problem is this, I cannot update the datarow to save my life. Been
    on this for 2 days now, and still am no closer to figuring it out than I
    was before. I'm basicly taking date from some text boxes, trying to put
    them into a datarow and using that datarow to update the database, but
    its not working. The btn_update is where I am sending the information
    back to the addclient class. If anyone can help, I would appeciate it.

    Heres the source:

    Heres the webform

    Public Class clients
    Inherits BasePage
    Dim dsClients As New DataSet
    Dim drClients As DataRow
    Dim dtClients As DataTable

    'Inherits System.Web.UI.Page

    #Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub
    InitializeComponent()

    End Sub
    Protected WithEvents dgClients As System.Web.UI.WebControls.DataGrid
    Protected WithEvents btnAddClient As System.Web.UI.WebControls.Button
    Protected WithEvents lblName As System.Web.UI.WebControls.Label
    Protected WithEvents txtName As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblContact As System.Web.UI.WebControls.Label
    Protected WithEvents txtContact As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblPhone As System.Web.UI.WebControls.Label
    Protected WithEvents txtPhone As System.Web.UI.WebControls.TextBox
    Protected WithEvents txtAddress1 As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblAddress2 As System.Web.UI.WebControls.Label
    Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblCity As System.Web.UI.WebControls.Label
    Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox
    Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblAddress1 As System.Web.UI.WebControls.Label
    Protected WithEvents txtState As System.Web.UI.WebControls.TextBox
    Protected WithEvents lblState As System.Web.UI.WebControls.Label
    Protected WithEvents lblState2 As System.Web.UI.WebControls.Label
    Protected WithEvents lblZip As System.Web.UI.WebControls.Label
    Protected WithEvents lblClientIDText As System.Web.UI.WebControls.Label
    Protected WithEvents lblClientID As System.Web.UI.WebControls.Label
    Protected WithEvents btnUpdate As System.Web.UI.WebControls.Button
    Protected WithEvents lblError As System.Web.UI.WebControls.Label

    'NOTE: The following placeholder declaration is required by the Web
    Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles MyBase.Init
    'CODEGEN: This method call is required by the Web Form Designer
    'Do not modify it using the code editor.
    InitializeComponent()
    End Sub

    #End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles MyBase.Load
    Me.PageTitle = "Client View"
    If Request.QueryString("Id") <> "" Then
    FormVisable(True)
    Filtered(Request.QueryString("Id"))
    Else
    FormVisable(False)
    FillDataGrid() ' Fills the Datagrid -- See FillDataGrid
    Function
    End If






    End Sub

    Private Sub dgClients_SelectedIndexChanged(ByVal sender As
    System.Object, ByVal e As System.EventArgs) Handles
    dgClients.SelectedIndexChanged

    End Sub

    Private Sub btnAddClient_Click(ByVal sender As System.Object, ByVal
    e As System.EventArgs) Handles btnAddClient.Click
    Response.Redirect("addclient.aspx")
    End Sub

    Private Sub FillDataGrid()
    dsClients = dbaddclient.GetClients() 'Calls the GetClients
    Function in the dbaddclient class, a dataset is returned
    dgClients.DataSource = dsClients
    dgClients.DataMember = "clients" 'Chooses the database
    dgClients.DataKeyField = "Id" 'Sets the key field to ID
    dgClients.DataBind() 'Finnally the datagrid is bound
    End Sub


    Public Sub FormVisable(ByVal o As Boolean)
    lblName.Visible = o
    lblContact.Visible = o
    lblPhone.Visible = o
    lblAddress1.Visible = o
    lblAddress2.Visible = o
    lblCity.Visible = o
    lblState2.Visible = o
    lblZip.Visible = o
    txtName.Visible = o
    txtContact.Visible = o
    txtPhone.Visible = o
    txtAddress1.Visible = o
    txtAddress2.Visible = o
    txtCity.Visible = o
    txtState.Visible = o
    txtZip.Visible = o
    lblClientIDText.Visible = o
    lblClientID.Visible = o
    btnUpdate.Visible = o
    lblError.Visible = o
    If o Then
    dgClients.Visible = False
    Else
    dgClients.Visible = True
    End If

    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e
    As System.EventArgs) Handles btnUpdate.Click
    dsClients = dbaddclient.GetClients() 'Calls the GetClients
    Function in the dbaddclient class, a dataset is returned
    drClients=
    dsClients.Tables("clients").Rows.Find(lblClientID.Text)
    drClients.BeginEdit()
    drClients("Name") = txtName.Text
    drClients("Contact") = txtContact.Text
    drClients("Phone") = txtPhone.Text
    drClients("Address1") = txtAddress1.Text
    drClients("Address2") = txtAddress2.Text
    drClients("City") = txtCity.Text
    drClients("State") = txtCity.Text
    drClients("Zip") = txtZip.Text
    drClients.AcceptChanges()
    drClients.EndEdit()
    Select Case dbaddclient.UpdateClients(dsClients)
    Case dbaddclient.UpdateResult.ConcurrencyError
    lblError.Text = "The data has been modified by anouther
    user, try again"
    Case dbaddclient.UpdateResult.SQLError
    lblError.Text = "An SQL error has occured"
    Case dbaddclient.UpdateResult.Success
    lblError.Text = "Updated"
    'Response.Redirect("clients.aspx")
    End Select


    End Sub

    Public Sub Filtered(ByVal Parameter As String)

    'dtClients = dsClients.Tables("clients")
    'drClients = dtClients.Rows.Find(Parameter)
    dsClients = dbaddclient.GetClients() 'Calls the GetClients
    Function in the dbaddclient class, a dataset is returned
    drClients = dsClients.Tables("clients").Rows.Find(Parameter)
    lblClientID.Text = drClients("Id")
    txtName.Text = drClients("Name")
    txtContact.Text = drClients("Contact")
    txtPhone.Text = drClients("Phone")
    txtAddress1.Text = drClients("Address1")
    txtAddress2.Text = drClients("Address2")
    txtCity.Text = drClients("City")
    txtState.Text = drClients("State")
    txtZip.Text = drClients("Zip")

    End Sub
    End Class




    Here is the addclient class

    Imports System.Data.SqlClient

    Public Class dbaddclient
    Public Enum UpdateResult
    Success
    ConcurrencyError
    SQLError
    End Enum
    Private Shared Function Connection() As SqlConnection 'SQL
    Connection function that is used internally by the class
    Dim sConnectionString As String
    sConnectionString = "Initial Catalog=wsconnect;Data
    Source=*****;User ID=*****;password=*******;"
    Return New SqlConnection(sConnectionString)
    End Function
    Private Shared Function DataAdapter() As SqlDataAdapter
    Dim sSelect As String = "Select * From clients"
    Dim daClients As New SqlDataAdapter(sSelect, Connection())
    daClients.MissingSchemaAction = MissingSchemaAction.AddWithKey
    Dim cbClients As New SqlCommandBuilder(daClients)
    Return daClients
    End Function

    'Experimental GetClients function, not tested yet
    Public Shared Function GetClients() As DataSet
    'Dim sSelect As String
    'sSelect = "Select * FROM clients"
    'Dim cmdClients As New SqlCommand(sSelect, Connection)
    'Dim daClients As New SqlDataAdapter
    'daClients.MissingSchemaAction = MissingSchemaAction.AddWithKey
    'daClients.SelectCommand = cmdClients
    Dim dsClients As New DataSet
    Dim daClients As SqlDataAdapter = DataAdapter()
    daClients.Fill(dsClients, "clients")
    Return dsClients
    End Function



    Public Shared Function addclient(ByVal client As clientclass) As
    Boolean 'the addclient function and returnd true or false based on success
    Dim sInsert As String _
    = "INSERT clients (Name, Contact, Phone, Address1, Address2,
    City, State, Zip) " _
    & "Values (@Name, @Contact, @Phone, @Address1, @Address2,
    @City, @State, @Zip)"

    Dim DBConnection As SqlConnection = Connection()
    Dim cmdClients As New SqlCommand(sInsert, DBConnection)
    cmdClients.Parameters.Add("@Name", client.Name)
    cmdClients.Parameters.Add("@Contact", client.Contact)
    cmdClients.Parameters.Add("@Phone", client.Phone)
    cmdClients.Parameters.Add("@Address1", client.Address1)
    cmdClients.Parameters.Add("@Address2", client.Address2)
    cmdClients.Parameters.Add("@City", client.City)
    cmdClients.Parameters.Add("@State", client.State)
    cmdClients.Parameters.Add("@Zip", client.Zip)
    addclient = True
    DBConnection.Open()
    Try
    cmdClients.ExecuteNonQuery()
    Catch e As SqlException
    addclient = False

    End Try
    DBConnection.Close()
    End Function

    Public Shared Function UpdateClients(ByVal Clients As DataSet) As
    UpdateResult
    Dim daClients As SqlDataAdapter = DataAdapter()
    Try
    daClients.Update(Clients, "clients")
    Return UpdateResult.Success
    Catch eConcurrency As DBConcurrencyException
    Return UpdateResult.ConcurrencyError
    Catch eSql As SqlException
    Return UpdateResult.SQLError
    End Try
    End Function
    End Class
    CaptRR, May 11, 2004
    #1
    1. Advertising

  2. Lots of code to wade through here, buddy - a little hard to see what's going on. But I would check the following things

    1) I'm not clear whether you are trying to Add rows, update them, or both. I also don't see where you're adding any rows to the table. If you want to add a new row to the DataTable, you should execute AddNew, which will instantiate a new Data Row - based on the column structure in whatever DataTable you're using. Then populate the fields (like you're doing) and then add actually add the row to the table (AddNew doesn't add a row to the table, it only instantiates one).

    2) It looks like your doing a find - and then changing the contents of whatever row was found. So you'll have some rows where the rowstate is set to "Changed" - but I didn't see an Update Command associated with your DataAdapter. If your table has changed rows in it - you'll need an update command. If changing existing rows is something you need to do, you'll need some form of unique identifier, like a primary key, or at least some combination of columns for the DataAdapter to determine which row to update. That has to be coded into your update command

    3) Again assuming you're doing Adds, Make sure your DataAdapter (daClients) has an Insert command associated with it. I see where you have a command object called cmdClients, which looks about right. But I don't see anything like "daClients.InsertCommand = cmdClients"

    4) It looks like your executing the AcceptChanges method on the datarow before you invoke the updateclients method you wrote. I think the effect of this will be that the rowstate on that record will be changed back to "unchanged" - so the dataadapter will not want to insert it - or update it. I always make sure I complete database updates first - and then invoke acceptchanges (unless, of course the changes should be cancelled). I think what is happenning is that you make some changes - then reset the rowstate with AcceptChanges - so the UpdateClients method doesn't really do anything. The dataadapter sees that there's no changes - so it doesn't issue any SQL to the DBMS

    5) In addition to the fact that there needs to be SELECT, INSERT, and (maybe) UPDATE commands linked to the DataAdapter, there also needs to be Data Mapping. I usually use the "Configure Adapter" tool in visual studio to auto-generate all that stuff. More often than not, I end up editting stuff a lot - and moving chunks of code into different modules, etc. But that tool is a big time-saver.

    Hope that helps
    =?Utf-8?B?UmljaA==?=, May 11, 2004
    #2
    1. Advertising

  3. Thanks for Rich's quick response!

    Hi CaptRR,

    First of all, I would like to confirm my understanding of your issue. From
    your description, I understand that you were unable to update the changes
    to data source with DataAdapter.Update method. If there is any
    misunderstanding, please feel free to let me know.

    I agree with Rich on the 4th point that AcceptChanges should never be
    called before you update the data source. After calling AcceptChanges, all
    the DataRows' RowState property will be set to Unchanged, so that the
    DataAdapter cannot decide which row needs to be updated. That might be why
    the data source cannot be updated. So please try to remove this line and
    try again.

    HTH.

    Kevin Yu
    =======
    "This posting is provided "AS IS" with no warranties, and confers no
    rights."
    Kevin Yu [MSFT], May 12, 2004
    #3
  4. CaptRR

    CaptRR Guest

    Thanks Rich, and Kevin

    After pulling on all nighter, I was able to figure out what is going on.
    Even if I don't understand why. It appears that for the most part the
    code I had put down was working the entire time. Well sort of.

    On the top of my page (in the page load event),I had an if statement
    that checked to see if I was getting a url postback from the datagrid.
    This check, made the textboxes and buttons for editing visable, and made
    the datagrid invisable, when it detected a url postback with a id number
    in it. It also used that id number to fill in the default data from

    After the textboxes became visable, I would edit the changes, and hit
    update. Well, when I did that the page load event would fire off again,
    putting the default values back in, and of course updateing the database
    with the default values.

    Once again Keven, and Rich, I appreciate your help on this one. I was
    about to give up on asp , but after this I think I am going to stick
    with it.

    Kevin Yu [MSFT] wrote:
    > Thanks for Rich's quick response!
    >
    > Hi CaptRR,
    >
    > First of all, I would like to confirm my understanding of your issue. From
    > your description, I understand that you were unable to update the changes
    > to data source with DataAdapter.Update method. If there is any
    > misunderstanding, please feel free to let me know.
    >
    > I agree with Rich on the 4th point that AcceptChanges should never be
    > called before you update the data source. After calling AcceptChanges, all
    > the DataRows' RowState property will be set to Unchanged, so that the
    > DataAdapter cannot decide which row needs to be updated. That might be why
    > the data source cannot be updated. So please try to remove this line and
    > try again.
    >
    > HTH.
    >
    > Kevin Yu
    > =======
    > "This posting is provided "AS IS" with no warranties, and confers no
    > rights."
    >
    CaptRR, May 12, 2004
    #4
  5. Hi CaptRR,

    It was nice to hear that you have had the problem resolved. Thanks for
    sharing your experience with all the people here. If you have any
    questions, please feel free to post them in the community.

    Kevin Yu
    =======
    "This posting is provided "AS IS" with no warranties, and confers no
    rights."
    Kevin Yu [MSFT], May 13, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Doug
    Replies:
    0
    Views:
    870
  2. Alex
    Replies:
    3
    Views:
    585
  3. =?Utf-8?B?SklNLkgu?=

    Q: update through datarow

    =?Utf-8?B?SklNLkgu?=, Mar 13, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    357
    =?Utf-8?B?SklNLkgu?=
    Mar 13, 2006
  4. Update DataRow

    , Feb 23, 2007, in forum: ASP .Net
    Replies:
    2
    Views:
    687
  5. Bobby Edward
    Replies:
    0
    Views:
    889
    Bobby Edward
    Sep 18, 2008
Loading...

Share This Page