Is there a way

Question

Post reply

Is there a way

John Tabor

Old Hand

Points: 381
More actions
August 6, 2005 at 12:40 am

#89333

Is there a way to just get the rows that pretain to lets say james?
strSQL = "SELECT Server, Port, Database_name, ticket, Address " _
& "FROM SearchDB, db_tlog_maint_plan, INFO " _
& "WHERE SearchDB.Fname = db_tlog_maint_plan.Fname and SearchDB_Fname = INFO.Fname " _
I always end up with info that does not pretain to james because th info is in the same columns. Any and all info may help. Thanks!
PS
The query is coming from a submit form, so the name can not be set in the actual select statment becaus it will be what ever the person types as the name. Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Uhm, not quite sure what you are looking for, but maybe something like this?

strSQL = "SELECT Server, Port, Database_name, ticket, Address " _

& "FROM SearchDB INNER JOIN db_tlog_maint_plan ON SearchDB.Fname = db_tlog_maint_plan.Fname " _

& "INNER JOIN INFO ON SearchDB.Fname = INFO.Fname " _

& "WHERE SearchDB.Fname = '" & strFnameInput & "'"

Now, strFnameInput would be a variable that contains the text the user entered in the form. Do not forget to validate the input data, at least do strFnameInput = Replace(strFnameInput, "'", "''")

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

John Tabor Old Hand Points: 381 More actions · Answer 2

Thanks Chris I am trying to get that to work. I keep geting ambiguous errors

Microsoft OLE DB Provider for SQL Server error '80040e14'

Ambiguous column name 'Date'.

/searchDB2.asp, line 78

I am using a web page with a search function to loacate data from multiple tables and columns and row. The search I that I posted ends up pulling the correct content from the first table and the returns all the rows from the othere two tables when it should be returning just the rows that pertain to the search. So if I where to look for james I will get just the info that pertains to james from the first table but then the othere tables will display all the content not just info that pertains to james. This is an ASP form if that sheds any light on this.

Any input may be helpful Thanks!

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

This error message tells you that somewhere in the query you are referring to a column called Date, but more than one of the tables that are used in the query have a column called Date. SQL Server can therefore not be sure which of those tables to take the column Date from. You should therefore prefix the column name to tell it which table to use. Like this:

SELECT TableA.SomeColumn, TableB.SomeColumn FROM TableA, TableB

To reduce the amount of characters in the code you can use aliases for tables, like so:

SELECT a.SomeColumn, b.SomeColumn FROM TableA AS a, TableB AS b

Regarding the non-filtering queries: ASP or whichever client does not really make any difference here. You need to write a query that filters the results using a WHERE clause such as the one I did above. I could help you if you supply the DDL (the definition of the tables involved), some sample data and the expected results for that data.

I would really suggest though that you get a hold of some basic book on SQL, since these queries are about the most basic ones there is.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

This book is a good start by the way: The Guru's Guide to Transact-SQL by Ken Henderson

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

John Tabor Old Hand Points: 381 More actions · Answer 5

Thanks Chris point taken, I dont do much coding but I am going to look into a book on it to make things a little easyer. Thanks again!