Error when executing SQLCommand using C#

Question

i'm pretty new to ADO.NET using C# (Visual Studio 2010). Using LinqToSql isn't an option because underlying database is a Compact Edition 3.5 (unfortunealty).

with the code displayed underneath I get an error: "There was an error parsing the query. [ Token line number = 2,Token line offset = 38,Token in error = AgentName ]"

Can someone tell me what i'm doing wrong?

 using (SqlCeConnection oConn = new SqlCeConnection(connectionstring))
        {
            string strSql = @"select 
                        a.name as 'AgentName',
                        t.description as 'JobType',
                        s.description as 'Status',
                        count(j.statusid) as 'Count'
                    from 
                        jobs as j 
                            inner join agents as a on j.agentid = a.id
                            inner join statusdictionary as s on j.statusid = s.id
                            inner join jobtypedictionary as t on j.jobtypeid = t.id
                    where 
                        convert(datetime,starttime,0) between @FirstDate and @LastDate 
                        AND j.JobTypeID = @JobTypeID AND j.AgentID = @AgentID
                    group by 
                        s.description, 
                        t.description, 
                        a.name order by a.name,     
                        t.description";
            SqlCeCommand oCmd = new SqlCeCommand(strSql, oConn);

            SqlCeParameter fdparam = new SqlCeParameter();
            fdparam.ParameterName = "@FirstDate";
            fdparam.Value = firstdate;
            oCmd.Parameters.Add(fdparam);

            SqlCeParameter ldparam = new SqlCeParameter();
            ldparam.ParameterName = "@LastDate";
            ldparam.Value = lastdate ;
            oCmd.Parameters.Add(ldparam);

            SqlCeParameter JIDparam = new SqlCeParameter();
            JIDparam.ParameterName = "@JobTypeID";
            JIDparam.Value = jobtypeid;
            oCmd.Parameters.Add(JIDparam);

            SqlCeParameter AIDparam = new SqlCeParameter();
            AIDparam.ParameterName = "@AgentID";
            AIDparam.Value = jobtypeid;
            oCmd.Parameters.Add(AIDparam);

            oConn.Open();
            SqlCeDataReader oReader = oCmd.ExecuteReader();

Did you try to execute the query without column aliases or did you try to change the aliases to 'col1', 'col2', ...? — Karel Frajták
– Karel Frajták, Commented Sep 27, 2011 at 8:50
Linq to SQL works with SQL Server Compact 3.5 (using sqlmetal.exe from command line) — ErikEJ
– ErikEJ, Commented Sep 27, 2011 at 8:52

hcb · Accepted Answer · 2011-09-27 08:49:32Z

4

i think you need to lose the quotes around 'AgentName'.

answered Sep 27, 2011 at 8:49

hcb

8,3971 gold badge20 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Markis · Accepted Answer · 2011-09-27 08:51:59Z

1

Change single quotes '' to brackets []

a.name as [AgentName],

answered Sep 27, 2011 at 8:51

Markis

1,83417 silver badges16 bronze badges

Collectives™ on Stack Overflow

Error when executing SQLCommand using C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related