Select Statement
-
hello, my method for retrieving data from the sql server ce database and display it in a data grid looks as follows: dataSet = new DataSet("Jobs"); daptJobs = new SqlCeDataAdapter("Select Priority, Problem, EmailClient From JobDescription Order By Priority", strConn); daptJobs.Fill(dataSet,"Jobs"); DataTable dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; But i want to modify the Select Statement that Priority, Problem and EmailClient is only selected when UserEngineerId - field has a special value and the StartDate field is equal or after the Today Date. The thing with the UserEngineerId works when i quote directly the value in the Select Statement but I want to quote a variable with the certain value..? And the thing with the Date is not really clear for me. thx patrick
Unfortunately your code does not work, I wrote it first without the DateTime Statement in that way: string help = "hattl70"; string selectString = " Select " + " Priority, Problem, EmailClient " + " From JobDescription " + " Where UserEngenieerId = " + help + " Order By Priority "; dataSet = new DataSet("Jobs"); daptJobs = new SqlCeDataAdapter(selectString, strConn); daptJobs.Fill(dataSet,"Jobs"); DataTable dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; I always got a SqlCeException. But the error must lie in the Select Statement because when i wrote it with indicating directly the value it works. Does anybody know what went wrong here?:(:( best regards patrick
-
It's best to use parameterised queries, for example:
SqlCeCommand cmd =
new SqlCeCommand(
"SELECT Priority, Problem, EmailClient " +
"FROM JobDescription " +
"WHERE UserEngineerId = ? AND StartDate >= ? " +
"ORDER BY Priority" );SqlCeParameter param1 =
new SqlCeParameter( "@userID", SqlDbType.Int );
param1.Value = userEngineerID;
cmd.Parameters.Add( param1 );SqlCeParameter param2 =
new SqlCeParameter( "@date", SqlDbType.DateTime );
param2.Value = DateTime.Today;
cmd.Parameters.Add( param2 );cmd.Connection = new SqlCeConnection( strConn );
daptJobs = new SqlCeDataAdapter( cmd );
The two parameters substitute for the ? in the query string, in the order they're added to the Parameters collection. Stability. What an interesting concept. -- Chris Maunder
That is impossible it does still not work, now I wrote it in that way, again without the date property: dataSet = new DataSet("Jobs"); SlCeCommand cmd = new SqlCeCommand( "SELECT Priority, Problem, EmailClient " + "FROM JobDescription " + "WHERE UserEngineerId = ? " + "ORDER BY Priority" ); string userEngineerId = "hattl70"; SqlCeParameter param1 = new SqlCeParameter("@UserEngineerId", SqlDbType.NVarChar ); param1.Value = userEngineerId; cmd.Parameters.Add( param1 ); cmd.Connection = new SqlCeConnection( strConn ); daptJobs = new SqlCeDataAdapter( cmd ); daptJobs.Fill(dataSet,"Jobs"); DataTable dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; The SqlCeException occured again. Is it all the same which name the parameter has in the constructor of the SqlCeParameter? - Because the name for the UserEngineerId is the name I have quoted in the constructor and is of type NVarChar. Does anybody have anymore ideas what went wrong here?:( best regards patrick
-
Unfortunately your code does not work, I wrote it first without the DateTime Statement in that way: string help = "hattl70"; string selectString = " Select " + " Priority, Problem, EmailClient " + " From JobDescription " + " Where UserEngenieerId = " + help + " Order By Priority "; dataSet = new DataSet("Jobs"); daptJobs = new SqlCeDataAdapter(selectString, strConn); daptJobs.Fill(dataSet,"Jobs"); DataTable dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; I always got a SqlCeException. But the error must lie in the Select Statement because when i wrote it with indicating directly the value it works. Does anybody know what went wrong here?:(:( best regards patrick
You have not put quotes around the help insertion. However, if you take Mike's advice to use parameterised queries your application will be more secure also because it is one of the defences you can put up against SQL Injection Attacks[^]
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
-
That is impossible it does still not work, now I wrote it in that way, again without the date property: dataSet = new DataSet("Jobs"); SlCeCommand cmd = new SqlCeCommand( "SELECT Priority, Problem, EmailClient " + "FROM JobDescription " + "WHERE UserEngineerId = ? " + "ORDER BY Priority" ); string userEngineerId = "hattl70"; SqlCeParameter param1 = new SqlCeParameter("@UserEngineerId", SqlDbType.NVarChar ); param1.Value = userEngineerId; cmd.Parameters.Add( param1 ); cmd.Connection = new SqlCeConnection( strConn ); daptJobs = new SqlCeDataAdapter( cmd ); daptJobs.Fill(dataSet,"Jobs"); DataTable dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; The SqlCeException occured again. Is it all the same which name the parameter has in the constructor of the SqlCeParameter? - Because the name for the UserEngineerId is the name I have quoted in the constructor and is of type NVarChar. Does anybody have anymore ideas what went wrong here?:( best regards patrick
pat270881 wrote: The SqlCeException occured again. Does anybody have anymore ideas what went wrong here? What are the details of the exception? There will be a message at least, some details about the state of the database and so on. These really help to track down problems.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
-
pat270881 wrote: The SqlCeException occured again. Does anybody have anymore ideas what went wrong here? What are the details of the exception? There will be a message at least, some details about the state of the database and so on. These really help to track down problems.
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
Okay the error notification looks as follows: Unexpected Error in MobileHelpDesk.exe SqlCeException SqlCeCommand::InitializeCommand+0xf9 SqlCeCommand::ExecuteCommand+0xa SqlCeCommand::ExecuteReaderInternal+0x30 SqlCeCommand::ExecuteReader+0x13 SqlCeCommand::System.Data.IDbCommand.ExecuteReader+0x7 DbDataAdapter::Fill+0x37 DbDataAdapter::Fill+0x85 DbDataAdapter::Fill+0x11 Are anybody able to see with the help of this error what went wrong??:( best regards patrick
-
You have not put quotes around the help insertion. However, if you take Mike's advice to use parameterised queries your application will be more secure also because it is one of the defences you can put up against SQL Injection Attacks[^]
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
-
try the following
string selectString = " Select " + " Priority, Problem, EmailClient " + " From JobDescription " + " Where UserEngenieerId = " + yourValueVariable + " And StartDate >= " + DateTime.Now.Date + " Order By Priority "; daptJobs = new SqlCeDataAdapter(selectString); ....and if the code is not acurate (mening that the date si not precise) onvert it with the sql convert function (you can find it in the sql bible or manual ;)) I hope you understand... By the way... visit http://nehe.gamedev.net[^]NEVER use string concatenation to build SQL queries. ADO.NET supports parameterized queries and you should use them. What you're doing allows for easy SQL injection attacks. Search previous comments for more reasons in the billions of times I've had to say this. Your code - and similar code you may write - is extremely insecure and does not follow good coding practices. Mike's reply right below yours mentions as much, too. This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles] [My Blog]
-
That is impossible it does still not work, now I wrote it in that way, again without the date property: dataSet = new DataSet("Jobs"); SlCeCommand cmd = new SqlCeCommand( "SELECT Priority, Problem, EmailClient " + "FROM JobDescription " + "WHERE UserEngineerId = ? " + "ORDER BY Priority" ); string userEngineerId = "hattl70"; SqlCeParameter param1 = new SqlCeParameter("@UserEngineerId", SqlDbType.NVarChar ); param1.Value = userEngineerId; cmd.Parameters.Add( param1 ); cmd.Connection = new SqlCeConnection( strConn ); daptJobs = new SqlCeDataAdapter( cmd ); daptJobs.Fill(dataSet,"Jobs"); DataTable dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; The SqlCeException occured again. Is it all the same which name the parameter has in the constructor of the SqlCeParameter? - Because the name for the UserEngineerId is the name I have quoted in the constructor and is of type NVarChar. Does anybody have anymore ideas what went wrong here?:( best regards patrick
SQL Server supports named paramters. Use ? for
OleDbCommand. Your expression should look like this:SqlCeCommand cmd = new SqlCeCommand(@"
SELECT Priority, Problem, EmailClient
FROM JobDescription
WHERE UserEngineerId = @UserEngineerId
ORDER BY Priority");When you specify a variable-width field type like
SqlDbType.NVarChar, you should also use the overloadedSqlCeParameterconstructor (orSqlCeCommand.Parameters.Addmethod) to specify the number of characters:SqlCeParameter id = cmd.Parameters.Add("@UserEngineerId", SqlDbType.NVarChar, 40);
id.Value = "hattl70";Read the documentation for either
SqlCeCommand.ParametersorSqlCommand.Parametersfor more information and examples of how to use parameterized queries with SQL Server (CE). This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles] [My Blog] -
SQL Server supports named paramters. Use ? for
OleDbCommand. Your expression should look like this:SqlCeCommand cmd = new SqlCeCommand(@"
SELECT Priority, Problem, EmailClient
FROM JobDescription
WHERE UserEngineerId = @UserEngineerId
ORDER BY Priority");When you specify a variable-width field type like
SqlDbType.NVarChar, you should also use the overloadedSqlCeParameterconstructor (orSqlCeCommand.Parameters.Addmethod) to specify the number of characters:SqlCeParameter id = cmd.Parameters.Add("@UserEngineerId", SqlDbType.NVarChar, 40);
id.Value = "hattl70";Read the documentation for either
SqlCeCommand.ParametersorSqlCommand.Parametersfor more information and examples of how to use parameterized queries with SQL Server (CE). This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles] [My Blog]Mr. Stewart i already tried the version with parameter, but the same error occurs again which i mentined in a previous posting. I wrote the code in that way: (UserEngineerId is EngineerUserId). SqlCeCommand cmd = new SqlCeCommand(@" SELECT Priority, Problem, EmailClient FROM JobDescription WHERE EngineerUserId = @EngineerUserId ORDER BY Priority"); SqlCeParameter id = cmd.Parameters.Add("@EngineerUserId", SqlDbType.NVarChar, 50); id.Value = "hattl70"; dataSet = new DataSet("Jobs"); cmd.Connection = new SqlCeConnection( strConn ); daptJobs = new SqlCeDataAdapter(cmd); daptJobs.Fill(dataSet,"Jobs"); dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; The problem ist that i could no other error then the Where statement because if i don't use this statement the code works without problems. I do not understand this phenomen...:(? regards patrick
-
Mr. Stewart i already tried the version with parameter, but the same error occurs again which i mentined in a previous posting. I wrote the code in that way: (UserEngineerId is EngineerUserId). SqlCeCommand cmd = new SqlCeCommand(@" SELECT Priority, Problem, EmailClient FROM JobDescription WHERE EngineerUserId = @EngineerUserId ORDER BY Priority"); SqlCeParameter id = cmd.Parameters.Add("@EngineerUserId", SqlDbType.NVarChar, 50); id.Value = "hattl70"; dataSet = new DataSet("Jobs"); cmd.Connection = new SqlCeConnection( strConn ); daptJobs = new SqlCeDataAdapter(cmd); daptJobs.Fill(dataSet,"Jobs"); dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; The problem ist that i could no other error then the Where statement because if i don't use this statement the code works without problems. I do not understand this phenomen...:(? regards patrick
Whether your statement works with or without parameters doesn't matter: NEVER, EVER use concatenated SQL expressions unless you like have credit card numbers stolen, employee hours changed, wages and salaries emailed throughout the company, and entire tables and even databases dropped. One guy who posted shortly after you makes it even easier for crackers since he's running the commands as the 'sa' account (which is even worse since he doesn't have a password for 'sa'). Always use the least amount of privileges required to run a particular command, even with SQL Server CE. What exception is occuring and on what line? Does the user you're running under have permissions to the database objects required? Are you sure that
EngineerUserIdis a string (in most database designs anything with "id" is typically an integer or GUID). Are you sure that it's 50 characters wide and that it's an NVarChar (and not a VarChar, "N" means Unicode or "nationalized"). There could also be a problem since yourDataSetandDataTableshare the same name. If you don't care about theDataSetname, then don't name it (just use default instantiation, i.e.new DataSet()). This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles] [My Blog] -
Whether your statement works with or without parameters doesn't matter: NEVER, EVER use concatenated SQL expressions unless you like have credit card numbers stolen, employee hours changed, wages and salaries emailed throughout the company, and entire tables and even databases dropped. One guy who posted shortly after you makes it even easier for crackers since he's running the commands as the 'sa' account (which is even worse since he doesn't have a password for 'sa'). Always use the least amount of privileges required to run a particular command, even with SQL Server CE. What exception is occuring and on what line? Does the user you're running under have permissions to the database objects required? Are you sure that
EngineerUserIdis a string (in most database designs anything with "id" is typically an integer or GUID). Are you sure that it's 50 characters wide and that it's an NVarChar (and not a VarChar, "N" means Unicode or "nationalized"). There could also be a problem since yourDataSetandDataTableshare the same name. If you don't care about theDataSetname, then don't name it (just use default instantiation, i.e.new DataSet()). This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles] [My Blog]The following error occurs: Error #1 of 1 Error Code: -2147217900 Message: There was an error parsing the query. [Token line number, Token line offset,, Token in error,,] Minor Err.: 25501 Source: Microsoft SQL Server 2000 Windows CE Edition Numeric Parameter: 3 Numeric Parameter: 49 Error Parameter: @EngineerUserId As is said it must lie with this Where Statement. Do you have further ideas? - I alread checked your remarks from the previous posting regards patrick
-
The following error occurs: Error #1 of 1 Error Code: -2147217900 Message: There was an error parsing the query. [Token line number, Token line offset,, Token in error,,] Minor Err.: 25501 Source: Microsoft SQL Server 2000 Windows CE Edition Numeric Parameter: 3 Numeric Parameter: 49 Error Parameter: @EngineerUserId As is said it must lie with this Where Statement. Do you have further ideas? - I alread checked your remarks from the previous posting regards patrick
All the syntax looks right. The next thing I would check is to make absolutely sure you spelled all your field and table names correctly in your SELECT statement. Don't think you did, know you did. Go back to the SQL Enterprise Table Designer and look and compare. The first rule of troubleshooting - don't think, know... RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
All the syntax looks right. The next thing I would check is to make absolutely sure you spelled all your field and table names correctly in your SELECT statement. Don't think you did, know you did. Go back to the SQL Enterprise Table Designer and look and compare. The first rule of troubleshooting - don't think, know... RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
I know that i spelled all my fields right. I also use Replication, can this causes the error??? But i do not use Identity property in the context of my Replication, the JobId is a uniqueidentifier. But I do simply not understand why the replication should cause this problem, because when I quote directly a value in the Where Statement - for example the value hattl70, then no error occured...:(( regards patrick
-
That is impossible it does still not work, now I wrote it in that way, again without the date property: dataSet = new DataSet("Jobs"); SlCeCommand cmd = new SqlCeCommand( "SELECT Priority, Problem, EmailClient " + "FROM JobDescription " + "WHERE UserEngineerId = ? " + "ORDER BY Priority" ); string userEngineerId = "hattl70"; SqlCeParameter param1 = new SqlCeParameter("@UserEngineerId", SqlDbType.NVarChar ); param1.Value = userEngineerId; cmd.Parameters.Add( param1 ); cmd.Connection = new SqlCeConnection( strConn ); daptJobs = new SqlCeDataAdapter( cmd ); daptJobs.Fill(dataSet,"Jobs"); DataTable dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; The SqlCeException occured again. Is it all the same which name the parameter has in the constructor of the SqlCeParameter? - Because the name for the UserEngineerId is the name I have quoted in the constructor and is of type NVarChar. Does anybody have anymore ideas what went wrong here?:( best regards patrick
You need to also supply the size of an
NVarCharfield. I assumedIntbecause you didn't say. If the field is declared as nvarchar(20), useSqlCeParameter param1 =
new SqlCeParameter( "@userID", SqlDbType.NVarChar, 20 );SQL Server CE 2.0 doesn't care what you set the name of the SqlCeParameter to, and accepts only a ? character as a parameter marker (as you found out). This is different from the desktop SQL Server, which uses named parameters with a leading @ symbol. You may also need to call
cmd.Connection.Open()before callingFill. If an exception is thrown, check the SqlCeException object'sErrorscollection for more information. Stability. What an interesting concept. -- Chris Maunder -
You need to also supply the size of an
NVarCharfield. I assumedIntbecause you didn't say. If the field is declared as nvarchar(20), useSqlCeParameter param1 =
new SqlCeParameter( "@userID", SqlDbType.NVarChar, 20 );SQL Server CE 2.0 doesn't care what you set the name of the SqlCeParameter to, and accepts only a ? character as a parameter marker (as you found out). This is different from the desktop SQL Server, which uses named parameters with a leading @ symbol. You may also need to call
cmd.Connection.Open()before callingFill. If an exception is thrown, check the SqlCeException object'sErrorscollection for more information. Stability. What an interesting concept. -- Chris MaunderYes i have checked all these things, like the type of the paramter and the size. And I wrote it in the previous posting - The code work without problems, when I quote directly the value, for example hattl70, so it cannot be anything with the connection because otherwise the statement with the direct value would also not work. regards patrick
-
You need to also supply the size of an
NVarCharfield. I assumedIntbecause you didn't say. If the field is declared as nvarchar(20), useSqlCeParameter param1 =
new SqlCeParameter( "@userID", SqlDbType.NVarChar, 20 );SQL Server CE 2.0 doesn't care what you set the name of the SqlCeParameter to, and accepts only a ? character as a parameter marker (as you found out). This is different from the desktop SQL Server, which uses named parameters with a leading @ symbol. You may also need to call
cmd.Connection.Open()before callingFill. If an exception is thrown, check the SqlCeException object'sErrorscollection for more information. Stability. What an interesting concept. -- Chris MaunderIs it in any wise possible that this error causes by the replication, because I created the tables on the desktop and replicated it to the SQL Server CE on my PocketPc. Maybe that the NVarChar was in that way converted in another datatype or anything else?? regards patrick
-
I know that i spelled all my fields right. I also use Replication, can this causes the error??? But i do not use Identity property in the context of my Replication, the JobId is a uniqueidentifier. But I do simply not understand why the replication should cause this problem, because when I quote directly a value in the Where Statement - for example the value hattl70, then no error occured...:(( regards patrick
Replication being turned on will not generate this kind of error for you. What do you mean by "when I quote directly"? There has got to be something wrong with the parameter
@EngieerUserId. You defined your parameter object to be NVARCHAR(50). Are you absolutely positive that this is how it is defined in the table structure? I'd be willing to bet it isn't... RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome -
Replication being turned on will not generate this kind of error for you. What do you mean by "when I quote directly"? There has got to be something wrong with the parameter
@EngieerUserId. You defined your parameter object to be NVARCHAR(50). Are you absolutely positive that this is how it is defined in the table structure? I'd be willing to bet it isn't... RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming GnomeReally, that specification of that field is NVarChar(50) - I can send you the tables, if you want. With direct mean, if I write it in that way SqlCeCommand cmd = new SqlCeCommand(@" SELECT Priority, Problem, EmailClient FROM JobDescription WHERE EngineerUserId = 'hattl70' ORDER BY Priority"); cmd.Connection = new SqlCeConnection( strConn ); dataSet = new DataSet(); daptJobs = new SqlCeDataAdapter(cmd); daptJobs.Fill(dataSet,"Jobs"); dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; MobileHelpDesk.UtilGUI.AddCustomDataTableStyle(dgridDisplay,"Jobs"); When i quote a certain value of the field, for example hattl70 than the corresponding rows are display without an error. Can you explain that yourselves??:( Believe me I tried so much, nearly the whole day. regards patrick
-
The following error occurs: Error #1 of 1 Error Code: -2147217900 Message: There was an error parsing the query. [Token line number, Token line offset,, Token in error,,] Minor Err.: 25501 Source: Microsoft SQL Server 2000 Windows CE Edition Numeric Parameter: 3 Numeric Parameter: 49 Error Parameter: @EngineerUserId As is said it must lie with this Where Statement. Do you have further ideas? - I alread checked your remarks from the previous posting regards patrick
Upon reading the documentation for the
SqlCeParameterandSqlCeCommandin the .NET Framework SDK - which is your job and you should've done - SQL Server CE doesn't use named parameters like SQL Server. You instead use ? and add your parameters in order (still with names) of the ? (question marks). Read the documentation for theSqlCeCommand.Parametersproperty in the .NET Framework SDK and you'll find a good example. This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles] [My Blog] -
All the syntax looks right. The next thing I would check is to make absolutely sure you spelled all your field and table names correctly in your SELECT statement. Don't think you did, know you did. Go back to the SQL Enterprise Table Designer and look and compare. The first rule of troubleshooting - don't think, know... RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
Turns out SQL Server CE doesn't support named parameters. I found that out by reading it quick, something the original poster obvious hasn't done. This posting is provided "AS IS" with no warranties, and confers no rights. Software Design Engineer Developer Division Sustained Engineering Microsoft [My Articles] [My Blog]