|
Hi D!shan,
thanks for your reply! Your example, which I find extremely interesting is unfortunately not, what I was looking for, because it assumes, that the person running my tool is required to have osql installed on his/her machine.
So I was actually looking for a way to do this through an SqlCommand object. What I've done now is that I've written a parser for SQL script files, which creates a queue of individual commands which can actually be executed through the SqlCommand. Now I'm looking for a way to hook up the output from Sql-Server when doing ExecuteNonQuery()...
Regardless of that, I find your code very useful for many other situations. Be sure of a good rating and thanks a lot.
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Hi!
I'm being plagued by the dreaded Jet error 3218 "Couldn't update; Currently locked" from time to time.
My application is a windows service that instatiates a server object that's being published for remoting using RemotingServices.Marshal(myObj, myURL).
This server object receives requests from remoting clients and reads and writes to an Access 2000 database using OleDb. In addition, the server object performs some actions at the tick of a System.Timers.Timer, reading and writing to the DB as well.
Now sometimes I get this error 3218, even when there are just 1 or 2 clients connected and I can't explain why this is happening.
I'm not using any transactions, so I can't see why any recordset should be locked for a longer period of time.
I also Dispose() of all OleDbConnections as soon as the query is through, thus accumulation of open DB connections shouldn't be an issue either.
Does anyone have an explanation or, even better, a clue on how to avoid this error?
TIA,
mav
|
|
|
|
|
|
Thank you Anders for your reply!
I'm closing all connections already. In fact I only closed the connections in the beginning without disposing and then read somewhere that this might be the reason. So I disposed of all connections after closing them, but to no avail.
I know it might sound silly, but just to make sure I'm not misinterpreting the whole situation, can somebody confirm this:
1) Several threads are accessing the database, so there's the possibility that two or more queries are started simultaneously.
2) I set the locking mode to row level locking in the connect string, so a collision should ONLY be possible when two threads try to access the same row in the same table and at least one of them is writing.
3) When such a collision occurs the DB retries for the number of times specified in the access db (I set it to 10), waiting for the period specified (250ms) every time.
4) So the writing update is taking > 2,5 seconds to complete and at the same time there's a reading access to the same row?
If these assumptions of mine are correct, then reducing the tries/interval should increase the probability of getting a 3218, right?
mav
|
|
|
|
|
Hi
I have a table CandidateTab Which contain the CandidateId and Skill fields and data in this table are in following format.
CandidateID Skill
---------------------------
10 VB.net
10 ASP.net
10 C#
11 Oracle
11 SQL SERVER
How can I make a query to get result in following format
CandidateID Skill
10 VB.net,ASP.net,C#
11 Oracle,SQL SERVER
Thanks
Arvind
|
|
|
|
|
Is not easy to get the result in one simple query! . At least you must write a cursor to doing this..
D!shan
|
|
|
|
|
Try this also.. its little bit matching to ur prob.. OR is it ok to doing this through cursors?
USE pubs
GO
DECLARE @title_ids varchar(150), @delimiter char
SET @delimiter = ','
SELECT @title_ids = COALESCE(@title_ids + @delimiter, '') + title_id FROM titles
SELECT @title_ids AS [List of Title IDs]
D!shan
|
|
|
|
|
THANKS,I will try it
Arvind
Arvind
|
|
|
|
|
Im trying to do the following query on a view in access db:
"SELECT List Totals.Expr1,List Totals.Order ID FROM List Totals"
this is the view definition that i got from the db:
"SELECT Sum([Orders Detail].[Unit Price]*[Orders Detail].[Quantity]) AS Expr1, Orders.[Order ID]\r\nFROM Orders INNER JOIN [Orders Detail] ON Orders.[Order ID] = [Orders Detail].[Order ID]\r\nGROUP BY Orders.[Order ID];\r\n"
and i get syntax error missing operator in query expression List Totals.Expr1
how do i do this right?
|
|
|
|
|
|
Thanks ,this is a step forward , what does the square
brackets mean?
|
|
|
|
|
blankg wrote:
Thanks ,this is a step forward , what does the square
brackets mean?
It's a quote char, so you can use spaces as part of field and table names. If you don't use spaces on field and table names, you can safely ommit them. Actually, most programmers use the "_" char instead of space exactly for this reason.
Yes, even I am blogging now!
|
|
|
|
|
ive been creating a query using date as one of its parameters. the date is of CTime data type.
my code is like this:
CString dbWorkName;
CString dbPICName;
CString dbElementName;
CTime dbDate;
CString readSQL;
double ReviewActualHour = 0;
CString strReviewActualHour;
CString ProjName;
CString strReviewDay;
SYSTEMTIME date;
dbDate.GetAsSystemTime(date);
strReviewDay.Format("%04d-%02d-%02d",date.wYear,date.wMonth,date.wDay);
_bstr_t bstrQuery;
_variant_t getFiled;
_variant_t vRecsAffected;
vRecsAffected = 0L;
readSQL = "SELECT L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd, Sum(L_EveryDay.difference)/60 AS TOTALHOUR ";
readSQL += "FROM T_OccasionName INNER JOIN (T_ProjectDetailName INNER JOIN (T_ProjectName INNER JOIN L_EveryDay ON T_ProjectName.seq = L_EveryDay.project) ON T_ProjectDetailName.seq = L_EveryDay.projectdetail) ON T_OccasionName.seq = L_EveryDay.occasion ";
readSQL += "GROUP BY L_EveryDay.username, T_ProjectName.project, T_ProjectDetailName.detail, T_OccasionName.occasion, L_EveryDay.ymd ";
readSQL += "HAVING (((L_EveryDay.username)=";
readSQL += '"';
readSQL += dbPICName;
readSQL += '"';
readSQL += ") ";
readSQL += "AND ((T_ProjectName.project) LIKE ";
readSQL += '"';
readSQL += '%';
readSQL += ProjName;
readSQL += '"';
readSQL += ") ";
readSQL += "AND ((T_ProjectDetailName.detail)=";
readSQL += '"';
readSQL += dbWorkName;
readSQL += '"';
readSQL += ") ";
readSQL += "AND ((T_OccasionName.occasion)=";
readSQL += '"';
readSQL += dbElementName;
readSQL += '"';
readSQL += "AND ((L_EveryDay.ymd)=";
readSQL += "#";
readSQL += strReviewDay;
readSQL += "#";
readSQL += "));";
bstrQuery = readSQL;
MessageBox(NULL, readSQL, "Test", MB_OK);
try
{
recordSet_ = connection_->Execute(bstrQuery,&vRecsAffected,adOptionUnspecified);
}
catch( _com_error &errMessage )
{
MessageBox(NULL, "failed", "Test", MB_OK);
TRACE(errMessage.Description());
TRACE(errMessage.ErrorMessage());
TRACE(bstrQuery);
}
When the program executes the query, runtime error appears. im having difficulty on how to format the date during the query. In MS Access, the date is enclosed with #. So, i tried the same way too. I tried so many times on formatting the date but no one returns the good result.. so for those, who have encountered the same problem as I am, please help me with this...
what should I do with my query?
thanks in advance
|
|
|
|
|
Use parameters. You'll need a Command object. Parameters send the values directly to the database engine with no translation to and from ambiguous string formats.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Like Mike said, using parameters is best way to do it.
But if you want to build your own SQL statement, try to format the date into yyyy-mm-dd or yyyy/mm/dd format in the SQL statement, e.g. for Access it is #1999/05/24#
By using this you'll:
1. Remove ambiguity between date and month.
2. Remove error when passing date into SQL statement.
Cheers,
Edbert P.
|
|
|
|
|
what is the syntax to perform SELECT query on a stored View in an Access Data Base?
|
|
|
|
|
I am not too familiar with MS Access, but I wouldn't have thought it would be any different from a table:
SELECT * FROM view_name WHERE blah=blah
SELECT * FROM table_name WHERE blah=blah
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
I tried this way ,it didnt work (it works fine with sql server) .
thanks anyway , does anyone have a diffrent way?
|
|
|
|
|
I think it is the same syntax as the above submitted comment. It might be the same between a view in sql server and view in access. Because view in access is using access probject and view is store in the sql server. But some issue you should consern about such as:
1. Does your system connect to sql server database every time? and make sure that it is not interrup.
2. When create database object (table, query, view...) try to use naming convention. (Ex: if you want to create table name "Customers", name it "tblCustomers" instead of "Customers). Naming convention help you to identify the right database object in your SELECT sql statement.
3. Use bracket if your database object name has a space in the middle. (Ex: you have table named "Customer name", in SELECT statement use the bracket to [Customer name], SELECT * FROM [Customer name])
Check it again and told us if it still have a probme. Post an error message when you challenges with it again.
Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Problem solved, thanks a lot to both u guys.
|
|
|
|
|
How could you solve it? Do you found any other solution to solve it or get the solution from our idea? I just want to know, if our idea is right then I will help the other people who have the same problem like you. If it is wrong I will try to research on it. Any way, I still try all of my best to help you and other people. 
Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Using the brackets solved the exception in my query.
|
|
|
|
|
Hello,
In my SQL Server, Table named Hardware, Attributes - HardwareID(VarChar), Price(money). When I want to get the edit text boxes, I declare as
Dim hprice As String = Ctype(e.Item.Cells(4).Controls(0), TextBox).Text
What can I do to stop the error below?
----------------------------------------------------------------------
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: System.FormatException: Input string was not in a correct format. at System.Number.ParseDecimal(String s, NumberStyles style, NumberFormatInfo info) at System.Decimal.Parse(String s, NumberStyles style, IFormatProvider provider) at System.Convert.ToDecimal(String value, IFormatProvider provider) at System.String.System.IConvertible.ToDecimal(IFormatProvider provider) at System.Convert.ToDecimal(Object value) at System.Data.Common.DecimalStorage.Set(Int32 record, Object value) at System.Data.DataColumn.set_Item(Int32 record, Object value)Couldn't store <> in Price Column. Expected type is Decimal.
Concerned
|
|
|
|
|
The error is not occuring on the line you have specified.
From the call stack, it looks like you are attempting to insert an empty string (I am assuming this is coming from the text box). Is this so? If that is the case then it is cannot be converted to a valid decimal format. You need to check for the user inputting invalid information before attempting to insert it into the database.
If you need to insert a NULL valid into the field use System.DBNull.Value
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
From the email that you sent me the appropriate line appears to be:
UpdateCommand.Parameters.Add("@Price", SqlDbType.Decimal, 9).Value = hprice
But only if the textbox used to populate hprice (which you mentioned in your previous post) contains a value that cannot be interpreted as a decimal.
As I've already mentioned, you need to perform validation on the user input to ensure that invalid and nonsense values are rejected properly. As this is a price, I would guess that the only valid characters are the numeric characters and the decimal point (or comma - depending on your locality)
One easy way around this to perform the conversion yourself, then pass the converted value as the parameter. Wrap the conversion in a try catch block so that if the input from the user is incorrect you can catch it and report to the user their input is incorrect.
Decimal convertedHPrice = Convert.ToDecimal(hprice);
UpdateCommand.Parameters.Add("@Price", SqlDbType.Decimal, 9).Value = convertedHPrice
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
The Second EuroCPian Event will be in Brussels on the 4th of September
Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
