Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Advice on developing application

    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?

  2. #2
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Has anyone any ideas of how I can approach this?

  3. #3
    Join Date
    Jun 2011
    Posts
    9
    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?

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    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

  6. #6
    Join Date
    Jun 2011
    Posts
    9
    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

  7. #7
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Quote Originally Posted by OpK View Post
    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

  8. #8
    Join Date
    Jun 2011
    Posts
    9
    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:

    <aspqlDataSource 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>
    </aspqlDataSource>

    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.
    Last edited by OpK; 06-22-11 at 13:48.

  9. #9
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    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

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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...
    Last edited by Teddy; 06-22-11 at 18:52.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    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

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  13. #13
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Quote Originally Posted by rokslide View Post
    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

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    Last edited by Teddy; 06-23-11 at 19:02.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •