Visual Basic

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.
rawilkenAsked:
Who is Participating?
 
nepaluzConnect With a Mentor Commented:
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
 
nepaluzCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rawilkenAuthor Commented:
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
 
rawilkenAuthor Commented:
FileHelprs throws errors on testing class.
0
 
rawilkenAuthor Commented:
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
 
rawilkenAuthor Commented:
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
 
nepaluzCommented:
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
 
rawilkenAuthor Commented:
Wow. I can track most of this. I am not picking up where the path and file for the csv is provided.
0
 
rawilkenAuthor Commented:
It throws an error when running it.
Error3.jpg
0
 
nepaluzCommented:
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
 
nepaluzCommented:
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
 
nepaluzCommented:
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
 
nepaluzCommented:
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
 
rawilkenAuthor Commented:
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
 
rawilkenAuthor Commented:
Is there a simple way to delete the data that is in these tables before I go and import more data?
0
 
nepaluzCommented:
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
 
rawilkenAuthor Commented:
This is the delete I came up with...

Dim sqlString As String = "DELETE FROM hours"
        Dim cmd = New SqlCeCommand(sqlString)
        cmd.ExecuteNonQuery()
0
 
rawilkenAuthor Commented:
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
 
rawilkenAuthor Commented:
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
 
rawilkenAuthor Commented:
Screen shot of error...
Error-5.JPG
0
 
rawilkenAuthor Commented:
Got it. The GetPath needed to be modified.
0
 
nepaluzCommented:
I have not heard a response from you for hours so I will assume that my code suggestion answered your query.
0
 
rawilkenAuthor Commented:
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
 
rawilkenAuthor Commented:
Still needs refining.
0
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.

All Courses

From novice to tech pro — start learning today.