|
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.
|
|
|
|
|
|
Everytime I spoke with other Sun/Java flavoured developer they sneered at M$ SQL Server as if it's not a real database server. Is Oracle really so much faster than SQL? I also heard Oracle has a more comprehensive set of reporting tools. Personally, I have no experience with Oracle...
|
|
|
|
|
The current TPC-C[^] raw performance benchmarks suggest that Oracle on HP/UX on outlandish hardware (64-way Itanium2 1.5GHz, 512Gb of memory) does currently outperform SQL Server 2000 (64-bit) on the same outlandish hardware (look at the second and third rows, the first had 1024Gb of RAM).
However, most of us are lucky to be using 512 megabytes rather than 512 gigabytes.
Frankly, SQL Server usually performs well enough on almost any hardware. Oracle really requires massive amounts of memory - 9i couldn't create a database on our test system with 192Mb (taking 6 hours and then the client crashed), while SQL Server 2000 creates a new database in about 5 seconds on the same hardware.
Basically, you need to decide what you're going to use your system for (transaction processing versus ad-hoc queries), decide what performance level you actually need (i.e. how many transactions per second you're planning to handle). The performance of your system will also depend very much on how you write your queries and implement your indexes.
For most general purpose applications, SQL Server is fine.
|
|
|
|
|
Mike Dimmick wrote:
Frankly, SQL Server usually performs well enough on almost any hardware. Oracle really requires massive amounts of memory - 9i couldn't create a database on our test system with 192Mb (taking 6 hours and then the client crashed), while SQL Server 2000 creates a new database in about 5 seconds on the same hardware.
I looked at the URL you specified... SQL Server isn't doing so bad at all.. But I remember I read some performance reports from Oracle site. According to their report, SQL performance is just a small fraction of Oracle.. I didn't read into the details however.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
