temporary table

[neenaprasad]

i created a temporary table like this

SqlStr = "CREATE TABLE #t_temp(MemberID int, First_Name varchar, Last_Name varchar, Introducedby int)"

inserted values id,firstname,lastname and x_intro(is the count)

SqlStr = "INSERT MemberID, First_Name,Last_Name from Membership AND x_intro INTO #t_temp where Introducedby = " &x_introAgent

i have to get the output order by first name.

StrSql4 ="SELECT * from #t_temp ORDER BY First_Name"
Set rsAgents2 = Server.CreateObject("ADODB.Recordset")
rsAgents2.Open StrSql4, conn, 1, 2

its not working
can u help me?

[shweta123]

Hi,

Can you please specify about the error you are getting. Please check the syntax of your sql statement :

SqlStr = "INSERT MemberID, First_Name,Last_Name from Membership AND x_intro INTO #t_temp where Introducedby = " &x_introAgent

It should be as following :

INSERT INTO #t_temp(MemberID, First_Name,Last_Name)
SELECT MemberID, First_Name,Last_Name
FROM Membership
WHERE Introducedby = " &x_introAgent

i created a temporary table like this

SqlStr = "CREATE TABLE #t_temp(MemberID int, First_Name varchar, Last_Name varchar, Introducedby int)"

inserted values id,firstname,lastname and x_intro(is the count)

SqlStr = "INSERT MemberID, First_Name,Last_Name from Membership AND x_intro INTO #t_temp where Introducedby = " &x_introAgent

i have to get the output order by first name.

StrSql4 ="SELECT * from #t_temp ORDER BY First_Name"
Set rsAgents2 = Server.CreateObject("ADODB.Recordset")
rsAgents2.Open StrSql4, conn, 1, 2

its not working
can u help me?

[neenaprasad]

hi,

From a table i displayed the Agents memberid, firstname,lastname & total introduced(no of members each Agent introduced)
the code is as follows..

StrSql = "SELECT MemberID,First_Name,Last_Name,Introducedby FROM Membership where IsAgent = 1"
Set rsAgents = Server.CreateObject("ADODB.Recordset")
rsAgents.Open StrSql, conn, 1, 2
rsAgents.Movefirst

Do until (rsAgents.Eof )
x_introAgent = rsAgents("MemberID")
StrSql2 ="SELECT First_Name from Membership where Introducedby = " &x_introAgent
Set rsAgents1 = Server.CreateObject("ADODB.Recordset")
rsAgents1.Open StrSql2, conn, 1, 2
x_intro = rsAgents1.recordcount
rsAgents.Movenext


I got the output in the order in which they are entered into the table.. But i want the output order by First_Name. So i created a temporary table like this

SqlStr = "create table #t_temp(MemberID int, First_Name varchar, Last_Name varchar, Introducedby int)"


Then i inserted into temporary table as follows..
If x_intro > 0 then
SqlStr = "INSERT INTO #t_temp(MemberID, First_Name, Last_Name, Introducedby) Values (select MemberID, First_Name,Last_Name from Membership) & x_intro where Introducedby = " &x_introAgent
End if


then i displayed from temporary table as follows..
SqlStr3 = "SELECT * FROM #t_temp order by First_Name"
Set rsAgents2 = Server.CreateObject("ADODB.Recordset")
rsAgents2.Open SqlStr3, conn, 1, 2

I am extremely new to asp & i am creating temporary table for the first time. I don't know whether the syntax is correct or not. After the select statement should i write the drop table statement?

During output the values are retrieved from the original table not from the temporary table. i want the values from the temporary table order by first name

Hope u understand it
Can u please help me?