Record Sorting By Last Name

[tekyge]

I am trying to get this sql stament to sourt a record via last name? Any help? Thanks guys

sql = "SELECT Projects.ProjectID, ProjectName, LastPaymentDate, Paid, Total, Mid([ProjectName],InStr([ProjectName]," ")+1) & ", " & Left([ProjectName],InStr([ProjectName]," ")-1) as LstFrst FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1")) & " ORDER BY [Projects].[ProjectName] ASC"

Error I get is below
Microsoft VBScript compilation error '800a0401'

Expected end of statement

 

[DotNetGnat]

Try this:

sql = "SELECT Projects.ProjectID, ProjectName, LastPaymentDate, Paid, Total, Mid([ProjectName],InStr([ProjectName]," ")+1) [red]+[/red] ', ' [red]+[/red] Left([ProjectName],InStr([ProjectName]," ")-1) as LstFrst FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1"))& " ORDER BY [Projects].[ProjectName] ASC"

-DNG

 

[tekyge]

Giving me the same error.

 

[DotNetGnat]

what database are you using...

do a

response.write sql

and see the output and make sure it works in the query analyzer...

-DNG

 

[tekyge]

Here is the code

dim conn, rs, sql, intTelephone, intMobile
set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Data\Data.mdb;" & _
"Jet OLEDB:System Database=C:\Data\Secur.mdw", _
"XXXX", "XXXXXXX"

sql = "SELECT Projects.ProjectID, ProjectName, LastPaymentDate, Paid, Total, Mid([ProjectName],InStr([ProjectName],"")+1) + ', ' + Left([ProjectName],InStr([ProjectName],"")-1) as LstFrst FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1"))& " ORDER BY [Projects].[ProjectName] ASC"
rs.open sql, conn

 

[tekyge]

Response write is

Do While NOT rs.EOF
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='100%'><b>" & rs("ProjectName") & "</b><br>Date Of Last Payment:&nbsp;" & rs("LastPaymentDate") & _
"&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;Payments Received:&nbsp;" & FormatCurrency(rs("Paid")) & "&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;" & _
"Outstanding Balance:&nbsp;" & FormatCurrency(rs("Total")) &"<br><hr noshade color=#000000 size=1><p></td>")
Response.Write("</tr>")
rs.MoveNext
Loop

 

[Sheco]

DNG wanted you to Response.Write the value of your sql variable so that the actual text of the query would be written to the web page.

This is often a handy debugging tool because it allows you to quickly spot syntax errors.

You remove the dubugging code once the proper query is restored.

 

[tekyge]

Thanks guys I got it going but now I get another error

sql = "SELECT Projects.ProjectID, ProjectName, LastPaymentDate, Paid, Total, Mid([ProjectName],InStr([ProjectName],"")+1) + ',' + Left([ProjectName],InStr([ProjectName],"")-1) as LstFrst FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1"))& " ORDER BY [Projects].[ProjectName] ASC"
rs.open sql, conn

Now I get this error
Microsoft JET Database Engine error '80040e10'

No value given for one or more required parameters.

 

[DotNetGnat]

that error means(without any doubt you are getting an empty value for your session variable...make sure session("cs_1") is not empty...

-DNG

 

[tekyge]

This is funny because when I remove Paid from the equation and from the right respose it works, but now it does not show the ProjectName and I have the rs as lstfrst.

 

[theniteowl]

tekyge, what Sheco and DotNetGnat are asking you for is this.
Right below the line where you setup your SQL statement add in a Response.Write sql like this:

sql = "SELECT Projects.ProjectID, ProjectName, LastPaymentDate, Paid, Total, Mid([ProjectName],InStr([ProjectName],"")+1) + ',' + Left([ProjectName],InStr([ProjectName],"")-1) as LstFrst FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1"))& " ORDER BY [Projects].[ProjectName] ASC"
[COLOR=green]Response.Write sql[/color]
rs.open sql, conn

This way you can see the actual statement as it will execute and make it far easier to spot any syntax errors or missing parameters. You need to do this before the rs.open line since that seems to be the point at which the script crashes and if you did the response.write afterwards it would never get to that point.

This is a very common and useful way to test that values are coming across the way you think they should be.


Stamp out, eliminate and abolish redundancy!

 

[tekyge]

ok got it to show the name

Mid([ProjectName],InStr([ProjectName],"")+1) + ',' + Left([ProjectName],InStr([ProjectName],"")-1) as LstFrst

should be

Mid([ProjectName],InStr([ProjectName],' ')+1) + ',' + Left([ProjectName],InStr([ProjectName],' ')-1) as LstFrst

Quotes in the InStr should be single quotes

 

[DotNetGnat]

glad you got it sorted...in future try to make the habit of using response.write statements for debugging purposes...

-DNG

 

[tekyge]

ok thanks. I have one more thing the sorting by LstFrst is not woring now

sql = "SELECT ProjectName, LastPaymentDate, Paid, Total, Mid([ProjectName],InStr([ProjectName],' ')+1) & " & _
"', ' & Left([ProjectName],InStr([ProjectName],' ')-1) as LstFrst FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1"))& _
" ORDER BY LstFrst DESC"
rs.open sql, conn

 

[DotNetGnat]

that is because...you cannot use alias names in the query directly...try this:

sql = "SELECT ProjectName, LastPaymentDate, Paid, Total, Mid([ProjectName],InStr([ProjectName],' ')+1) & " & _
"', ' & Left([ProjectName],InStr([ProjectName],' ')-1) as LstFrst FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1"))& _
" ORDER BY 5 DESC"

-DNG

 

[tekyge]

got it thanks for all your help.

sql = "SELECT LastPaymentDate, Paid, Total, Right([ProjectName],InStr([ProjectName],' ')+1) & " & _
"', ' & Left([ProjectName],InStr([ProjectName],' ')-1) as LstFrst FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1"))& _
" ORDER BY 4 ASC"
rs.open sql, conn

 

[DotNetGnat]

no problem...glad to help

-DNG

 

[tekyge]

For any that needs to know use this one rather than above for better performance

sql = "SELECT LastPaymentDate, Paid, Total, Mid([ProjectName],InStr([ProjectName],' ')+1) & " & _
"', ' & Left([ProjectName],InStr([ProjectName],' ')-1) as LstFrst FROM [Projects] WHERE [Projects].[ClientID]=" &Trim(session("cs_1"))& _
" ORDER BY 4 ASC"
rs.open sql, conn