|
Select Convert(smalldatetime,'01/02/2006',102)
"Aim to go where U have never been B4 and Strive to achieve it"
http://groups.yahoo.com/subscribe/dotnetforfreshers
http://himabinduvejella.blogspot.com
|
|
|
|
|
Hi All,
Can't we use temporary table as part of the SHAPE command? I got the following error when I am trying to use. "[MERANT][ODBC Oracle 8 Driver][Oracle]ORA-00942: table or view does not exist". I will appreciate that if someone help me with this. My SQL goes like this.
SHAPE {SELECT * FROM PARENT }
APPEND ((SHAPE {SELECT * FROM CHILD }
APPEND ({SELECT * FROM GRANDCHILD,
(SELECT * FROM (Select * From ext_attr_val a Where upper(a.attribute_cd) = 'GET') get, (Select * From ext_attr_val b Where upper(b.attribute_cd) = 'SET') set Where get.seq = set.seq and get.c_seq = set.c_seq and get.t_seq = set.t_seq) temp
} Relate otc_trade_Seq to otc_trade_Seq, otc_ci_seq to otc_ci_seq) as adoBotRs) RELATE trade_seq to otc_trade_seq) as adoMidRs
Here get, set and temp are temporary tables.
|
|
|
|
|
Hi,
Excuse my newbian ways to the world of SQL. Thanks to Mike Ellison i have the following code working:
SELECT orderTableID, orderUserID, SUM(orderPrice) AS totalPrice FROM tbl_Orders WHERE orderPaid = true GROUP BY orderTableID, orderUserID
Which shows me the Table Number, Name, and Total order cost, of customers. However i really need to be able to adapt this to produce total costs by category for each user. Ive come up with some SQL but it creates a new line for each user and each category total.
What i really need is one statement that will give me the following format:
Table, Name, Drinks Total, Food Total, Total Cost<br />
Table, Name, Drinks Total, Food Total, Total Cost<br />
Table, Name, Drinks Total, Food Total, Total Cost<br />
Table, Name, Drinks Total, Food Total, Total Cost<br />
Table, Name, Drinks Total, Food Total, Total Cost
etc, whereby each customers order total(s) is display as one line.
Hope this makes sense, and that someone can help me sort tihs out.
Thanks in advance!
munklefish
|
|
|
|
|
We don't have enough information to answer this question. How is the 'Drinks' and 'Food' stored in your schema? Is 'Total Cost' a summation of these two?
|
|
|
|
|
Hi,
Ive tried my best to determine the SQL i need to match and retrieve data from two tables. Below is the SQL ive come up with so far but when i test it in Access, it gives me a non descriptive error.
Here is the code so far:
SELECT a.*, b.* FROM tbl_Payment AS a, (SUM (orderPrice) AS totalPrice FROM tbl_Orders, WHERE orderPaid = false AND orderTableID = '12' AND orderUserID = 'Healey') AS b WHERE a.paymentTable=b.orderTableID AND a.paymentTable = b.orderUserID
Can anyone help me sort this out. Basically i need to get all the data from one table and its corresponding data in a second table. If you could give me any help id really appreciate it.
Happy Easter!
Munklefish
|
|
|
|
|
SELECT a.*, b.*,
SUM (orderPrice) AS totalPrice
FROM tbl_Payment a
INNER JOIN tbl_Orders b ON a.paymentTable=b.orderTableID
AND a.paymentTable=b.orderUserID
WHERE orderPaid = false
AND orderTableID = '12'
AND orderUserID = 'Healey'
|
|
|
|
|
Mike,
When i run this in access it tells me that the Query doesnt include the specified expression paymentID as part of an aggregate function.
Not sure what this means. This field is an auto number & key field in tbl_payment.
Sounds like my database design is a bit messed up?
Munklefish
|
|
|
|
|
Oops - forgot the GROUP BY clause.
SELECT fieldList,
SUM (orderPrice) AS totalPrice
FROM tbl_Payment a
INNER JOIN tbl_Orders b ON a.paymentTable=b.orderTableID
AND a.paymentTable=b.orderUserID
WHERE orderPaid = false
AND orderTableID = '12'
AND orderUserID = 'Healey'
GROUP BY fieldList
|
|
|
|
|
Mike,
Ive now got:
SELECT paymentTable, paymentName, SUM (orderPrice) AS totalPrice FROM tbl_Payment a INNER JOIN tbl_Orders b ON a.paymentTable=b.orderTableID AND a.paymentName=b.orderUserID WHERE orderPaid = false AND orderTableID = '12' AND orderUserID = 'Healey' GROUP BY paymentTable, paymentName
but how do i get say paymentValue from tbl_Payment ???
Thanks a lot Mike!!!
munklefish
|
|
|
|
|
SELECT paymentTable
, paymentName
, paymentValue
, SUM (orderPrice) AS totalPrice
FROM tbl_Payment a
INNER JOIN tbl_Orders b
ON a.paymentTable=b.orderTableID AND a.paymentName=b.orderUserID
WHERE orderPaid = false
AND orderTableID = '12'
AND orderUserID = 'Healey'
GROUP BY paymentTable, paymentName, paymentValue
Sounds like you would benefit from a good book or tutorial on SQL. Here are a few links:
W3Schools Tutorial on SQL[^]
Learning SQL[^] (by Alan Beaulieu, O'Reilly)
SQL for Dummies[^] (by Allen Taylor, Wiley Publishing)
|
|
|
|
|
Mike,
Thanks again for the help!
I intend to do a lot more study into SQL over the next few months, once i have more time. Ive been using the W3Schools site for a few years now, but ill be sure to take a look at the other links.
Thanks!!!!
munklefish
|
|
|
|
|
What I need is a simple way to add a new record to my database based on the data collected in my web site.
I have create an aspx form (vb) (vwd express) with several text boxes and a button.
I'd like that clicking the button will create a new record in my database (mdb) with the text entered in the texts box as the new record fields values.
(this was posted also on asp.net forum)
|
|
|
|
|
So using ADO.NET is out of the question?
Pictures of the menu available at the drive-thru
|
|
|
|
|
I've created a form view and change its mode from read-only to insert. now get an error that i need to use an insercommand.
where should I put the insert command and in what syntax?
|
|
|
|
|
Hi
I have a table in remote sql server
I am trying to display records in sql query anylazer
It is not display the records
It is giving Error
"Invalid object name 'ext'"
But it is working in sql Enterprice Manager
I try with "select * from dbo.ext"
It is also not working
Can any one help me
asdfasdfasdfasdfasdfasdfasdfasdfasdfasdf
|
|
|
|
|
Is the correct database active for the query window?
type use databasename or select the database from the drop-down first.
Michael
CP Blog [^] Development Blog [^]
|
|
|
|
|
I have recently installed sql server 2005 express, but don't know how to use it to create tables that contain data to work with. I would appreciate it if someone could point me to some article or guide to setup and use sql server 2005.
|
|
|
|
|
Download SQL Server Management Studio Express CTP from Microsoft's website so you can create databases, tables, and do other dba type of tasks.
PC
|
|
|
|
|
|
And don't forget articles here at CP. Though a majority of them are for SQL 2000, they still can apply to 2005
PC
|
|
|
|
|
Absolutely.
Pictures of the menu available at the drive-thru
|
|
|
|
|
I just switched to Visual Studio 2005 so the answer to this maybe easy. I am working on a web application that retrieves Data from a SQL 2000 server. The sql statement takes between 25 and 40 seconds to execute and runs fine in SQL Analyzer. However, it times out in the application until you do a preview Data from the table adapter. Once you successfully get the data to retrieve by testing the table adapter the application works for a while. I have the connection timeout set for 900 in the connection string. Under Tools > Options > Database Tools > I have the cancel long query selection unchecked. It seems that the connection times out at 30 seconds no matter what you set it for.
In Visual Studio 2003 you could set the SQLDataAdapter connection time. In VS 2005 there does not seem to be a setting for that.
Data Source=PSY133;Initial Catalog=WindoPath;Integrated Security=True;Connect Timeout=900
GTWebb
IT System Manager
gtwebb@coastalpath.com
|
|
|
|
|
There are two timeouts. The Connection Timeout is the length of time that you are prepared to wait for it to connect to the database (not do any work). You are looking for the Command Timeout which defines the length of time that the command can take. By default it is set at 30 seconds. You need to change the CommandTimeout on the SqlCommand object you are using.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Thanks for guiding me in the right direction. I still have another problem. When I create a partial class to adjust the command timeout.
The _commandCollection and _adapter are always null.
namespace mdsTableAdapters
{
public partial class MedDiagTableTableAdapter
{
public int SelectCommandTimeout
{
get
{
return (this._commandCollection[0].CommandTimeout);
}
set
{
for (int i = 0; i < this._commandCollection.Length; i++)
{
if ((this._commandCollection[i] != null))
{
((System.Data.SqlClient.SqlCommand)
(this._commandCollection[i])).CommandTimeout = value;
}
}
}
}
}
}
|
|
|
|
|
What benefits are you getting from using a partial class in this instance?
GTWebb wrote: The _commandCollection and _adapter are always null.
Do you ever initialise them somewhere? You don't show that code so I can't see what could be wrong.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|