Re: parameter in sql query

From: Alex Feinman [MVP] (public_news_at_alexfeinman.com)
Date: 10/10/04 

Date: Sun, 10 Oct 2004 00:49:31 -0700

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
>> > > >
>> > >
>> > >
>> >
>>
>>

Relevant Pages

  • RE: parameter in sql query
    ... Just set connection string to run it. ... > 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 ... > creating a datacomponent with a sql param, ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • Re: Select question
    ... Posting CREATE TABLEs, INSERTs for sample data and expected ... do have in common: SQL. ... >but if table is derived you can't create the same query ... still find SQL Server's optimizer producing odd-looking plans. ...
    (microsoft.public.sqlserver.programming)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
Loading