|
CPians glad to solve probs
<< >>
|
|
|
|
|
How to retrieve 100 records when joining 2 tables in a select statement?
Aarsth
|
|
|
|
|
SELECT TOP 100 t1.field1, t1.field2, t2.field2, t3,field3
from t1, t2
WHERE t1.field1 = t2.field2
ORDER BY t1.field1 DESC
The "SELECT top 100" is dependant on your ORDER BY. So you can order by "SALES DESC" and get the TOP 100 sales by customers.
another example: This will get top 100 customers by DESCending Year to Date sales.
SELECT TOP 100 Cust.CustID, Cust.Name, Sum(orders.order_value)
from orders, Cust
WHERE orders.CustID = Cust.CustID
AND YEAR(orders.date) = 2005
GROUP BY Cust.CustID, Cust.Name
ORDER BY Sum(orders.order_value) DESC
|
|
|
|
|
Hi,
I am using ODBC to look up information in a small table (approx 400 rows of columns AutoID(auto incrementing), ID(long passed on row addition), House_Num (text passed on row addition), Place_Name (text passed on row addition) and Reserve_Name (text also passed on row addition).
House_Num can be either a series of numbers or a letter/number combination. Adding all of the rows went fine.
I'm now trying to search the database to return the ID of a specific House_Num and Reserve_Name combination. Using CRecordset.m_strFilter I can find the all numbers House_Num, however every time I try and use a number/letter combination I get an undefined error and the program is killed.
It doesn't seem to matter if I separate the letters/numbers at all in the m_strFilter.
Does anyone have any ideas?
Thanks,
Drew Doering, Paramedic
|
|
|
|
|
Did you forget to put ' (apostrophe) between your number/letter combination?
Remember that you don't need apostrophe for numbers but you need apostrophe for number/letter combination.
[Edit]Could you please include a sample of the filter string?[/Edit]
Ed
|
|
|
|
|
Hi,
The m_strFilter code looks like this
Do a bunch of stuff, including open the database and declare recordset derived variable resRecset...<br />
UpdateData(TRUE);
resRecset.m_strFilter = "House_Num = '" + m_sHouseNum + "' AND Reserve_Name = '" + m_sReserveName + "'";
I'm just re-reading your answer now. Do you mean that for a combination of say ME1 or 104SD that I should pass ME'1 and 104'SD?
Thanks,
Drew
|
|
|
|
|
Drew wrote:
Do you mean that for a combination of say ME1 or 104SD that I should pass ME'1 and 104'SD?
No, no. I meant to put single apostrophe between the argument you're passing just like the way you did it.
Are you sure that the line throwing an error is within the record-accessing code? Because the query seems ok to me.
Try running your query in an Enterprise Manager / Query Analyzer or something similar.
If it runs, use TRY CATCH statement to see which part is returning an error. It may be that you're accessing the House_Num as number instead of string.
Ed
Signature
The only valid use of the BLINK tag: Schrodinger's cat is <blink>not dead .
|
|
|
|
|
SQLXML component from microsoft can convert XML data into relational data and load it into an existing SQL Server 2000 database tables. Is such facility available for Oracle database too? Can someone give me links?
Salil Khedkar [^]
|
|
|
|
|
|
Help me,
i have downloaded merge modules of cr9 as lisence.msm,mapping.msm etc. and i have included in my deployment project, but it my crystal reports still saying keycode.dll error. help me integrate the modules to keep the crystal report work on the client's system. and how to do so please help me.
FIRE On moon
|
|
|
|
|
|
Any pointers, suggestions, links, hints on making an enterprise app database independent... Thanks…
Salil Khedkar [^]
|
|
|
|
|
Have a look at the Data Provider model (DotNetNuke[^] has a good example).
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Hmmm... Thanks for the info... O/R mapper seems to be the keyword for this subject. I will read more...
Salil Khedkar [^]
|
|
|
|
|
There are OOP design patterns that allow you to completely decouple an application from various parts of it's implementation, including database implementation.
Basically you would define an abstract interface that describes database operations in a standard, high level, platform independent fashion. Then you use a pattern known as an Abstract Factory to create the classes that actually provide the platform specific database implementation.
Having said that, it;s easy to describe the concept, the implementation is another matter altogether. 
I suggest you get a good book, or several that describe Design Patterns for the language you are using and be prepared to spend a lot of time studying and experimenting.
|
|
|
|
|
Hmmm... Thanks for the info man... Yup I am starting the study phase...
Salil Khedkar [^]
|
|
|
|
|
Can someone help me with this
InPatientId AdmissionDate ActualDischargeDate Days
Client-100027 22/02/2005 01/04/2005 39
Client-100027 09/06/2005 23/06/2005 15
I want show clients who came back within 40 days of last discharge(Second admissiondate(09/06/2005) check with first ActualDischargedate(01/04/2005)).
Can anyone tell me what else i need to add on to it.
SELECT Cut.InPatientId, Cut.AdmissionDate, Cut.ActualDischargeDate, DateDiff("d",[AdmissionDate],[ActualDischargeDate]+1) AS Days
FROM Cut
WHERE (((Cut.InPatientId) In (SELECT [InPatientId] FROM [Cut] As Tmp GROUP BY [InPatientId] HAVING Count(*)>1 )))
ORDER BY Cut.InPatientId, Cut.AdmissionDate, Cut.ActualDischargeDate;
|
|
|
|
|
Something like this,
SELECT *
from Cut, Cut as SecondCut
WHERE (Cut.InPatientId = SecondCut.InPatientId )
AND (SecondCut.AdmissionDate > Cut.AdmissionDate)
AND (DATEDIFF(dd, Cut.ActualDischargeDate, Secondcut.AdmissionDate ) < 40)
I've not checked this SQL as its "off the cuff" but you can get the idea.
|
|
|
|
|
I have a working database and a 'DetailView'. I've added a ListBox which is databound to the same dataset (it works too). But when I select an item off the ListBox, I would like the detailview to update to the selected item in the ListBox.
In otherwords, How do I set the CurrencyManager Index (?) to the ListBox.selectedIndex?
I'm using VB.Net 2005 (beta 2), which shields me from alot of the code I'm trying to figure out. In way of these new objects, I have: abindingSource, DataSet, bindingNavigator, and a TableAdapter. I'm not even sure which of these objects handles the currencyManager (or if any).
If someone can point me to an article or some code, I would greatly appreciate it. I'm lost.
Thanks,
Karen
Nooobie to OOP and VB.Net 2005
|
|
|
|
|
I would use a datagrid to drive a second datagrid using "Item Command"
See my article on easy ADO, its in C# which can be translated in VB
http://www.codeproject.com/aspnet/EasyADODgrids.asp
|
|
|
|
|
Friends,
Please tell me something about MSDE as i don't have much information about it and MSDN docs are confusing me 
Can i distribute MSDE with my commercial apllication without worrying about the licensing isses so that my clients can use it ??
Can i use MSDE in client/server environment i.e MSDE running on some machine on the network and clients on some other machines ??? If yes, the how many concurrent connections are supported ?
Ahsan
|
|
|
|
|
Ahsan Askare wrote:
Can i distribute MSDE with my commercial apllication without worrying about the licensing isses so that my clients can use it ??
Yes. MSDE, unlike SQL Server, is intended for distribution along with your application - you may freely distribute MSDE with your applications.
Ahsan Askare wrote:
Can i use MSDE in client/server environment i.e MSDE running on some machine on the network and clients on some other machines ???
Yes.
Ahsan Askare wrote:
If yes, the how many concurrent connections are supported ?
OK, this is a tricky question. Many DB platforms are licensed by purchasing license packs for x number of users. For example, you may purchase a license that allows 1-15 connections, and if you need more you purchase an additional license for users 16-30, etc.
MSDE does not work this way.
MSDE uses an internal connection pool to support concurrent connections. The documents do not divulge how many threads are in this pool, nor can you configure a maximum number of connections. Instead, the way MSDE works is it will accept a practically unlimited number of concurrent connections, but depending on the load, some operations on some connections may time out because the connection pool could not service them in time.
There is an absolute limit on the maximum number of concurrent connections, and that limit is 32,767. But what this really represents is the upper limit of a small integer. MSDE will accept up to 32,767 concurrent connections because that is the maximum number of unique connection IDs (positive non-zero integer values) that it can assign.
So basically MSDE will accept concurrent connections until it runs out of IDs to assign each new connection - 32,767.
In practical terms, the maximum number of concurrent users MSDE will support is determined entirely by your application - what database operations are being requested, and how long is the maximum response time you consider acceptable. Only you can determine what the practical capacity limit is for your particular application.
|
|
|
|
|
Adding to the other response, some places say that MSDE will accept 5 connections (and other 8) without deliberately slowing down.
In my experience, the SQL .NET Data Provider uses about two connections per process, even if I try to only use one at a time. So that makes for about 4 clients connected to the database. More than that and it starts to slow down sometimes.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
hi,
the type of the column is "Sytem.Int32" in my datatable,for example,
columnName type
----------------------
ID System.Int32
employer System.String
FilterString is "ID like '%1'".When the application run at DataView.RowFilter = "ID like '%1'",I get the message that is "Cannot perform 'Like' operation on System.Int32 and System.String."
Can you help me?
Thanks.
|
|
|
|
|
Internally the following query is generated.
select * from Table1 where ID like '%1'
like operator is spported only for string type columns eg ( varchar)
So the RowFilter is incorrect.
The correct query for finding all the ID's ending with 1 is
select * from TestTable where (convert(int,ID) % 10 = 1)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
