Solved

Visual Basic

Posted on 2012-03-10
25
212 Views
Last Modified: 2012-03-11
I am fairly new to VB. What I want to do is to have a button on a form that when activated will import a csv file into a local database in my application.
0
Comment
Question by:rawilken
  • 15
  • 9
25 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 37705251
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37705254
handle the button's click event, add code to parse the csv file then import into your database. Some clarification wanted though:
1. Local database you say, what database?
2. How is your CSV file formed? i.e, what fields in the CSV file?
3. What are the fields that you want imported into the database?
0
 

Author Comment

by:rawilken
ID: 37705322
The csv is comma delimited. There are 6 fields but no field labels. Four of the fields are enclosed in single quotes that I want to remove. The fields are...
EmployeeID string (enclosed in single quotes)
Reg single
OT single
From DateTime (enclosed in single quotes)
To datatime (enclosed in single quotes)
Position string (enclosed in single quotes)

I have a local table called hours that I want to import the data into
0
 

Author Comment

by:rawilken
ID: 37705376
FileHelprs throws errors on testing class.
0
 

Author Comment

by:rawilken
ID: 37705443
I think I have the code to read the csv into an array. Now I need to input that array into the local sdf database table named hours.
0
 

Author Comment

by:rawilken
ID: 37705445
Public Function ReadCSV(FileName As String)
        Dim strPath As String
        Dim strAp As String
        strPath = GetPath()
        strAp = "\" & FileName & ".csv"
        Dim engine As New FileHelperEngine(Of Hours)()
        Dim res As Hours() = engine.ReadFile("C:\Data Control\Macro Network Solutions\TimeAndPay\TimeAndPay\bin\Debug\hours.csv")
        For Each record As Hours In res
            Return record
        Next
        Return res
    End Function
    <DelimitedRecord(",")> _
    Public NotInheritable Class Hours
        <FieldQuoted("'"c, QuoteMode.OptionalForRead, MultilineMode.AllowForRead)> _
        Public EmployeeID As String
        Public Reg As Single
        Public OT As Single
        <FieldQuoted("'"c, QuoteMode.OptionalForRead, MultilineMode.AllowForRead)> _
        Public FromDate As String
        <FieldQuoted("'"c, QuoteMode.OptionalForRead, MultilineMode.AllowForRead)> _
        Public ToDate As String
        <FieldQuoted("'"c, QuoteMode.OptionalForRead, MultilineMode.AllowForRead)> _
        Public Position As String
    End Class
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37705540
I have put together a boolean function for you. Pass the file path as argument and it will read your csv and update a database. Feel free to change the names etc
    Public Function SaveToDB(ByVal FileName As String) As Boolean

        Dim DBName As String = "YourDataBase.sdf"
        Dim password As String = "YourPassword"
        Dim connectionString = String.Format("Data Source=""{0}""; Password=’{1}’", FileName, password)
        'connectionString = String.Format("Data Source=""{0}"";", fileName)
        Dim cn1 As New SqlCeConnection(connectionString)
        If Not File.Exists(DBName) Then
            Dim eng As SqlCeEngine = New SqlCeEngine(connectionString)
            eng.CreateDatabase()
            Dim sqlString As String = "create table hours (" +
                "EmployeeID nvarchar (25) not null, " +
                "Reg nvarchar (10) not null, " +
                "OT nvarchar (12) not null, " +
                "FromDT nvarchar (15) not null, " +
                "ToDT nvarchar (15) not null, " +
                "constraint pk_EmployeeID primary key (EmployeeID))"

            If cn1.State = ConnectionState.Closed Then
                cn1.Open()
            End If
            Dim cmd = New SqlCeCommand(sqlString, cn1)
            cmd.ExecuteNonQuery()

        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
            cmd1.CommandText = "SELECT * FROM AccountsTable"

            While Not sr.EndOfData
                Dim Line = sr.ReadFields()
                Dim rs As SqlCeResultSet = cmd1.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
                Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
                rec.SetString(0, Line(0))
                rec.SetString(1, Line(1))
                rec.SetString(2, Line(2))
                rec.SetString(3, Line(3))
                rec.SetString(4, Line(4))
                rs.Insert(rec)
            End While
        End Using
        Return True
    End Function

Open in new window

PS. I have added a password to the database (on creation) to automatically encrypt the database on creation. Simply remove the password to create an un-encrypted one.
0
 

Author Comment

by:rawilken
ID: 37705574
Wow. I can track most of this. I am not picking up where the path and file for the csv is provided.
0
 

Author Comment

by:rawilken
ID: 37705588
It throws an error when running it.
Error3.jpg
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37705590
when you call the function, you pass the file path in the variable FileName.
Have you tried running it, and what are your results?
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37705597
move thisline
Dim cmd1 As SqlCeCommand = cn1.CreateCommand()

Open in new window

from line 27 to between lines 30 and 31 (i.e after the While Not sr.EndOfData)
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37705604
Actually, move both lines and amend the second one too!
Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
cmd1.CommandText = "SELECT * FROM hours"

Open in new window

0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 17

Expert Comment

by:nepaluz
ID: 37705629
I have also added a check for the number of fields in any line read from the CSV to be more than 4 fields, so you have
    Public Function SaveToDB(ByVal FileName As String) As Boolean

        Dim DBName As String = "YourDataBase.sdf"
        Dim password As String = "YourPassword"
        Dim connectionString = String.Format("Data Source=""{0}""; Password=’{1}’", FileName, password)
        'connectionString = String.Format("Data Source=""{0}"";", fileName)
        Dim cn1 As New SqlCeConnection(connectionString)
        If Not File.Exists(DBName) Then
            Dim eng As SqlCeEngine = New SqlCeEngine(connectionString)
            eng.CreateDatabase()
            Dim sqlString As String = "create table hours (" +
                "EmployeeID nvarchar (25) not null, " +
                "Reg nvarchar (10) not null, " +
                "OT nvarchar (12) not null, " +
                "FromDT nvarchar (15) not null, " +
                "ToDT nvarchar (15) not null, " +
                "constraint pk_EmployeeID primary key (EmployeeID))"

            If cn1.State = ConnectionState.Closed Then
                cn1.Open()
            End If
            Dim cmd = New SqlCeCommand(sqlString, cn1)
            cmd.ExecuteNonQuery()

        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            While Not sr.EndOfData
                Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
                cmd1.CommandText = "SELECT * FROM hours"
                If cn1.State = ConnectionState.Closed Then
                    cn1.Open()
                End If
                Dim Line = sr.ReadFields()
                If Not Line.Count > 4 Then Continue While
                Dim rs As SqlCeResultSet = cmd1.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
                Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
                rec.SetString(0, Line(0))
                rec.SetString(1, Line(1))
                rec.SetString(2, Line(2))
                rec.SetString(3, Line(3))
                rec.SetString(4, Line(4))
                rs.Insert(rec)
            End While
        End Using
        Return True
    End Function

Open in new window

0
 

Author Comment

by:rawilken
ID: 37705669
I modified the filed check to the five fields I actually am using. I am getting a URI format not supported error. Here is a screenshot of it.
Error-4.JPG
0
 

Author Comment

by:rawilken
ID: 37705680
Is there a simple way to delete the data that is in these tables before I go and import more data?
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37705683
What is the path that you supplied?
I also found another error in the connectionString variable, change it to:
Dim connectionString = String.Format("Data Source=""{0}""; Password=’{1}’", DBName, password)

Open in new window

I have checked the code and it runs OK, infact, you did not need to change the position of the lines of code I made earlier, but you need to add the check for a connection, i.e you can move these lines to just before the second while loop
Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
cmd1.CommandText = "SELECT * FROM hours"
If cn1.State = ConnectionState.Closed Then
    cn1.Open()
End If

Open in new window

0
 

Author Comment

by:rawilken
ID: 37705687
This is the delete I came up with...

Dim sqlString As String = "DELETE FROM hours"
        Dim cmd = New SqlCeCommand(sqlString)
        cmd.ExecuteNonQuery()
0
 

Author Comment

by:rawilken
ID: 37705690
Path is current path of application plus file name...
I do a get path and then append the file name...

file:\C:\Data Control\Macro~\hours.csv
0
 

Author Comment

by:rawilken
ID: 37705699
I am still getting the URI function error on the "Using sr..." line

Here is the code as I have it...

Public Function SaveHoursToDB(ByVal FileName As String) As Boolean
        Dim DBName As String = "DataControl.sdf"
        'Dim password As String = ""
        Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
        'connectionString = String.Format("Data Source=""{0}""; Password=’{1}’", DbName, password)
        Dim cn1 As New SqlCeConnection(connectionString)
        If Not System.IO.File.Exists(DBName) Then
            Dim eng As SqlCeEngine = New SqlCeEngine(connectionString)
            eng.CreateDatabase()
            Dim sqlString As String = "create table hours (" +
                "EmployeeID nvarchar (15) null, " +
                "Reg float (8,2) null, " +
                "OT float (8,2) null, " +
                "FromDT nvarchar (25) null, " +
                "ToDT nvarchar (25) null, " +
                "Position nvarchar(20) null) "
            Dim cmd = New SqlCeCommand(sqlString, cn1)
            cmd.ExecuteNonQuery()
        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            While Not sr.EndOfData
                Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
                cmd1.CommandText = "SELECT * FROM hours"
                If cn1.State = ConnectionState.Closed Then
                    cn1.Open()
                End If
                Dim Line = sr.ReadFields()
                Dim rs As SqlCeResultSet = cmd1.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
                Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
                rec.SetString(0, Line(0))
                rec.SetString(1, Line(1))
                rec.SetString(2, Line(2))
                rec.SetString(3, Line(3))
                rec.SetString(4, Line(4))
                rec.SetString(5, Line(5))
                rs.Insert(rec)
            End While
        End Using
        Return True
    End Function
0
 

Author Comment

by:rawilken
ID: 37705701
Screen shot of error...
Error-5.JPG
0
 

Author Comment

by:rawilken
ID: 37705712
Got it. The GetPath needed to be modified.
0
 
LVL 17

Accepted Solution

by:
nepaluz earned 500 total points
ID: 37705728
OK, I saw your error before. How do you get the path? It should NOT contain the file:\ bit, just the path, i.e C:\.......
Thecode you show is OK but have changed it slightly (to move the lines I mentioned earlier)
    Public Function SaveHoursToDB(ByVal FileName As String) As Boolean
        Dim DBName As String = "DataControl.sdf"
        'Dim password As String = ""
        Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
        'connectionString = String.Format("Data Source=""{0}""; Password=’{1}’", DbName, password)
        Dim cn1 As New SqlCeConnection(connectionString)
        If Not System.IO.File.Exists(DBName) Then
            Dim eng As SqlCeEngine = New SqlCeEngine(connectionString)
            eng.CreateDatabase()
            Dim sqlString As String = "create table hours (" +
                "EmployeeID nvarchar (15) null, " +
                "Reg float (8,2) null, " +
                "OT float (8,2) null, " +
                "FromDT nvarchar (25) null, " +
                "ToDT nvarchar (25) null, " +
                "Position nvarchar(20) null) "
            Dim cmd = New SqlCeCommand(sqlString, cn1)
            cmd.ExecuteNonQuery()
        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
            cmd1.CommandText = "SELECT * FROM hours"
            If cn1.State = ConnectionState.Closed Then
                cn1.Open()
            End If
            While Not sr.EndOfData
                Dim Line = sr.ReadFields()
                Dim rs As SqlCeResultSet = cmd1.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
                Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
                rec.SetString(0, Line(0))
                rec.SetString(1, Line(1))
                rec.SetString(2, Line(2))
                rec.SetString(3, Line(3))
                rec.SetString(4, Line(4))
                rec.SetString(5, Line(5))
                rs.Insert(rec)
            End While
        End Using
        Return True
    End Function

Open in new window

PS. The issue you have is to resolve your path. Show me how you get the path to the file before calling the function.
Also, you may want to define a connection for your delete query, e.g
Dim DBName As String = "DataControl.sdf"
Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
Dim cn1 As New SqlCeConnection(connectionString)
Dim sql1 As String = "delete from hours"
Dim cmdX = New SqlCeCommand(sql1, cn1)
cmdX.ExecuteNonQuery()

Open in new window

Of course, you already have all the variables, just a matter of adding the connection you want to use. (when you overcome the issues you have with the file path, then it may be a good idea to introduce you to the shared connection string procedure so you do not have to keep declaring a few lines!)
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37706456
I have not heard a response from you for hours so I will assume that my code suggestion answered your query.
0
 

Author Comment

by:rawilken
ID: 37706945
I am getting a strange error on the database. It is saying the "path is not a legal form". I fixed this once by deleting and recreating the database. Is there a different solution?
0
 

Author Closing Comment

by:rawilken
ID: 37707378
Still needs refining.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now