Database

followait wrote:

Sometimes toInsert contains single quotation mark(s), how to do deal with it efficiently?

pCmd->CommandText=_bstr_t(L"INSERT INTO tmp(id,a,b,c) "
			L"VALUES(?,?,?,?)");

followait wrote:

pCmd->CommandText=_bstr_t(L"INSERT INTO tmp(id,a,b,c) "
L"VALUES(?,?,?,?)");

INSERT INTO tmp(id,a,b,c) VALUES(@id,@a,@b,@c)
CommandObject.Parameters.Add(@id,value);
CommandObject.Parameters.Add(@a,value);
CommandObject.Parameters.Add(@b,value);
CommandObject.Parameters.Add(@c,value);

select * <br />
from (select username,designation, count(*) as points <br />
      from (select username,designation <br />
	    from messages <br />
	   union all <br />
	    select username,designation <br />
	    from replies<br />
	   )t1 <br />
      group by username,designation<br />
     )t2<br />
order by points desc

select * from (
  select username, designation, count(*) as points
  from (
    select username, designation from messages
    union all
    select username, designation from replies
  ) as A
  group by username, designation
) as B
order by points desc

Mostafa Siraj wrote:

I'm building a web application which has lots of users, each with lots of security rules. what is better???
1- to make a table for users and another table for permissions. and insure by coding(in ASP.NET for example) the security
2- to create for each user in the system new user in the DBMS itself and assign the security from the database.

SELECT Contract_ID <br />
FROM Contracts <br />
WHERE DateDue BETWEEN GetDate() AND DateAdd(mm, 3, GetDate())

DECLARE @CatList VARCHAR(100)<br />
DECLARE @strSQL VARCHAR(500)<br />
<br />
SET @CatList = ''''+ 'Hardware' + '''' + ',' + '''' + 'Custom' + ''''<br />
PRINT @CatList<br />
<br />
SET @strSQL = 'SELECT p.ProductCode<br />
			FROM Product p<br />
				JOIN ProductCategory pc ON p.ProductCatID = pc.ProductCatID <br />
			WHERE ProductCategory IN (' + @CatList + ')'<br />
<br />
PRINT @strsql<br />
EXECUTE (@strSQL)

Krish - KP wrote:

DECLARE @CatList VARCHAR(100)
DECLARE @strSQL VARCHAR(500)

SET @CatList = ''''+ 'Hardware' + '''' + ',' + '''' + 'Custom' + ''''
PRINT @CatList

SET @strSQL = 'SELECT p.ProductCode
FROM Product p
JOIN ProductCategory pc ON p.ProductCatID = pc.ProductCatID
WHERE ProductCategory IN (' + @CatList + ')'

PRINT @strsql
EXECUTE (@strSQL)

select unitprice, quantity, (unitprice*quantitiy) as sales<br />
from salesreport<br />
where (unitprice*quantitiy)> (select avg(unitprice*quantitiy) from salesreport)