Hi,
I was wondering if there was a way to create two temp tables within the same stored procedure.
Can we have two create statements one after the other?
Thanks
Crazy:
I have done so many, many times.
|||Dave
what exactly error you are getting?
i too did so many number of tmes.
Gurpreet S. Gill
Everything is good except for the WHERE section, which is created from values in a list box.
For Example:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"
I build my SQL statement with these values like so:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2
The problem I am having is when there are multiple values of the same type in the list box. Say:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"
lstCriteria.items(1).value = "COMPANY = 'moo'"
My employer wants this to be valid, but I am having a tough time coming up with a solution.
I know that my SQL statement needs to now read:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2 OR COMPANY = 'poo' AND DAY = 2
I have code set up to read the values of each list box item up to the "=". And I know that I need to compair this value with the others in the list box...but I am not running into any good solutions.
Any HELP?How about OR like values together?
SELECT * FROM POO WHERE (COMPANY = 'foo' OR COMPANY = 'poo') AND DAY = 2|||Yes, that would work. But I am looking more at how to extract this data from the listbox to a usable format. I am working on setting the first value (the left(N) characters of the listbox item, which is also a column name) in an array, then inserting the secondvalue, checking if it is in the array, if it is, adding it to the array. If it is not, then adding it to a new array. This way I could loop thought the arrays and create my where statement...but It's just a thought on a whiteboard now.
Any Other suggestions?
On my site, I have a drop down list where users can select different columns from their database, and then once they select any given field, all the unique values that the field contains are brought up in a CheckBoxList for the user to select which ones they want to search for. (Note: that part of the site is already done--this next part is what I need help with) I want to create a SQL statement based on what the user checked. So like, if from FieldX they checked Item1, Item3, and Item8, the SQL statement created should be something along the lines of:
SELECT * FROM Orders Where FieldX='Item1' OR FieldX='Item3' OR FieldX='Item8'
This is going to be in an intranet, so I'm not too worried about SQL Injection attacks, which I've heard of, but don't really know what they are particulary. Although I guess it would be better to be safe rather than sorry.
Also, as far as creating the SQL statement, some items from the database will be text and others will be numbers, so I guess I also need to know how to find out whether an item in question is a string or a number of some type so that I can know whether to enclose that item in single quotes within the SQL statement.
Okay, I think that's it for now.
Thanks in advance.
Welcome to the forums.
This question has been answered a few times in these forums. Search in these forums and if you still dont find any possible solutio post back.
I know there is sp_addrole and sp_droprole to create roles. I just
found out that I could not use these two statements in an application
because the application is interacting with the database within
transactions through ODBC (SQLServer complains that these two stored
procedures cannot be executed within transactions).
Is there an alternative way of creating and dropping roles in plain
SQL?Hi
Adding and removing roles is not something that you should need to do
dynamically, but is more of an installation task. You may want to add people
to roles, but usually those roles are fixed.
Not being able to add a role within a transaction is documented in Books
Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsq
lref.chm::/ts_sp_addp_33s5.htm
I suggest you find out when/why your trasactions are started and why you are
trying to create a role at that time! To do this you may want to use
profiler.
John
"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0405311620.4fa9a98b@.posting.google.c om...
> Hello,
> I know there is sp_addrole and sp_droprole to create roles. I just
> found out that I could not use these two statements in an application
> because the application is interacting with the database within
> transactions through ODBC (SQLServer complains that these two stored
> procedures cannot be executed within transactions).
> Is there an alternative way of creating and dropping roles in plain
> SQL?
I can't get the right join statement to work. I've used the code below,
submitting an isbn (book id number) to identify the book, but the return
from the query simply sends me back all of the authors that are in the
many_to_many table
(called the book_to_author table here). I'd like it to return only the
authors attached to that isbn, instead of all the authors that are in
that table. What's wrong with the code below? Thanks for your help!
SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
Expr3 FROM author a INNER JOIN book_to_author m ON
a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
bf.isbn='"&isbn&"' order by m.id desc
Bill
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Do the following:
Your inner join statement should be:INNER JOIN book_to_author m ON
a.author_id= book_to_author.author_id
--
__________________________________________________ _________________
Remotely manage MS SQL db with SQLdirector -
www.ciquery.com/tools/sqldirector/
"Bill" <BillZimmerman@.gospellight.com> wrote in message
news:3f7b2243$0$24217$75868355@.news.frii.net...
> I have the following situation. We sell books on our website, and some
> of the books have more than one author. So I needed to create a
> many-to-many table, which is the intermidiate table between the author
> table and the book table.
> I can't get the right join statement to work. I've used the code below,
> submitting an isbn (book id number) to identify the book, but the return
> from the query simply sends me back all of the authors that are in the
> many_to_many table
> (called the book_to_author table here). I'd like it to return only the
> authors attached to that isbn, instead of all the authors that are in
> that table. What's wrong with the code below? Thanks for your help!
> SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
> bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
> Expr3 FROM author a INNER JOIN book_to_author m ON
> a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
> bf.isbn='"&isbn&"' order by m.id desc
> Bill
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Bill (BillZimmerman@.gospellight.com) writes:
> I have the following situation. We sell books on our website, and some
> of the books have more than one author. So I needed to create a
> many-to-many table, which is the intermidiate table between the author
> table and the book table.
> I can't get the right join statement to work. I've used the code below,
> submitting an isbn (book id number) to identify the book, but the return
> from the query simply sends me back all of the authors that are in the
> many_to_many table
> (called the book_to_author table here). I'd like it to return only the
> authors attached to that isbn, instead of all the authors that are in
> that table. What's wrong with the code below? Thanks for your help!
> SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
> bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
> Expr3 FROM author a INNER JOIN book_to_author m ON
> a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
> bf.isbn='"&isbn&"' order by m.id desc
That CROSS JOIN is most certainly not what you like. (author JOIN
book_to_author) gives your all authors and the books they have
written. Then you form a cartesian product with the book_detail_final
table, and finally you restrict the book with that isbn. But there
is no connection with the first two tables.
It is possible that this query cuts it:
SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id,
m.author_id AS Expr3
FROM author a
JOIN book_to_author m ON a.author_id = m.author_id
JOIN book_detail_final bf ON bf.isbn = m.isbn
WHERE bf.isbn='"&isbn&"'
ORDER BY m.id DESC
But since I don't know your tables, this is a guess.
If the guess is wrong, please post the following:
o CREATE TABLE statements of your tables.
o INSERT statements with sample data.
o The desired given from the sample data.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
