Advice on developing application

[TedMosby]

I'm new to VB.net and am looking for some advice. To start with I need to develop something that I can import 3 different csv files into and then once I've created a query then return my results in a datagrid view. I'm not sure where to start with how I can create the form to be able to select the csv files and then import into an access mdb. I have to use this as we don't have SQL Server and can't do it all in Access as we don't have licences for that either (only certain people have it)

1.How could I create a form in VB.net to be able select the csv files
2.would it be best to import directly into mdb or show in VB first
3. Once imported how can I return a dataset from access into a VB datagridview?

Thanks :)

[codeorder]

1. Use a OpenFileDialog with .MultiSelect=True .

Dim myCoolOpenFileDialog As New OpenFileDialog
        With myCoolOpenFileDialog
            .Title = "Hold CTRL key while selecting a file to select multiple files at once..."
            .Filter = "CSV Files (.csv)|*.csv"
            .Multiselect = True
            If .ShowDialog = DialogResult.OK Then
                For Each selectedFile As String In .FileNames
                    MsgBox(selectedFile) '// get FullPath of each file.
                Next
            End If
        End With

2. If there is no need for it to be loaded in a Form, then there is no need.
3. Not my specialty, good luck. :)

Hope this helps.

[TedMosby]

Thanks for that :) I have now got a form that will enable me to select the files. I have the database built too. My question now is that once I have selected the files needed to import (3files always going to be the same3) how can I import them into Access?

With the code being a multi select how is possible to import into multiple tables, are there any demo ways of it being done?

[Momerath]

I have to use this as we don't have SQL Server and can't do it all in Access as we don't have licences for that either (only certain people have it)

More of a side note, but SQL Server 2008 R2 Express is free and better than using Access.

[codeorder]

>>With the code being a multi select how is possible to import into multiple tables, are there any demo ways of it being done?

How do you plan to select which table to load from what file?
.By file name?
.By file content?

If any of those 2 options, in the For/Next loop that displays the MsgBox, instead of displaying the fullPath of the file, load it in each table as needed.

[TedMosby]

Do you mean, use the filename? Then if the file was selected in a different order it could still easily import into the correct table

My question was to find how I can do this, how do you tell it which file to import and then how can I write the import code? That’s what I was asking if there are any demo ways of it being done

[codeorder]

See if this helps.

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim myCoolOpenFileDialog As New OpenFileDialog
        With myCoolOpenFileDialog
            .Title = "Hold CTRL key while selecting a file to select multiple files at once..."
            .Filter = "CSV Files (.csv)|*.csv"
            .Multiselect = True
            If .ShowDialog = DialogResult.OK Then
                For Each selectedFile As String In .FileNames
                    loadCSVfileToTable(IO.Path.GetFileName(selectedFile)) '// send only the FileName and .Extension to your load CSV Sub.
                Next
            End If
        End With
    End Sub

    Private Sub loadCSVfileToTable(ByVal myCoolFile As String)
        Select Case myCoolFile.ToLower '// .ToLower in case the FileName and .Extension might be upper or lower case.
            Case "1.csv" '// FileName set to LowerCase here.
                MsgBox("loading file 1.csv into table 1") '// add code here to load the file into selected table.
            Case "2.csv"
                MsgBox("loading file 2.csv into table 2") '// add code here to load the file into selected table.
            Case "3.csv"
                MsgBox("loading file 3.csv into table 3") '// add code here to load the file into selected table.
        End Select
    End Sub

You could use another Parameter in the load CSV Sub for your Table and specify which table to load into to.
Something as:

Private Sub loadCSVfileToTable(ByVal myCoolFile As String, ByVal myCoolTableToUse As DataTable)

..and this in your OpenFileDialog.

For Each selectedFile As String In .FileNames
                    Select Case IO.Path.GetFileName(selectedFile).ToLower '// .ToLower in case the FileName and .Extension might be upper or lower case.
                        Case "1.csv" '// FileName set to LowerCase here.
                            loadCSVfileToTable(selectedFile, table1)
                        Case "2.csv"
                            loadCSVfileToTable(selectedFile, table2)
                        Case "3.csv"
                            loadCSVfileToTable(selectedFile, table3)
                    End Select
                Next

[TedMosby]

Thanks for this :)

I've tried it and have a question...

Why would the Private Sub LoadCSVFileToTable not display the Msgbox when selected?

I've used the code

Dim myCoolOpenFileDialog As New OpenFileDialog
        With myCoolOpenFileDialog
            .Title = "Hold CTRL key while selecting a file to select multiple files at once..."
            .Filter = "CSV Files (.csv)|*.csv"
            .Multiselect = True
            If .ShowDialog = DialogResult.OK Then
                For Each selectedFile As String In .FileNames
                    loadCSVfileToTable(IO.Path.GetFileName(selectedFile)) '// send only the FileName and .Extension to your load CSV Sub.
                Next
            End If
        End With
    End Sub

    Private Sub loadCSVfileToTable(ByVal myCoolFile As String)
        Select Case myCoolFile.ToLower '// .ToLower in case the FileName and .Extension might be upper or lower case.
            Case "1.csv" '// FileName set to LowerCase here.
                MsgBox("loading file 1.csv into table 1") '// add code here to load the file into selected table.
            Case "2.csv"
                MsgBox("loading file 2.csv into table 2") '// add code here to load the file into selected table.
            Case "3.csv"
                MsgBox("loading file 3.csv into table 3") '// add code here to load the file into selected table.
        End Select
    End Sub

and then created some text files named 1.csv, 2.csv & 3.csv.

When the File dialog box opens, I select the 3 files and click on Open and nothing happens. The OpenDialog box closes and returns me to my form.

I want to understand what its doing before I add the import to tables code.

[Barbarian59]

Maybe the filename you send to the loadCSVfileToTable sub does not match one of the cases

Another question is, where do you need the database for if you want to show some output in a datagrid?

[TedMosby]

Maybe the filename you send to the loadCSVfileToTable sub does not match one of the cases

The csv files are named exactly the same in the code. I put a MsgBox on the code after the line loadCSVfileToTable(IO.Path.GetFileName(selectedFile)) as I wanted to see what the filename was that was selected.

How can I fix this?

Another question is, where do you need the database for if you want to show some output in a datagrid?

I need the database as because when I have imported the 3 files into the database I will being doing some additional work and then my idea of the datagridview was to show what my output.

[codeorder]

>>When the File dialog box opens, I select the 3 files and click on Open and nothing happens.

Works here just fine. Try in a new project if not new project already, otherwise, good luck.

[TedMosby]

Works here just fine. Try in a new project if not new project already, otherwise, good luck

I've tried it in another project and all works well. Wierd!! Thanks :)

Ive got hold of a spare PC now so I'm going to install SQL Server 2008 Express instead of using Access so that will be better.

Would the best thing now to create Stored Procedures and run the insert into table code that way be best or not?

[TedMosby]

I've started to look at how I can Execute a Stored Procedure from within the code.

This is what I have so far

Imports System.IO
Imports System.Configuration
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlDbType

Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click
        Dim myOpenFileDialog As New OpenFileDialog
        With myOpenFileDialog
            .Title = "Hold CTRL key while selecting a file to select multiple files at once..."
            .Filter = "CSV Files (.csv)|*.csv"
            .Multiselect = True
            If .ShowDialog = DialogResult.OK Then
                For Each selectedFile As String In .FileNames
                    loadCSVfileToTable(IO.Path.GetFileName(selectedFile)) '// send only the FileName and .Extension to your load CSV Sub.
                    'MsgBox(selectedFile)
                Next
            End If
        End With
    End Sub

Private Sub loadCSVfileToTable(ByVal myFile As String)
        Select Case myFile
            Case "MyCSVFile.csv"
                MsgBox("loading file into table 1")
                Dim cn As New SqlConnection
                Dim cmd As New SqlCommand
                cn.ConnectionString = "Data Source=MyServer;Initial Catalog=MyDatabase;User Id=MyUserID;Password=MyPwd;"
                cn.Open()
                cmd.Connection = cn
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "ps_CSVFileImport"
                cmd.Parameters.AddWithValue("@pCSVFile", myFile)
                MsgBox(myFile)
                cmd.ExecuteNonQuery()
                cn.Close()
                MsgBox("File load is complete")
                'Case "LincsPCTInvestigations.csv"
                '    MsgBox("loading file into table 2")
                'Case "LincsPCTDischarge.csv"
                '    MsgBox("loading file into table 3")
        End Select
    End Sub

The problem I have is that the code errors on cmd.ExecuteNonQuery() with error

'Procedure or function 'ps_CSVFileImport' expects parameter '@pFilePathName', which was not supplied.'

Where have I gone wrong?

[adam_k]

Sounds like your sproc is expecting the @pFilePathName parameter, which you are not providing.
It could just be that you've declared the filename as @pFilePathName in your sproc and you are passing it as @pCSVFile in your VB.

[TedMosby]

@pFilePathName Is declared in my SP why would I put this into my VB code?

I want to pass a variable in my VB code to link it to the ps_CSVFileImport so that it would be ps_CSVFileImport & @VariableHere ( I want my variable to be the filename of the csv selected in the OpenFileDialog box

[adam_k]

If you've created the procedure like this:

CREATE  PROCEDURE ps_CSVFileImport @pFilePathName VARCHAR(50) ...

you are expected to run the procedure like

exec ps_CSVFileImport @pFilePathName = 'c:\something\something.txt'

or like

exec ps_CSVFileImport 'c:\something\something.txt'

provided that you state the parameters with the same order as declared in your sproc.

The fact that you are using

cmd.Parameters.AddWithValue("@pCSVFile", myFile)

Means that you are passing a parameter named @pCSVFile.
The error means that your procedure was waiting for a parameter named @pFilePathName.

In order for your code to work, you have 3 options:
a) change your procedure to expect a parameter called @pCSVFile
b) change your VB.NET code to pass the parameter as @pFilePathName
c) don't use commandtype and parameters, but execute your procedure like this:

cmd.Connection = cn
cmd.CommandText = "exec ps_CSVFileImport '" & myfile.tostring &"'"  'or = "exec ps_CSVFileImport @pFilePathName='" & myfile.tostring &"'"
MsgBox(myFile)
cmd.ExecuteNonQuery()

[TedMosby]

Thanks for that :) I'll give it a go and see how I get on