Click here to Skip to main content
15,899,679 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Im using this code to import .dat file to SQLite Table
My connection
VB
Public strConn As String = "Data Source =DTS.db;Version=3;"

My Procedure
VB
 Private Sub btnImportDB_Click(sender As Object, e As EventArgs) Handles btnImportDB.Click

        If txtDatFile.Text = String.Empty Then Exit Sub

        Dim fname As String = txtDatFile.Text
        Dim colsexpected As Integer = 6
        Dim Filereader As New StreamReader(fname, Encoding.Default)
        Dim sLine As String = String.Empty

        Using MyConn As New SQLiteConnection(strConn)
            Do
                sLine = Filereader.ReadLine
                If sLine Is Nothing Then Exit Do
                Dim vArray() As String = sLine.Split(CType(vbTab, Char()))
                'Save data to table
                Dim InsertData As String = "INSERT INTO tblTempData (fldMacId,fldDateTime,fldRem1,fldRem2,fldRem3,fldRem4) Values (@MacId,@xDateTime,@Rem1,@Rem2,@Rem3,@Rem4)"
                Dim cmd As New SQLiteCommand(InsertData, MyConn)
                cmd.Parameters.AddWithValue("@MacId", vArray(0))
                cmd.Parameters.AddWithValue("@xDateTime", vArray(1))
                cmd.Parameters.AddWithValue("@Rem1", vArray(2))
                cmd.Parameters.AddWithValue("@Rem2", vArray(3))
                cmd.Parameters.AddWithValue("@Rem3", vArray(4))
                cmd.Parameters.AddWithValue("@Rem4", vArray(5))
                Try
                    With MyConn
                        If .State = ConnectionState.Open Then .Close()
                        .ConnectionString = strConn
                        .Open()
                        MyConn.Open()
                        cmd.ExecuteNonQuery()
                        Cursor.Current = Cursors.WaitCursor
                        'MsgBox("Connection is  Open")
                    End With
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try

            Loop
        End Using
        MessageBox.Show("Records successfully imported.", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
        txtDatFile.Clear()
End Sub


This is the error
VB
{"Operation is not valid due to the current state of the object."}


What I have tried:

I cant find which part cause the error..
Posted
Updated 13-Jan-18 4:53am
Comments
Mehdi Gholam 13-Jan-18 8:42am    
What format is your .dat file (dat is a generic extension so what application created it)?
Richard MacCutchan 13-Jan-18 9:20am    
You are calling Open() twice.
IamLance1578 13-Jan-18 9:58am    
@Mehdi Gholam
This is format of .dat file
41 2017-09-07 17:01:09 1 1 1 0
27 2017-09-07 17:01:13 1 1 1 0
42 2017-09-07 17:01:31 1 1 1 0
46 2017-09-07 17:04:05 1 1 1 0
44 2017-09-07 17:04:12 1 1 1 0
45 2017-09-07 17:04:16 1 1 1 0
47 2017-09-07 17:05:09 1 1 1 0
17 2017-09-07 17:05:19 1 1 1 0
29 2017-09-07 17:05:47 1 1 1 0
35 2017-09-07 17:09:14 1 1 1 0
8 2017-09-07 17:09:22 1 1 1 0
8 2017-09-07 17:09:28 1 1 1 0
11 2017-09-07 17:10:49 1 1 1 0
10 2017-09-07 17:13:17 1 1 1 0
33 2017-09-07 17:13:44 1 1 1 0
30 2017-09-07 17:13:56 1 1 1 0

@Richard MacCutchan
If I do this I got the same error
Private Sub btnImportDB_Click(sender As Object, e As EventArgs) Handles btnImportDB.Click

       If txtDatFile.Text = String.Empty Then Exit Sub

       Dim fname As String = txtDatFile.Text
       Dim colsexpected As Integer = 6
       Dim Filereader As New StreamReader(fname, Encoding.Default)
       Dim sLine As String = String.Empty

       Using MyConn As New SQLiteConnection(strConn)
           Do
               sLine = Filereader.ReadLine
               If sLine Is Nothing Then Exit Do
               Dim vArray() As String = sLine.Split(CType(vbTab, Char()))
               'Save data to table
               Dim InsertData As String = "INSERT INTO tblTempData (fldMacId,fldDateTime,fldRem1,fldRem2,fldRem3,fldRem4) Values (@MacId,@xDateTime,@Rem1,@Rem2,@Rem3,@Rem4)"
               Dim cmd As New SQLiteCommand(InsertData, MyConn)
               cmd.Parameters.AddWithValue("@MacId", vArray(0))
               cmd.Parameters.AddWithValue("@xDateTime", vArray(1))
               cmd.Parameters.AddWithValue("@Rem1", vArray(2))
               cmd.Parameters.AddWithValue("@Rem2", vArray(3))
               cmd.Parameters.AddWithValue("@Rem3", vArray(4))
               cmd.Parameters.AddWithValue("@Rem4", vArray(5))

               MyConn.Open()
               cmd.ExecuteNonQuery()

           Loop
       End Using
       MessageBox.Show("Records successfully imported.", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
       txtDatFile.Clear()
   End Sub

1 solution

Move the .Open call out of the loop. You only need to call it ONCE, before you start the loop.

Also, Move the code to create your SQLiteCommand object and it's parameters out of the loop. Create it ONCE and reuse it! Do NOT use .AddWithValue, use .Add instead and just describe the parameter.

Inside the loop, is where you set the values of your parameter objects.
 
Share this answer
 
Comments
IamLance1578 13-Jan-18 20:03pm    
It works thank you very much. Here is my updated code
Private Sub btnImportDB_Click(sender As Object, e As EventArgs) Handles btnImportDB.Click

        If txtDatFile.Text = String.Empty Then Exit Sub

        Dim fname As String = txtDatFile.Text
        Dim colsexpected As Integer = 6
        Dim Filereader As New StreamReader(fname, Encoding.Default)
        Dim sLine As String = String.Empty

        Using MyConn As New SQLiteConnection(strConn)
            MyConn.Open()
            'Save data to table
            Dim InsertData As String = "INSERT INTO tblTempData (fldMacId,fldDateTime,fldRem1,fldRem2,fldRem3,fldRem4) Values (@MacId,@xDateTime,@Rem1,@Rem2,@Rem3,@Rem4)"

            Do
                sLine = Filereader.ReadLine
                If sLine Is Nothing Then Exit Do
                Dim vArray() As String = sLine.Split(CType(vbTab, Char()))
                Dim cmd As New SQLiteCommand(InsertData, MyConn)
                cmd.Parameters.Add("@MacId", CInt(vArray(0)))
                cmd.Parameters.Add("@xDateTime", FormatDateTime(vArray(1), DateFormat.GeneralDate))
                cmd.Parameters.Add("@Rem1", CStr(vArray(2)))
                cmd.Parameters.Add("@Rem2", CStr(vArray(3)))
                cmd.Parameters.Add("@Rem3", CStr(vArray(4)))
                cmd.Parameters.Add("@Rem4", CStr(vArray(5)))
                cmd.ExecuteNonQuery()
                MsgBox(InsertData)
            Loop
        End Using
        MessageBox.Show("Records successfully imported.", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
        txtDatFile.Clear()
    End Sub
Dave Kreskowiak 13-Jan-18 20:14pm    
Oh, I know it works. It's just something I wouldn't consider "production quality" code in the slightest. You're making a ton of objects you don't need to create PER RECORD YOU IMPORT, limiting the scalability of your code and slowing it down needlessly with more and more allocations. You're also creating a ton of extra work for the garbage collector to return all those blocks of memory back to the managed heap and possibly creating a fragmented memory enviornment.

It would be a good idea to get into good coding habits now instead of trying to unlearn these bad habits down the road.
IamLance1578 13-Jan-18 20:27pm    
@Dave Kreskowiak
Thanks you very much for the input. Im not really a pro. I just trying to create a program that can be used by schools here in my place for free. :-)
I still have problems in the code it throws an error in inserting datetime. However i put another question for it.
I know my coding sucks :-)
Would you mind explaining me this
you don't need to create PER RECORD YOU IMPORT 


Do you have any idea how to improve my code?
Dave Kreskowiak 13-Jan-18 21:21pm    
I already told you. Re-read my post.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900