|
you gave him the answer - I was going to make him work for it, then again, Ive been at work on a long weekend so Im a grumpy b@stard today (my team would say Im a grumpy b@stard everyday, but thats different  )
I was more interested in his multiple rows issues - I tend to find bad database design everywhere, forcing people to use distinct, max, min etc to get unique rows when it could have been prevented (and its a pet hate of mine if someone doesnt know why they are using such a qualifier)
'g'
|
|
|
|
|
Garth J Lancaster wrote: Ive been at work on a long weekend so Im a grumpy b@stard today
So at least your getting paid to faff around with code today, I'm just bored at the moment.
Garth J Lancaster wrote: you gave him the answer
The answer, yes, the code not a chance. He still needs to do some reseach into the exact syntax. Desperately needs to do some study if this has stumped him.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Let me clarify what I just said,
using a simple select I get BID,
Select BID FROM A WHERE ID = x
If I was sure I had one record returned I would write a function to do the job but most of the time I get more than one BID, No for each BID I get I have to Fetch C.Name ON B.ID = C.B_ID
I Can't use inner join cause I have to have all the Fkeys in the main table A.
regards,
|
|
|
|
|
I still don't quite understand your problem, I must be missing something.
You select B_ID from A.
Then you use this to join to B to get all matching C_ID and D_ID values.
Then you use these to join to C to get C.Name and to D to get D.Name.
If there will be any missing rows or null values anywhere along the line, use an outer join instead of an inner join.
You can do all of this in one single SELECT statement. No need for functions, no need for FETCH or cursors or anything like that. If there are any N:M relationships along the chain, you may end up with more than one row for any given value of A.ID value. By using outer joins, you can ensure that you get at least one row for every A.ID value.
I think I must be missing something in your problem because it seems quite straightforward to me.
|
|
|
|
|
Try a left or right join for which ever table has the multiple.
|
|
|
|
|
Try this
Select a.Id, x.BName, x.CName,x.DName
From TableA a
Join(
Select
b.B_Id
,BName = b.Name
,CName = c.Name
,DName = d.Name
From TableB b
Join TableC c On b.C_Id = c.C_Id
Join TableD d On b.D_Id = d.D_Id)x
On a.B_Id = x.B_Id
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi all,
I just ran some code to create a database and a table within it. I then try to open it within the same program using the same connection string and get an exception:
You are trying to access an older version of a SQL Server Compact Edition database.
If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe.
If this is a SQL Server Compact Edition 3.0 or later database, run Compact / Repair.
[ Db version = 3505053,Requested version = 3004180,File name = XXXX ]
How can I be running a program that creates a database using a different version of SQL CE? 
modified on Friday, June 11, 2010 9:05 AM
|
|
|
|
|
Hello All ,
I am facing problem with following plz help me ,
I want to use a subquery in Spaqrl.Can anyone help me how to use subquery in Sparql.I am using WSS 3.0 version.When i tried subquery it throws me with following exception.
SELECT ?shiftName WHERE{ harePointItems(?uri,?shiftName); { SELECT ?shiftName WHERE
{ harePointItems(?uri, "LogbookShift",?shiftInfo). } } } GROUP BY ?shiftName
Exception: Intuition Data Service has failed to execute the specified SPARQL query. } expected
Thanx in Advance
|
|
|
|
|
Hello, I am trying to find information on log shipping between SQL Server 2000 and 2008.
We are upgrading hardware, OS, and SQL, and are doing the fail-over server first then the production server. The problem is getting log shipping to work from 2000 to 2008 between the time we have the fail-over done and getting the production server done. (We estimate about a week or less.)
I have found some articles for migration but since this is a new build we are not upgrading. Not to mention we are going from Enterprise to Standard which has no migration path.
Thanks for any assistance.
djj
|
|
|
|
|
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)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
