Import ascii and .dat tables into access

Hello,
  Before I yank any more hair out tryign to figure this one out I figured I would ask the experts... I'm new to vb.net and am trying to write a program that will allow for end users to navigate to 2 files, *.asc and *.dat each with its own openfiledialog and inport the data in these files to tables that are created in an access database...
Any help if much appreciated
TIA
John

Below is some of the code i am Using to get the files into a datagrid, but the .dat fiel is generating an error that oledbexception was unhandled. after I get the data in the datagrid, how do i ge tit into the access table i want...

Code snipit...

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim catl = New ADOX.Catalog
        Dim sDBName As String
        '        Dim sDBPass As String
        Dim sAppPath As String
        Dim dbPath As String
        Dim ADOXtable1 As New ADOX.Table
        Dim ADOXTab2 As New ADOX.Table
        Dim ADOXTab3 As New ADOX.Table
        Dim ADOXindex As New ADOX.Index
        Dim Col As New ADOX.Column
        Dim Col2 As New ADOX.Column
        Dim Col3 As New ADOX.Column

        sAppPath = System.Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)
        dbPath = "&sAppPath & sDBName"
        sDBName = "//PntCon_V2.mdb"
   
Private Sub btnASCII_Impt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnASCII_Impt.Click
        OpenFileDialog1.Title = "Select ASCII File to be converted"

        OpenFileDialog1.Filter = "ASCII File (*.txt)|*.txt"
        OpenFileDialog1.ShowDialog()


        Dim sFilePath1 As String
        sFilePath1 = OpenFileDialog1.FileName
        If sFilePath1 = "" Then Exit Sub
        txtAscII.Text = sFilePath1
        'Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath1 & ";Extended Properties=text;"
        'Dim objConn As New OleDbConnection(sConnectionString)
        'objConn.Open()
        Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\; Extended Properties=Text;"
        Dim objConn As New OleDbConnection(sConnectionString)
        objConn.Open()
        Dim objCmdSelect As New OleDbCommand("SELECT * FROM " & sFilePath1 & "", objConn)
        Dim objAdapter1 As New OleDbDataAdapter()
        objAdapter1.SelectCommand = objCmdSelect

        Dim objDataset As New DataSet()
        objAdapter1.Fill(objDataset, "Pnts_DT")
        DataGrid1.DataSource = objDataset.Tables(0).DefaultView
        objConn.Close()
    End Sub

    Private Sub btnOutputLoc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOutputLoc.Click
        SaveFileDialog1.Title = "Select ASCII File to be Saved"

        SaveFileDialog1.Filter = "ASCII File (*.asc)|*.asc"
        SaveFileDialog1.ShowDialog()

        'Exit if no map document is selected
        Dim sFilePath2 As String
        sFilePath2 = SaveFileDialog1.FileName
        If sFilePath2 = "" Then Exit Sub
    End Sub

    Private Sub btnCLine_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCLine.Click
        'Open a file dialog for selecting map documents
        OpenFileDialog2.Title = "Select C-Line File (Must have file extension .txt)"

        OpenFileDialog2.Filter = ".dat file (*.dat)|*.dat"
        OpenFileDialog2.ShowDialog()

        'Exit if no map document is selected
        Dim sFilePath2 As String
        sFilePath2 = OpenFileDialog2.FileName
        If sFilePath2 = "" Then Exit Sub
        txtCline.Text = sFilePath2
        Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=Text;"
        Dim objConn2 As New OleDbConnection(sConnectionString)
        objConn2.Open()
        Dim objCmdSelect2 As New OleDbCommand("SELECT * FROM " & sFilePath2 & "", objConn2)
        Dim objAdapter2 As New OleDbDataAdapter()
        objAdapter2.SelectCommand = objCmdSelect2

        Dim objDataset2 As New DataSet()
        objAdapter2.Fill(objDataset2, " Clin")
        DataGrid2.DataSource = objDataset2.Tables(0).DefaultView
        objConn2.Close()
    End Sub
JohnEddinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PockyMasterCommented:
So try to catch your exception.
0
JohnEddinsAuthor Commented:
How would you suggest doing that?
0
PockyMasterCommented:
you might put a try/catch around your code, and see what's inside your exception.. if you post the exact message here, it gets more clear what's your problem
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

JohnEddinsAuthor Commented:
here is a copy of the text in the .dat file (which i have converted to .txt to try and get working)

the entire contents

10
1
87
265
289
315
343

This is generating the OLEDBexception every time and i have no clue why , I am going to read up on the try/catch method now to see if that helps
0
JohnEddinsAuthor Commented:
Well, I gave the try/catch shot and the code ran successfully, but the error was just bypassed... any ideas on what I need to do to correct this problem? The program creates a datbase in a locationa along with 3 tables, it is then going to insert the .dat file and the .asc file into the corrosponfding table, which i am then going to run several trig functions against to generate an output file.
   Suggestions or ideas on how what the root of the error may be and what I need to do to fix it? Thanks
0
JohnEddinsAuthor Commented:
Here is the excption log:
>>>
System.Data.OleDb.OleDbException was unhandled
  ErrorCode=-2147217900
  Message="Syntax error in FROM clause."
  Source="Microsoft JET Database Engine"
  StackTrace:
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
       at Duke_GPSOffset.Form1.btnCLine_Click(Object sender, EventArgs e) in C:\Projects\Energy\Duke\Trans_buffer\Design_2\Duke_GPSOffset\Duke_GPSOffset\Form1.vb:line 72
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at Duke_GPSOffset.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
0
PockyMasterCommented:
Uhm, so your FROM query is wrong.
What I suspect that's going wrong:
You should provide the folder of your file in your connectionstring
and the filename in the FROM query.

Now you're providing c:\ as the folder
So it will work if your sFilePath contains e.g. test.txt
but when I interpret your code, it's containing the filename from the OpenFileDialog, which is a filename as FullPath.

use this to get the path for your connection string
Dim sPath as string =System.IO.Path.GetDirectoryName(sFilePath1 )
use this to get the filename for your query
dim sFileName as string =        System.IO.Path.GetFileName(sFilePath1)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnEddinsAuthor Commented:
okay, here is what I tried to do...

        Dim sPath As String = System.IO.Path.GetDirectoryName(sFilePath2)
        ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sPath & " ;" & "Extended Properties=""Text;HDR=No;"""

        Dim TextConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
        TextConnection.Open()

        Dim sFileName As String = System.IO.Path.GetFileName(sFilePath2)
        Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " & sFileName & "", TextConnection)
        ' Try
        Dim ds As New DataSet("TextFiles")
        da.Fill(ds, "ta")
        DataGrid2.DataSource = ds.Tables(0).DefaultView

And I am still reciving the Syntax error in the from clause of the < da.fill(ds, "ta") > Line
Any thoughts?
0
PockyMasterCommented:
hmm...working like a charm here... I copy/pasted your new code into a project, and tried a random CSV file I had.
What kind of encoding you are using in your source file?
0
JohnEddinsAuthor Commented:
not sure i understand the question as far as encoding (Do you mean txt or ascii format?) Let me thorw on more thing at you real qucik, do you think the imports I have could be presenting a problem?

Option Explicit On
Imports System.Data.odbc
Imports ADOX
Imports ADODB
Imports System.Data
Imports System.Data.OleDb
Imports System
Imports System.IO
Imports System.Collections
Imports System.Text
Imports VB = Microsoft.VisualBasic
Imports System.Math

Thanks,
 
0
PockyMasterCommented:
Hmm.. where you need all these imports for?
Are you using ADOX and ADODB and odbc as well?

Usually you don't need to import system explicitly.
Your environment puts default references in your solution already.
Also the Microsoft.VisualBasic I would avoid, since it's mostly used out of compatibility with old stuff, and you no longer need it.
Try to find your equivalents in the .NET framework
0
JohnEddinsAuthor Commented:
PockyMAster, I am eternaly greatfull... you are the man (or woman...) anyhow, thanks for the help, The encoding was the...  you earned your points and thanks again!

On a side note, do you have any code sniptis floating around for inserting the records from a data grid (like the one above) in an access database table?
0
PockyMasterCommented:
No sorry, I haven't got any snippets.. most is from memory..
But I recall some people ask the same question last week around here.. So it should be easy to find it :D
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.