|
Upon reading the documentation for the SqlCeParameter and SqlCeCommand in 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 the SqlCeCommand.Parameters property 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
|
|
|
|
|
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]
|
|
|
|
|
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]
|
|
|
|
|
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 EngineerUserId is a string - not an integer - so you need to quote it:
WHERE ENgineerUserId = '" + EngineerUserId "'"; Now all someone would have to do is set EngineerUserId to the following and you or your company is in trouble:
asdf' AND 1=1; DROP TABLE JobDescription; -- Assuming EngineerUserId comes from a TextBox, 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 the SqlCeConnection class 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]
|
|
|
|
|
Mr. Stewart I finally fixed the problem! The problem was in the SQL Query, very trivial.
Thank you very much for your support!!
regards
patrick
|
|
|
|
|
You need to also supply the size of an NVarChar field. I assumed Int because you didn't say. If the field is declared as nvarchar(20), use
SqlCeParameter 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 calling Fill. If an exception is thrown, check the SqlCeException object's Errors collection for more information.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Yes 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
|
|
|
|
|
Is 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
|
|
|
|
|
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[^]
Do you want to know more?
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.
|
|
|
|
|
No more ideas??? (
patrick
|
|
|
|
|
HI~
I want to know the advantage of writing a Window Service instead
of writing a normal window application. It seem to be that swindow
service is kind of application that can be start automatically, or stop
or restart by the Window Service Control Manager. But there seems no more
advantage from it. And all those can be achieved by writing a normal application also. Can the window service communicate with the shell or from other processes (like IPC) so that it excels normal application ?
thanks
|
|
|
|
|
1. The service control manager can restart a service automatically in case of exceptions.
2. A service can run as any user account or system authority.
3. The service control manager checks dependencies (if there are any).
4. You can force a service to be started with Windows, and prevent it from being stopped by the user.
|
|
|
|
|
Just to expand on point 4 above - You can get an application to start WITHOUT any user being logged on. This is one of the main things a service can do that a normal app cannot.
|
|
|
|
|
Services are not meant to have a user interface. They are applications that just provide "services". Such as a Web Server, DHCP Server, an Indexing Service, Network DDE, Authentication Service, ... These servers don't have a user interface and don't need one. They provide their services through an exposed API and/or through communications channels. They run regardless if a user is logged in or not.
There is no question about services having an advantage, but rather these servers REQUIRE that they are run as services. For instance, the services that a Web Server or DHCP Server provides has no use for a user interface, they must run and be available so long as the machine in turned on, and neither cares if a user is logged in or not. Since neither requires any user interaction in order them to work, these are the perfect candidates for a service app.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hi~
I add a process object to the windows service by choosing the "process" in component of toolBox. I specify the processstartInfo and try to create a process when the service start(statement "process1.Start()" is inside "onStart()" method). However, the process only appear in the task manager but the window of the application doesnot appear. Why this happen?
if the type of service is "Local system", the problem above happens. if the service is "Local service", when i try to start the service, a message box come out and said "The service has started and then stop. Some services will stop if they cannot be executed. E.g efficiency record or alert services".
What is the difference between local system and local service?
Thanks
|
|
|
|
|
In order to interact with the desktop you must check the "Interactive" setting in the service dialog, or when you install the service set this property to true.
As for the accounts, here is some info straight from MS:
There are three built-in service accounts:
. LocalSystem: The LocalSystem account is a predefined local account.
It has extensive privileges on the local computer, and acts as the computer
on the network. The name of the account is LocalSystem. This account does
not have a password.
. LocalService: The LocalService account is a predefined local
account. It has minimum privileges on the local computer and presents
anonymous credentials on the network. The name of the account is NT
AUTHORITY\LocalService. This account does not have a password.
. NetworkService: The NetworkService account is a predefined local
account. It has minimum privileges on the local computer and acts as the
computer on the network. The name of the account is NT
AUTHORITY\NetworkService. This account does not have a password.
|
|
|
|
|
HI~
But where should i check the interactive setting in the service dialog and where should i set the property to be true?
Thanks
|
|
|
|
|
Ok, goto the Services applet (Start-->Control Panel-->Adminstrative Tools-->Services).
Find your service and double click (or Right Click-->Properties)
Goto the second tab (Log On)
Choose "Local System Account" radio
Check box labeled "Allow this service to interact with the desktop"
|
|
|
|
|
Thanks^.^
Then can I set this property in my new windows service in program? Then this property can be set automatically after installation of the windows service.
Also, can I write a UI for the windows service to call? For example, in the service solution, add a new project which contains a form. However, when I create a new instance of the form and show it on the service start (i.e inside the onStart() method, there are 2 statements : Form1 f1 = new Form1(); f1.Show();), the form has been shown but the form hang. The controls inside the form cannot be shown and the title of the form show that the form has no response. Is there anything goes wrong?
Thanks
|
|
|
|
|
Hi guys
Does anyone know how to get the label names from logical drives in C# .Net? For example, if I use Directory.GetLogicalDrives(), how do I get the LABELs of the drives:-
- A:\ -> Floppy Disk ( A: )
- C:\ -> Local Disk ( C: )
- E:\ -> CD_ROM ( E: )
Thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
