|
You can't selectively join tables. You should probably use a union. Without knowing your table structure, here is a stab at it...
select max(dateentered), otherfields
from table1
join table2 on (table1.trantype = table2.trantype)
union
select max(dateentered), otherfields
from table1
join table3 on (table1.trantype = table3.trantype)
|
|
|
|
|
My more detail problem
CREATE TABLE [dbo].[table1] (
[TranID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SID] [varchar] (10) NOT NULL ,
[GID] [varchar] (5) NOT NULL ,
[TranType] [varchar] (1)NOT NULL ,
[DateEntered] [datetime] NULL ,
) ON [PRIMARY]
CREATE TABLE [dbo].[Table2] (
[TranID] [numeric](18, 0) NOT NULL ,
[GID] [varchar] (5) NOT NULL ,
[SID] [varchar] (10)
) ON [PRIMARY]
CREATE TABLE [dbo].[Table3] (
[TranID] [numeric](18, 0) NOT NULL ,
[GID] [varchar] (5) NOT NULL ,
[SID] [varchar] (10)
) ON [PRIMARY]
Example1: I need select info from table1 and 2
Table1 Table2 Table3
TranID 1 1
SID 12 12
GID 1 1
TranType 'Y'
DateEntered '9/1/2003'
---------------
Example2: Infor from table1 and 3 (Maxdate)
Table1 Table2 Table3
TranID 1 1
SID 12 12
GID 1 1
TranType 'Y'
DateEntered '9/1/2003'
Table1 Table2 Table3
TranID 1 1
SID 12 12
GID 1 1
TranType 'N'
DateEntered '9/2/2003'
|
|
|
|
|
how to check for the access permissions of each user in a sql database?
can some one help me with a c++ code or this
ranjani
|
|
|
|
|
 I am working on a new asp.net project using a Access 2000 database. I want to create some update queries in Access and then be able to call them using parameters to update certain fields. I have read some articles on how to do this using a SQL Server database, but I can't find any information on how to do this for Access. What I don't want to do is hard code any queries in code.
bgeroux
|
|
|
|
|
in Access .. on the left bar of the database window..
Select Queries then ->create Query in design view ->Close the show table screen -> right click ->choose SQL View
write your update query :
Update table1 set userName="bla bla" where ID=@MyID
@MyID is the value you pass to the query..(parameter)
you can declare it like this :at the beginning of the query (not required)
parameters @MyID long;
|
|
|
|
|
Here's an example[^] that demonstrates using stored queries in Access. It also briefly mentions some of the advantages of SQL Server over Access. Downloadable code and a sample database are available, as well.
"Your village called -
They're missing their idiot."
|
|
|
|
|
Thank for responding so quickly. The example code is great, but this code is for using a ADO recordset and I am looking for a ADO.net solution. Is this possible?;)
|
|
|
|
|
I haven't worked with ADO.Net, as I can't afford the tools and my web host won't support it. But surely they haven't completely abandoned the architecture? Isn't there an equivalent to a recordset in ADO.Net? The key point in the article for me was how to structure the Command to Access; the details of how the platform accomplishes it should be fairly minor to implement. Just a guess though; as I said, I don't use ADO.Net yet.
"Your village called -
They're missing their idiot."
|
|
|
|
|
A VB.NET console application (requires the database that Roger linked to above):
Imports System.Data.OleDb
Module Module1
Sub Main()
Dim conn As OleDbConnection
Dim cmd As OleDbCommand
Dim param As OleDbParameter
Dim rdr As OleDbDataReader
Try
conn = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=""courses.mdb"";" & _
"Persist Security Info=False")
conn.Open()
cmd = New OleDbCommand("qryStudentsOnCourse", conn)
cmd.CommandType = CommandType.StoredProcedure
param = cmd.Parameters.Add("@courseID", OleDbType.VarChar, 10)
param.Value = "C0450"
rdr = cmd.ExecuteReader()
While rdr.Read()
Console.WriteLine("{0} {1}", rdr("firstName"), rdr("lastName"))
End While
Finally
If Not rdr Is Nothing Then
rdr.Close()
End If
If Not cmd Is Nothing Then
cmd.Dispose()
End If
If Not conn Is Nothing Then
conn.Close()
End If
End Try
End Sub
End ModuleHope this helps. Some people have reported that using the ODBC classes rather than OLE DB is faster in the current version of the framework (probably due to lower interop overhead for 'flat' APIs versus COM APIs).
|
|
|
|
|
This is exactly what I was looking for, thank you very much.
|
|
|
|
|
did any body know any equvalent to dbms_pipe (Oracle Pack) in SQL Server ??
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
It depends what you want to do with it.
If you want to return arbitrary out-of-band messages to the client, use PRINT. If you want to communicate between two server processes, you'll have to write that functionality yourself. You could do it as a set of extended stored procedures, but I would recommend using SQL Server just as a database server, and perform any required computations on the client.
This is basically a difference of philosophy between Oracle and SQL Server; Oracle says 'I am the master server and you are all my slaves', while SQL Server says 'Why can't we all work together?' 
But seriously, SQL Server offers less support for building everything into the database. While it supports background operations, the only way 'foreground' operations (explicitly requested by a client) talk to background operations is through data stored in the database, unless you use some method of extending SQL Server.
|
|
|
|
|
Dear Mike,
Thank you for your replay, you have toled me to use print command is i would like to send out of band messages but the qustion is who can the cline catch this message , as if you like to send an alert to one of the clients after adding new data row in a table so you will create a triiger which will use print statement and how can the client (or many Clients catch it)?
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
Ah, I see: you're trying to notify clients that some data has changed.
This can't be done in the current versions of SQL Server. The next version, codenamed 'Yukon', will support this feature.
At present, I would recommend periodically polling the table from the client. This technique will apparently be used by the ASP.NET cache management code in the next version of ASP.NET, codename 'Whidbey', due to be released next year (IIRC), for SQL Server 7.0 and 2000.
A PRINT statement is only received by the client whose commands caused the PRINT to be executed. In ADO.NET, this message is treated as an informational message of severity 0 and can be handled by handling the InfoMessage event of your SqlConnection. The same is true for classic ADO (the event is on the Connection object).
It looks like (and I'll admit I'm just trawling the documentation here) ODBC will return SQL_SUCCESS_WITH_INFO (as opposed to SQL_SUCCESS) if a PRINT or a warning RAISERROR (severity 10 or below) occurs - you can then retrieve the message from SQLError. See also KB article 280109[^].
|
|
|
|
|
So Mr.Mike,
In this case i have create a table for sessions where any client can open a session by adding row to that table then i had create an extended exteneded procedure in c++ for SQL server which will send an alert on the TCP socket to all clients (executed on trigger) which had opened session to monitor a table so it is the best way to do it in this verssion of sql server 2000
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
SELECT Sum(Amount) as "Total Expenditures", Month(eDate) as "Month"
FROM Expenditure
GROUP BY Month(eDate)
ORDER BY Month(eDate)
Above is my current SQL statement. I would like to change it so that it would generate a table that shows zeros for months that have zero "Total Expenditures". Currently it only shows amounts for months that have
total > 0. I think I could do it once I get it into my datatable but the query would be nicer.
Thanks
"It has become appallingly obvious that our technology has exceeded our humanity."
- Albert Einstein (1879-1955)
"I think there is a world market for maybe five computers."
- Thomas Watson (1874-1956), Chairman of IBM, 1943
"640K ought to be enough for anybody."
- Bill Gates (1955-), in 1981
"Half this game is ninety percent mental."
- Yogi Berra
|
|
|
|
|
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
1 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=1
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
2 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=2
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
3 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=3
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
4 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=4
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
5 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=5
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
6 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=6
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
7 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=7
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
8 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=8
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
9 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=9
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
10 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=10
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
11 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=11
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
12 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=12
It's not the nicest way in the world, I admit. I'll let you know if I come up with a better solution. HTH.
Jeff Varszegi
P.S. You should join up-- membership's free, you know. That way you can get an email when somebody answers one of your posts. JKV
|
|
|
|
|
Yeah--- I see where that should work but when I run it in QTADO it tells me that there are :
Wrong number of arguments used with function in query expression 'ISNULL(SUM(Amount), 0)'.
From my understanding it works like this ISNULL(expression, value if null) so it should work. Maybe it is something with MS JET or something.
"It has become appallingly obvious that our technology has exceeded our humanity."
- Albert Einstein (1879-1955)
"I think there is a world market for maybe five computers."
- Thomas Watson (1874-1956), Chairman of IBM, 1943
"640K ought to be enough for anybody."
- Bill Gates (1955-), in 1981
"Half this game is ninety percent mental."
- Yogi Berra
|
|
|
|
|
It must be the MS JET Engine. It only wants one arguement for the ISNULL function. Back to the drawing board.
"It has become appallingly obvious that our technology has exceeded our humanity."
- Albert Einstein (1879-1955)
"I think there is a world market for maybe five computers."
- Thomas Watson (1874-1956), Chairman of IBM, 1943
"640K ought to be enough for anybody."
- Bill Gates (1955-), in 1981
"Half this game is ninety percent mental."
- Yogi Berra
|
|
|
|
|
I think jet has a different ISNULL. The syntax is ISNULL(value).
Try using IIF to check the condition and set it to 0 if it is null.
e.g. IIF(ISNULL(value),0, value).
|
|
|
|
|
Good one. I shouldn't have blindly assumed that he was using SQL Server, I guess.
Regards,
Jeff Varszegi
|
|
|
|
|
Yes this works:
SELECT IIF(ISNULL(Sum(Amount)),'0', Sum(Amount)) as 'Total Expenditures', '1' as 'Month'
FROM Expenditure
WHERE MONTH(eDate)=1
I suppose I will write it as Jeff suggested using the above.
Thanks for the help guys! THE CODE PROJECT IS AWESOME!
|
|
|
|
|
I would recommend just setting up a months table with 1 columns (month number). Then join that to your query (something like ... month(a.edate) = b.month_nm ...)
|
|
|
|
|
Hi!
Could you show me the difference between ADO and DAO?
Thanks!
|
|
|
|
|
DAO = Data Access Objects
ADO = ActiveX Data Objects
in brief .. use ADO .. don't use DAO
ADO is more general .. you can access many data sorces : XML,Active Directory,File System + SQL server , oracle , MySQL etc.. using OLEDB provides or ODBC..
DAO is more dedicated for access..ADO can work perfectly with Access.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
