SQLServerCE Problem with parameterized queries from .NET

Question

Hello,

I am pulling the hair out of my head trying to figure this one out.

I can't make Parameterized queries to work in VB.Net, when I am using parameters.

From what I have found, using a parameter in a function, from .NET raises an error (see sample code). However, running the not working query in the Query Window in Visual studio works properly.

The error raised is:

25922 - The arguments for function are not valid.

Info from: http://msdn.microsoft.com/en-us/library/aa256772%28SQL.80%29.aspx

Sample Code:

Imports System.Data.SqlServerCe

Public Class MiniDemo

    Public Shared Sub Main()
     Dim cs As String = "Data Source=Inventory.sdf; Persist Security Info=False;"
     Dim db As New SqlCeConnection(cs)
     db.Open()


     ''#Using parameters works
     Dim cmd As SqlCeCommand = db.CreateCommand()

     cmd.CommandText = "INSERT INTO inventory_type (inv_type_id, inv_type_name) VALUES (@id, @name)"
     cmd.Parameters.Add("@id", 1)
     cmd.Parameters.Add("@name", "test")
     cmd.ExecuteNonQuery()



     ''#Using parameters in functions does not work
     Dim cmd2 As SqlCeCommand = db.CreateCommand()

            ''#Broken on multiple lines for readability (built using string builders in real code)
     cmd2.CommandText = 
                     "SELECT 
                        inv_type_id, 
                        inv_type_name 
                      FROM
                        inventory_type 
                      WHERE 
                        ((@id IS NULL) OR (inv_type_id = @id)) AND 
                        ((@name IS NULL) OR (inv_type_name = @name))"

     cmd2.Parameters.Add("@id", 1)
     cmd2.Parameters.Add("@name", "test") ''# Would not work with DBNull.Value either

     Dim da2 As New SqlCeDataAdapter(cmd2)
     Dim dt2 As New DataTable()
     da2.Fill(dt2)
     db.Close()
    End Sub

End Class

Answer 1

Finally, I have found the solution for this problem.

Using a parameter in a function crashes if the DBType property of the parameter is not set:

This will crash:

    Dim cmd As SqlCeCommand = db.CreateCommand()

    cmd.CommandText = "SELECT COALESCE(@param1, @param2);"
    cmd.Parameters.Add("@param1", 1)
    cmd.Parameters.Add("@param2", "test")
    cmd.ExecuteScalar()

Using a parameter in a function will work if the DBType property of the parameter is set

This will work:

    Dim cmd As SqlCeCommand = db.CreateCommand()

    cmd.CommandText = "SELECT COALESCE(@param1, @param2);"
    cmd.Parameters.Add("@param1", 1).DbType = DbType.Int32
    cmd.Parameters.Add("@param2", "test").DbType = DbType.String
    cmd.ExecuteScalar()