help with converting from VB4.0

[jmarzion]

I am trying to convert an old VB4.0 application to VB.net and I am
pulling my hair out over understanding changes in database connections,
etc. I am using a ACCESS database.

In the old code, if I wanted to write to the database, I'd simply put
something like:

myDB = Workspaces(0).OpenDatabase("C:\productAPP\QuoteDB.mdb")
myTABLE = myDB.OpenRecordset("Quote Details", dbOpenTable)
myTABLE.LockEdits = False
myTABLE.AddNew() ' Create new record.
If modIDhold <> "" Then myTABLE("ModelID") = modIDhold ' Set record
key.
myTABLE("Qty") = itemQTY
myTABLE("PN") = itemPN
myTABLE("Description") = itemDESC
myTABLE("Price") = itemPRICE
myTABLE.Update() ' Save changes.
myTABLE.Close() ' Close Table.

Simple and straightforward, right? I have spent hours reading books
and websites trying to find a clear concise way of doing the same thing
in VB.net. Can someone out there please post code for doing the same
thing in VB.net. Please make the assumption that I (or someone else
who later reads this) knows nothing about VB.net. Looking for someone
who can explain this in easy-to-understand, common terms. Everything
I come across related to this topic makes me feel pretty inept...

Thanks in advance,

jmar

 

[Cor Ligthert [MVP]]

Jmarzion,

What version of VBNet although basicly they work the same has the version
2.0 this simple trick to make it complete as you want with about 8 clicks.

http://www.vb-tips.com/default.aspx?ID=1139f14a-c236-4ad7-8882-b1ed16424252

I hope this helps,

If you are using another version than just reply that difficult is it not to
make a piece of sample code for you based on yours

Cor

 

[jmar]

Cor,

I am using Visual Studio.Net 2003. I would appreciate if you could
make a peice of sample code based on mine.

Thanks.

Jmar

 

[jmar]

I should add that I'd rather do this in code than with the data
wizards. I prefer to understand how it works rather than just get it
to work.

Jmar

 

[Cor Ligthert [MVP]]

Jmar,

Because that it is your code. I could not test it.
However it would not be far from this.
The variables that I placed in it, do you have to change of course yourself
again.
And it is than your code without any error check. or correction on that.


\\\
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Dim conn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=C:\ProductAPP\QuoteDB.mdb;User
Id=admin;Password=;")
Dim dt As New DataTable
Dim da As New OleDb.OleDbDataAdapter("Select * from Quote Details",
conn)
da.FillSchema(dt, SchemaType.Mapped)
Dim myTable As DataRow = dt.NewRow 'mytable is a datarow
myTable("ModelID") = "Whatever" 'Set record key.
myTable("Qty") = "itemQTY"
myTable("PN") = "itemPN"
myTable("Description") = "itemDESC"
myTable("Price") = "itemPRICE"
dt.Rows.Add(myTable)
Dim cmb As New OleDb.OleDbCommandBuilder(da)
da.Update(dt) ' Save changes.
End Sub
///

I hope this helps,

Cor

 

[jmar]

Great response Cor. Thank you! I was able to quickly and easily write
to the database using your help.

It leads to a few more questions:

1) What does the Dim cmb line do? I don't understand it.

2) More importantly, if I wanted to follow this code up with
additional code that writes to another table in the same database
(table name QTEMODEL), then how would I do this? If you could use the
above code and append to it, I'd be very grateful. I'm running
into a "row already exists" error. Do I somehow have to close
myTABLE before I start the next?

3) If I wanted to put several rows in one table (within a loop),
how would I change the above?

Once again, thank you, thank you, thank you!

Jmar

 

[Cor Ligthert [MVP]]

John,

If you want to Insert a record in a table and and you are sure it does not
exist, use than a SQL insert command. You will have to do a lot with those
transactions SQL commands.

This are nice pages on MSDN for Access

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

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

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

To use those, you use an OleDB.Command.executeNonQuery

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbcommand.executenonquery(VS.80).aspx

I have the idea that on that page is everything you needed.

Use a dataadapter and a datatable/dataset if you need the user to do
interactions with that.

I hope this helps,

Cor

 

[jmar]

Cor,

Excellent information. I can see that SQL commands are what I'm
looking for, I just don't know the syntax required to code them. Would
it be possible for you to revise the code you sent in Reply #4 and
incorporate the SQL so that I get a handle on the syntax? It would
help me to see it with my own code.

Thanks,

John

 

[Cor Ligthert [MVP]]

Would

it be possible for you to revise the code you sent in Reply #4 and
incorporate the SQL so that I get a handle on the syntax? It would
help me to see it with my own code.

No John, I try you to teach you how to fish, I don't give the fish.

You have a start now, we all had to try.

Sorry,

Cor