I am working to develop an app that will import two CSV files. During the import I need the single quotation marks removed from a couple of the fields. Then I need the data imported into a local SQLCE database. Once the user makes their changes I need to then take the two tables, group the data into three queries that then Union merge into one query that I can export as a CSV.
I have the forms completed. I need help with the easy part :) the code to make it all work.
Here is the code I currently have for importing it. It worked once, then I switched PCs and it stopped working. I get an error that too many connections are open. The code is...
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 numeric (5,2) null, " +
"OT numeric (5,2) null, " +
"FromDate nvarchar (100) null, " +
"ToDate nvarchar (100) 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.SetDecimal(1, Line(1))
rec.SetDecimal(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
Open in new window
outside the While Not sr.EndOfData line.