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;

---

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)

---

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