parameter in sql query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi again,

I created several datasets in a PPC project by dragging the table from the
server explorer into the dataset area. For several of them, I modified the
Fill command to do something a little more interesting that just get the
whole table.

One particular dataset is not working *when running on the ppc*. In 'My
computer' it's fine.

I modified the dataset's fill function to take two parameters, specified in
the query with @Parameter name.

The query is something like this:
"SELECT DataItem1, DataItem2 WHERE DataDate >= @Start AND DataDate <= @End"

The query works fine on My Computer but it's crashing the PPC with a generic
'SqlException' error. I am passing in fine DateTime objects for the @Start
and @End parameters of the Fill().

I found an older newsgroup posting that said Sql queries with parameters
need to use '?' instead of @Start and @End in the CE framework. I tried
replacing @Start and @End w/ '?' in the configure query dialog of the dataset
but whidbey complained when parsing.

Can I do parameterized queries with datasets on PPCs? Is there a workaround
that lets me still use the MS generated dataset code?

thanks again,

- Zack
 
Yes, you can use parameterized queries with DataAdapter on PPC.
You do not need to worry about '?' instead of parameter names, it's only
relevant to SQL Server CE 2.0 provider.

Catch the exception and print out errors from error collection to see why
your query is failing (see documentation on how to do that).
Also note: Whidbey code is in beta. While runtime is quite stable,
designers are not and might generate problematic code.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
 
Thanks for the tip about the error collection -- very useful!

I tried to redo my parameterized query and found that the actual (and only)
error I'm getting is:
"Line 1: Incorrect syntax near 'Start'."

The query that I entered into the dataset config screens for the table
adapter is:
"SELECT BAR_UniqueIdentifier, BAR_EmployeeID,<other fields> FROM dbo.BAR
WHERE BAR_StartTime >= @Start AND BAR_PostedStatus = 'Open'"

When I'm debugging the code, I'm able to get to the code below before it
fails on the this.Adapter.Fill(dataset) line. Inspecting the values of the
Start dateTime show that it looks fine. Any ideas how I should change the
'Start' section of the code so that PPC is ok with it? I tried running the
same code with My Computer as the deployment target and it was ok.

thanks for the help,

- Zack

last bit of code I can see:
public virtual int Fill(BarDataSet dataSet, System.DateTime Start) {
this.Adapter.SelectCommand =
((System.Data.SqlClient.SqlCommand)(this.CommandCollection[0]));
this.Adapter.SelectCommand.Parameters[0].Value =
((System.DateTime)(Start));
if ((this.m_clearBeforeFill == true)) {
dataSet.BAR.Clear();
}
int returnValue = this.Adapter.Fill(dataSet);
return returnValue;
}




Incorrect syntax near 'Start'

"Ilya Tumanov [MS]" said:
Yes, you can use parameterized queries with DataAdapter on PPC.
You do not need to worry about '?' instead of parameter names, it's only
relevant to SQL Server CE 2.0 provider.

Catch the exception and print out errors from error collection to see why
your query is failing (see documentation on how to do that).
Also note: Whidbey code is in beta. While runtime is quite stable,
designers are not and might generate problematic code.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Thread-Topic: parameter in sql query
thread-index: AcStfUohRflTqY+wQ46L4biDOdzIHg==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
Subject: parameter in sql query
Date: Fri, 8 Oct 2004 14:25:09 -0700
Lines: 31
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:62838
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hi again,

I created several datasets in a PPC project by dragging the table from the
server explorer into the dataset area. For several of them, I modified the
Fill command to do something a little more interesting that just get the
whole table.

One particular dataset is not working *when running on the ppc*. In 'My
computer' it's fine.

I modified the dataset's fill function to take two parameters, specified in
the query with @Parameter name.

The query is something like this:
"SELECT DataItem1, DataItem2 WHERE DataDate >= @Start AND DataDate <= @End"

The query works fine on My Computer but it's crashing the PPC with a generic
'SqlException' error. I am passing in fine DateTime objects for the @Start
and @End parameters of the Fill().

I found an older newsgroup posting that said Sql queries with parameters
need to use '?' instead of @Start and @End in the CE framework. I tried
replacing @Start and @End w/ '?' in the configure query dialog of the dataset
but whidbey complained when parsing.

Can I do parameterized queries with datasets on PPCs? Is there a workaround
that lets me still use the MS generated dataset code?

thanks again,

- Zack
Click to expand...
Click to expand...
 
This error comes from SQL Server; it does not like your query for some
reason.
For starters, I don't see how you set parameter name if your code.
I would also change the parameter name in case it matches some reserved
word.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Thread-Topic: parameter in sql query
thread-index: AcSti82CsD86/Eq4QWGDDRanJ/veIw==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
References: <[email protected]>
Click to expand...
Subject: RE: parameter in sql query
Date: Fri, 8 Oct 2004 16:09:03 -0700
Lines: 123
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:62844
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Thanks for the tip about the error collection -- very useful!

I tried to redo my parameterized query and found that the actual (and only)
error I'm getting is:
"Line 1: Incorrect syntax near 'Start'."

The query that I entered into the dataset config screens for the table
adapter is:
"SELECT BAR_UniqueIdentifier, BAR_EmployeeID,<other fields> FROM dbo.BAR
WHERE BAR_StartTime >= @Start AND BAR_PostedStatus = 'Open'"

When I'm debugging the code, I'm able to get to the code below before it
fails on the this.Adapter.Fill(dataset) line. Inspecting the values of the
Start dateTime show that it looks fine. Any ideas how I should change the
'Start' section of the code so that PPC is ok with it? I tried running the
same code with My Computer as the deployment target and it was ok.

thanks for the help,

- Zack

last bit of code I can see:
public virtual int Fill(BarDataSet dataSet, System.DateTime Start) {
this.Adapter.SelectCommand =
((System.Data.SqlClient.SqlCommand)(this.CommandCollection[0]));
this.Adapter.SelectCommand.Parameters[0].Value =
((System.DateTime)(Start));
if ((this.m_clearBeforeFill == true)) {
dataSet.BAR.Clear();
}
int returnValue = this.Adapter.Fill(dataSet);
return returnValue;
}




Incorrect syntax near 'Start'

"Ilya Tumanov [MS]" said:
Yes, you can use parameterized queries with DataAdapter on PPC.
You do not need to worry about '?' instead of parameter names, it's only
relevant to SQL Server CE 2.0 provider.

Catch the exception and print out errors from error collection to see why
your query is failing (see documentation on how to do that).
Also note: Whidbey code is in beta. While runtime is quite stable,
designers are not and might generate problematic code.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Thread-Topic: parameter in sql query
thread-index: AcStfUohRflTqY+wQ46L4biDOdzIHg==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
Subject: parameter in sql query
Date: Fri, 8 Oct 2004 14:25:09 -0700
Lines: 31
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:62838
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hi again,

I created several datasets in a PPC project by dragging the table
Click to expand...
from
the
server explorer into the dataset area. For several of them, I
Click to expand...
modified
the
Fill command to do something a little more interesting that just get the
whole table.

One particular dataset is not working *when running on the ppc*. In 'My
computer' it's fine.

I modified the dataset's fill function to take two parameters,
Click to expand...
specified
in
the query with @Parameter name.

The query is something like this:
"SELECT DataItem1, DataItem2 WHERE DataDate >= @Start AND DataDate
Click to expand...
<=
@End"
The query works fine on My Computer but it's crashing the PPC with a generic
'SqlException' error. I am passing in fine DateTime objects for the @Start
and @End parameters of the Fill().

I found an older newsgroup posting that said Sql queries with parameters
need to use '?' instead of @Start and @End in the CE framework. I tried
replacing @Start and @End w/ '?' in the configure query dialog of the dataset
but whidbey complained when parsing.

Can I do parameterized queries with datasets on PPCs? Is there a workaround
that lets me still use the MS generated dataset code?

thanks again,

- Zack
Click to expand...
Click to expand...
Click to expand...
 
Does the parameter substitution occur after or before the query is sent to
the DB?

If the substition is before...the error seems to imply that the query is
being transferred to the sql server without the parameter being replaced by
my own data. '@Start' is the name of the param and the error says there is a
syntax error near 'Start'....did my query get transferred over to SQL as
simply ...
WHERE StartTime = @Start (or something like this)
instead of
WHERE StartTime = '<replaced param by C#>'

I tried writing some queries to the SQL server but couldn't reproduce the
exact incorrect syntax error to see what SQL thinks I'm sending it.

Btw -- I set the parameter in the Fill function. When I modify the query to
have an params with @'s, whidbey automatically adds the params to the Fill()
arguments of the adapter. For example, instead of Fill(DataSet ds) I get
Fill(DataSet ds, DateTime Start) when I have an @Start in the query.

To make sure nothing else is interfering I tried starting a new project,
creating a datacomponent with a sql param, and running it. Same error with
the new param in the query.

All of these datasets and queries work fine when I run the project to My
Computer instead of Pocket PC device.

Thanks for your continued help,
- Zack


"Ilya Tumanov [MS]" said:
This error comes from SQL Server; it does not like your query for some
reason.
For starters, I don't see how you set parameter name if your code.
I would also change the parameter name in case it matches some reserved
word.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Thread-Topic: parameter in sql query
thread-index: AcSti82CsD86/Eq4QWGDDRanJ/veIw==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
References: <[email protected]>
Click to expand...
Subject: RE: parameter in sql query
Date: Fri, 8 Oct 2004 16:09:03 -0700
Lines: 123
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:62844
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Thanks for the tip about the error collection -- very useful!

I tried to redo my parameterized query and found that the actual (and only)
error I'm getting is:
"Line 1: Incorrect syntax near 'Start'."

The query that I entered into the dataset config screens for the table
adapter is:
"SELECT BAR_UniqueIdentifier, BAR_EmployeeID,<other fields> FROM dbo.BAR
WHERE BAR_StartTime >= @Start AND BAR_PostedStatus = 'Open'"

When I'm debugging the code, I'm able to get to the code below before it
fails on the this.Adapter.Fill(dataset) line. Inspecting the values of the
Start dateTime show that it looks fine. Any ideas how I should change the
'Start' section of the code so that PPC is ok with it? I tried running the
same code with My Computer as the deployment target and it was ok.

thanks for the help,

- Zack

last bit of code I can see:
public virtual int Fill(BarDataSet dataSet, System.DateTime Start) {
this.Adapter.SelectCommand =
((System.Data.SqlClient.SqlCommand)(this.CommandCollection[0]));
this.Adapter.SelectCommand.Parameters[0].Value =
((System.DateTime)(Start));
if ((this.m_clearBeforeFill == true)) {
dataSet.BAR.Clear();
}
int returnValue = this.Adapter.Fill(dataSet);
return returnValue;
}




Incorrect syntax near 'Start'

"Ilya Tumanov [MS]" said:
Yes, you can use parameterized queries with DataAdapter on PPC.
You do not need to worry about '?' instead of parameter names, it's only
relevant to SQL Server CE 2.0 provider.

Catch the exception and print out errors from error collection to see why
your query is failing (see documentation on how to do that).
Also note: Whidbey code is in beta. While runtime is quite stable,
designers are not and might generate problematic code.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Thread-Topic: parameter in sql query
thread-index: AcStfUohRflTqY+wQ46L4biDOdzIHg==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
Subject: parameter in sql query
Date: Fri, 8 Oct 2004 14:25:09 -0700
Lines: 31
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:62838
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hi again,

I created several datasets in a PPC project by dragging the table from
the
server explorer into the dataset area. For several of them, I modified
the
Fill command to do something a little more interesting that just get the
whole table.

One particular dataset is not working *when running on the ppc*. In 'My
computer' it's fine.

I modified the dataset's fill function to take two parameters, specified
in
the query with @Parameter name.

The query is something like this:
"SELECT DataItem1, DataItem2 WHERE DataDate >= @Start AND DataDate <=
@End"

The query works fine on My Computer but it's crashing the PPC with a
generic
'SqlException' error. I am passing in fine DateTime objects for the
@Start
and @End parameters of the Fill().

I found an older newsgroup posting that said Sql queries with parameters
need to use '?' instead of @Start and @End in the CE framework. I tried
replacing @Start and @End w/ '?' in the configure query dialog of the
dataset
but whidbey complained when parsing.

Can I do parameterized queries with datasets on PPCs? Is there a
workaround
that lets me still use the MS generated dataset code?

thanks again,

- Zack
Click to expand...
Click to expand...
Click to expand...
 
Here's a working sample which gets some data from Northwind database and
uses parameter.
Just set connection string to run it.

using System;
using System.Data;
using System.Text;
using System.IO;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace tests
{
public class TestAddNew : Form
{
DataGrid dg;

public TestAddNew(String[] args)
{

dg = new DataGrid();
dg.Parent = this;

string connString = "SET CONNECTION STRING HERE!!!";

SqlCommand command = new SqlCommand("select * from customers
where Country = @Country");

SqlConnection conn = new SqlConnection(connString);
conn.Open();
command.Connection = conn;
command.Parameters.Add (new SqlParameter("@Country", "Spain"));

DataSet ds = new DataSet();

SqlDataAdapter da = new SqlDataAdapter(command);

da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

da.Fill(ds);

dg.DataSource = ds.Tables[0];

conn.Close();
}


public static void Main(string[] args)
{
Application.Run(new TestAddNew(args));
}
}

}

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
Thread-Topic: parameter in sql query
thread-index: AcSto4xdUgkNvlaITdKrYpDbsmRITg==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
References: <[email protected]>
Click to expand...
<[email protected]>
Subject: RE: parameter in sql query
Date: Fri, 8 Oct 2004 18:59:01 -0700
Lines: 214
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:62848
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Does the parameter substitution occur after or before the query is sent to
the DB?

If the substition is before...the error seems to imply that the query is
being transferred to the sql server without the parameter being replaced by
my own data. '@Start' is the name of the param and the error says there is a
syntax error near 'Start'....did my query get transferred over to SQL as
simply ...
WHERE StartTime = @Start (or something like this)
instead of
WHERE StartTime = '<replaced param by C#>'

I tried writing some queries to the SQL server but couldn't reproduce the
exact incorrect syntax error to see what SQL thinks I'm sending it.

Btw -- I set the parameter in the Fill function. When I modify the query to
have an params with @'s, whidbey automatically adds the params to the Fill()
arguments of the adapter. For example, instead of Fill(DataSet ds) I get
Fill(DataSet ds, DateTime Start) when I have an @Start in the query.

To make sure nothing else is interfering I tried starting a new project,
creating a datacomponent with a sql param, and running it. Same error with
the new param in the query.

All of these datasets and queries work fine when I run the project to My
Computer instead of Pocket PC device.

Thanks for your continued help,
- Zack


"Ilya Tumanov [MS]" said:
This error comes from SQL Server; it does not like your query for some
reason.
For starters, I don't see how you set parameter name if your code.
I would also change the parameter name in case it matches some reserved
word.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Thread-Topic: parameter in sql query
thread-index: AcSti82CsD86/Eq4QWGDDRanJ/veIw==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
References: <[email protected]>
Click to expand...
Subject: RE: parameter in sql query
Date: Fri, 8 Oct 2004 16:09:03 -0700
Lines: 123
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:62844
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Thanks for the tip about the error collection -- very useful!

I tried to redo my parameterized query and found that the actual (and only)
error I'm getting is:
"Line 1: Incorrect syntax near 'Start'."

The query that I entered into the dataset config screens for the table
adapter is:
"SELECT BAR_UniqueIdentifier, BAR_EmployeeID,<other fields> FROM dbo.BAR
WHERE BAR_StartTime >= @Start AND BAR_PostedStatus = 'Open'"

When I'm debugging the code, I'm able to get to the code below before it
fails on the this.Adapter.Fill(dataset) line. Inspecting the values
Click to expand...
of
the
Start dateTime show that it looks fine. Any ideas how I should
Click to expand...
change
the
'Start' section of the code so that PPC is ok with it? I tried
Click to expand...
running
the
same code with My Computer as the deployment target and it was ok.

thanks for the help,

- Zack

last bit of code I can see:
public virtual int Fill(BarDataSet dataSet, System.DateTime Start) {
this.Adapter.SelectCommand =
((System.Data.SqlClient.SqlCommand)(this.CommandCollection[0]));
this.Adapter.SelectCommand.Parameters[0].Value =
((System.DateTime)(Start));
if ((this.m_clearBeforeFill == true)) {
dataSet.BAR.Clear();
}
int returnValue = this.Adapter.Fill(dataSet);
return returnValue;
}




Incorrect syntax near 'Start'

:

Yes, you can use parameterized queries with DataAdapter on PPC.
You do not need to worry about '?' instead of parameter names, it's only
relevant to SQL Server CE 2.0 provider.

Catch the exception and print out errors from error collection to
Click to expand...
see
why
your query is failing (see documentation on how to do that).
Also note: Whidbey code is in beta. While runtime is quite stable,
designers are not and might generate problematic code.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Thread-Topic: parameter in sql query
thread-index: AcStfUohRflTqY+wQ46L4biDOdzIHg==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
Subject: parameter in sql query
Date: Fri, 8 Oct 2004 14:25:09 -0700
Lines: 31
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:62838
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hi again,

I created several datasets in a PPC project by dragging the table from
the
server explorer into the dataset area. For several of them, I modified
the
Fill command to do something a little more interesting that just
Click to expand...
get
the
whole table.

One particular dataset is not working *when running on the ppc*.
Click to expand...
In
'My
computer' it's fine.

I modified the dataset's fill function to take two parameters, specified
in
the query with @Parameter name.

The query is something like this:
"SELECT DataItem1, DataItem2 WHERE DataDate >= @Start AND
Click to expand...
DataDate
<=
@End"

The query works fine on My Computer but it's crashing the PPC with a
generic
'SqlException' error. I am passing in fine DateTime objects for the
@Start
and @End parameters of the Fill().

I found an older newsgroup posting that said Sql queries with parameters
need to use '?' instead of @Start and @End in the CE framework.
Click to expand...
I
tried
replacing @Start and @End w/ '?' in the configure query dialog of the
dataset
but whidbey complained when parsing.

Can I do parameterized queries with datasets on PPCs? Is there a
workaround
that lets me still use the MS generated dataset code?

thanks again,

- Zack
Click to expand...
Click to expand...
Click to expand...
 
It's hard to tell what's happening because the designer seems to have mind
of its own. Could you try expanding the autogenerated code and posting
relevant parts of it (the ones dealing with dataset population)?

--
Alex Feinman
---
Visit http://www.opennetcf.org
Zwyatt said:
Does the parameter substitution occur after or before the query is sent to
the DB?

If the substition is before...the error seems to imply that the query is
being transferred to the sql server without the parameter being replaced
by
my own data. '@Start' is the name of the param and the error says there
is a
syntax error near 'Start'....did my query get transferred over to SQL as
simply ...
WHERE StartTime = @Start (or something like this)
instead of
WHERE StartTime = '<replaced param by C#>'

I tried writing some queries to the SQL server but couldn't reproduce the
exact incorrect syntax error to see what SQL thinks I'm sending it.

Btw -- I set the parameter in the Fill function. When I modify the query
to
have an params with @'s, whidbey automatically adds the params to the
Fill()
arguments of the adapter. For example, instead of Fill(DataSet ds) I get
Fill(DataSet ds, DateTime Start) when I have an @Start in the query.

To make sure nothing else is interfering I tried starting a new project,
creating a datacomponent with a sql param, and running it. Same error
with
the new param in the query.

All of these datasets and queries work fine when I run the project to My
Computer instead of Pocket PC device.

Thanks for your continued help,
- Zack


"Ilya Tumanov [MS]" said:
This error comes from SQL Server; it does not like your query for some
reason.
For starters, I don't see how you set parameter name if your code.
I would also change the parameter name in case it matches some reserved
word.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------
Thread-Topic: parameter in sql query
thread-index: AcSti82CsD86/Eq4QWGDDRanJ/veIw==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
References: <[email protected]>
Click to expand...
Subject: RE: parameter in sql query
Date: Fri, 8 Oct 2004 16:09:03 -0700
Lines: 123
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:62844
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Thanks for the tip about the error collection -- very useful!

I tried to redo my parameterized query and found that the actual (and only)
error I'm getting is:
"Line 1: Incorrect syntax near 'Start'."

The query that I entered into the dataset config screens for the table
adapter is:
"SELECT BAR_UniqueIdentifier, BAR_EmployeeID,<other fields> FROM
dbo.BAR
WHERE BAR_StartTime >= @Start AND BAR_PostedStatus = 'Open'"

When I'm debugging the code, I'm able to get to the code below before
it
fails on the this.Adapter.Fill(dataset) line. Inspecting the values of the
Start dateTime show that it looks fine. Any ideas how I should change the
'Start' section of the code so that PPC is ok with it? I tried running the
same code with My Computer as the deployment target and it was ok.

thanks for the help,

- Zack

last bit of code I can see:
public virtual int Fill(BarDataSet dataSet, System.DateTime Start) {
this.Adapter.SelectCommand =
((System.Data.SqlClient.SqlCommand)(this.CommandCollection[0]));
this.Adapter.SelectCommand.Parameters[0].Value =
((System.DateTime)(Start));
if ((this.m_clearBeforeFill == true)) {
dataSet.BAR.Clear();
}
int returnValue = this.Adapter.Fill(dataSet);
return returnValue;
}




Incorrect syntax near 'Start'

:

Yes, you can use parameterized queries with DataAdapter on PPC.
You do not need to worry about '?' instead of parameter names, it's only
relevant to SQL Server CE 2.0 provider.

Catch the exception and print out errors from error collection to see why
your query is failing (see documentation on how to do that).
Also note: Whidbey code is in beta. While runtime is quite stable,
designers are not and might generate problematic code.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
Thread-Topic: parameter in sql query
thread-index: AcStfUohRflTqY+wQ46L4biDOdzIHg==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
Subject: parameter in sql query
Date: Fri, 8 Oct 2004 14:25:09 -0700
Lines: 31
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:62838
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hi again,

I created several datasets in a PPC project by dragging the table from
the
server explorer into the dataset area. For several of them, I modified
the
Fill command to do something a little more interesting that just
get the
whole table.

One particular dataset is not working *when running on the ppc*.
In 'My
computer' it's fine.

I modified the dataset's fill function to take two parameters, specified
in
the query with @Parameter name.

The query is something like this:
"SELECT DataItem1, DataItem2 WHERE DataDate >= @Start AND DataDate <=
@End"

The query works fine on My Computer but it's crashing the PPC with
a
generic
'SqlException' error. I am passing in fine DateTime objects for
the
@Start
and @End parameters of the Fill().

I found an older newsgroup posting that said Sql queries with parameters
need to use '?' instead of @Start and @End in the CE framework. I tried
replacing @Start and @End w/ '?' in the configure query dialog of
the
dataset
but whidbey complained when parsing.

Can I do parameterized queries with datasets on PPCs? Is there a
workaround
that lets me still use the MS generated dataset code?

thanks again,

- Zack
Click to expand...
Click to expand...
Click to expand...
 
Most likely designer omits '@' in parameter name.
Desktop provider would silently add it, CF provider won't.
I would be very careful with designers in Whidbey beta.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
From: "Alex Feinman [MVP]" <[email protected]>
References: <[email protected]>
Click to expand...
<[email protected]>
<[email protected]>
Subject: Re: parameter in sql query
Date: Sun, 10 Oct 2004 00:49:31 -0700
Lines: 240
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
Message-ID: <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: 204.249.181.133
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.compactframework:62877
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

It's hard to tell what's happening because the designer seems to have mind
of its own. Could you try expanding the autogenerated code and posting
relevant parts of it (the ones dealing with dataset population)?

--
Alex Feinman
---
Visit http://www.opennetcf.org
Zwyatt said:
Does the parameter substitution occur after or before the query is sent to
the DB?

If the substition is before...the error seems to imply that the query is
being transferred to the sql server without the parameter being replaced
by
my own data. '@Start' is the name of the param and the error says there
is a
syntax error near 'Start'....did my query get transferred over to SQL as
simply ...
WHERE StartTime = @Start (or something like this)
instead of
WHERE StartTime = '<replaced param by C#>'

I tried writing some queries to the SQL server but couldn't reproduce the
exact incorrect syntax error to see what SQL thinks I'm sending it.

Btw -- I set the parameter in the Fill function. When I modify the query
to
have an params with @'s, whidbey automatically adds the params to the
Fill()
arguments of the adapter. For example, instead of Fill(DataSet ds) I get
Fill(DataSet ds, DateTime Start) when I have an @Start in the query.

To make sure nothing else is interfering I tried starting a new project,
creating a datacomponent with a sql param, and running it. Same error
with
the new param in the query.

All of these datasets and queries work fine when I run the project to My
Computer instead of Pocket PC device.

Thanks for your continued help,
- Zack


"Ilya Tumanov [MS]" said:
This error comes from SQL Server; it does not like your query for some
reason.
For starters, I don't see how you set parameter name if your code.
I would also change the parameter name in case it matches some reserved
word.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------
Thread-Topic: parameter in sql query
thread-index: AcSti82CsD86/Eq4QWGDDRanJ/veIw==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
References: <[email protected]>
<[email protected]>
Subject: RE: parameter in sql query
Date: Fri, 8 Oct 2004 16:09:03 -0700
Lines: 123
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:62844
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Thanks for the tip about the error collection -- very useful!

I tried to redo my parameterized query and found that the actual (and
only)
error I'm getting is:
"Line 1: Incorrect syntax near 'Start'."

The query that I entered into the dataset config screens for the table
adapter is:
"SELECT BAR_UniqueIdentifier, BAR_EmployeeID,<other fields> FROM
dbo.BAR
WHERE BAR_StartTime >= @Start AND BAR_PostedStatus = 'Open'"

When I'm debugging the code, I'm able to get to the code below before
it
fails on the this.Adapter.Fill(dataset) line. Inspecting the values of
the
Start dateTime show that it looks fine. Any ideas how I should change
the
'Start' section of the code so that PPC is ok with it? I tried running
the
same code with My Computer as the deployment target and it was ok.

thanks for the help,

- Zack

last bit of code I can see:
public virtual int Fill(BarDataSet dataSet, System.DateTime
Start) {
this.Adapter.SelectCommand =
((System.Data.SqlClient.SqlCommand)(this.CommandCollection[0]));
this.Adapter.SelectCommand.Parameters[0].Value =
((System.DateTime)(Start));
if ((this.m_clearBeforeFill == true)) {
dataSet.BAR.Clear();
}
int returnValue = this.Adapter.Fill(dataSet);
return returnValue;
}




Incorrect syntax near 'Start'

:

Yes, you can use parameterized queries with DataAdapter on PPC.
You do not need to worry about '?' instead of parameter names, it's
only
relevant to SQL Server CE 2.0 provider.

Catch the exception and print out errors from error collection to see
why
your query is failing (see documentation on how to do that).
Also note: Whidbey code is in beta. While runtime is quite stable,
designers are not and might generate problematic code.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------
Thread-Topic: parameter in sql query
thread-index: AcStfUohRflTqY+wQ46L4biDOdzIHg==
X-WBNR-Posting-Host: 216.153.151.17
From: =?Utf-8?B?Wnd5YXR0?= <[email protected]>
Subject: parameter in sql query
Date: Fri, 8 Oct 2004 14:25:09 -0700
Lines: 31
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:62838
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hi again,

I created several datasets in a PPC project by dragging the table
from
the
server explorer into the dataset area. For several of them, I
modified
the
Fill command to do something a little more interesting that just
get
the
whole table.

One particular dataset is not working *when running on the ppc*.
In
'My
computer' it's fine.

I modified the dataset's fill function to take two parameters,
specified
in
the query with @Parameter name.

The query is something like this:
"SELECT DataItem1, DataItem2 WHERE DataDate >= @Start AND DataDate
<=
@End"

The query works fine on My Computer but it's crashing the PPC with
a
generic
'SqlException' error. I am passing in fine DateTime objects for
the
@Start
and @End parameters of the Fill().

I found an older newsgroup posting that said Sql queries with
parameters
need to use '?' instead of @Start and @End in the CE framework. I
tried
replacing @Start and @End w/ '?' in the configure query dialog of
the
dataset
but whidbey complained when parsing.

Can I do parameterized queries with datasets on PPCs? Is there a
workaround
that lets me still use the MS generated dataset code?

thanks again,

- Zack
Click to expand...
Click to expand...
Click to expand...
 
Back
Top