Re: SQL CE 2.0 parameterised queries

Ah, no.
The way Parameters are managed is a function of the .NET Data Provider. If you use OleDB to access any data source, you must (as Alberto says) use ? placeholders. However, if you use the .NET provider (SqlCe) you can (and must) use named Parameters--as shown in this example.

Imports System.Data.SqlServerCe
Public Class Form1
Dim cn As SqlCeConnection
Dim cmd As SqlCeCommand
Dim tb As New DataTable
Dim sqlRS As SqlCeResultSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
cn = New SqlCeConnection(My.Settings.NorthwindConnectionString)
cn.Open()
cmd = New SqlCeCommand("SELECT [Last Name], [First Name], City" _
& " FROM Employees" _
& " WHERE Region = @RegionWanted", cn)
cmd.Parameters.AddWithValue("@RegionWanted", "WA")
sqlRS = cmd.ExecuteResultSet(ResultSetOptions.Scrollable + ResultSetOptions.Updatable)
DataGridView1.DataSource = sqlRS
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
End Class

Hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________



"Alberto Silva - Microsoft MVP - Device Application Development" <alberto.silva@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:466C3BAC-B27F-4941-A1C7-7401FE519968@xxxxxxxxxxxxxxxx
Hi,
With SSCE 2.0 you can't use named parameters inside the SQL statement, you must use the interrogation mark instead. When executing the command, you can name the parameters you add, but you must add them by the same order they are referenced in the SQL statement, something like...

string sql = "SELECT * FROM myUsersTable WHERE user_code = ? AND password = ?"
SqlCeConnection cn = new SqlCeConnection("Data Source=\\My Documents\
\Tervia.sdf");
SqlCeCommand cmd = new SqlCeCommand(sql,cn);
cmd.Parameters.Add("@UserCode", sUserCode.ToUpper());
cmd.Parameters.Add("@Password", sPassword.ToUpper());

--

Alberto Silva
www.moving2u.pt - R&D Manager
http://msmvps.com/AlbertoSilva - Blog
Microsoft MVP - Device Application Development

"JaffaB" <jaffa_brown@xxxxxxxxxxx> wrote in message news:cc6a7bd3-f1a2-4e62-83dc-15e1572fb6e2@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
All,

I am starting to loose the will to live with SQL Server CE. My
latest problem is with paramitised queries. Some references on the
web say to use "?" as parameters, and others say you can follow the
SQL Server 2000 @parameters (which is the option I have gone for to
make it compatible).

However, when I use the parameters with the @ (as per the code below),
I get a "System.Data.SqlServerCe.SqlCeException" error. I havnt tried
the "?" parameters yet.

what am I doing wrong? My code is as follows:

System.Reflection.Assembly asm =
System.Reflection.Assembly.GetExecutingAssembly();
ResourceManager rm = new ResourceManager
("MBTHandheld.SQLCEQuery",asm);
string sql = rm.GetString("VerifyLogon1");
// Sql returned is SELECT active Active, user_type UserType FROM
bt_user WHERE user_code = @UserCode AND password = @Password
SqlCeConnection cn = new SqlCeConnection("Data Source=\\My Documents\
\Tervia.sdf");
SqlCeCommand cmd = new SqlCeCommand(sql,cn);
cmd.Parameters.Add("@UserCode", sUserCode.ToUpper());
cmd.Parameters.Add("@Password", sPassword.ToUpper());
cmd.Connection.Open();

I know the its the parameters causing the problem, becuase without the
parameters (as per the code below), it works:

System.Reflection.Assembly asm =
System.Reflection.Assembly.GetExecutingAssembly();
ResourceManager rm = new ResourceManager
("MBTHandheld.SQLCEQuery",asm);
string sql = rm.GetString("VerifyLogon2");
// Sql returned is SELECT active Active, user_type UserType FROM
bt_user
SqlCeConnection cn = new SqlCeConnection("Data Source=\\My Documents\
\Tervia.sdf");
SqlCeCommand cmd = new SqlCeCommand(sql,cn);
cmd.Connection.Open();

.

Relevant Pages

  • Re: SQLCeConnection
    ... Thank you so much but this is for SQL net frame work, ... can ping the main server that holds the SQL Server Enterprise as will ... Dim MyCommand As New Data.SqlClient.SqlCommand ... SQlCeConnection and when I actually need to use the SQlceConnection? ...
    (microsoft.public.sqlserver.ce)
  • Re: SQLCeConnection
    ... You only use SqlCe namespace to connect to SQL Mobile, ... SQL Mobile database that came from SQL Server, ... Dim objSQlConnection As New SQlConnection ... SQlCeConnection and when I actually need to use the SQlceConnection? ...
    (microsoft.public.sqlserver.ce)
  • Re: SQL CE 2.0 parameterised queries
    ... As far as I remember, there was no support at all for named parameters in SQL CE 2.0, just in SQL Mobile 3.0 & onwards. ... Microsoft MVP - Device Application Development ... If you use OleDB to access any data source, you must (as Alberto says) use? ... Dim cn As SqlCeConnection ...
    (microsoft.public.sqlserver.ce)
  • Keeping SqlCeConnection Open and Thread Safety
    ... SqlCeConnection object, particularly with regards to two interrelated ... assume that we can safely share the connection object? ... desktop version of SQL Server (SqlConnection and SQL Server Mobile ...
    (microsoft.public.sqlserver.ce)
  • Re: SQL CE 2.0 parameterised queries
    ... When executing the command, you can name the parameters you add, but you must add them by the same order they are referenced in the SQL statement, something like... ... SqlCeCommand cmd = new SqlCeCommand; ... ResourceManager rm = new ResourceManager ...
    (microsoft.public.sqlserver.ce)
Loading