help needed for 2 tables

hi everyone, i've added 2 tables one is on adding, the other one is on returning. but when i return, the dropdownlist keeps displaying those which are returned already. it shouldnt display because i've already returned.

here are the codes that i add,

"INSERT INTO record VALUES( '" + dateOfIncoming + "', '" + manifestNo + "', '" + hoName + "','" + hoRemarks + "', '" + toName + "', '" + toRemarks + "', '" + purpose + "', '" + timeEntered + "', '" + dateEntered + "', 'Out')"

here are the codes that i return

"INSERT INTO returnRecord VALUES( '" + dateOfIncoming + "', '" + manifestNo + "', '" + hoName + "','" + hoRemarks + "', '" + timeEntered + "', 'In')"

here are the codes that i wanna display in the dropdownlist which i have set the status = IN. means they have returned already so it should display.

this is wad i tried what its wrong -->

"select a.manifestNo from record a, returnRecord r where a.status = 'Out' And r.status = 'In'"

my dropdownlist cant retrieve those records that are already returned. i suspect is the select statement that is wrong. but i cant figure out how. please help me!

regards, ethan

Instead of using inline SQL you really should use stored procedures, if you rewrite these as stored procedures you will be able to test them more effectively in sql management studio. Are you trying to sleect manifestNo from both tables, i think the error lies around returnecord r - you are specifiying the table but the select.

Help me with SP OrderBy

I have a SP that accepts parameters, but the problem is that it is not ORDERING Data as required, It is returning ORDER BY NULL instead of order by my parameter "NAME "here is the output from SQL 2000 DB

'ELP B4 I jump out of LondonBridge

**********************************
SELECT * FROM #TempTable WHERE ID > 0 AND ID < 6 AND EmployerID = 54 AND Job_no = 40

ORDER BY NULL
****************************************

SELECT @.MYSTATEMENT = ' SELECT * FROM #TempTable
WHERE
ID > '+ convert(varchar(20),@.FirstRec) +'
AND
ID < '+convert(varchar(20),@.LastRec) +'
AND EmployerID = '+ convert(varchar(20),@.EmployerID)+'
AND Job_no = '+convert(varchar(20),@.Job_no)+'
ORDER BY ' +
CASE WHEN @.WhichColumn= 'name' AND @.sortby= 'asc'
THEN 'name'
ELSE 'NULL'
END
+
CASE WHEN @.WhichColumn='name' AND @.sortby='DESC'
THEN ' desc'
ELSE ''
END
EXEC (@.myStatement)

try :

ORDER BY ' +
CASE WHEN @.WhichColumn= 'name' AND @.sortby= 'asc' THEN 'name ASC'
 WHEN @.WhichColumn = 'name' AND @.sortby = 'desc' THEN 'name desc'
ELSE 'NULL'
END

also do a print @.myStatement before you execute the statement to see how its building up.

hth|||Hi this is the output, it is still showing null

SELECT * FROM #TempTable

WHERE
ID > 0
AND
ID < 6
AND EmployerID = 54
AND Job_no = 40

ORDER BY NULL

(5 row(s) affected)|||what value are you passing to the parameter @.WhichColumn ?|||I have done a print on @.whichColumn, it is giving me the right value which is 'NAME'
So the problem must be from the case statement, but I can't figure it out

Thanks|||

declare @.MYSTATEMENT varchar(500), @.WhichColumn varchar(100), @.sortby varchar(100)
set @.sortby = 'desc'
set @.WhichColumn = 'name'
SELECT @.MYSTATEMENT = ' SELECT * FROM #TempTable WHERE ID > 6 AND ID < 10 ORDER BY ' +
CASE WHEN @.WhichColumn= 'name' AND @.sortby= 'asc' THEN 'name ASC'
 WHEN @.WhichColumn = 'name' AND @.sortby = 'desc' THEN 'name desc'
ELSE 'NULL'
END
print @.mystatement

i just did this in my QA and it worked fine for me. so double check your code.

hth