|
Hi there,
we store a duration value as a Long Integer in milliseconds in our database (MS Access or MS SQL Server or others). Now I want to get that value formatted in H:mm:ss, and for the Access version I wrote a short VBA function (Public Function FormatTime(inputValue As Long) As String) to do that.
When I open Access, and run a query (e.g. SELECT ID, FormatTime([Duration]) From MyTable), it works fine.
But our applications are written in C#, and the communication with the Access database is via OleDb. Now I get an error message that function FormatTime is unknown.
Is it possible at all to use a custom function in MS Access via OleDb?
Looking forward to your ideas.
Bernhard
|
|
|
|
|
Have you tried placing your select statement inside a MS-Access saved query ? Your C# application would then execute the saved query, not the SQL statement directly.
Give it a shot.
|
|
|
|
|
Thanks for the idea. Since our application generates quite complex queries on the fly (and must be able to use other databases like SQL Server or Oracle also), this "stored procedure" approach is not usable.
|
|
|
|
|
bhiller wrote: Is it possible at all to use a custom function in MS Access via OleDb?
No.
You are asking OLEDB to pass FormatTime and OLEDB does not know of this function therefore the error.
You are going to have to either move the formattime into your sql statement or format the data AFTER you get the duration from the database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That's not correct:
1. The function is already included in the SQL statement:
SELECT ID, FormatTime([Duration]) From MyTable
2. I can connect to a SQL Server with OleDb. Here, it works just fine. Hence it is not necessary for OleDb to know my custom functions.
It seems to be a Microsoft Access problem...
|
|
|
|
|
You are correct in that it will work with SQL Server, you can use a UDF in a SQL string as it is a TSQL construct in SQL Server.
Your function in Access is a VBA function and I'd bet that will be the difference.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Did you ever figure this one out? I am having the exact same issue. Running a query with a custom function in Access works fine, but trying to run it through C++ using OldDB fails. 
|
|
|
|
|
Unfortunately, I haven't yet found a solution...
|
|
|
|
|
Hi ,
In my table tbldataOrg i ve data like this
pk slno name addr Date
1 1 see this 5/29/2010
1 1 see this 5/29/2010
1 1 see this 5/29/2010
2 2 Hi Us 5/30/2010
3 3 Hi Us 5/28/2010
I just want only 1 orginal data in tblDataOrg and insert other (in this case Pk 2 & 3, data entered from date 5/29/2010, not before that date is needed ) in new table tbldataDup and remove the duplicated data from tblDataOrg in MS SQl.
Any Idea?
Known is a drop, unknown is an ocean
-- Modified Thursday, June 10, 2010 11:59 PM
|
|
|
|
|
Select distinct * into newtablename
This will put distinct records into a new table, you then need to deal with your problem. I suggest you engage a consultant to design your data structure for you or get a book on database and data design and start learning.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi everyone.
I am trying to develope an application on iPhone but i come up with a question. Do u know which database do I need to use in my iPhone application. do u have any idea?
thx
|
|
|
|
|
try doing some research [^]before posting!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I love the URL for the first result. www.database4iphone.com[^]. Who'd have thunk? 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Hi and thank you in advance.
As i write this, I have tried to research the internet on database applications for devises running windows mobile or windows CE and to my surprise most examples I find, even msdn itself concentrates on C#.net and VB.net
I have been looking for an example on how to do a database a c++ program connecting to sqlce database (.sdf) using ado.net
I use VS 2010. I have done the following so far.
Have added a common file I found - Ado.h
I have installed sql server ce on my emulator,
I am using Windows mobile 5.0.
I have the following code in my InitInstance() function
<br />
CDatabase g_db;<br />
m_strConnection = _T("Data Source=\\My Documents\\DB2.sdf");<br />
if(!g_db.Open(m_strConnection))<br />
{<br />
AfxMessageBox(_T("Cannot open database"));<br />
}<br />
when i deploy the emulator, this is the error i get,
"A problem has occured... please report this to microsoft".
Meaning my program crushes and cannot open the DB.
the DB2.sdf is saved in the My documents of the emulator.
Please assist, I need to know how to successfully connect to the DM.
Accept my thanks in advance
|
|
|
|
|
I think you can only access SQLCE from .NET. MS imply that access from C++ is either impossible or nearly so.
|
|
|
|
|
Hi,
so what database can I use with c++,
they must have put MFC Smart Device applications development with a reason.
Does pocket access work (.cdb)
|
|
|
|
|
Hi guys,
I have these three queries and i would like to join them as one table so that i can
read from one table using my DataReader(). I don't want to use subqueries because certain queries will
return more than one result and Sql Server does not like that one.
(
SELECT [number], [dateTime]
FROM [Database].[dbo].[table]
WHERE [dateTime]
BETWEEN '2010-04-30 00:00:00' AND '2010-04-30 23:59:59' AND
[number] NOT IN ('227000000', '2274000000', '2770000000')
)
(
SELECT RIGHT ([number_pair], 11) AS [number_pair] FROM [Database].[dbo].[table]
WHERE [number_pair] != '' AND [used] = '1' AND [number_pair] IN
(
SELECT [Database].[dbo].[table].[num_pair] FROM [Database].[dbo].[table]
WHERE [dateTime] BETWEEN '2010-04-30 00:00:00' AND '2010-04-30 23:59:59' AND
[number] NOT IN ('227000000', '2274000000', '2770000000')
)
)
(
SELECT column FROM [Database].[dbo].[table]
WHERE [sent] = '1' AND
[sent_date] BETWEEN '2010-04-30 00:00:00' AND '2010-04-30 23:59:59'
)
Please anyone help me 
Thanks,
Morgs
|
|
|
|
|
The three queries don't return the same number of columns so they can't be combined using "union". So first you need to make the three queries to return the same number of columns with corresponding columns having the same data types. Then you can use "union" or "union all" to combine them into one big table.
|
|
|
|
|
Hi Loyal,
Thanks for your reply, well the problem is that these queries will return different
number of columns and rows.e.g 1st query will return 3 columns,
2nd and 3rd will return 1 column
|
|
|
|
|
How do you expect to read from one table if they all have different number of columns?
You can have 3 select queries in 1 stored procedure and still use DataReader. The DataReader class contains a NextReader() method which moves onto the next table in the resultset.
|
|
|
|
|
Thanks alot, i will try this one
|
|
|
|
|
That is not a problem. Simply determine which query returns the most columns, and add dummy columns to the other queries, such that each query returns the same amount of columns. use 0 for columns with numerical values, and '' for columns with text values.
So for example:
SELECT NumericalColumn1, NumericalColumn1, textColumn1
FROM Table1
WHERE blabla
UNION ALL
SELECT NumericalColumn1, 0, ''
FROM Table2
WHERE blabla
UNION ALL
SELECT 0, 0, textColumn1
FROM Table3
WHERE blabla
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi everyone,
I'm using a huge database of geostatistical blocks (million of records). I would like to read the records one by one and process only some. I tried the datareader but it's slowing terribly. Is there a read next or something like that in SQL?
I'm programming in C#.
Thanks in advance,
Yvon
|
|
|
|
|
If you are only processing a subset of records why not cut down the size of the result set by using a where clause to filter out those records you will not need? (Sorry if you already know that but your question isn't clear).
Like:
select * from [table] where [column] = 'foo'
It's best to bring back only those records you'll actually need: perhaps if you were to explain your need a little more clearly: e.g. are you intending to do this in Sql or c#?
me, me, me
"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven
nils illegitimus carborundum
|
|
|
|
|
In fact, I had something like SELECT [IJK],[XC],[YC],[ZC] FROM [MSDP]; and it was taking 10 to 15 minutes to get the records.
I tried to change it to SELECT TOP 200 [IJK],[XC],[YC],[ZC] FROM [MSDP] ORDER BY [IJK]. It's fast for the first 200 records.
To get the following records, I use SELECT TOP 200 [IJK],[XC],[YC],[ZC] FROM [MSDP] WHERE [IJK] NOT IN (SELECT TOP 200 [IJK],[XC],[YC],[ZC] FROM [MSDP] ORDER BY [IJK]) ORDER BY [IJK];
the second 200 changes in a loop to 400,600 and so on.
At the beginning, it's quite fast but the performances are degrading quite fast.
Any help?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
