Re: parameter in sql query

• Previous message: Alex Feinman [MVP]: "Re: Problem with RasDial function"
• In reply to: Alex Feinman [MVP]: "Re: parameter in sql query"
• Messages sorted by: [ date ] [ thread ]

Date: Sun, 10 Oct 2004 17:42:49 GMT

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]" <public_news@alexfeinman.com>
> References: <D1A689FA-2517-4589-A623-B5307EDA7245@microsoft.com>
<fRycKGYrEHA.4060@cpmsftngxa06.phx.gbl>
<345E7471-8A4D-4B5B-B72F-B35E05763529@microsoft.com>
<VehVPXZrEHA.3356@cpmsftngxa06.phx.gbl>
<BF97311C-75F9-470E-8658-B71F3138F301@microsoft.com>
> 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: <Or3Eu2prEHA.192@tk2msftngp13.phx.gbl>
> 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" <Zwyatt@discussions.microsoft.com> wrote in message
> news:BF97311C-75F9-470E-8658-B71F3138F301@microsoft.com...
> > 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]"" wrote:
> >
> >> 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?= <Zwyatt@discussions.microsoft.com>
> >> > References: <D1A689FA-2517-4589-A623-B5307EDA7245@microsoft.com>
> >> <fRycKGYrEHA.4060@cpmsftngxa06.phx.gbl>
> >> > Subject: RE: parameter in sql query
> >> > Date: Fri, 8 Oct 2004 16:09:03 -0700
> >> > Lines: 123
> >> > Message-ID: <345E7471-8A4D-4B5B-B72F-B35E05763529@microsoft.com>
> >> > 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]"" wrote:
> >> >
> >> > > 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?= <Zwyatt@discussions.microsoft.com>
> >> > > > Subject: parameter in sql query
> >> > > > Date: Fri, 8 Oct 2004 14:25:09 -0700
> >> > > > Lines: 31
> >> > > > Message-ID: <D1A689FA-2517-4589-A623-B5307EDA7245@microsoft.com>
> >> > > > 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
> >> > > >
> >> > >
> >> > >
> >> >
> >>
> >>
>
>
>

• Previous message: Alex Feinman [MVP]: "Re: Problem with RasDial function"
• In reply to: Alex Feinman [MVP]: "Re: parameter in sql query"
• Messages sorted by: [ date ] [ thread ]