Write to an Access database

4 May 2007 12:07 AM

Jim

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?

4 May 2007 12:51 AM

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.

Jim wrote:
Show quoteHide quote

> 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 dsTable dataTable = ds.tables(0)
>
> 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?
>
>

4 May 2007 1:14 AM

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

Show quoteHide quote

"Jim" <J**@discussions.microsoft.com> schreef in bericht
news:55F7658A-E81C-4ACD-8F0B-F756BD45F2D5@microsoft.com...
>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?
>
>

4 May 2007 1:22 PM

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

Cor Ligthert [MVP] wrote:
Show quoteHide quote

> 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" <J**@discussions.microsoft.com> schreef in bericht
> news:55F7658A-E81C-4ACD-8F0B-F756BD45F2D5@microsoft.com...
>> 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?
>>
>>
>
>