Can someone please tell me why I'm receiving these error messages when attempting to take on customer data from a flat file:
- Error 1 Name 'OpenFileDialog1' is not declared.
- Error 5 Overload resolution failed because no accessible 'Split' can be called without a narrowing conversion:
'Public Function Split(separator() As Char, options As System.StringSplitOptions) As String()': Argument matching parameter 'separator' narrows from 'String' to '1-dimensional array of Char'.
'Public Function Split(separator() As Char, options As System.StringSplitOptions) As String()': Argument matching parameter 'options' narrows from 'Integer' to 'System.StringSplitOptions'.
'Public Function Split(separator() As Char, count As Integer) As String()': Argument matching parameter 'separator' narrows from 'String' to '1-dimensional array of Char'.
CODE
Public Class frmCustomerList
Private Sub frmCustomerList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'MessageOut and ListBox1 were used to debug the script. Put ListBox1 on your form and uncomment lines
'referring to these as an aid to debugging if needed
Dim ALineIn, MessageOut As String
Dim Fields(99) As String
Dim RecordsRead, Updated, Inserted, Blanks As Integer
Dim UsersId, LastName As String
Dim Delimiter As String
Delimiter = Chr(9)
Dim UsersCnxn As New System.Data.OleDb.OleDbConnection
'Point UsersCnxn to the database
UsersCnxn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=C:\Documents and Settings\test\My Documents\!!DatabaseAp1\test\Test1.mdb"
Dim UsersCommand As New System.Data.OleDb.OleDbCommand
Dim SQLStmt As String
Dim CommandResult As Integer
'Set OpenFile dialog to find files with CustomerList in the name and a .tab extension
OpenFileDialog1.Filter = "CustomerFiles | *CustomerList.tab"
OpenFileDialog1.ShowDialog()
If OpenFileDialog1.FileName <> "" Then
'Open the file
FileOpen(1, OpenFileDialog1.FileName, OpenMode.Input)
'Open the database cnxn and associate it with the UsersCommand
UsersCnxn.Open()
UsersCommand.Connection = UsersCnxn
UsersCommand.CommandType = CommandType.Text
While Not EOF(1)
'Input a record from the Customers file
ALineIn = LineInput(1)
RecordsRead += 1
'Consider a record that's less than 5 characters long as a Blank record
If Len(ALineIn) > 5 Then
'Split ALineIn into the array Fields
Fields = ALineIn.Split(Delimiter, 9)
'MessageOut = "Fields(0)='" & Fields(0) & "' Fields(2)='" & Fields(2) & "'"
'ListBox1.Items.Add(MessageOut)
'Check to see if a record from this source with this SourcesId is already in the table
SQLStmt = String.Format("SELECT Id FROM Users WHERE RecordSource='Website' and SourcesId='{0}'", Fields(0))
'Use string.Replace on any fields that might have a single quote in them before
'sticking them into the SQLStmt where they'll cause the statement to fail
Fields(2) = Fields(2).Replace("'", "''")
UsersCommand.CommandText = SQLStmt
Try
UsersId = UsersCommand.ExecuteScalar
Catch ErrorMessage As Exception
MessageOut = String.Format("This SQLStmt '{0}' Got this exception '{1}'", SQLStmt, ErrorMessage)
MsgBox(MessageOut)
End Try
'MessageOut = "UsersId='" + UsersId + "'"
If UsersId = "" Then
'An empty string was returned if this record is not already in the table, INSERT it
'ListBox1.Items.Add(MessageOut)
SQLStmt = "INSERT INTO Users (RecordSource, SourcesId, LastName) " _
& String.Format("VALUES ('Website', '{0}', '{1}')", Fields(0), Fields(2))
'ListBox1.Items.Add(SQLStmt)
UsersCommand.CommandText = SQLStmt
Try
CommandResult = UsersCommand.ExecuteNonQuery()
Catch ErrorMessage As Exception
MessageOut = String.Format("This SQLStmt '{0}' Got this exception '{1}'", SQLStmt, ErrorMessage)
MsgBox(MessageOut)
End Try
Inserted += 1
'ListBox1.Items.Add(Str(CommandResult))
'ListBox1.Refresh()
Else
'The record is already in the table, UPDATE it
SQLStmt = "UPDATE Users SET " _
& String.Format("RecordSource='Website', SourcesId='{0}', LastName='{1}' ", Fields(0), Fields(2)) _
& String.Format("WHERE Id={0}", UsersId)
Try
UsersCommand.CommandText = SQLStmt
CommandResult = UsersCommand.ExecuteNonQuery()
Catch ErrorMessage As Exception
MessageOut = String.Format("This SQLStmt '{0}' Got this exception '{1}'", SQLStmt, ErrorMessage)
MsgBox(MessageOut)
End Try
'ListBox1.Items.Add(SQLStmt)
Updated += 1
End If
Else
'Don't do anything with a Blank record but count it
'ListBox1.Items.Add("Blank Record")
Blanks += 1
End If
End While
'Close the file and Cnxn and show the control counts
FileClose(1)
UsersCnxn.Close()
MessageOut = "Read " + Str(RecordsRead) & " records from the file." & vbCrLf _
& Str(Blanks) & " were Blank records. " & vbCrLf _
& Str(Inserted) & " were Inserted into the Users table. " & vbCrLf _
& Str(Updated) & " were Updated."
MsgBox(MessageOut)
End If
End Sub
End Class