Help with updating table

[bladernr]

Hello all... I am stuck. Can someone get me un stuck?

First of all, yes this is a project for a class I am taking, I know that, and I dont want anyone to write the app for me, but what I do need is someone to look at the update subs and tell me whats wrong.

My app simulates an ATM login... it contains two forms, a login form and a main form.

How it works is basically, the app loads the login form, prompting for an account number and pin. this form has a data connector to the database and it creates a dataset. The form accesses the dataset directly (no binding) to match the account number and pin entered, and passes that to a friend variable in the included module. after that is done, the login form closes and the main form loads.

When it loads, it pulls a row from the db based on the account number saved in the friend variable in the module. The main form also has a data connector to the db, and has its own dataset as well (the dataset for the login form only includes 2 fields from the db, and the ds for the main form includes all fields from the db).

Now at this point, the user can make a withdrawal, do a deposit, change pin or name, and logout.

Here is where I am stuck. The initializaion works fine, and the main form loads with the correct user info based on the the account number given during login. The problem is when I go to do an update to the dataset on the main form. The way it should work is that when the user clicks on the deposit finalize button the app updates that row in the database. That way the deposit, withdrawal, or info change is saved for future use.

Problem is this line here:

If Not dsmodifiedrows Is Nothing Then
            odbdaCustData.Update(dsmodifiedrows)
            Atm_custData1.AcceptChanges()
        End If

Now, if i use the if statement, the update never fires because the row is not shown as changed, apparently. If I remove the if statement, the app throws an unknown exception error trying to update with null values or something like that...

So anyone care to help get me unstuck? I have used the same update setup on several other apps with no problems.

At first I thought it may have something to do with the db being accessed twice, but I cant see that being a problem, IMHO. Next I thought it may be because the login was creating the dataset using the acct_no and pin fields with readonly permissions set. So I changed the dataset to use share deny none for both forms, but that didnt help.

I am attaching the entire solution as a zip file... I cant see an issue, but then again, I am not that proficient with vb so I am probably overlooking something.

Keep in mind that the withdrawal update does not work yet, and the deposit update is the one I have been messing with to figure this out. Some of the buttons are not complete, but all the text boxes work and the bindings are all present.

Thanks!

Jeff

[Boumxyz2]

ok,

after a quick look...

I don't see any dataBindings on your textboxes.. Which means if you enter a new value, your update statement don't get fired.


If you want to fire your update statement try to modify the dataset.

Example :

Customer enters value in the deposit.

Then you can do something like this


instead of this
txtAcctBalance.Text = Val(txtAcctBalance.Text) + Val(txtDepositAmount.Text)

do

Atm_custData1.tables(0).rows(0).items("Balance") = Val(txtAcctBalance.Text) + Val(txtDepositAmount.Text)

' or whatever row your suppose to be on, but from my quick look you're suppose
to open only 1 row.


After that, your modified row state will be changed.

if you never touch the dataset, it will never has modified rows.

Also

always use Option Explicit and Option Strict

in your project, explicit is on, strict is off

Strict enforces type to type assignation which will solve major issue at compile time.


Hope my hints are clear enough.

[bladernr]

Cool... Thanks for the hints... One question though, I thought I did do data binding on the text boxes in the form_load sub...

in from_load() I have this:

 lblWelcome.Visible = True
        SetDisplay(4)   'set our display to state 4
        'fill the dataset
        intRecords = odbdaCustData.Fill(Atm_custData1)
        Dim count As Integer
        Dim bndTemp As Binding
        'roll through the dataset until we find the right records
        For count = 0 To intRecords - 1
            'if our acct numbers match, we load correct data from the dataset based on row number
            If Atm_custData1.custacct(count).acct_no = directormodule.intAcctNumber Then
                'save the current row number
                currentRecord = count
            End If
        Next

        'bind our controls to the data in the datarow
        bndTemp = New Binding("text", Atm_custData1, "custacct.pin")
        txtPIN.DataBindings.Add(bndTemp)
        bndTemp = New Binding("text", Atm_custData1, "custacct.fname")
        txtFirstName.DataBindings.Add(bndTemp)
        bndTemp = New Binding("text", Atm_custData1, "custacct.lname")
        txtLastName.DataBindings.Add(bndTemp)
        bndTemp = New Binding("text", Atm_custData1, "custacct.balance")
        txtAcctBalance.DataBindings.Add(bndTemp)
        bndTemp = New Binding("text", Atm_custData1, "custacct.acct_no")
        txtAcctNumber.DataBindings.Add(bndTemp)
        'navigate to correct user
        Me.BindingContext(Atm_custData1, "custacct").Position = currentRecord

so what you are suggesting makes me wonder if I havent messed up the binding... perhaps by specifying "table.field" instead of simply "field" in the binding setup....

But if I use what you suggested, (since I am still kinda new to this), will that basically be directly accessign the database/dataset instead of doing it via bindings and such??

Thanks
Jeff

[Boumxyz2]

Ok here's a little explanation on how Datasets works in .NET.


First of all, you work in a disconnected manner. Which means, you have a COPY of the database locally.

So you're not ACTUALLY modifying the data on the server. You can try it.

Example :

do this,

ds.tables(0).rows(0).items("Balance") = 0

And open table on your server.

You will see it won't be = to 0.


Once you do the UPDATE Statement then it will be 0.

The local copy of the data can be modified as much as you want. As long as you don't use the UPDATE statement yet.

So mainly, that kind of database working (please note that in ADO.NET you can't work in a connected manner as in "normal" ADO.) isn't really a good choice for a bank simulation hehe. Because you could get really weird result if many people can connect on the same account and in the process you would lose some transactions.

But

there'S a way to know in ADO.NET if data has been tampered with on the server while you're working on your local copy.

An exception will be raised on the update statement. It will be a concurrent exception.

Anyway I hope this makes it clearer on how ADO.NET works.

[bladernr]

OK I have got most all done... I need to reset the form sizes to maximize, but otherwise its complete, or as complete as I want it to be for the moment....

One problem still exists though

I still can not update the actual db.

I CAN, using your method update the dataset with withdrawals, deposits, and personal info changes. I can change the info, make the withdrawals and deposits, log out, log back in and see that the changes to the dataset are present...

however, I have the update lines commented out because every time the app runs the update it exits with an unhandled exception and no info at all.

so I can pull the data from the database (by the way, there are 21 records in the db and they are all accessible by entering the correct pin and account number, I just have it set to automatically use the first record for testing purposes...) but I cant write back to it.

The dataset is configured using Share Deny None, and the text box controls ARE bound to the fields...

any ideas?

I have attached the current version to this post...

Thanks!
Jeff

[Boumxyz2]

ok I didn't checked how you open your data.

when you use your dataAdapter

to be able to UPDATE a table through dataset you must open a table

to open a table here's a sample code

dim conn as new OleDb.OleDbConnection
dim Cmd As new OleDb.OleDbCommand
dim CmdBuilder As OleDb.O1leDbCommandBuilder
dim adap as dataadapter
dim ds as dataset

Conn.ConnectionString = YourConnectstring
conn.Open()
cmd.connection = conn
cmd.commandtype = commandtype.tabledirect 'THIS IS IMPORTANT ELSE YOU CAN'T UPDATE
adap = new dataAdapter(cmd)
adap.fill(ds)

'THIS PART YOU DID THIS MANUALLY, I'M LAZY SO I'M USING CMDBUILDER
CmdBuilder = New OleDb.OleDbCommandBuilder(adap)
adap.InsertCommand = CmdBuilder.GetInsertCommand()
adap.UpdateCommand = CmdBuilder.GetUpdateCommand()
adap.DeleteCommand = CmdBuilder.GetDeleteCommand()

'from this point you can use your insert,update and delete statement.

or just call ds.updatechanges() and it will do all the work for you.


ELSE

if you want to use select statement

then you have to run your own Update and delete statement on the table

Example :

'Open your dataset, do your stuff as normal

'It's time to update you can use an update statement or build a cmd that update the table


Example
Dim Cmd As new SqlClient.SqlCommand
cmd.commandtext = "update table set field1 = 'bob' where field1 = 'Barker' "
cmd.ExecuteNonQuery()

this will update your table too.

(In that case you don't need to keep your dataset opened, just need to have the current data).