Writing to a MS access db from visual studio .net

  • Thread starter Thread starter gordy
  • Start date Start date
G

gordy

Hey all,

I have a fairly simple app which goes out to the web to download data.
I want to store this data in a database (1 table, ~8 fields or so). My
program is written in VB.net and works fine to get the data. My
question is, how do I get the data into a database? I want to use
Microsoft Access.

I have seen several articles on using VB.net and ADO.net or Jet to read
data in, but I haven't seen anything to write data out. Can anyone
suggest articles? Thanks,

Brian
 
Hi, I cant really help with adding the data to an access database, but
is there anyway I could have a look at your program? I'm trying to do
something like this myself.
 
You can use the OLEDB objects
to read and write to an Access database.

Here's an example with VB2005/.Net2.0.

Imports System.Data.Oledb


Dim ConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\myAccessDatabase.mdb;" & _
"Persist Security Info=False"
Dim ds2 As DataSet = New DataSet
Dim conn As OleDbConnection = _
New OleDbConnection(ConnectionString)
conn.Open()
Dim cmd As OleDbCommand = _
New OleDbCommand("select * from Carriers", conn)
Dim adapter2 As OleDbDataAdapter = New OleDbDataAdapter(cmd)
adapter2.Fill(ds2, "Carriers")
For Each dr As DataRow In ds2.Tables(0).Rows
Dim Carrier As String = dr.Item("c_Carrier").ToString
Console.WriteLine(String.Format("Carrier = '{0}'", Carrier))
Next
conn.Close()
conn = Nothing


Robin S.
 
Oh, and here's what you *really* wanted, an example
of writing to Access. (Sorry; it's been a long day.)
To do this, I use the strongly-typed datasets. You can
define one through the DataSet designer.

This goes through and changes all the entries in a table.
(I was just mucking around.) Then it adds 2 rows to the
table just for the heck of it.

Dim ds As CarriersDataSet = New CarriersDataSet
Dim adapter As CarriersTableAdapter = New CarriersTableAdapter
adapter.Fill(ds.Carriers)
For Each dr As DataRow In ds.Tables(0).Rows
Dim Carrier As String = dr.Item("c_Carrier").ToString
Console.WriteLine(String.Format("Carrier = '{0}'", Carrier))
Carrier &= "_x"
dr.Item("c_carrier") = Carrier
Next
'one way to add a row
Dim dr2 As DataRow = ds.Carriers.NewRow()
dr2("c_Carrier") = "Robin"
dr2("c_serv_ctr_code") = "RRR"
ds.Carriers.Rows.Add(dr2)
'another way to add a row
ds.Carriers.Rows.Add("SSS", "Scott")
'update the database with the changes to the dataset
adapter.Update(ds)
ds = Nothing

Again, this is VB2005/.Net2.0.

Robin S.
--------------------------------
 
gordy said:
Hey all,

I have a fairly simple app which goes out to the web to download data.
I want to store this data in a database (1 table, ~8 fields or so). My
program is written in VB.net and works fine to get the data. My
question is, how do I get the data into a database? I want to use
Microsoft Access.

I have seen several articles on using VB.net and ADO.net or Jet to read
data in, but I haven't seen anything to write data out. Can anyone
suggest articles? Thanks,

Brian
Click to expand...

Add reference - adodb

'Database variables
Dim sDataPath As String
Dim sAccessDB As String
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String

'Assign the Database
sDataPath = "C:\MyAccessDB.mdb"
sAccessDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
sAccessDB = sAccessDB & sDataPath
sAccessDB = sAccessDB & ";User Id=admin;Password=;"

'Open the Access DB connection
Conn = New ADODB.Connection
Conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Conn.ConnectionString = sAccessDB
Conn.Open()

'Write entry to the Access DB
rs = New ADODB.Recordset
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
sSQL = "SELECT TOP 1 * FROM tblTableName"
rs.Open(sSQL, Conn, ADODB.CursorTypeEnum.adOpenStatic, _
ADODB.LockTypeEnum.adLockPessimistic)
rs.AddNew()
rs("Field1Name").Value = "Whatever"
rs("Field2Name").Value = "Whatever"
rs("Field3Name").Value = "Whatever"
rs("Field4Name").Value = "Whatever"
rs("Field5Name").Value = "Whatever"
rs("Field6Name").Value = "Whatever"
rs.Update()
rs.Close()
rs = Nothing

'Close the Database connections
Conn.Close()
Conn = Nothing
 
Gordy,

Create like you see in the samples from the others a databaseconnection and
a oledbcommand.

However don't use an datatable, that is far to much work.

If it is only inserting than create an SQL transact Insert command something
as

Insert into mytable (a,b) as (myId, myfield)

And use than the command.executeNonQuery to process that.

If you want to update tables, than use the sample given by the others.

Cor
 
I think there are a few million users that would disagree with you
Aaron.
But I see you are spouting your grand words of unqualified wisdom here
like you are in the VB is dead thread also.
 
Back
Top