|
Hi, I have some problem with ADO.
I use Access and MFC.
My problem is that I get an error when I try to get data from an empty field in a record. In my table I have FirstName, LastName and PhoneNumber. It's not necessary to put a value into PhoneNumber. So when I try to get the data, I have "invalide data type" error. All my data are CString type.
So I supposed that ADO cannot change something "empty" to CString...
Any ideas?
thanks
Everything's beautiful if you look at it long enough...
Everything's beautiful if you look at it long enough...
|
|
|
|
|
I'm looking at storing text in a MS Access database, but the amount of text may very easily exceed the 64k restriction on the "memo" data type. What would be the best way of implementing this? Yes, I know I could store the data in a text file and place a filename in the database, but I'd rather keep it all in the database if possible.
Any help would be greatly appreciated
Ryan
Being little and getting pushed around by big guys all my life I guess I compensate by pushing electrons and holes around. What a bully I am, but I do enjoy making subatomic particles hop at my bidding - Roger Wright (2nd April 2003, The Lounge)
|
|
|
|
|
Hi,,
try the Ole object type and deal with text as binary..
use AppendChunk function (ADO,DAO) to add text to field.
|
|
|
|
|
I have table (in sql server database) which contains two fields ,Name(varchar) and Date_of_Birth(Datetime)
I want to fetch those records whose birth day is in this week (incuding today(system date)).
Please help me with the query
|
|
|
|
|
SELECT t.Name <br />
FROM MyTable AS t <br />
WHERE DATEPART(week,t.Date_of_Birth)=DATEPART(week,GETDATE())
Alexandre Kojevnikov
MCP (SQL2K, Win/C#)
Leuven, Belgium
|
|
|
|
|
thats nice ,but i want only those date of birth which is between today and 7 days after today(not before).
for example today date is 4/1/2003
i want to fetch those date of birth which is today and 7 days from today i.e
the records should be like below()
4/1/2003
4/2/2003
4/3/2003
4/4/2003
4/5/2003
.........
|
|
|
|
|
pradipta wrote:
but i want only those date of birth which is between today and 7 days after today(not before).
SELECT t.Name <br />
FROM MyTable AS t <br />
WHERE t.Date_of_Birth between GETDATE() AND DATEADD(d, 7, GETDATE())
You might need to manipulate this a bit. It will return exactly 7 days (7 days * 24 hours). The time element may throw the query off. For example, if the current system date/time is 04/02/2002 13:00:00 then a birthday with the following will not appear: 04/02/2002 1:00:00 and 04/02/2002 00:00:00.
Jeremy Oldham
|
|
|
|
|
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.
|
|
|
|