Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1520
  • Last Modified:

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

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
Jedeye
Asked:
Jedeye
1 Solution
 
buraksaricaCommented:
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
 
systanCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now