|
Using SQL Server & Stored Procedures...
I am getting a bad RecordSet as a result of an Execute() call. Upon attempting to use the RecordSet, and checking EOF I am getting an HRESULT of 0x800A0E78 ("operation not allowed when object is closed")
Upon checking the database to see if the data made it -- it did. So the stored procedure looks OK. Also I tested the stored procedure from the "SQL Query Analyzer" and it was returning the expected data -- a single row with a single field.
Any clues out there to what could be causing this? I know it's hard without seeing code, but any hints about common issues would be great.
Thanks in advance
-peter
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br />
Peter Weyzen<br />
Staff Engineer<br />
<A HREF="http://www.santacruznetworks.com">Santa Cruz Networks</A>
|
|
|
|
|
When a stored procedure does other things before returning a set of records, it may be interpreted as returning multiple recordsets by the code. When I worked with stored procedures a lot, I used to put a SET NOCOUNT ON line at the top of the stored procedure to fool it into thinking there was only one recordset.
That was a long time ago, so I do not know if it still works with today's drivers/providers.
my blog
|
|
|
|
|
Thank you so much -- that was it.
My stored procedures "do a few things"... and adding the "SET NOCOUNT ON" made em work!
Thanks
and Regards
-peter
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<br />
Peter Weyzen<br />
Staff Engineer<br />
<A HREF="http://www.santacruznetworks.com">Santa Cruz Networks</A>
|
|
|
|
|
I need a little brainstorming from anyone here who is willing to help me figure out a way to represent the following scenerio in a database.
I have a setup routine that will allow users to identify Skills and Subskills for an individual. I have the Skills represented in a Table called SKILLS and the SubSkill in a table of course called SUBSKILLS. Subskills are simply just that, a skill within a skill, such as a skill could be Programming and a subskill could be C++.
there is a possibility that a skill could have 100s of subskills. I was thinking of separating such subskills into a separate table but it seems as if there is so much over head and in many instances categorizing some of the subskills into separate tables seems more positive than having them crammed into the same table. Unless there is something I may not be seeing here, which is why I am looking for a second opinion.
Say you have these two tables SKILLS and SUBSKILLS and SUBSKILLS is dependant on the SKILL but exists N skills, perhaps 20 or more, that will have at least 100 or even 1000 Subskills. How could you easily represent this in a relational setting and allow for an easy way to update. I hope I explained this well. If not, I will elaborate further.
Thanks for the help!
|
|
|
|
|
Thats a fairly common problem, at least in my experience. The simplest from a data capture point of view is to make no distinction between a skill and a subskill, and to store them in the same table, with one of the fields being a foreign key referencing the same table. That allows a lot of flexibility in terms of setting up heirarchies.
A top-level skill will usually have a NULL value in the foreign key field, indicating that it does not have a parent.
If you stick to allowing only a few levels of skills, then this structure is adequate. If you need more, then it may lead to performance concerns when attempting to display the structure on the screen.
If you just need a simple heirarchy of 2 levels (skill & subskill) then you can just have a skill & subskill table, with a simple 1 to many relation.
You can extend either model with categories by creating a category table, linked to the subskills or the skills table, such that a skill/subskill can have multiple categories, independent of its relation to other skills.
my blog
|
|
|
|
|
There was also another part I forgot to mention but your idea seems interesting. The one thing I forgot to mention is that another table exists called SKILLSINAGES that represents Skills which reflect different periods of time. That is a skill may become non-existant after a while so I would see this skill either appears or not if looking at a specific period of time. SKILLS would thus hold all information, that is, the Description and other pertenant info.
So far, from your ideas, let me show you what I have to see if I not only understand your idea but to also see if this is a good enough solution. 
SKILLS (represents all skills and subskills. Key is SkillID)
SKILLSINAGES (AgeID, SkillID, ReferencesTableID)
Where ReferencesTableID represents the name of the table that holds my Skill field.
In my original design I had a table for each set of SubSkills, i.e. HISTORY, plus another "adjoining" table which would follow in name HISTORYINAGES. This led to a database with the possibility of a LOT of tables in the end result. I wanted to curb that. So it seems you may have given me an idea.
I can have just ONE table which holds the Description much like the SKILLS table for Skills for for my SubSkills. Then when it comes to defining these Skills into an Age I was thinking of putting them in the SKILLSINAGES table. Or should I put them in another table and not complicate the SKILLSINAGES table further. There is possibility that SKILLSINAGES could get into the thousands of records, maybe even break 10,000.
Thanks for that idea. It certainly helped! 
|
|
|
|
|
Friends,
I got a table in which records are in the following manner:
category product
======== =======
clothes jeans
clothes shirts
clothes ties
weapons Machine Gun
weapons Missile
ColdDrinks Pepsi
ColdDrinks Coca Cola
Now i want to select a result from the above table in such a manner that that the first record of each "category" gets returned. For example, select result should be:
category product
======== =======
clothes jeans
weapons Machine Gun
ColdDrinks Pepsi
How can i do so ?
Imtiaz
|
|
|
|
|
Dear Friend,
I don;t know which type of query u want, as ur mind asks..
but i have one query which will give u same result which u want..
select category,product from table_name where product
in('jeans','machine gun','pepsi');
from taht u can view same result as u want,
Raj Khatri
|
|
|
|
|
Try the following SQL statement:
SELECT Category, FIRST(Product) AS FirstOfProduct
FROM YourTable GROUP BY Category;
You may substitue FIRST() with either MIN() or MAX() if FIRST() is not supported. Hope that works...
Go2Canada
|
|
|
|
|
Your solution is here it works fine for your problem.
select distinct(category),
(select top 1 product from myTable b where b.category = a.category) as 'Product' from myTable a
|
|
|
|
|
i m using VC++ n Access as Database
if i m using hard code values of date n month n year its gives result fine
as query is
rs->Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT * FROM Record WHERE (([Record].[Date] Between #9/27/2001# And #7/30/2004#))");
But i wanaa use values of date . year n month that r input by user .. in form of String or integer in variables .. m_date . m_month .. and m_year.
how can i use these variables values in Query now
i used like this but Wrong Query message appears
rs->Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT * FROM Record WHERE (([Record].[Date] Between #m_month/m_date/m_year# And #m_month1/m_date1/m_year1#))");
I also Tried by TO_Date Function ,, but its said undefined function
how can i do it by user values ..
thanx
|
|
|
|
|
Hello,
I've been banging my head on the wall trying to figure this out.
I'm very new to Visual Studio (as you may be able to tell..)
I have a very simple C# program that calls a stored procedure on a sql server. The SP takes a bit of time, so I just wanted to do something to show that the user has actually hit the button and the system is busy processing.
I found a couple of excellent examples of what I could do here on the code project, but the problem is that no matter what I do, the system won't paint it on the screen until after the SP returns. (kind of defeating the purpose)
For simplicity, and to reduce the problem as far as possible, I even use a dummy SP that simply goes:
WAITFOR DELAY '00:00:10'<br />
select * from My_Table where Last_Name = 'Smith'
The select is just so that it'll return something, and I know it worked.
My C# program as I say is dead simple. I use sqlDataAdapter and Fill to fill my dataset.
Again to make things simple, I just put a label on my form with the text "Working.." in it and set it's visible property to false. When the user hits the run button, I set it to true and then do the Fill... but it doesn't display until after the SP returns, 10 seconds later, with the data. I just go:
label1.Visible = true;<br />
sqlDataAdapter1.Fill(dataSet11);
I found that I can at least set the cursor to hourglass.. but that seems inadequate (if the user slips the cursor off the window, it's no longer hourglass) as well as it seems like I should be able to do what I'm trying to do.
So.. well.. Any help or suggestions would be very much appreciated!
Thank you.
- Andrew
|
|
|
|
|
Rheal_Notes wrote:
When the user hits the run button, I set it to true and then do the Fill... but it doesn't display until after the SP returns, 10 seconds later, with the data.
When you set the visible property the system will fire an event to say that the visible property has changed. However the events are only processed when the application is about to become idle, which your application is not about to do.
Off the top of my head: You could call DoEvents() after setting the visible property to get the paint event to get processed. Alternatively you could force a refresh by calling Refresh() on the form.
Does this help?
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
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!
My Blog
|
|
|
|
|
Colin,
Awesome! A most excellent answer. I tried them both and either one worked perfectly!
I cannot thank you enough, much appreciated!
Cheers,
- Andrew
|
|
|
|
|
Aargh. Reentrancy is not a fun thing. Steer well clear of DoEvents - IMO, it's dangerous.
The OP should be using a function called by a worker thread to perform the SQL query, then use Control.Invoke to get back on to the UI thread to update the UI. You can use the thread pool in .NET by calling ThreadPool.QueueUserWorkItem, passing in a WaitCallback delegate.
I don't think what you say about events is true in .NET. It was in VB6, IIRC, but events are called directly in .NET. What the poster is seeing is simply that the thread isn't processing the Windows message queue (since it's blocked doing the stored procedure call) and so isn't getting the WM_PAINT message to repaint itself. If the user brings up a window that covers the UI and then brings this app up again, whole areas won't be redrawn on some operating systems (XP caches some current displays, but IIRC not invalid areas).
Leave the UI thread solely for user interaction - do all processing on another thread, unless you're sure that it cannot possibly take less than about 0.3 seconds. More than that is noticeable delay.
If you do need to update a control synchronously, call Update.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Mike Dimmick wrote:
Steer well clear of DoEvents - IMO, it's dangerous
A few months ago someone shot me down for suggesting Refresh() and "suggested" DoEvents() instead. So, to play it safe I suggested both ways. I think Bart Simpson said it best "Your damned if you do and your damned if you don't"
However, I do agree that if there are other events waiting it can go off and do some weird stuff.
Mike Dimmick wrote:
I don't think what you say about events is true in .NET. It was in VB6
I've never used VB6 so I don't know. I come from an C++/MFC background.
Mike Dimmick wrote:
, but events are called directly in .NET. What the poster is seeing is simply that the thread isn't processing the Windows message queue
Looking back at my post, it would appear that I misused the terminology somewhat. But, like I said "events[*] are only processed when the application is about to become idle"
[*] events here are the ones that are fired off in the Application.Run method that come from the Windows Message Queue.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
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!
My Blog
|
|
|
|
|
Mike,
Thanks for your reply. I think that once I understand what you're trying to tell me, I'll be able to have a lot more fun.
I was looking at an example here on the The Code Project:
Proper Threading in Winforms .NET
By Shawn Cicoria
http://www.thecodeproject.com/csharp/winformthreading.asp
but I'm still having trouble wrapping my mind around it.
I don't think I have any trouble with the concept or anything, but the exact syntax and calling procedures are still confusing me.
I'll keep at it.. I'm definitely enjoying this Visual Studio thing.
While Colin's suggestion works (thanks again Colin) .. I can still only display a static message... To the user, I don't think it will make any difference at all.. but I think it would be a lot more fun to put up an animated icon or busy-bar or something... and hey, if it's safer, and it'll make my code more re-usable down the line, all the more reason to keep playing. I'm sure I'll figure it out eventually.
In the mean time, any pointers to other sample code or tips or whatever are always much appreciated.
Thanks again to both of you.
Cheers
- Andrew
|
|
|
|
|
I have to transfer a data base from old to new one in batch.
I am working with the Data transformation services at the sql server.
How can i run/excute a dts package from a stored procedure or from the query analyzer? .
Thanks in advance
Be Good...
Dudi
|
|
|
|
|
Any master in SQL Queries
Help: Complicated query
I have a table, as similar to this
ID Status Deleted
1 Repeat y
2 Repeat y
3 Repeat N
4 Repeat N
5 sUBMIT y
6 sUBMIT y
7 sUBMIT N
8 sUBMIT N
9 Pending Y
.
.
.
.
from the table like this i have to retrive distinct records where
delted = N
eg: in the above table after executing query result should be
ID Status Deleted
3 Repeat N
7 SUBMIT N
9 Pending Y
|
|
|
|
|
irshu wrote:
from the table like this i have to retrive distinct records where
delted = N
eg: in the above table after executing query result should be
ID Status Deleted
3 Repeat N
7 SUBMIT N
9 Pending Y
The query does not seem to be complicated, but it is not clear what you want. You said "deleted = N" and your result set has a record with "deleted = Y". Please explain.
My articles and software tools
|
|
|
|
|
say if i have records with same status field and with deleted ='N' and delted = 'Y' then i should select one record with deleted = 'N' and i should not select deleted = 'Y'
and say if i have records with the same status and with deleted ='y' and No records with delted='N' with the same status then i should select delted ='Y'
and one more thing in this is, ID is the primary Key
|
|
|
|
|
Wow, that is complicated, but not impossible. Here is the SQL statement that will get what you want (using union and nested query):
SELECT min(ID), Status, 'N'
FROM myTable
WHERE Status in (select distinct Status from myTable where Deleted = 'N')
GROUP BY Status
UNION
SELECT min(ID), Status, 'Y'
FROM myTable
WHERE Status in (select distinct Status from myTable where not Status in (select distinct Status from myTable where Deleted = 'N'))
GROUP BY Status;
My articles and software tools
|
|
|
|
|
Oops, the following is simpler:
<br />
SELECT min(ID), Status, 'N'<br />
FROM myTable<br />
WHERE Status in (select distinct Status from myTable where Deleted = 'N')<br />
GROUP BY Status<br />
<br />
UNION <br />
<br />
SELECT min(ID), Status, 'Y'<br />
FROM myTable<br />
WHERE not Status in (select distinct Status from myTable where Deleted = 'N')<br />
GROUP BY Status;<br />
My articles and software tools
|
|
|
|
|
Thank you very much for helping in this regard.
with regards
irsh
|
|
|
|
|
Thank you very much for helping in this regard.
with regards
irsh
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
