Can't update datarow

[CaptRR]

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

 

[Guest]

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

 

[Kevin Yu [MSFT]]

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]

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]]

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."