Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
erico
Starting Member
34 Posts |
 Posted - 2007-04-24 : 13:47:58
|
I am trying to put this select code together correctly so that I get the desired result. I'm trying query the database and look for the default program entry (ala a story with content and links) for that day. When I try to run the query in sql query analyzer I get this:I have three tables. They are T_Programs, T_ProgramGuests and T_ProgramLinks. The key is in the T_Programs called ID.Syntax error converting the varchar value ""&id&"" to a column of data type int.The second time I tested I modified the query some and the result in sql query was a blank row of data.Here is the sql code:SELECT *, T_Programs.ID AS Expr1, T_ProgramGuests.GuestName AS Expr2, T_ProgramGuests.GuestDescription AS Expr3, T_ProgramLinks.URL AS Expr4, T_ProgramLinks.Description AS Expr5, T_Programs.Air_Date AS Expr6 FROM T_ProgramGuests CROSS JOIN T_Programs CROSS JOIN T_ProgramLinks WHERE (T_Programs.Air_Date = - 1) My thinking is that initially get the date from T_Programs and to get the guests and links. Maybe add a date column to extract the id of the story. When I get the date query use the id to query the other tables. Not sure but should I add a date column to the guests table?Here is a sample of what the screen output should look like to the user:Spring Gardening (story title)Audio available at 11:05 a.m.Do certain plants help the air and the environment more than others? More story here...Guests:John Doe is a gardening columnist for...Willi Getaway is the west coast editor of...Greg Rayband is co-manager of...Related Links:Company does not endorse nor control the content viewed on these links as they appear now or in the future. * The John Doe website (this is a text link) * Organic Gardening (this is a text link) * Yard Talk episodes (this is a text link)I'm close on this I just need some help finishing it. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-24 : 14:13:03
|
1. put some line breaks in your sql code so it's readable.2. from error msg, you are likely pasting together a sql statement in VB, and have some quotes missing. That's the source of your syntax error. 3. if you create sql statements by concatenating strings based on user input, you open yourself up to sql injections attacks.
www.elsasoft.org |
 |
|
|
jshepler
Yak Posting Veteran
60 Posts |
Posted - 2007-04-24 : 21:39:31
|
| [code]SELECT * , T_Programs.ID AS Expr1 , T_ProgramGuests.GuestName AS Expr2 , T_ProgramGuests.GuestDescription AS Expr3 , T_ProgramLinks.URL AS Expr4 , T_ProgramLinks.Description AS Expr5 , T_Programs.Air_Date AS Expr6FROM T_ProgramGuests CROSS JOIN T_Programs CROSS JOIN T_ProgramLinksWHERE (T_Programs.Air_Date = - 1)[/code]Few things wrong here.1) *2) CROSS JOIN3) Air_Date = -1"My thinking is that initially get the date from T_Programs and to get the guests and links. Maybe add a date column to extract the id of the story. When I get the date query use the id to query the other tables. Not sure but should I add a date column to the guests table?"Incorrect way of thinking.1) What do Programs, Guests, and Links all have in common? It's not the air date.2) What would a datetime column in the guests table mean - couldn't someone be a guest on more than 1 program?"I'm close on this I just need some help finishing it."Unfortunately, you're not close. Your query is wrong, your logic is flawed, and you're missing a table.It's wrong to come here and have someone do your homework for you. It's ok to get pointers or a nudge in the right direction, but you should be upfront about it. You would get better quality help without someone just giving you the answer.As for your error...... you should DEFINATELY read up on sql injection vulnerabilities./jeff |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
|
|
|
|
|
error converting varchar value