dcsimg
Results 1 to 3 of 3

Thread: Data table to Sqlite table

  1. #1

    Thread Starter
    Member
    Join Date
    Nov 2016
    Posts
    58

    Data table to Sqlite table

    I am trying to insert data from a data table into a Sqlite table. The data table is filled from a .csv file.

    Code:
      Private Sub VoerWeighbridgehistorycsvInToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles VoerWeighbridgehistorycsvInToolStripMenuItem.Click
    
            Dim dt As New System.Data.DataTable
    
            With dt
    
                .Columns.Add("grower", System.Type.GetType("System.String"))
                .Columns.Add("alias", System.Type.GetType("System.String"))
                .Columns.Add("blkname", System.Type.GetType("System.String"))
    
    
            End With
    
    
            Using myReader As New TextFieldParser("C:\Users\User\Wingerdbou\Kultivar analise Program\EZY Wine data en Databasis\wbridge_history.csv", Encoding.Default)
                myReader.TextFieldType = FieldType.Delimited
                myReader.SetDelimiters(",")
                myReader.HasFieldsEnclosedInQuotes = True
    
    
                Do While Not myReader.EndOfData
                    Dim myData() As String = myReader.ReadFields
    
                    dt.Rows.Add(myData(3),
                              myData(4),
                              myData(5))
    
                Loop
            End Using
    
    
            Dim strsql As String = "Insert into skaalkaartjies2 " &
                                    "(LidNo,Alias,Plaasnaam) " &
                                    "values ( @grower,@alias,@blkname)"
    
            Dim sqlconnectionstring As String = "Data Source = C:\Users\User\Wingerdbou\Kultivar analise Program\EZY Wine data en Databasis\weegbrugGeskiedenis.db;Version=3;"
    
            Using connection As New SQLiteConnection(sqlconnectionstring)
                connection.Open()
                Dim cmd As New SQLiteCommand(strsql, connection)
    
    
                With cmd.Parameters
                    .Add("@grower", "LidNo")
                    .Add("@alias", "Alias")
                    .Add("@blkname", "Plaasnaam")
                End With
    
                cmd.ExecuteNonQuery()
    
    
            End Using
    
    
    
        End Sub
    I get an error at the line .Add("@grower", "LidNo"). The error is : System.InvalidCastException: 'Conversion from string "LidNo" to type 'Integer' is not valid.'


    I cannot understand where the integer issue comes from as my Sqlite table's structure is :

    Code:
    PRAGMA foreign_keys = 0;
    
    CREATE TABLE sqlitestudio_temp_table AS SELECT *
                                              FROM skaalkaartjies2;
    
    DROP TABLE skaalkaartjies2;
    
    CREATE TABLE skaalkaartjies2 (
        LidNo     VARCHAR (100),
        Alias     VARCHAR (100),
        Plaasnaam VARCHAR (100) 
    );
    
    INSERT INTO skaalkaartjies2 (
                                    LidNo,
                                    Alias,
                                    Plaasnaam
                                )
                                SELECT LidNo,
                                       Alias,
                                       Plaasnaam
                                  FROM sqlitestudio_temp_table;
    
    DROP TABLE sqlitestudio_temp_table;
    
    PRAGMA foreign_keys = 1;

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,450

    Re: Data table to Sqlite table

    Your Parameters.Add syntax is wrong.

    Code:
    cmd.Parameters.Add("@LidNo", SqlDbType.VarChar)
    
    If you want to add a value,
    
    cmd.Parameters.Add("@LidNo", SqlDbType.VarChar).Value = yourValue
    
    Or you can use AddWithValue method
    
    cmd.Parameters.AddWithValue("@LidNo", yourValue)

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,803

    Re: Data table to Sqlite table

    Try reading the documentation on the .Add method of the parameters collection.
    https://docs.microsoft.com/en-us/dot...tframework-4.8

    There's two overloads that has two parameters... and both expect something that can be converted to integers in that second parameter. You didn't supply something that could be converted to an integer. You supplied a string. As Yosemite Sam would say "No that's not right, try it again!"

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

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