|
The problem is the way Access locks records. You can read about this on Technet[^]
Wout Louwers
|
|
|
|
|
Thanks for assistant. I will read this article.
|
|
|
|
|
I have a problem using ADO to run a Stored Procedure in SQL Server from Visual C++ 6.0.
In fact, the problem happens because the stored procedure is parameterized. I create one parameter, append to the command e run. It works fine.
But then I have to run another procedure using the same Command variable. But the parameter I have added to the command is stil there, so I get an error that the number of arguments is invalid for the stored procedure.
Let me show the code:
//Here I create a parameter and append to the command variable
m_pCommand->CommandText = "sp_ODMGroupList";
pParam1 = m_pCommand->CreateParameter ( _bstr_t ("AppID"), adVarChar, adParamInput, 5, _variant_t (m_appID.c_str ()));
m_pCommand->Parameters->Append (pParam1);
//Next, I repeat the step, with another stored procedure
m_pCommand->CommandText = "sp_ODMGroupParamList";
pParam1 = m_pCommand->CreateParameter ( _bstr_t ("AppID"), adVarChar, adParamInput, 5, _variant_t (m_appID.c_str ()));
m_pCommand->Parameters->Append (pParam1);
But when I execute, I get an error that the stored procedure has too many parameters.
So, how do I delete the first parameter?
I have tried this
m_pCommand->Parameters->Delete ((_variant_t) pParam1);
But it says that cannot find the item with this name.
So, how do I append and delete parameters in _CommandPtr type?
Thanks in advance!
|
|
|
|
|
The cleanest way of doing this is to use an entirely new Command object and build it with new parameters.
In your case here, since your reusing the same parameter name with the same type and size, all you need to do is change its value. But even then, I don't recommend doing this.
The reason being is code maintainability. If you change the first stored procedure and its accompanying C++ code, you now have to go and fix the code that modifies this object to be reused with the old 2nd stored procedure.
In short, don't reuse the database objects unless you absolutely have to. Just destroy the old ones and create new ones for each stored procedure you're going to execute.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I have a sql string that gets the top 50 items in a search.
Shomthing like:
<br />
string SQL = "SELECT TOP 50 U.strName " +<br />
"FROM tbUser U " +<br />
"WHERE U.strName LIKE '%om%' "<br />
DataManager dm = new DataManager();<br />
DataTable table = dm.GetDataTable(SQL);<br />
but I also want to find exact how many there are.
My result would be something like "There are 387 names with the searchstring 'om' here are the top 50: ...."
Can this be done with the "SELECT TOP" statement. I'm using MS SQL Server 2000.
Thanks
Thomas
-- modified at 13:15 Friday 20th January, 2006
|
|
|
|
|
You can always run a second query - "SELECT Count(*) From tbUser WHERE ... " - in your search procedure, and return that count with the results.
|
|
|
|
|
Try this
"SELECT TOP 50 U.strName , (Select Count(*) From tbUser) as TotalRecords " +<br />
"FROM tbUser U " +<br />
"WHERE U.strName LIKE '%om%' "
Regards
|
|
|
|
|
Hi,
I have the following SQL:
select a.codeid, b.codeidalias from tblcodes a
left outer join tblcodeslookup b on a.codeid = b.codeid
where a.codeid not in (select codeidalias from tblcodeslookup)
and it produces the following kind of data
codeid codeidalias
1 null
2 3
2 4
2 5
6 7
6 8
6 9
10 null
11 12
11 13
14 null
if you can see the pattern. I have been trying for hours to modify it but i cant . Basically, of the codeids that appear more than once, I just want the first appear, ie.
1 null
2 3
6 7
10 null
11 12
14 null
Can anyone help?
Thanks in advance
Eddie
|
|
|
|
|
Try this (with a MIN and GROUP BY added):
select a.codeid, MIN(b.codeidalias) as MinCodeIdalias from tblcodes a
left outer join tblcodeslookup b on a.codeid = b.codeid
where a.codeid not in (select codeidalias from tblcodeslookup)
group by a.codeid
|
|
|
|
|
Hey there database fans,
If I do this:
declare @stuff xml
set @stuff = '<xml><thing>Word</thing></xml>'
select @stuff.query('/xml/thing') I get returned:
<thing>Word</thing>
How do I make it so I get just:
Word
Any ideas?
Regards,
Rob Philpott.
|
|
|
|
|
Chage it to this:
declare @stuff xml
set @stuff = '<xml><thing>Word</thing></xml>'
select @stuff.value('/xml[1]', 'varchar(20)')
Your query will return an XML element, not a value. You have to call the value method on XML element returned get what you want. Value will only work on one element and return 1 value though, hence the [1] you see in the XQuery.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-- modified at 12:56 Friday 20th January, 2006
|
|
|
|
|
Genius. Thank you!
Regards,
Rob Philpott.
|
|
|
|
|
I have a stored procedure that has two cursors running one after another. I say while @@fetch_status = 0 to enter the loop. when the first one finishes fetch_status is -1 and wont enter the 2nd cursor. can i manually set fetch_status?
Thanks in advance
Stephen
|
|
|
|
|
Let's see the code. It sounds like you haven't "primed the pump" so to speak. The way to do it is to setup your cursor, read the first record, THEN enter the loop controled by @@FETCH_STATUS. Something like this:
DECLARE curMasterGames CURSOR FOR
SELECT MasterGameID FROM MasterGames
OPEN curMasterGames
DECLARE @GameID bigint
-- Prime the pump.
FETCH NEXT FROM curMasterGames INTO @GameID
-- Check to see if the FETCH failed on the first record BEFORE we try
-- to do any processing on that records data.
WHILE @@FETCH_STATUS = 0
BEGIN
-- It seems the FETCH worked, do something with it.
EXEC UpdatePlayerScoresOnMasterGameID @GameID
-- Get the next record.
FETCH NEXT FROM curMasterGames INTO @GameID
END
CLOSE curMasterGames
DEALLOCATE curMasterGames
-- Do the same exact pattern for the next cursor you open.
-- Prime, test for failure, then process your loop.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-- modified at 10:37 Friday 20th January, 2006
|
|
|
|
|
Nice one, thats exactly the problem, thank you ver much
|
|
|
|
|
Hi Dave. I love your responses to CP questions. Always thorough and informative.
Just thought I'd offer you a virtual for your efforts.
|
|
|
|
|
Hey, Thanks! I'm glad to hear to it!
But, I'm not always this way! I can get rushed by my job and just don't have the time to put in a full answer (maybe I shouldn't answer then ), but I try. I also have lots of room for improvement.
I can get pretty short with some people. Specially when they ask a question like "what's wrong with the object I'm holding behind my back?" and don't give ANY details about the code or error message. I swear, I'm just waiting for the post that says "My app doesn't work." and that's it, nothing else!
But, if the question is a good one and all the necessary tidbits are there to figure out what they're trying to do with the code, I'll take the time to dig up the details of the answer and come up with an example if I can.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I know the feeling - all around
|
|
|
|
|
Hi guys howz!!
I 'm trying to retrieve an image from the DB,how can I get the size of that image and display it on a picturebox,or what else can I use to display an image,'cause the picturebox doestn't have a scroll so as to be able to scroll & see the whole image??
pls help with a c# code.
thanks in advance
|
|
|
|
|
Hi,
You can think of some picture viewers whose controls are available. Google might prove to be best for it.
Hope it might help,
With warm regards,
KEDAR
|
|
|
|
|
If you don't want to try to automatically scale the image, you could also try putting the image control inside a Panel with scrollbars and a fixed size.
As for getting the size of the image, that should not be too hard - if you're retrieving it from a database, you'll have to load the image from a stream, so you should be able to check the length of the byte array fetched from the table.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
Thanks Turbochimp!!
I used a picturebox inside a panel with scrollbars & fixed size,and is working exactly as I wanted.
Thanks a lot Guys!!! ;P
|
|
|
|
|
Hi all,
I'm writting importing data from Excel module. But there is a bug I can't fix.
Example: I have Student Name field (I want type of this field is Text) in Excel file, the value of first row is "John", the value of second row is "901". Then I read data from this file by using ADO.NET, the Student Name column in DataTable I receiving has double type. And the value "John" of first row is lost, it becomes DBNULL.
If I input value "'901" for second row, it will run well. But I dont want to use this way. Is there any way to solve this problem, fixing the type of field in Excel?
Note: I had fix type of field in Excel by using Format Cells, but it didn't work.
Please help me!!!!!
Thanks,
Thai Nam
|
|
|
|
|
You might be helped by knowing that there are some options you can apply in the connection string for your ADO.NET connection. The site http://www.connectionstrings.com is a great reference - here's what it posts for an Excel connection string:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
"HDR=Yes;" indicates that the first row contains columnnames, not data
"IMEX=1;" tells the driver to always read "intermixed" data columns as text
TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.
I'm thinking in your case you want the IMEX=1 extended property... maybe the HDR=No property too?
|
|
|
|
|
i have a table like this
|Name|Number|
Sasuko 09
Nichol 07
Joy 07
Michael 09
Simon 09
i try with the following query to get the value integer "2" that is the count of all the row have distinct value in this case 09 and 07:
SELECT DISTINCT TableInformation.Kind
FROM TableInformation;
but i get this:
|Temp|
09
07
if i do Select Count('DISTINCT Number') FROM TableInfo i get 5 that is the number of all the rows
then if i do Select Count(DISTINCT Number) FROM TableInfo i can't run couse sintax error!
What is the correct query?
|
|
|
|