Advice on developing application

JezLisle · #1 (**permalink**) 06-07-11, 18:42

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?

JezLisle · #2 (**permalink**) 06-09-11, 17:49

Has anyone any ideas of how I can approach this?

OpK · #3 (**permalink**) 06-15-11, 15:30

1. sr = New StreamReader (This allows you to select the files)
2. Just populate a collection then use it how you want.
3. Once you add the dataset just bind to the data. (easiest way is drag and drop your table into the form, are you using Visual Web Developer?) Then mod for your exact use.

Not sure exactly what you want to do. No database just flat csv?

rokslide · #4 (**permalink**) 06-15-11, 17:50

Not sure exactly what you are trying to achieve here. Do you want to keep the imported contents of the files in memory?? and query that??

Assuming this is what you are after....

You should be able to use the ReadAllLines method to pull in this data, google on that and you should get some good starters.

If you create an in memory DataTable (google for examples) you could then process each line of your file and convert it into a row in the table.

Once all your rows are in the table you should be able to use the Select method on the DataTable to bring back a collection of rows (or you might need to use a DataView) which you can bind to your DataGrid.

JezLisle · #5 (**permalink**) 06-22-11, 09:36

Thanks for the replies...

I created a form to have a button for a FileOpenDialog box. I have given this the MultiSelect option so that I can have all my csv file import at the same time.

This is my code below. The problem I have is that when I run the code to execute the Stored Procedure it tells me that I havent add the parameters the SP needs. I have included it in my code but cant see where I have gone wrong.

Where have I gone wrong?

Code:

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

Public Class Form3

    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(selectedFile)
                Next
            End If
        End With
    End Sub

    Private Sub loadCSVfileToTable(ByVal myFile As String)
        Select Case IO.Path.GetFileName(myFile)
            Case "file1.csv"
                MsgBox("loading file into table 1")
                Dim cn As New SqlConnection
                Dim cmd As New SqlCommand
                cn.ConnectionString = "Data Source=MyServer;Initial Catalog=MyDB;User Id=MyUID;Password=MyPwd;"
                cn.Open()
                cmd.Connection = cn
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "ps_ReportImport"
                Dim param = New SqlParameter(myFile, SqlDbType.VarChar)
                cmd.Parameters.Add(param)
                cmd.ExecuteNonQuery()
                cn.Close()
                MsgBox("File load is complete")
                'Case "file2.csv"
                '    MsgBox("loading file into table 2")
                'Case "file.csv"
                '    MsgBox("loading file into table 3")
        End Select
    End Sub

End Class

OpK · #6 (**permalink**) 06-22-11, 10:03

Can you post the error details form the error in the bottom window? Also can you tell us the exact line it throws the error? If you don't have the line then go to Tools> Options > General> Source View> Checkmark Line Numbers.

Assuming your using 2010.

If you also mouse over when it throws the error is it showing the collection polulated with data? Is it throwing the error in the button click sub or the Load CVS sub?

Let me know I can make alot of assumptions just looking at it but its best to go one line at a time. Do you use breaks at all yet?

-Mike

JezLisle · #7 (**permalink**) 06-22-11, 10:57

Quote:

Originally Posted by OpK

Can you post the error details form the error in the bottom window? Also can you tell us the exact line it throws the error? If you don't have the line then go to Tools> Options > General> Source View> Checkmark Line Numbers.

Assuming your using 2010.

If you also mouse over when it throws the error is it showing the collection polulated with data? Is it throwing the error in the button click sub or the Load CVS sub?

Let me know I can make alot of assumptions just looking at it but its best to go one line at a time. Do you use breaks at all yet?

-Mike

I am using VS2010, am new to it, so getting used to how it works, I used to use Access before

This is a copy of what the error is

System.Data.SqlClient.SqlException was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=0
Message=Procedure or function 'ps_ReportImport' expects parameter '@pFilePathName', which was not supplied.
Number=201
Procedure=ps_ReportImport
Server=MyServer
Source=.Net SqlClient Data Provider
State=4

I tried to follow your instructions in terms of line numbers but doesnt match what you had written

OpK · #8 (**permalink**) 06-22-11, 12:41

I started with ms access files too. Are you developing an Application or a Web Application? In either case here is an example of references for the SQL query and how it knows where the location of the data is. In this case this is a Web Application and in order to link the data in the Update or Insert it has to reference the web form with the @, hope it helps but let me know if you aren't developing a web app. These are the update and insert parameters and the NAME and ID in the parameters links the data to the correct web form. How big is the file you are importing? How many entries? I think you might want to read a line at a time and then use a HiddenField to reference or loop through. Depending on the amount of data you want to import and how often it will occur you would want to do it a little differently.

The code in the page:
<asp:TextBox ID="WeightBox1" runat="server" Width="55px" AutoPostBack="True"
TabIndex="5" BorderStyle="Inset"></asp:TextBox>

The code for the query and update/insert:

<asp

qlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:utsahealthConnectionString1 %>"
ProviderName="<%$ ConnectionStrings:utsahealthConnectionString1.Prov iderName %>"
SelectCommand="SELECT *
FROM [tbl_users]
INNER JOIN [tbl_anth]
On tbl_users.h_studyid=tbl_anth.h_studyid
WHERE tbl_users.h_studyid = @studyid AND tbl_anth.h_measuretype = @measuretype"
UpdateCommand="UPDATE [tbl_anth] SET [h_weight1] = @h_weight1 WHERE [h_studyid] = @form_studyid AND [h_measuretype] = @measuretype"

InsertCommand="INSERT INTO [tbl_anth] h_weight1) VALUES @weight1)">

<SelectParameters>

<asp:ControlParameter Name="studyid" ControlID="TextBox1" Type="String" PropertyName="Text" />
<asp:ControlParameter Name="measuretype" ControlID="RadioButtonList1" Type="String" PropertyName="SelectedItem.Text" />
</SelectParameters>

<UpdateParameters>
<asp:ControlParameter Name="h_weight1" ControlID="WeightBox1" Type="String" PropertyName="Text" />

</UpdateParameters>

<InsertParameters>
<asp:ControlParameter ControlID="WeightBox1" Name="weight1" Type="String" PropertyName="Text" />
</InsertParameters>
</asp

qlDataSource>

The ControlID is the ID of the asp text box ID then the @ is the Name of the Parameter, these tie the two togethor. In the UPDATE I used "@h_weight1", for the INSERT I used "@weight1". Kinda of a good example in this instance but I should have used "@weight1" in both locations but it is simply a pointer to reference the variables within the web control.

JezLisle · #9 (**permalink**) 06-22-11, 17:36

I'm developing an Application not a Web App.

What I have so far is a form that loads on Open which then the user will click to select files. An OpenDialog box appears in which the user can select the files needed to import into the DB. This will be done daily. The users will be getting the data from external sourcs which needs to be loaded into my Application.

From that I have the OpenFileDialog box. Once the user clicks OK to select the files it then looks at the file name and then determines which table it will be imported into. The StoredProc I have in the code at the mo is what I want to run as this will then tell the CSV where it will be going.

My issue is that I'm not quite sure on how I can get the variable added to my StoredProc name. If I run my StoredProc through SSMS it looks like

EXEC ps_ReportImport 'C:\Files\file1.csv'

This works perfect first time everytime. I want to write in my code this. but I want the variable to be added instead. My variable is the filename that I pick up from the OpenFileDialog box. Once it has done that I can then repeat the process for the next 2 files

Hope this makes it clearer in what I'm trying to do

Teddy · #10 (**permalink**) 06-22-11, 17:49

You should be able to find A LOT of tutorials for this.

Here's one:

Importing CSV Data and saving it in database - CodeProject

You can find even more for executing stored procedures. Depends on what you like to use for data access. If you're using the regular ADO.NET stuff, google for "vb.net stored procedure".

If you get stuck on something you find in one of the tutorials let us know. It will be a lot more efficient to walk through a tutorial custom tailored to take you from zero knowledge to functional and have us fill in the gaps where needed than for us to write a brand new introductory tutorial for you in piecemeal thread format.

Sidenote: It's not clear what you intend to do with the file path you're passing to the rs_ReportImport stored procedure. If you're going to look for a file at c:\file\file1.csv from inside your stored procedure, be aware that stored procedures execute on the database server, not the client. If your users are executing that stored procedure from their own computer and the database is running on a server somewhere, it will not find that file. You can observe this behavior by using another computer and connecting to your current database with SSMS. Not sure if that's what you're trying to do...

JezLisle · #11 (**permalink**) 06-22-11, 18:01

Thanks for that I'll have look

On the Sidenote I want to take the path of where the csv file is stored and add it to my StoredProc so it will be ps_ReportImport 'Location of the file1.csv' The files the users will add will be stored on a file server when I deploy the App, but for the time being my test file is on the C:\file location. When I have tested the file location (fileserver location) through SSMS it works and I just want to replicate that through the VB App

rokslide · #12 (**permalink**) 06-22-11, 18:23

To find out what is happening put a breakpoint in your code by clicking in the left margin.

This will cause the program to pause and let you examine the data in your variables.

I suspect you need to create your parameter using the parameter name (so it knows where to map the value to). There should be an overload of the new SqlParameter that will accept this. Actually looking over the examples I have on hand it should be

Code:

Dim param = New SqlParameter("@pFilePathName", myFile )
param.SqlDbType = SqlDbType.VarChar
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
cn.Close()

Currently are trying to pass a parameter called "file1.csv" which doesn't exist in your proc.

JezLisle · #13 (**permalink**) 06-23-11, 17:44

Quote:

Originally Posted by rokslide

To find out what is happening put a breakpoint in your code by clicking in the left margin.

This will cause the program to pause and let you examine the data in your variables.

I suspect you need to create your parameter using the parameter name (so it knows where to map the value to). There should be an overload of the new SqlParameter that will accept this. Actually looking over the examples I have on hand it should be

Code:

Dim param = New SqlParameter("@pFilePathName", myFile )
param.SqlDbType = SqlDbType.VarChar
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
cn.Close()

Currently are trying to pass a parameter called "file1.csv" which doesn't exist in your proc.

Thanks for that it worked far better than it was... My problem now is how can I get the ("@pFilePathName", myFile ) to show single quotes around it so that when it builds the EXEC statement in StoredProc is will run as it would if to run through SSMS

Teddy · #14 (**permalink**) 06-23-11, 17:57

It should do that automatically. The reason you need single quotes in SSMS is because it doesn't have any way of knowing where the parameter starts and ends otherwise. when you're using a SqlCommand with a SqlParameter in .NET, it doesn't need the single quotes because it knows the entire value you passed is to be treated as a single VARCHAR parameter.

In other words this in TSQL:

Code:

exec sp_SomeProcedure 'MyParameterValue'

is equal to this in .NET:

Code:

...
Dim myParamValue As String = "MyParameterValue"
Dim param = New SqlParameter("@pFilePathName", myParamValue)
...

which is also the same as this in TSQL:

Code:

DECLARE @MyParameterValue VARCHAR(50)
SET @MyParameterValue = 'MyParameterValue'

EXEC sp_SomeProcedure @MyParamaterValue

The single quotes in TSQL act like the double quotes in VB, it's just a way of saying "the stuff in the middle is what I want you to care about". Stored procedure arguments can be used directly without the quotes because SQL can tell what value you want it to use without any quotes.

rokslide · #15 (**permalink**) 06-23-11, 18:12

Yup, Teddy is 100% correct in his post.

No quotes needed.

So if you are having problems it will be in a different section of your code.

Feel free to give info on any new issues and we can help you through them.

Internet Services

Web Development

Digital Marketing

Consumer Tech