Writing to a MS access db from visual studio .net

19 Dec 2006 6:37 PM

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

19 Dec 2006 7:31 PM

CribbsStyle

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.

gordy wrote:
Show quoteHide quote

> 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

19 Dec 2006 11:42 PM

RobinS

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.
------------------------------
Show quoteHide quote

"gordy" <bp***@drexel.edu> wrote in message
news:1166553451.444061.9220@i12g2000cwa.googlegroups.com...
> 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
>

19 Dec 2006 11:55 PM

RobinS

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.
--------------------------------
Show quoteHide quote

"RobinS" <RobinS@NoSpam.yah.none> wrote in message
news:YMudnVcMqrFP5RXYnZ2dnUVZ_oqmnZ2d@comcast.com...
> 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.
> ------------------------------
> "gordy" <bp***@drexel.edu> wrote in message
> news:1166553451.444061.9220@i12g2000cwa.googlegroups.com...
>> 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
>>
>
>

20 Dec 2006 1:08 AM

Greg

Show quote Hide quote

"gordy" <bp***@drexel.edu> wrote in message
news:1166553451.444061.9220@i12g2000cwa.googlegroups.com...
> 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

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

20 Dec 2006 5:26 AM

Cor Ligthert [MVP]

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

Show quoteHide quote

"gordy" <bp***@drexel.edu> schreef in bericht
news:1166553451.444061.9220@i12g2000cwa.googlegroups.com...
> 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
>