Access and datatypes with Vb net.....sigh

Okay, I give. I am using an Access Db. I am having troublewith data type conversions...
driving me nuts!
In the Access table, the column is a Integer
When I try to update it or assign a variable to it, it simply phews.:(((
What I am trying to do, is assign the variable the current value of the column, then update the column by 1
I keep receiving cast errors, not matter how I dim the variable.

        Dim vtagnbr As Integer = 0
        Dim vsqltxt As String = ""
        Dim vsep As String = "'"

        dsTagNbr.Clear()
        vsqltxt = "SELECT RecId, BegTagNbr FROM  IdTagNbrs"
        daTagNbrs.SelectCommand.CommandText = vsqltxt
        datagNbrs.Fill(dsTagNbr)
        vtagnbr = CInt(dsTagNbr.IdTagNbrs("BegTagNbr"))

I need to have the begtagnbr, add one to it and populate a text box (that I can do..)
and update the Counter by 1
What the magic key for Access data types??
Any help appericated...
bearpawsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DabasCommented:
Hi bearpaws:
I do not know if this helps, but there might be a confusion between the meaning of "Integer"
In VB.NET an Integer is Int32, while in Access it is Int16....

Dabas
SanclerCommented:
What you are trying to do is change a DataColumn into an Integer, and one will never cast to the other.

Values are in DataRows.  So you need something like (I've expressed it generically, you can substitute your own indexes or names)

        Dim dr As DataRow = ds.Tables(0).Rows(0)
        Dim myval As Integer = CInt(dr.Item(0))

in place of

        vtagnbr = CInt(dsTagNbr.IdTagNbrs("BegTagNbr"))

Roger

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bearpawsAuthor Commented:
That worked! I am so use to asp and sql server..sigh
Using Vb net is new to me and it seems no matter how much I read, I trip..
but it will get better with experience too.
Can you explain why this snippet of coding doesn't work??

'update the counter in the table
        vsqltxt = "UPDATE IdTagNbrs SET IdTagNbrs.BegCounter = ([IdTagNbrs.BegCounter]+1)"
        datagNbrs.UpdateCommand.CommandText = vsqltxt
        datagNbrs.Update(dsTagNbr, "IdTagNbrs")
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

SanclerCommented:
Two things.

First, again you are trying to update a generic field, rather than a field in a single identified record.  In which record do you you want BegCounter to be incremented by one?  And how is the database supposed to know that?  I assume this is the record you were calling with the previous code.  So you would need something like

        vsqltxt = "UPDATE IdTagNbrs SET IdTagNbrs.BegCounter = ([IdTagNbrs.BegCounter]+1) WHERE RecID = " & myRecID

Second, with this sort of query I would use the ExecuteNonQuery method.  Have a look at this

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbcode/html/vbtskcodecallingexecutenonquerymethodofdatacommandvisualbasic.asp

Roger
bearpawsAuthor Commented:
Thank you for responding and the information. The info is applicable to Sql and not Access, but I do appreciate it greatly.
Still will not update the record. There is and always will be, just one record in that table which is why I thought it would not matter if I do not use a where clause.
 All it does is create a new tag nbr by incrementing the counter by one and adding it to the begining tag nbr.
I know one thing, vb.net is really occupying me...ha ha
ps, I had also up your points when accepting your solution...

SanclerCommented:
What you need to do then is alter the value in the dataset and use an oledbdataadapter.update command to write that to the database.

Your best bet is to drag an OleDbDataAdapter from the toolbox onto your form and use the wizard to create the commands.  I've created a database in Access, containing just one table - IdTagNbrs - containing just two fields - RecID as AutoNumber as PrimaryKey and BegCounter as Integer.  A PrimaryKey is essential for the OleDBDataAdapter update command to work.

I configured an oledbdataadapter with the wizard and used the following code in my form's button_click event.

        Dim ds As New DataSet
        OleDbDataAdapter1.Fill(ds)
        Dim dr As DataRow = ds.Tables(0).Rows(0) 'there's only ever going to be one row
        dr.Item(1) = dr.Item(1) + 1 'item(0) is RecID, so we want to update item(1)
        OleDbDataAdapter1.Update(ds)

The table is updating OK in Access for me.

Roger
bearpawsAuthor Commented:
WOW - I just posted this and deleted the question since I got the code to work by doing this, which is very much same as your coding.
But I reloaded the question, 500 points, do did you recieve them??
I might have messed up here and if I did, we can contact the modeator to have points given to you etc. I just like to do the right thing..
I had upped the points to 500.. how do contact modeator??
I thank you for responding, it is appreciated..
An


    Dim dsTagNbr As New DataSet
            dsTagNbr.Clear()
            vsqltxt = "SELECT RecId, BegTagNbr,BegCounter FROM  IdTagNbrs"
            datagNbrs.SelectCommand.CommandText = vsqltxt
            datagNbrs.Fill(dsTagNbr)
            Dim dr As DataRow = dsTagNbr.Tables(0).Rows(0)
            Dim vtagnbr As Integer = CInt(dr.Item(1) + dr.Item(2))
            Dim voldcnt As Integer = CInt(dr.Item(2))
            voldcnt = voldcnt + 1
            dr.Item(2) = voldcnt
            vsqltxt = "UPDATE IdTagNbrs SET BegCounter =" & voldcnt
            datagNbrs.UpdateCommand.CommandText = vsqltxt
            'datagNbrs.Update(dsTagNbr.Tables(0).Rows(0).Item(2))
            datagNbrs.Update(dsTagNbr)
SanclerCommented:
Don't worry, I got the points from the original question - 250 increased to 350 - and I'm perfectly happy with that.

I also got the points from the other question.

Thanks for them all.

The main thing is that you've got things working as you want them.

Roger
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.