|
i think U did not understand the problem.the query u have written only to retrive those DOB which will lie within this week.But DOB means only the month and day will be same whatever may be the year ,for exammple today is 4/4/2003 and DOB of a member 4/4/1975 then today is the birth day of that memeber.
so i want only the dob from database whose day and month will be equal to today's day and month and also those whose b'day will be within 7 days beyond today
just see the example
dob in the data base
DOB(mm/dd/yyyy)
4/4/1976
4/4/1970
4/6/1976
4/8/1999
1/1/1990
1/3/2000
------------
Today is -4/4/2003
i want the the data from database
4/4/1976
4/4/1970
4/6/1976
4/8/1999
because the above dobs will lie on this week
pradipta
|
|
|
|
|
pradipta wrote:
the query u have written only to retrive those DOB which will lie within this week.But DOB means only the month and day will be same whatever may be the year...
Sorry about that. Who knows where my mind has be drifting. 
Try the following:
<br />
SELECT t.myName, cast(cast(Month(getdate()) as varchar(2)) + '/' + cast(Day(getdate())as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime), <br />
cast(cast(Month(t.DOB)as varchar(2)) + '/' + cast(Day(t.DOB)as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime)<br />
FROM DOB AS t <br />
WHERE datediff(d, <br />
cast(cast(Month(getdate()) as varchar(2)) + '/' + cast(Day(getdate())as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime), <br />
cast(cast(Month(t.DOB)as varchar(2)) + '/' + cast(Day(t.DOB)as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime)) Between 0 and 6 <br />
<br />
Let me know for sure that this works properly.
Jeremy Oldham
|
|
|
|
|
<br />
DECLARE @now AS datetime<br />
SET @now = GETDATE()<br />
DECLARE @today AS datetime<br />
SET @today = CAST(CONVERT(char(8), @now, 112) AS datetime)<br />
SELECT t.Name <br />
FROM MyTable AS t <br />
WHERE DATEDIFF(Day,@today,t.Date_of_Birth) BETWEEN 0 AND 6<br />
112 is ISO format: yyyyMMdd. SQL Server handles conversion from this format to datetime no matter which DATEFORMAT is active.
Alexandre Kojevnikov
MCP (SQL2K, Win/C#)
Leuven, Belgium
|
|
|
|
|
Hello,
I had written a few articles for a major publishing
company which went out of business. Hence I decided to
publish them myself. Couple of articles are on Windows
form properties and one is on writing a DAL in C#. Check
them out:
http://dotnetuser.www2.dotnetplayground.com/
Madhuri Mittal
|
|
|
|
|
Madhuri Mittal wrote:
Check
them out:
Why not submit this as an article on CodeProject, then there is no need to cross-post. 
Article Submission Wizard[^]
-Nick Parker
|
|
|
|
|
Hi, is there any way to create a MS Access database (.mdb) file using C# code and ADO.NET? I've done it in the past using ADOX (ADO Extensions). Does anybody know if I still have to use ADOX to do this?
Thanks,
Barry
|
|
|
|
|
ADOX is the only way I ever found in VS.Net and I spent a few days trying to get around this problem.
Regards
Wayne Phipps
|
|
|
|
|
I like to connect an access database to some controls on a form (checkbox, combobix, optioncontrol and textbox) without using the ado control. Is there anywhere a tutorial to learn, how to write the code?
THANX
|
|
|
|
|
I was trying to fill a dataset from another thread so that my current one won't be blocked, it's working fine except follwing case:
I have a big table called "orders", running "select * from orders" doesn't block my user input thread, but "select * from orders where orderid=12345" will block the current thread; just wonder if there is anyone having the similar situation and knowing the work-around. Also "waitfor delay '00:00:08'" at the first line of script will block current thread.
I was using C# and Oledb.net with code similar to following:
Thread t = new Thread (new ThreadStart(ExecSql));
t.IsBackground = true;
t.Start();
private void ExecuteSql()
{
OleDbDataAdapter myAdapter = new OleDbDataAdapter();
DataSet ds = new DataSet();
OleDbCommand cmd = new OleDbCommand("select * from orders", conn)
myAdapter.SelectCommand = cmd;
myAdapter.Fill(ds);
}
It's intersting that Odbc.net doesn't have this blocking problem but the perfomance of filling dataset with Odbc.net is much slower than OleDB.net.
(tried the sample tabl Northwind.Orders, 10 time slower, very strange)
Thanks
|
|
|
|
|
I just found out today that a Microsoft SQL Server 2000 Stored Procedure will not allow me to assign ntext or text as the datatype to an output parameter.
It is not a huge problem as I am using a View rather to get the info, but it still seems a bit daft.
How is one supposed to return any data over 8000 characters using a SP?
Paul Watson
Bluegrass
Cape Town, South Africa
Macbeth muttered:
I am in blood / Stepped in so far, that should I wade no more, / Returning were as tedious as go o'er
DavidW wrote:
You are totally mad. Nice.
|
|
|
|
|
We store large image files in our db. I've been using this technique to extract them:
CREATE PROCEDURE locSP_OpenBlob( @idsd_file uniqueidentifier )
AS
begin
SELECT
sd_file.data
from sd_file
WHERE sd_file.idsd_file = @idsd_file
end
GO
Where sd_file.data is defined as an "image" type in the table.
on the Client side (using C#) I use a DataReader
MemoryStream st = new MemoryStream();
BinaryWriter bw = new BinaryWriter( st,System.Text.Encoding.Default );
while(( retval = datareader.GetBytes(0, len, outbyte, 0, bufferSize )) > 0 )
{
bw.Write( outbyte );
bw.Flush();
len += retval;
}
I don't know if that is the best way or not, but it seems to work well enough for our purposes...
"My job is to protect America"
George W. Bush.
|
|
|
|
|
Thanks Stan, much appreciated. I wonder why MS put this limitation in. As you demonstrated people are going to get the data out one way or the other.
Paul Watson
Bluegrass
Cape Town, South Africa
Macbeth muttered:
I am in blood / Stepped in so far, that should I wade no more, / Returning were as tedious as go o'er
DavidW wrote:
You are totally mad. Nice.
|
|
|
|
|
Paul Watson wrote:
I wonder why MS put this limitation in.
I'm not much of a network guy, but I can't help but wonder if this limitation is just based upon sound network/server programming principles. Would it be a good idea to allow one client to request a chunk of data of any size, no matter how large? By forcing each client to request data in smaller chunks, is optimal dataflow ensured for the remaining stations? I don't know...
"My job is to protect America"
George W. Bush.
|
|
|
|
|
This is an annoying limitation, we've run into it a number of times, when trying to create local variable greater than 8000 chars.
There are some work arounds, but none of them are pretty. We usually just rethink the process.
I'm sure there is a reason, trying to keep stored proc resource usage down, I imagine.
Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.
|
|
|
|
|
Bruce Duncan wrote:
I'm sure there is a reason, trying to keep stored proc resource usage down, I imagine
That would be odd IMO, surely MS would encourage SP use? Or at least all the architectures recommend the use of SPs, certainly makes sense doing that logic as close to the database as possible.
Bruce Duncan wrote:
when trying to create local variable greater than 8000 chars
Yeah, that is dead annoying. Drove me up the wall the other day as instead of returning an error, it simply stopped the output param from returning anything (not even truncuated which would have tipped me off.)
Plus concactenating output params is also a no-no.
Anyway, my SP is working and that is all I am willing to sacrifice my sanity for this week. Someone can hire a DBA if they want the thing opitmised.
Paul Watson
Bluegrass
Cape Town, South Africa
Macbeth muttered:
I am in blood / Stepped in so far, that should I wade no more, / Returning were as tedious as go o'er
DavidW wrote:
You are totally mad. Nice.
|
|
|
|
|
Paul Watson wrote:
Yeah, that is dead annoying.
It kinda puts paid to manually building large xml fragments. We wanted to build up an xml doc, and place it into a message queue, I wrote an extended proc to do the msmq bit, even had support for text and ntext, worked like a charm until we wanted something larger than about 8kb. 
Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.
|
|
|
|
|
I am developing a MFC-based program (using CRecordset and CDatabase) dealt with MS Access. I want to retrieve the column names of a table. Could I do that without using ODBC APIs?
|
|
|
|
|
As far as I know, there is no way to get the column names with CRecordset or CDatabase. You can only use the m_hdbc member to feed it in the SQLGet**** - functions.
|
|
|
|
|
You can use member GetODBCFieldInfo of CRecordset:
void GetODBCFieldInfo( short nIndex, CODBCFieldInfo& fieldinfo );
// Definition of CODBCFieldInfo
struct CODBCFieldInfo
{
CString m_strName; //thats what you want
SWORD m_nSQLType;
UDWORD m_nPrecision;
SWORD m_nScale;
SWORD m_nNullability;
};
|
|
|
|
|
Sorry,I found that I hadn't expressed myself clearly. What I want to know is all the column names of the table, not only the names in the recordset.
|
|
|
|
|
I think you got to do it this way (in pseudocode):
SQLExecDirect( "Select * from <tablename>")
SQLNumResultCols();
for( i < NumResultCols )
{
SQLDescribeCol( i )
}
that should do the trick.
dirk
|
|
|
|
|
Hi..
I use ASP , ADO to open AccessXP database
my connection string :
strCon="Data Source=" & Server.MapPath("db.mdb") & <br />
"; Provider=Microsoft.jet.oledb.4.0;password=mypass;"i get the eror :
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
can anyone help
|
|
|
|
|
instead of password=mypass it should be:Jet OLEDB:Database Password=mypass;
|
|
|
|
|
you will unlock this database .
if it was be created with another user,you will not use it.
|
|
|
|
|
I didn't get what you mean
do you mean the using this connection string ,only one user can open the database at the same time ?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
