Write to an Access database

[Guest]

I have code that reads and parses a text file using a schema.ini file. Works
great. When I see the dataGrid it's exactly what I want.

Dim CString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\;" & _
"Extended Properties=""text;HDR=No;"""

Dim TConnect As New System.Data.OleDb.OleDbConnection(CString)

TConnect.Open()

Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from
837P.txt", TConnect)

Dim ds As New DataSet("Bananas")

da.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)

I now want to take this table from the dataGridVew and write it out to my
Access database.

I have code to connect to the db. I can run delete queries against it but I
can't figure out how to add this data to it. I've been all over the internet
and I get close but no joy.

Here's the code I have for the Access half:

Dim objConnection As New OleDbConnection(accessConnect)
'Dim strSQL As String = "Select * INTO z FROM " & table & ";"
Dim strSQL As String = "DELETE text_table.* FROM text_table;"
Dim objCommand As New OleDbCommand(strSQL, objConnection)
Dim objDataAdapter As New OleDbDataAdapter(objCommand)
Dim objDataTable As New Data.DataTable("text_table")
Dim objDataRow As DataRow
Dim intRowsAffected As Integer

Try
'open db connection
objConnection.Open()
intRowsAffected = objCommand.ExecuteNonQuery()

Catch oledbexceptionerr As Exception
MessageBox.Show(oledbexceptionerr.Message)
End Try

objConnection.Close()

Is there a way to just tweak this code to get it to write my table to
"text_table" in my db?

 

[Jim]

To clarify: what I think I need is a way to construct the SQL to
reference the ds.tables(0) below is something like "SELECT * INTO
text_table FROM dsTable" Where Dim dsTable dataTable = ds.tables(0),
but I can't get that to work.

I saw on one helpful post that an SQL to update the db was all that's
needed. HA!

Jim

P.S. I've seen the question asked in other posts but I've never seen a
direct answer. Please don't send me off to a reference on the topic.
That's what I've been looking at for the last 8 hours and still can't
get it. Maybe its just me and its too late and I'm too tired. :-( oh
oh... self pity is starting to creep in. I'm out of here.

 

[Cor Ligthert [MVP]]

Jim,

In Net you are mostly working with binded controls. The DataGridView is a
complex databinded control.

You can work with strongly typed datasets (datatable) and with non strongly
typed ones, let say basic ones.

In the area of the strongly typed datasets we see every time much more
progress. As example in the time of Net 1.x everybody was talking here only
about non strongly typed ones, now it becomes more and more strongly typed
ones.

However nothing wrong how you did it, however you have now to build your own
Update, Insert and Delete SQL statemements.

But you can also use the OleDBCommandbuilder a command wich is disliked by
many people however is in my idea perfect as long as that your updates are
as simple that they come in fact from a grid.

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbcommandbuilder.aspx

I hope this helps,

Cor

 

[Jim]

Cor,

Thanks for the insight. I've looked at your reference and I'm still
somewhat confused. I'm a newbie in .net. I've read other posts where
it was said "just build the SQL statements". I can't see how to do that
nor can I see how the OleDBCommandbuilder will help do that. I know I'm
missing something simple here.

Can someone give me a short example of what that SQL statement might
look like based on my code below?

Jim

 

[dbahooker]

CORRECTION!

Jet shouldn't be used by anyone for any reason

SERIOUS KID
move to SQL Server

 

[dbahooker]

select from where

or insert select
or insert values

or delete from table where

or update table set

 

[Jim]

That I could do. The problem is I'm working from:
See below for details. I can't figuer out how to reference the table.

 

[dbahooker]

you don't need an INTO statement if you're just referencing a sql
statement in ADO.net

an INTO statement will run a 'make table query'


hth

 

[Jim]

OK so what DO I need?

Coming from here:

<snip> ==== This Works Fine ====
Dim CString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\;" & _
"Extended Properties=""text;HDR=No;"""
Dim TConnect As New System.Data.OleDb.OleDbConnection(CString)
TConnect.Open()
Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from
837P.txt", TConnect)
Dim ds As New DataSet("Bananas")
da.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
<snip>

How do I get that ds.tables(0) into a table here?
Dim AString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\abc.mdb;"


Sorry I'm the newbie. I just can't see how to that dataset(Bananas).

 

[dbahooker]

you just need to fire a SQL Statemnet

Dim strSql as string
strSql = "BULK INSERT TblName FROM 'C:\MyPath.txt'

You can lookup more about it in SQL Server books online

 

[dbahooker]

and about the whole data adapter thing; I don't believe in ADO.net--
It doesnt' allow you to share a connection (two recordsets on the same
connection) and it doesn't allow you to keep a connection open.

To me? That's a worthless Data Access Library.
I'd rather use DAO or ADO Classic

 

[RobinS]

Try posting your question to microsoft.public.dotnet.framework.adonet.

And watch out for advice from dbahooker; he is a known troll in this
newsgroup.

Robin S.
=======================

 

[dbahooker]

ROFL

a known troll.. like it is a bad thign

I stand up for freedom of speech and freedom from buggy software
I stand up for a consistent marketing message

and I stand up for VB

everyone else around here is a big fat lazy wuss.. oh are the widdle
baby programmers AFWAID OF DEMANDING EXCELLENCE?