|
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)
|
|
|
|
|
Hi
Suppose this table (ADV)
ID | Type | Value
___|______|_______
1 | 1 |100
2 | 2 |20000
3 | 1 |12
4 | 1 |21
5 | 2 |10000
I am working on an advertisment(adv) project!I have a table contains adv. Each adv is expired as:
If adv.Type is 1 adv.Value will be remain days.
If adv.Type is 2 adv.Value will be remain ViewCounter( number of times that the adv is presented)
I want to have a select statement that gives me all valid advs.
For each adv in result set if adv.Type is 2 adv.Value must decreases one unit.
|
|
|
|
|
I'm curious why you are decreasing all the adverts of type 2 in the result set, are you really displaying all these adverts? If you are just picking one out of the result set to display then you'll be decreasing some adverts unnecessarily - which would likely upset the advertiser who is paying for so many page impressions that he is not getting.
Anyway, probably the best way to do this is in a transaction so you can perform the SELECT and UPDATE in one operation such that if one fails so does the other.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I use Top n , and order by newID() to return n advers from resulset as random!
Can you write code plz???
What do you offer to me to do this?
|
|
|
|
|
WDI wrote:
I use Top n , and order by newID() to return n advers from resulset as random
Okay - that would make more sense.
WDI wrote:
Can you write code plz???
Show me what you've got already and I'll try and help you fix it up. I'll also try and explain the steps to go from one to the other so that you learn how it works.
WDI wrote:
What do you offer to me to do this?
Shouldn't that be the other way around? In order to reap the harvest you must first sow the seeds.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Can anyone help!
I am capturing data through scanning (OMR) and it might happen that some of the data is not recognised by the scanner hence it will be left blank, and I do not want variables with blank entries.
Now, I am storing that dataset in an SQL database table. All I want is to have a stored procedure that when I run on that data it will pick up the variable that are left blank with no entry.
I also want something that will help me say to check consistency in my data set. Here is the pseudocode:
- Open table i.e. MyTable
- Input a parameter say ID, i.e for searching
- If found, locate all the variables for that parameter where the appears to be some blanks.
- If blanks are found, then print thru a printer, say Gender empty for person i
- Check if married couples are not of different sex, if they are print a message giving out the id for that particular record.
Please help me!
phokojoe
|
|
|
|
|
Okay, I'm having a bit of trouble understanding what you want because the terminology you are using is inconsistent with the domain.
What do you mean by "variable"? Do you mean a column in the table?
By "input a parameter", do you mean you want to take as a parameter to the stored procedure the name of a column that you want to search?
You use the word "variables" a second time, but this time I'm thinking that "row" may be the correct interpretation for this instance. Do you mean you want to return the rows in the table that have a null value in some column (as specified by the user)?
Printing is not really a recommended action for a database server to take. It can be done, but it makes for messy applications.
Your last part on married couples is more of a business rule rather than a check of the OMR.
I am wondering if these checks cannot be made before the data from the OMR gets to the data. Or is this a senario where a lot of bad data has already gone into the database and you need to find it?
Answering your question as best I can
CREATE PROCEDURE dbo.SearchForNull
@columnName sysname
AS
DECLARE @dynamicSql nvarchar(4000);
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
AND COLUMN_NAME = @columnName)
BEGIN
-- The column does not exist
RETURN;
END
SET @dynamicSql = '"SELECT id FROM MyTable WHERE ['+@columnName+'] IS NULL';
EXEC(@dynamicSql);
GO
This stored procedure will return a set containing the id of any row that has a null value for the supplied @columnName. The check of the INFORMATION_SCHEMA.COLUMNS table is important as it helps prevent an error or attack if someone has managed to put in a dodgy column name (either accidentally or maliciously).
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thank you for the code, it looks like it is going to work just few things.
When I try to execute the procedure giving the parameter, it says"
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'SELECT id FROM MyTable WHERE [a1] IS NULL'.
--------
By the way let me clarify it. Data is already in the database. This is the example:
Id Sex Subject1 Subject2 Subject3
001 1 45 .. 50
002 . 65 80 ..
003 2 .. 90 ..
Now all i want is if i supply '001' for the column id, it has to retrive the columnnames that have empty or .. for that particular id.
this time it has to retrive subject2 for id 001. etc.
Thank you.
I am working on that message I get when executing the procedure.
Let's take a ride.
phokojoe
|
|
|
|
|
phokojoe wrote:
When I try to execute the procedure giving the parameter, it says"
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'SELECT id FROM MyTable WHERE [a1] IS NULL'.
That's what I get by typing in SQL directly onto the forum. The " symbol just before the SELECT statement should be removed.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
This stored procedure should be a bit easier - no dynamic SQL - but probably a bit more tedious as it is a bit repetative.
CREATE PROCEDURE dbo.GetEmptyColumnNames
@id int
AS
SELECT 'Sex' AS ColumnName
FROM MyTable
WHERE (Sex IS NULL OR Sex='..') AND id = @id
UNION
SELECT 'Subject1' AS ColumnName
FROM MyTable
WHERE (Subject1 IS NULL OR Subject1='..') AND id = @id
UNION
SELECT 'Subject2' AS ColumnName
FROM MyTable
WHERE (Subject2 IS NULL OR Subject2='..') AND id = @id
UNION -- And so on for all the columns in your table that you want to check.
GO
This just performs a number of SELECT statements and UNIONs them all together into one result set.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
well this looks like it will work. Hoever, I foxed the first procedure 'searchfornull' and when I give it the parameter it returns all the columns names without records.
But as for procedur GetEmptyColumnNames, it returns only the first column name in the first select but not records and it looks like it does not return all other coulmn names.
e.g. This is how it looks:
CREATE PROCEDURE dbo.GetEmptyColumnNames
@id numeric
AS
Select 'a1' as A1
FROM hhdata
WHERE (a1 IS NuLL Or a1='..') AND refno = @id
UNION
Select 'a2' as A2
FROM hhdata
WHERE (a2 IS NuLL Or a2='..') AND refno = @id
UNION
Select 'a3' as A3
FROM hhdata
WHERE (a3 IS NuLL Or a3='.') AND refno = @id
exec getemptycolumnnames 043701137010041
where 043701137010041 is a record in the refno and of course there are many records in the refno column, I thought it will return all the column names in that 043701137010041 which are null or '..'. Infact that is what I want to see.
------------returns
A1 not even a record in a1.
phokojoe
|
|
|
|
|
I notice that you are putting things like
SELECT 'a1' AS A1...
SELECT 'a2' AS A2...
Remember this is UNIONing all these into one set so it might confuse it* if you give all these different column names, they are supposed to be returning the result into a single column
So, my first suggestion (and its a long shot) is to ensure that the A1, A2, A3 are all the same column name (If I remember I said ColumnName)
DROP PROCEDURE dbo.GetEmptyColumnNames
CREATE PROCEDURE dbo.GetEmptyColumnNames
@id numeric
AS
Select 'a1' as ColumnName
FROM hhdata
WHERE (a1 IS NULL Or a1='..') AND refno = @id
UNION
Select 'a2' as ColumnName
FROM hhdata
WHERE (a2 IS NULL Or a2='..') AND refno = @id
UNION
Select 'a3' as ColumnName
FROM hhdata
WHERE (a3 IS NULL Or a3='.') AND refno = @id
phokojoe wrote:
where 043701137010041 is a record in the refno and of course there are many records in the refno column, I thought it will return all the column names in that 043701137010041 which are null or '..'. Infact that is what I want to see.
I'm getting confused by your terminology.
"043701137010041 is a record in the refno" --> a column value in the refno column??
"there are many records in the refno column" --> there are many rows with the same refno??? (A column does not contain records, it contains values)
* DISCLAIMER: I've never thought of putting different column names here, so I don't know what the result would be, but it could explain why you only get the first result back.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Is there a good comparison of the various types of products out there like codebase vs msde 2000 vs sql server express 2005.
.............................
There's nothing like the sound of incoming rifle and mortar rounds to cure the blues. No matter how down you are, you take an active and immediate interest in life.
Fiat justitia, et ruat cælum
|
|
|
|
|
I have a MS-SQL production database on a server running under the default instance. I have a copy of that database running on my desktop under the NetSDK instance of MSDE. I created a new copy of MSDE on a laptop to demo the product. It is also a named instance (NetSDK). The mystery is that I can create a database backup on the production server (db file path is c:\program files\Microsoft SQL server\MSSQL\Data\database.mdf), copy it to my machine and restore it (I use Access to restore it.) Works in spite of the fact that the data files are in slightly different paths (MSSQL\Data versus MSSQL$NETSDK\Data). But if I try to restore the backup on the new laptop installation it blows up telling me I need to use the RESTORE WITH MOVE.
As an interim solution, I am backing up the server, restoring on the desktop, then backing up the desktop and restoring the second backup to the laptop.
Can anyone tell me why the restore works on the desktop but not the laptop? I would like to avoid this giant shuffling of files if possible.
Thx
Mark Jackson
|
|
|
|
|
When you are restoring on the laptop, check the physical destination path of the data files being restored.
It might be pointing to a different directory than the default data directory present on the laptop.
Or maybe there are the "same named" .mdf and .ldf files already present in the data folder of the laptop.
cheers,
[r a w]
Little Johnny was a scientist. Little Johnny is no more.
For what he thought was H2O, turned out to be H2SO4.
|
|
|
|
|
The following function is used in my code to store PDF as BLOB in SQL Server database. When i am running my application, sometimes after the application is running for long time period and continusly updating database single field in SQL server containing millions of record it gives the following exception on
cmdSQL.ExecuteNonQuery()
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I have checked the query it does not take much time to execute againts SQL Server so please dont give me suggestion of setting timeout property of command object. I need to know why this error comes after long execution time and how can resolve it.
Your immediate response will be higly appreciated.
Protected Overrides Function PutPDFFile(ByVal strFileName As String) As Boolean
Dim cmdSQL As SqlClient.SqlCommand
Dim blnReturn As Boolean = False
Dim objParam As SqlClient.SqlParameter
Dim arrData() As Byte
Try
If Not IO.File.Exists(strFileName) Then
Throw New Exception("PDF file not found:" & strFileName)
Else
arrData = ReadFile(strFileName)
End If
cmdSQL = New SqlClient.SqlCommand("UPDATE [<job>] SET [File_Name] = @PDFName, [File] = @PDFData WHERE [File_Name] = @DATName".Replace("<job>", Me.Job.JobName), Me.m_cnnSQL)
cmdSQL.CommandType = CommandType.Text
objParam = cmdSQL.Parameters.Add("@PDFName", SqlDbType.VarChar)
objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1)
objParam = cmdSQL.Parameters.Add("@PDFData", SqlDbType.Image)
objParam.Value = arrData
objParam = cmdSQL.Parameters.Add("@DATName", SqlDbType.VarChar)
objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1).Replace("pdf", "dat")
cmdSQL.ExecuteNonQuery()
cmdSQL.Dispose()
blnReturn = True
Catch ex As Exception
Me.m_objLog.Log(ex.Message, SOARLogType.sltLogOnly)
Finally
If IO.File.Exists(strFileName) Then IO.File.Delete(strFileName)
cmdSQL = Nothing
End Try
Return blnReturn
End Function
|
|
|
|
|
in your connection string try adding pooling=false
will
|
|
|
|
|
cmdSQL.CommandTimeout = 30
is by default.
Try setting it to 60.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
