PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
VS 2015 Importing .dat file to SQLite Table-VBForums
Results 1 to 11 of 11

Thread: Importing .dat file to SQLite Table

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    Question Importing .dat file to SQLite Table

    Im trying to import .dat files to SQLite Table via vb.net

    Sample Data:
    Name:  capture093.jpg
Views: 200
Size:  54.5 KB

    My SQLite Table
    Name:  capture094.jpg
Views: 170
Size:  7.3 KB

    Table Structure
    Code:
    TABLE "tblTempData" ( `fldMacId` INTEGER, `fldDateTime` TEXT, `fldRem1` TEXT, `fldRem2` TEXT, `fldRem3` TEXT, `fldRem4` TEXT )
    My Connection
    Code:
    Imports System.Data.SQLite
    Module Module1
        Public MyCon As New SQLiteConnection
        Dim strConn As String = "Data Source =DTS.db;Version=3;"
        Public Sub Main()
            Try
                With MyCon
                    If .State = ConnectionState.Open Then .Close()
                    .ConnectionString = strConn
                    .Open()
                    'MsgBox("Connection is  Open")
                End With
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    
        Public Function ExecNonQuery(ByVal strSQL As String)
            Try
                Dim cmd As New SQLiteCommand(strSQL, MyCon)
                cmd.ExecuteNonQuery()
                Return "Success"
            Catch ex As Exception
                Return ex.Message
            End Try
        End Function
    End Module
    I trying to use the code below to import the data to table
    Code:
    Imports System.Data.SQLite
    Imports System.IO
    Imports System.Text
    
    Private Sub btnImportDB_Click(sender As Object, e As EventArgs) Handles btnImportDB.Click
            If txtDatFile.Text = "" 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 = ""
    
            Do
                sLine = Filereader.ReadLine
                If sLine Is Nothing Then Exit Do
                Dim vArray() As String = sLine.Split(CType(vbTab, Char()))
                'Save data to table
                strSQL = "INSERT INTO tblTempData (fldMacId,fldDateTime,fldRem1,fldRem2,fldRem3,fldRem4) Values (" & vArray(0) & ", " & vArray(1) & ", " & vArray(2) & "," & vArray(3) & "," & vArray(4) & "," & vArray(5) & ")"
                Result = ExecNonQuery(strSQL)
                Cursor.Current = Cursors.WaitCursor
            Loop
    
            If Result = "Success" Then
                MessageBox.Show("Records successfully imported.", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                MessageBox.Show(Result, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
            txtDatFile.Clear()
        End Sub
    Somehow I cant get it done..
    Thanks for the time..

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,054

    Re: Importing .dat file to SQLite Table

    Maybe if you were to tell us what actually happened when you used that code then we would be able to focus in on potential issues. It's a waste of our time to try to work out what the code actually does when you already know that. If an exception is thrown, what is it and where does it occur? Have you debugged the code? If so, where does it's behaviour deviate from your expectation and exactly how? If you haven't debugged, you should do that first.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,054

    Re: Importing .dat file to SQLite Table

    It's also a very, very bad idea to use string concatenation to build SQL code like that. You should always use parameters, which helps avoid a number of issues, one of which may well be what's causing your code to fail. To learn how and why to use parameters, follow the Blog link in my signature below and check out my post on the subject.

    Also, if you want to insert multiple records then I'd strongly suggest using a data adapter and a DataTable instead of doing it one by one. For one thing, that will force you to use parameters. I'd suggest that you follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data for an example. I think the one most relevant top you may not be in the first post, so make sure to scroll down.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    Re: Importing .dat file to SQLite Table

    @jmcilhinney

    this is the error i get
    Name:  capture096.jpg
Views: 100
Size:  24.4 KB

    I try this but no luck
    Code:
    strSQL = "INSERT INTO tblTempData (fldMacId,fldDateTime,fldRem1,fldRem2,fldRem3,fldRem4)" &
                            "Values ( " & CInt(Trim(vArray(0))) & "," &
                CStr(vArray(1)) & "," & CStr(vArray(2)) & "," &
                            CStr(vArray(3)) & "," & CStr(vArray(4)) & "," &
                            CStr(vArray(5)) & ")"
    I used that in importing .dat file to ms access table and it works. But applying the same code in SQLite wont. I think im missing something.

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,054

    Re: Importing .dat file to SQLite Table

    The error message is telling you that your SQL code contains a syntax error but I'll wager that you haven't even looked at your SQL code. That's part of debugging, i.e. looking at the actual values of your variables as your code executes.. The error is most likely a result of one of the issues that I said that the use of parameters helps to avoid.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    Re: Importing .dat file to SQLite Table

    @jmcilhinney

    I change my code to this
    Code:
    Public strConn As String = "Data Source =DTS.db;Version=3;"
    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)
                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
    I got this error

    Code:
    {"Operation is not valid due to the current state of the object."}
    Any idea how to resolve this.
    Thanks
    Last edited by lance1578; Jan 13th, 2018 at 07:02 AM. Reason: Adding try catch to see the error

  7. #7
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,054

    Re: Importing .dat file to SQLite Table

    Why are you opening and closing the connection inside the loop? Create the connection, open it, perform all the data access and then close it. You don't even have to close it manually because it will happen automatically at the 'End Using' line.

    Also, AddWithValue will infer the data type based on the value you provide and you are providing a String in every case. I'm not sure whether SQLite has a dedicated type for date/time values but, if it does, you should absolutely be using it, which would mean converting the text you read from the file into a .NET DateTime value first. You should also be converting anything else that isn't specifically text, e.g. numeric data should be converted to the appropriate numeric type.

    I would still strongly recommend populating a DataTable and saving all the data in one go.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    Re: Importing .dat file to SQLite Table

    I would still strongly recommend populating a DataTable and saving all the data in one go.
    Mind telling me how to do it.
    Thanks for the time.

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,054

    Re: Importing .dat file to SQLite Table

    Quote Originally Posted by lance1578 View Post
    Mind telling me how to do it.
    Go back and read post #3.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Aug 2017
    Posts
    22

    Re: Importing .dat file to SQLite Table

    Thanks for the help I found the problem I put the .Open inside the loop.
    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
    This line of code throws an error
    Code:
    cmd.Parameters.Add("@xDateTime", FormatDateTime(vArray(1), DateFormat.GeneralDate))
    error: Conversion from string "9/7/2017 5:01:09 PM" to type 'Integer' is not valid.

  11. #11
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,054

    Re: Importing .dat file to SQLite Table

    Quote Originally Posted by lance1578 View Post
    This line of code throws an error
    Code:
    cmd.Parameters.Add("@xDateTime", FormatDateTime(vArray(1), DateFormat.GeneralDate))
    error: Conversion from string "9/7/2017 5:01:09 PM" to type 'Integer' is not valid.
    That code is nonsensical in that context. The point of FormatDateTime is to take a DateTime and create a String. That is the complete opposite of what you want. You have a String and you want to create a DateTime. What you should be using is either DateTime.Parse or DateTime.ParseExact if the data is guaranteed to be valid, or else DateTime.TryParse o DateTime.TryParseExact if the data may be invalid. Please read the documentation for each of those four methods to determine which is the correct one in your situation. They are all different and only one will ever be the most correct choice in any given situation.

    By the way, vArray is a String array so there's no point using CStr to convert a String to a String. You only need to convert if the type you have is not the type you want.

    Also, this is overly convoluted and doesn't really make sense anyway:
    vb.net Code:
    1. sLine.Split(CType(vbTab, Char()))
    Use this:
    vb.net Code:
    1. sLine.Split(ControlChars.Tab)
    ControlChars should be used in preference to constants like vbTab and the String.Split method has a ParamArray parameter, meaning that you can pass zero, one or more discrete values rather than a single array.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width