Solved

System.Data.SQLite - Parameterised query won't work

Posted on 2010-11-25
2
1,407 Views
Last Modified: 2013-11-13
When I run the section of code below I get the error message "SQLite error Insufficient parameters supplied to the command"

Can anyone give me an indication of what I am doing wrong?
Private Sub test()
        Dim ConnectionString As String = "C:\Users\Des\Documents\Visual Studio 2008\Projects\QD-People\test1.dta"
       
        Dim cnn As New SQLite.SQLiteConnection()
        cnn.ConnectionString = "Data Source=" & ConnectionString & ";"
        Dim cmd As SQLite.SQLiteCommand
        cmd = cnn.CreateCommand
        Dim p1 As New SQLiteParameter("pTest")
        p1.Direction = ParameterDirection.Input
        p1.Value = #1/2/1948#
        cmd.Parameters.Add(p1)

        'cmd.CommandText = "INSERT INTO tblSessions (Connection, StartTime) VALUES ('" _
        ' & ConnectionString & "'," & "(@test)" & ");"

        cmd.CommandText = "INSERT INTO tblSessions (Connection, StartTime) VALUES " _
         & "('" _
         & ConnectionString _
         & "'," _
         & "(@test)" _
         & ");"

        MsgBox(cmd.CommandText)
        cnn.Open()
        cmd.ExecuteNonQuery()

        cmd.Dispose()
        cnn.Close()
        cnn.Dispose()
    End Sub

Open in new window

0
Comment
Question by:Jedeye
2 Comments
 
LVL 5

Expert Comment

by:buraksarica
ID: 34212258
You are setting parameter before setting CommandText, it may be about that. Also, your parameter name here

Dim p1 As New SQLiteParameter("pTest")

doesn't match with


 & "(@test)" _


here.

it may be about these.
0
 
LVL 14

Accepted Solution

by:
systan earned 500 total points
ID: 34212923
use $ not @

code snippet;
Private Sub ZeroIncomeDate()

        Dim dt As DateTime = DateTime.Now

        Dim ddate As String = dt.ToShortDateString()

        Dim ss As String = "select dtime from dincome where dtime=$xddate"

        Dim sc As New SQLiteCommand(ss, mycon)

        sc.Parameters.Add("$xddate", DbType.String).Value = ddate

        Dim sr As SQLiteDataReader = sc.ExecuteReader

        If Not sr.HasRows Then

            ss = "insert into dincome(dtime,dcash,dpaper)values" & _

            "(" & _

            "$xdtime,$xdcash,$xdpaper" & _

            ")"

            sc = New SQLiteCommand(ss, mycon)

            sc.Parameters.Add("$xdtime", DbType.String).Value = ddate

            sc.Parameters.Add("$xdcash", DbType.Decimal).Value = 0

            sc.Parameters.Add("$xdpaper", DbType.Decimal).Value = 0

            sc.ExecuteNonQuery()

            sc.Parameters.Clear()

            sr.Close()

            sc.Dispose()

        End If

    End Sub

Open in new window

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

A short article about a problem I had getting the GPS LocationListener working.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now