Data Access Layer (N-Tier) Help

[Charles]

Because I am still new at this I am not sure what I am
missing. I am having a problem with getting the Textbox
value to update the second time. For example, I change
the value of the textbox two times for whatever reason.
I get an error message of "Concurrency violation: the
UpdateCommand affected 0 records." on the second change.

Why am I getting that error message? Is this a good
example of a DAL? If it is not where could I find one?
Any kind of help will be great.

I have a class (DataAccess) that I am calling from a
Windows Form. The class is connecting to the NorthWind
database using the Suppliers table. The Windows Form has
a Textbox (ContactName) and a Button (Save).

My code below is based off from Microsoft's "N-Tier Data
Form and Data Layer" example.

Thanks in Advance
Charles

--DataAccess Class--

Option Strict On
Imports System.Data.SqlClient

Public Class DataAccess
Protected strConn As String
= "server=localhost;database=northwind;integrated
security=sspi"
Protected DidPreviouslyConnect As Boolean = False
Private daSuppliers As SqlDataAdapter

Public Function CreateDataSet() As DataSet
Dim dsSupplier As DataSet
Dim IsConnecting As Boolean = True
While IsConnecting
Try
Dim SQLConn As New SqlConnection(strConn)
Dim strSQL As String = "SELECT * FROM
Suppliers"
Dim Cmd As New SqlCommand(strSQL, SQLConn)
daSuppliers = New SqlDataAdapter(Cmd)
Dim CmdBuild As New SqlCommandBuilder
(daSuppliers)
dsSupplier = New DataSet
daSuppliers.Fill(dsSupplier, "Supplier")
IsConnecting = False
DidPreviouslyConnect = True
Catch ex As Exception
Debug.WriteLine(ex.Message)
End Try
Return dsSupplier
End While
End Function

Public Sub UpdateDataSet(ByVal inDS As DataSet)
If inDS Is Nothing Then
Exit Sub
End If
Try
If (Me.daSuppliers Is Nothing) Then
CreateDataSet()
End If
inDS.EnforceConstraints = False
daSuppliers.Update(inDS, "Supplier")
Catch exc As Exception
Debug.WriteLine(exc.Message)
End Try
End Sub

End Class

--Form1 Class--

Option Strict On
Imports System.Data.SqlClient
Imports DataAccessLayertrl1

Public Class Form1
Inherits System.Windows.Forms.Form

Protected DidPreviouslyConnect As Boolean = False
Private dsSupplier As DataSet
Private dtSupplier As DataTable
Private dvSupplier As DataView
Dim m_DAL As DataAccess

'#Region " Windows Form Designer generated code " Here

Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
GetDataSet()
DataBindControls()
End Sub

Sub DataBindControls()
txtContactName.DataBindings.Add("Text",
dvSupplier, "ContactName")
End Sub

Sub GetDataSet()
If m_DAL Is Nothing Then
m_DAL = New DataAccess
End If
dsSupplier = m_DAL.CreateDataSet()
dtSupplier = dsSupplier.Tables("Supplier")
dvSupplier = dtSupplier.DefaultView
End Sub

Private Sub btnSave_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
btnSave.Click
Dim m_DAL As New DataAccess
Me.BindingContext(dsSupplier.Tables
("Supplier")).EndCurrentEdit()
m_DAL.UpdateDataSet(dsSupplier.GetChanges())
End Sub
End Class

 

[Cenk Civici]

http://www.asp.net/ControlGallery/ControlDetail.aspx?
Control=1387&tabindex=2
A free data access layer helper component. Thought you
might be interested.

 

[Michael Lang]

Take a look at the entire application code that is generated by "Db
Object-er", use the latest template available. Link to where you can get it
is in my signature.

 

[Charles]

Thank you for your reply! That did help to explain what
is going on with my dataset. However,
using .AcceptChanges did not help. I tried to refresh
the dataset by running GetDataSet again, that did not
work.

I did check the rowstate though. In my DataAccess class
before the update the RowState = Modified, after the
update, UnChanged which uses inDS as a dataset. In my
windows form class I checked the RowState, that is
Modified which uses dsSuppliers as a dataset. Should the
Modified rowstate be UnChanged? Could that be causing a
problem?

I also thought that .AcceptChanges got called after
a .Update. Is that correct?

Thanks again,
Charles

 

[Charles]

After a day and a half I am still no closer to getting
this to work.

TIA,
Charles

 

[Michael Lang]

Note,

The DataAdapter.Update(DataSet ds) method implicitly calls
DataSet.AcceptChanges() upon a sucessful update. There is no need to call
AcceptChanges() in your code.

 

[Dave Stienessen]

OK, I'm not giving up - I'm just a little stumpted myself -
you said you update the value in the textbox 2 times - are you doing the
database update after each change? If the answer is no, are you accepting
the changes to the dataset after the 1st update without doing the database
update? If this is the case it could cause the problem you are seeing -
I tried the sample app you said you based it on from Microsoft - it appears
to work when updating 2 times - granted its a different app from what you
are describing, but...

If possible, I'd run the most simple scenario possible - 1 datatable in the
dataset with loaded with 1 row and add a bunch of watches in the debugger
watching rowstate as you step through your app - if the rowstate is not
Unchanged after you call update, something is wrong -

One additional thought - I have had instances where the sqlcommandbuilder
didn't recognize my primary key on the table correctly - I wound up dropping
and re-creating the table with the primary key as the 1st column of the
table (no other changes) and that seemed to fix the problem - you might
consider looking at that - if you want, create a new windows app project and
drag your table onto the form (From Server Explorer) so it creates the data
adapter for you (I think it uses the sqlcommandbuilder class to do it) and
see if you can update the table with that (bypass your DAL).

HTH -
Dave

 

[Charles]

Yes, I am updating the database after each change. I do
that by clicking on a button that has:

Me.BindingContext(dsSupplier.Tables
("Supplier")).EndCurrentEdit()
m_DAL.UpdateDataSet(dsSupplier.GetChanges())

The sample app from Microsoft works great. My app does
not. The only part of my program that could be the same
now is the DataAccess class.

I do not know if I can get any simplier then what I have
now. I only have one datatable and dataset with two
datarows. My form only has one textbox and button.

I did drop the PK column and recreated that column as the
PK. I am working from the Northwind sample database so
there should not be anything wrong there. The rowstates
do seem to be correct. The rowstate is Modified before
the update and after the update it is Unchanged.
However, if I check the rowstate in the ds in the client
that is still Modified even after the update.

I do not think that my client side ds gets updated at all
with the new values of the updated datarows. If that is
the case, how would I 'refresh' the client side (winform)
ds?

I did include my code on my first post. I am using
VS.NET 2003 and SQL Server 2000. The code should be
complete for someone to recreate my program.

Thank you so much for your help,
Charles

 

[Charles]

Thank you!

That seems to have done it. That is interesting becuase
I thought that I did put .AcceptChanges in there and that
did not work. However, I did still have the Dim m_DAL
line in there at that time. I did take that line out
earlier. So, maybe the two things together did the trick.

Thanks again,
Charles