Select Statement
-
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]
-
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]
Damn. :doh: Neither did I. Who would have thought! I can see Jet not supporting them, but SQL CE? RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
Damn. :doh: Neither did I. Who would have thought! I can see Jet not supporting them, but SQL CE? RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
That's fine - it's not our problem. We're just trying to help, but trying to help one's self should always be the first step. :) SQL CE has to pack a lot of functionality into a little space. Just look at .NET CF. I didn't realize it didn't use named parameters either since I've never really used it much (evaluated it once for a project that I never got around to), but it certainly doesn't surprise me. 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]
-
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]The first time I wanted it to do it that way, but one has quoted me that I can do it with this parameter. Now I implemented it like in the example: string EngineerUserId = "hattl70"; SqlCeConnection conn = new SqlCeConnection(strConn); conn.Open(); SqlCeCommand command = conn.CreateCommand(); command.CommandText = "SELECT JobId, Priority, Problem, EmailClient FROM JobDescription WHERE EngineerUserId = ?"; command.Parameters.Add( "@EngineerUserId", EngineerUserId); command.Prepare(); command.ExecuteNonQuery(); dataSet = new DataSet(); daptJobs = new SqlCeDataAdapter(selectCommand, strConn); daptJobs.Fill(dataSet,"Jobs"); dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; MobileHelpDesk.UtilGUI.AddCustomDataTableStyle(dgridDisplay,"Jobs"); But it would have wondered me very much if it would work.:(:( No the following error occurs: There is a file sharing violation. A different process might be using the file. Minor Err.: 25035 Is it really possible that anything else went wrong? 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] -
And i have no more connection open, only this one. (Because the error indicates that there is another process which has access to the file)=
You do realize that when you close a PocketPC app by default the window is only closed, right? The application is not terminated (unless you explicitly exit the process). This could explain why the file is in use. Other processes may be using the file, too. If you have added a connection between this file and SQL Server Enterprise Manager, for example, it may have a lock on the file (or your application is requesting an exclusive lock on the file). 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 first time I wanted it to do it that way, but one has quoted me that I can do it with this parameter. Now I implemented it like in the example: string EngineerUserId = "hattl70"; SqlCeConnection conn = new SqlCeConnection(strConn); conn.Open(); SqlCeCommand command = conn.CreateCommand(); command.CommandText = "SELECT JobId, Priority, Problem, EmailClient FROM JobDescription WHERE EngineerUserId = ?"; command.Parameters.Add( "@EngineerUserId", EngineerUserId); command.Prepare(); command.ExecuteNonQuery(); dataSet = new DataSet(); daptJobs = new SqlCeDataAdapter(selectCommand, strConn); daptJobs.Fill(dataSet,"Jobs"); dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; MobileHelpDesk.UtilGUI.AddCustomDataTableStyle(dgridDisplay,"Jobs"); But it would have wondered me very much if it would work.:(:( No the following error occurs: There is a file sharing violation. A different process might be using the file. Minor Err.: 25035 Is it really possible that anything else went wrong? patrick
See my comment below about the file being in use. The problem is not related to using or not using parameters. ALWAYS use parameters when possible (for field values and conditionals in WHERE clauses). Except for the possibility of SQL injection attachs when using string concatenation and not properly encoding and checking input (like with PHP or back in the glory days of the original ASP) the resultant SQL expression would be the same. Parameters mitigate almost all - if not all - SQL injection attacks. 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]
-
You do realize that when you close a PocketPC app by default the window is only closed, right? The application is not terminated (unless you explicitly exit the process). This could explain why the file is in use. Other processes may be using the file, too. If you have added a connection between this file and SQL Server Enterprise Manager, for example, it may have a lock on the file (or your application is requesting an exclusive lock on the file). 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]
No, I have checked all these things. There exists only one SQL connection. And where and for which purpose should exist a connection between the SQL Server and the SQL Server Ce. I have a connection between the two only when I press the synchronize button. when I do the same with this code: string EngineerUserId = "hattl70"; SqlCeCommand cmndDB = new SqlCeCommand(); cmndDB.Connection = connection; cmndDB.CommandText = "SELECT JobId, Priority, Problem, EmailClient" + "FROM JobDescription" + "WHERE EngineerUserId=" + EngineerUserId; cmndDB.ExecuteNonQuery(); dataSet = new DataSet(); daptJobs = new SqlCeDataAdapter(selectCommand, strConn); daptJobs.Fill(dataSet,"Jobs"); dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; MobileHelpDesk.UtilGUI.AddCustomDataTableStyle(dgridDisplay,"Jobs"); this.connection.Close(); I got not the file violation, but the error "There Was an error parsing the query" Minor Err:: 25501. I do simply what know what else I should try...?:(:(
-
No, I have checked all these things. There exists only one SQL connection. And where and for which purpose should exist a connection between the SQL Server and the SQL Server Ce. I have a connection between the two only when I press the synchronize button. when I do the same with this code: string EngineerUserId = "hattl70"; SqlCeCommand cmndDB = new SqlCeCommand(); cmndDB.Connection = connection; cmndDB.CommandText = "SELECT JobId, Priority, Problem, EmailClient" + "FROM JobDescription" + "WHERE EngineerUserId=" + EngineerUserId; cmndDB.ExecuteNonQuery(); dataSet = new DataSet(); daptJobs = new SqlCeDataAdapter(selectCommand, strConn); daptJobs.Fill(dataSet,"Jobs"); dtabJobs = dataSet.Tables["Jobs"]; dgridDisplay.DataSource = dtabJobs; MobileHelpDesk.UtilGUI.AddCustomDataTableStyle(dgridDisplay,"Jobs"); this.connection.Close(); I got not the file violation, but the error "There Was an error parsing the query" Minor Err:: 25501. I do simply what know what else I should try...?:(:(
You don't get the file sharing violation because your query couldn't be parsed, which happens before it can execute, which is when it accesses the SQL CE database. If you insist on opening your program and the database up for SQL injection attacks, then remember that
EngineerUserIdis a string - not an integer - so you need to quote it:WHERE ENgineerUserId = '" + EngineerUserId "'";
Now all someone would have to do is set
EngineerUserIdto the following and you or your company is in trouble:asdf' AND 1=1; DROP TABLE JobDescription; --
Assuming
EngineerUserIdcomes from aTextBox, it's no problem to set the text to the string about. Now, no more "JobDescription" table. If they want - and most RDBMS's support schema information - they could get a catalog of all the tables and their columns and, if available, start querying and emailing salaries, changing salaries, or making purchases on company goods, etc. Use parameterized queries - seriously - and make sure all other applications are shutdown. If there is a file sharing violation then another process or thread is most definitely using the SQL CE database file. Make sure you read about the connection string properties and be sure you pass any file sharing property values necessary so that you're not trying to obtain an exclusive lock on the file. You should start by reading the documentation for theSqlCeConnectionclass in the .NET Framework SDK. 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] -
You don't get the file sharing violation because your query couldn't be parsed, which happens before it can execute, which is when it accesses the SQL CE database. If you insist on opening your program and the database up for SQL injection attacks, then remember that
EngineerUserIdis a string - not an integer - so you need to quote it:WHERE ENgineerUserId = '" + EngineerUserId "'";
Now all someone would have to do is set
EngineerUserIdto the following and you or your company is in trouble:asdf' AND 1=1; DROP TABLE JobDescription; --
Assuming
EngineerUserIdcomes from aTextBox, it's no problem to set the text to the string about. Now, no more "JobDescription" table. If they want - and most RDBMS's support schema information - they could get a catalog of all the tables and their columns and, if available, start querying and emailing salaries, changing salaries, or making purchases on company goods, etc. Use parameterized queries - seriously - and make sure all other applications are shutdown. If there is a file sharing violation then another process or thread is most definitely using the SQL CE database file. Make sure you read about the connection string properties and be sure you pass any file sharing property values necessary so that you're not trying to obtain an exclusive lock on the file. You should start by reading the documentation for theSqlCeConnectionclass in the .NET Framework SDK. 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]