RE: parameter in sql query

• Previous message: Zwyatt: "RE: parameter in sql query"
• In reply to: Zwyatt: "RE: parameter in sql query"
• Next in thread: Alex Feinman [MVP]: "Re: parameter in sql query"
• Messages sorted by: [ date ] [ thread ]

Date: Sat, 09 Oct 2004 04:45:11 GMT

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?= <Zwyatt@discussions.microsoft.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>
> Subject: RE: parameter in sql query
> Date: Fri, 8 Oct 2004 18:59:01 -0700
> Lines: 214
> Message-ID: <BF97311C-75F9-470E-8658-B71F3138F301@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: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]"" 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: Zwyatt: "RE: parameter in sql query"
• In reply to: Zwyatt: "RE: parameter in sql query"
• Next in thread: Alex Feinman [MVP]: "Re: parameter in sql query"
• Messages sorted by: [ date ] [ thread ]