|
Hi there I'm playing around on SQL server 2000 and I get a error when i try to run this query:
SELECT *, COUNT(P.PTICKETNO) AS counter from TICKETS_PURCHASED T,PASSENGER P WHERE P.PTICKETNO = T.TKTNO AND T.TKTNO = 'TKT000000000001'
The error is as such.
Column 'T.TKTNO' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
What could be the problem guys . My SQL is very weak and I' just getting my feet wet, I would appreciate all the help
|
|
|
|
|
Add a GROUP BY clause at the the end with the name of proper column and you will see that it will works. Look into GROUP BY in SQLServer Books Online for how to use it.
Mazy
You're face to face,
With the man who sold the world - David Bowie
|
|
|
|
|
SqlString = "SELECT Start1, End1, Start2, End2, Start3, End3, Start4, "
"End4, Start5, End5, Start6, End6, Start7, End7, Start8, End8, "
"Start9, End9, Start10, End10, Start11, End11, Start12, End12 "
"FROM BANDSETTING "
"WHERE BANDNAME = &",
" &m_Store ";
Hi... the above is my code. m_Store is the variable i want to concatenate.
but it doesnt work the way i want it. what's wrong?? =)
by the way, im not using VBA...
this query is called in the windows application accessing the database.
|
|
|
|
|
Firstly, where possible, you should use parameters. Parameters get rid of a whole host of SQL injection and invalid character problems.
It looks like you're writing a letter C# or C++ code. If it's C#, you could write
SqlString = "SELECT Start1, End1, Start2, End2, Start3, End3, Start4, "
"End4, Start5, End5, Start6, End6, Start7, End7, Start8, End8, "
"Start9, End9, Start10, End10, Start11, End11, Start12, End12 "
"FROM BANDSETTING "
"WHERE BANDNAME = " + m_Store; If it's C++, then it depends on the type of m_Store : if it's a CString or a std::string , then the same as above; otherwise, you'll probably have to use strcat .
At base, it's a string concatenation problem; use whatever your language and environment provide for concatenating strings.
|
|
|
|
|
Hi,
Is this C#? If so, you need a concatination operator (+) between each pair of strings that you want to concatination:
SqlString = "SELECT Start1, End1, Start2, End2, Start3, End3, Start4, " +
"End4, Start5, End5, Start6, End6, Start7, End7, Start8, End8, " +
Bill
|
|
|
|
|
|
How about not posting the same advertisement in every single forum?
Hey, how about no advertising at all?
|
|
|
|
|
I want to write some long encryted text during my code in .doc file. I have used Stream Object and use write() method of stream object which take object of type variant as input. MY code throws exception 0x800A0BB9 when it execute write() method. Below is my code. I have spend around 1.5 days in this. Pls help me how to solve this.
If i read text from some other using Read() method of Stream object and use output of Read() method (i.e. output of Read() is Variant object) then it work find and write() is not throwing exception.
CString outileName = "myenc.doc";
ADODB::_StreamPtr outputStream = NULL;
hr = outputStream.CreateInstance(__uuidof(ADODB::Stream));
outputStream->Charset = "ascii" ;//"iso-8859-1";
outputStream->Type = ADODB::adTypeBinary;
outputStream->Open(vtEmpty, ADODB::adModeUnknown, ADODB::adOpenStreamUnspecified,bstrEmpty, bstrEmpty);
outputStream->Position = 0;
VARIANT myVt;
VariantInit(&myVt);
myVt.vt = VT_BSTR;
myVt.bstrVal = SysAllocString(L"testing");
outputStream->SaveToFile(outileName.AllocSysString(), ADODB::adSaveCreateOverWrite);
outputStream->Write(&myVt);
|
|
|
|
|
I am using the ODBC classes in ADO.NET. I just found that some connections being made by a database maintenance program I am writing are connecting to the wrong server because they are using named pipes rather than connecting through TCP/IP using the DSN I have set up on my system.
Is there a way to FORCE the connection to always use the TCP/IP connection and never use the named pipes??? Using named pipes for this particular program can be dangerous because the program is a database management program, and it is designed connect to the desired database exclusively using the DSN. If it uses a named pipe to connect, then it could perform maintenance on the wrong database with disasterous results!!!
|
|
|
|
|
Hi all,
I have an OleDbConnection object and I would like to be able to get a list of all the tables and views available through it.
Do I need to know anything about the underlying database architecture to do this? For example, if it were mssql, I would know to query sysobjects . But if I do not know where the underlying database stores the data am I SOL?
I suspect the answer is "no" because I can get a list of tables and views when I add this data connection to my server explorer in VS.NET but I haven't a clue how the do it in code.
Thanks in advance,
Bill
|
|
|
|
|
The easiest way is to use the following query.
Select name, type from Sysobjects
where type = 'U'
or type = 'V'
The other way is to use ADOX. However, I have not used it in .Net only in the old VB world.
Michael
|
|
|
|
|
Michael,
Thanks for the response! I did not realize it was that easy.
Bill
|
|
|
|
|
I would recommend doing this from a stored procedure.
Really don't want users messing around with sysobjects unless there very knowlegeable.
Michael
|
|
|
|
|
If I had sql server on the back-end...I would. But we are trying to get at data in Informix...which as far as I know doesn't support sprocs.
Like I said before, I really a complete novice with this particular back-end so I was looking for a way to do this that generalized to all ado.net.
Your suggestion works great btw, even with the Informix back-end. Thanks again.
Bill
|
|
|
|
|
hi
how can i compare time part of datetime field, for exaple :
time of datetimefield between '12:24:36' and '18:50:25'
thank you
|
|
|
|
|
Please Tell to me how can i make a progress bar control for showing the loaded data ine dataset or all data in any winforms.
cause this work is easy in the ado classic .
but i cant find any method or event or object for this problem .
so thanks
Saeed Sa_tabrizi@yahoo.com
|
|
|
|
|
I cannot create a stored procedure like that:
CREATE PROCEDURE sp_GetLatestNews<br />
@nCount int<br />
AS<br />
Select top @nCount * from NEWS <br />
GO
But I CAN do like that:
CREATE PROCEDURE sp_GetLatestNews<br />
@nCount int<br />
AS<br />
Select top 5 * from NEWS <br />
GO
How can I throw a parameter to "Select TOP" command
=======================
Nothing is perfect
|
|
|
|
|
CREATE PROCEDURE sp_GetLatestNews
@nCount int
AS
SET ROWCOUNT @nCount
Select * from NEWS
SET ROWCOUNT 0
GO
|
|
|
|
|
CREATE PROCEDURE sp_GetLatestNews
@nCount int
AS
declare @cCount varchar(10)
select @cCount = Convert(varchar(10),@nCount)
exec ('select top ' + @cCount + ' * from NEWS')
GO
onwards and upwards...
|
|
|
|
|
As the other two replies mentioned, you can use the SET ROWCOUNT statement or construct and execute a sql statement dynamically.
But I read the other day the Yukon will allow expressions to be used as an argument to the TOP statement.
This is from http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/default.aspx[^]
"The T-SQL TOP option in Yukon has two significant enhancements. You can now specify as an argument to TOP an expression that can contain variables or even a self-contained query. You can also use the TOP option with modifying DML (INSERT, UPDATE, DELETE).
To specify an expression, you must enclose it in parentheses. The expression should be of the BIGINT datatype when not using the PERCENT option, and a float value in the range 0 through 100 when using the PERCENT option. The following code shows how to use an expression with a variable to return the requested number of earliest orders from the SalesOrderHeader in the AdventureWorks database:"
USE AdventureWorks
DECLARE @n AS BIGINT
SET @n = 5
SELECT TOP (@n) *
FROM SalesOrderHeader AS SOH
ORDER BY OrderDate, SalesOrderID
Searching the web without Google is like straining sewage with your teeth. Userfriendly, 2003/06/07
|
|
|
|
|
I wondered that what is faster in executing?
CREATE PROCEDURE sp_GetLatestNews <br />
@nCount int <br />
AS <br />
SET ROWCOUNT @nCount <br />
Select * from NEWS <br />
SET ROWCOUNT 0 <br />
GO
or
CREATE PROCEDURE sp_GetLatestNews <br />
@nCount int <br />
AS <br />
declare @cCount varchar(10) <br />
select @cCount = Convert(varchar(10),@nCount) <br />
exec ('select top ' + @cCount + ' * from NEWS') <br />
GO
And what is T-SQL ? Help me !
=======================
Nothing is perfect
|
|
|
|
|
I believe that the first is faster
It gives SQL server the ability to comiple the SP and save it
but in second case ,, a query is constructed on the fly and then it will be parsed.
T-SQL = Transact SQL ( the language used in SQL Server )
|
|
|
|
|
It'd really make this project simple if i could, but this part doesn't appear to be as easy I thought.
|
|
|
|
|
You could use a combination of xp_cmdshell extended stored procedure and the dtsrun utility. Look in the BOL for the dtsrun utility, it's a command line program to execute DTS packages.
Jeff Martin
Triple20 Software
|
|
|
|
|
I need to know how can we grant permission to access the Msysobjects through C# coding.
Any kind of suggestion is highly appreciated!
|
|
|
|
|