|
You didn't include Category table in your select query
here is my quick analyze
SELECT Product.*,ProductInCategory.*,Category.*<br />
FROM Product,ProductInCategory,Category<br />
where Product.ID = ProductInCategory.[ProductID]<br />
and ProductInCategory.CategoryID = Category.[ID]<br />
and Product.[ID] = '9780817637606';
I Love SQL
|
|
|
|
|
Thanks.
But I would like to use JOINS. Can you please help me now?
|
|
|
|
|
Just open Enterprise Manager of SQL2000 then create new View, copy code and paste in area of view then SQL Manager generates joins.
I Love SQL
|
|
|
|
|
I did the following and seems to work just fine:
SELECT
Product.ID,
Category.ID,
Category.Name
FROM
ProductInCategory
INNER JOIN Product
ON ProductInCategory.ProductID = Product.ID
INNER JOIN Category
ON ProductInCategory.CategoryID = Category.ID
WHERE
Product.ID = '9780817637606';
|
|
|
|
|
You haven't actually joined onto Category anywhere. Instead, you join onto the ProductInCategory table twice. I assume this is meant to read
SELECT *
FROM ProductInCategory
INNER JOIN Product
ON Product.ID = ProductID
INNER JOIN Category
ON CategoryID = Category.[ID]
WHERE Product.ID = '9780817637606';
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi,
This post has been posted about 3 times and no one has been able to help me.
I am using full text searching. When I type in text1 in my title textbox, then my CONTAINSTABLE looks like:
CONTAINSTABLE(tblFullTextSearch, [fldTitle], ' "text1" ')
When I type in text1 text2 then my CONTAINSTABLE looks like:
CONTAINSTABLE(tblFullTextSearch, [fldTitle], ' "text1" AND "text2" ')
But when I type in "text1 text2" then I NEED it to look like:
CONTAINSTABLE(tblFullTextSearch, [fldTitle], ' "text1 text2" ')
I'm not sure how to do this, please can some one help me?
Regards
ma se
|
|
|
|
|
hi,
i have an asp page which takes user input say password,with both uppercase and lower case letters and his password has to check with database and allow the user to see the member page,
the only problem is that when the user enter the only lowercase word ,its accepting..
how to correct that?
thanks
|
|
|
|
|
Are you storing the unencrypted password in your database? If you are then the quick way round this would be to change the collation on that column so that it is case-sensitive.
Most people use a "hash" of the password rather than actually storing the password in plain-text.
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
hi
no am storing it as text..
how to use the hash in storing the password?
thanks
|
|
|
|
|
Your text password column probably has a case-insensitive collation. Making it case-sensitive depends on your database software. Unser SQL-Server you would use the COLLATE clause of the ALTER TABLE statement.
If you are using SQL-Server 2005 then the HashBytes function can be used to create a hash of your password. To store the hash:
insert into UserTable values (@UserName, HashBytes('SHA1', @UserName + @Password))To check if a user's password is correct:
select 1 from UserTable
where UserName = @UserName
and PasswordHash = HashBytes('SHA1', @UserName + @Password)If you just hash the password then you would be able to see which users have the same password (because they would have the same hash value). It is better practice to "seed" the hash - so most people concatenate the user id (as above).
Hope that helps.
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Am using Ms access
any idea how to do it
|
|
|
|
|
i used the following sql statement within my asp page.where the user input his username and passwords.th sql check them with ms access database
SQL = "SELECT username, lpassword FROM clientlist WHERE username ='" & UserName & "'" & _<br />
" AND lpassword = '" & sPassword & "'"
thanks
-- modified at 7:47 Wednesday 15th August, 2007
|
|
|
|
|
I think that MS-Access is always case-insensitive, and that it does not have any hashing functions build-in. If you are using ASP.NET then you should be able to use one of the classes in the .NET framework to hash the password - and then store that in your database. If you are using "classic" ASP then you should be able to search Google for a SHA1 or MD5 implementation.
On a separate note - try entering a password of "' or '' = '". You should find that bypasses you password check. If you just want an error then enter a single-quote as your password. Read Colin's article on SQL injetion attacks[^] for information about how to stop this.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
hi all
i am developing application on asp.net and database sqlserver 2000
i am building function paging for my web
but i dont know how do that on Database
ex:
in my data base i have 100 row and my web in page have 10 row
i think this sql is
Select top 10 * from ( select ProductName, UnitPrice, linkimg from products a join tblimgproduct b on a.ProductID = b.productid )
Order by a.ProductID desc
but it error and the second i have select top 20 and i dont know cut 10 fist row
Some body tell me
Could u write command ex for me
|
|
|
|
|
|
Hi there!
I have a table of users, that has a city column.
I would like to find the cities from which I have only one registered user.
This is the code I use:
SELECT city<br />
FROM users<br />
GROUP BY city<br />
HAVING (COUNT(city) = 1)
It works fine, but I also want to get the id of the user, and that's where the problem is.
If I use this code:
SELECT city, id<br />
FROM users<br />
GROUP BY city, id<br />
HAVING (COUNT(city) = 1)
I get wrong results because the id column is unique, and the GROUP BY makes a mess.
I hope you'll understand... 
Thanks in advance!
|
|
|
|
|
Have a subquery:
SELECT u.id, c.city
FROM users AS u
INNER JOIN (SELECT city
FROM users
GROUP BY city
HAVING (COUNT(city) = 1)
) AS c ON c.city = u.city
|
|
|
|
|
And my previous post was worth a one for what reason?
If the answer didn't help or I didn't fully understand the question then I'd prefer an opportunity to resolve the problem rather than be randomly voted down.
|
|
|
|
|
I'm connecting to an old DBF file (dBase 5.0) and after sevral commands are executed (about 570-590 times)
it shows me thw following error
ERROR [HY001] [Microsoft][ODBC dBase Driver] System resource exceeded.
and reading the DB failes after that for good.
<br />
private void CollectDBData()<br />
{<br />
try<br />
{<br />
OdbcConnection conn = new OdbcConnection();<br />
OdbcDataReader reader = null;<br />
<br />
while ((m_areThreadActive) && <br />
(Thread.CurrentThread.ThreadState ==<br />
System.Threading.ThreadState.Running))<br />
{<br />
try<br />
{<br />
conn.ConnectionString = @"Driver={Microsoft dBASE Driver(*.dbf)};DriverID=533;Dbq=c:\";<br />
<br />
<br />
conn.Open();<br />
<br />
<br />
string commandStr = "SELECT * FROM TABLE1";<br />
<br />
<br />
<br />
OdbcCommand comm = new OdbcCommand(commandStr, conn);<br />
<br />
<br />
<br />
reader = comm.ExecuteReader();<br />
<br />
while ((reader!=null) && (reader.Read()))<br />
{<br />
string data = GetColumnValStr(reader,DBColumnsEnum.DATA1));<br />
<br />
}<br />
<br />
reader.Close();<br />
reader = null;<br />
comm.Dispose();<br />
conn.Close();<br />
}<br />
catch (Exception ex)<br />
{<br />
Console.WriteLine(ex.Message);<br />
}<br />
finally<br />
{<br />
if (reader != null)<br />
{<br />
reader.Close();<br />
}<br />
if (conn != null)<br />
{<br />
conn.Close();<br />
}<br />
}<br />
<br />
Thread.Sleep(1000);<br />
}<br />
}<br />
catch (Exception exc)<br />
{<br />
Console.WriteLine(exc.Message);<br />
}<br />
<br />
}<br />
<br />
private string GetColumnValStr(OdbcDataReader reader, DBColumnsEnum column)<br />
{<br />
try<br />
{<br />
string col = column.ToString().ToUpper();<br />
return Encoding.GetEncoding(1255).GetString(<br />
Encoding.GetEncoding(862).GetBytes(reader[col].ToString()));<br />
}<br />
catch (Exception ex)<br />
{<br />
Console.WriteLine(ex.Message);<br />
}<br />
return string.Empty;<br />
}<br />
the thread sleeps for 1 second (in the real code it is changable by app.config)
but for frequent updated sometime it will be as low as 1 sec...
i've tried to play with connection string and other stuff
i also tried connecting through Access file and got same errors...
i get another message from time to time
ERROR [HY000] [Microsoft][ODBC dBase Driver] Unexpected error from external database driver (15877).
this something i've searched the web and came out empty
except shuting down the application and opening it again
not a good thing for me
and replcaing the DB ain't possible cause it's not my system and i can't touch\don't have the code
(this application works over another application's DB without changing the data in it, only reading it)
btw i'm using the latest MDAC (2.8 SP1) and the computer is running win 2000
in the mean while running the same application on win 2003 over 10 times the time it runs on the destination computer
anyway, every help will be much appreciated to solve this mind crazy problems
Thanks
|
|
|
|
|
I will take a shot at this:
Start Quote: DbConnection.Close Method Documentation
The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled.
An application can call Close more than one time. No exception is generated.
If the DbConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close or Dispose, which are functionally equivalent. If the connection pooling value Pooling is set to true or yes, this also releases the physical connection.
End Quote
Close() and Dispose() are functionally equivalent. This means that you are lucky that your application functions at all. Most likely you have lots of memory and it takes time for the garbage collector to catch up and completely deallocate your connection object. The driver is eventually getting a pointer to a jumble of crap. To fix this, you need to reallocate (conn = new OdbcConnection();) just before you assign the connection string in the second try block.
|
|
|
|
|
i'll try it now and get back to you... thanks!
hope it works for the destination computer...
in a different computer (stronger one as i said with the win 2003 server) the app is working almost 24hours
with no problem.. :\
|
|
|
|
|
as expected... not working 
thanks for the help anyway.. if you have any other ideas
i'd appreciate them
|
|
|
|
|
|
declare @TableName varchar(128)<br />
select @TableName = 'tablename'<br />
<br />
select c.COLUMN_NAME <br />
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,<br />
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c<br />
where pk.TABLE_NAME = @TableName<br />
and CONSTRAINT_TYPE = 'PRIMARY KEY'<br />
and c.TABLE_NAME = pk.TABLE_NAME<br />
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
I Love SQL
|
|
|
|
|
Hi,
I know that you cannot connect remotely from SQL server 2005 Express to any other sql server.
But is it maybe possible to connect remotely from SQL Server 2005 (full edition) to SQL server 2005 express?
Thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)