Dear All, What's wrong with the following select statement? string M_Surname=""; string M_GivenName=""; string M_MiddleName=""; int M_StudentID=0 string sSql = "SELECT Top 14 StudentID,Surname,GivenName,MiddleName,ChiName,Remarks FROM Student " "Order by Surname,GivenName,MiddleName,StudentID " "Where Pad(Surname,50)+Pad(GivenName,50)+Pad(MiddleName,50)+Cast(StudentID as Char(8))>" M_Surname.PadRight(50, ' ') + M_GivenName.PadRight(50, ' ') + M_MiddleName.PadRight(50, ' ') + M_StudentID.PadLeft(8, ' '); TIA

What's wrong with the following select statement? Nearly everthing. The command order ist wrong, right is SELECT FROM WHERE ORDER. And PAD isn't a valid Transact SQL function.

What is wrong with this T-Sql statement

Ben Tam 216

Dear All,

What's wrong with the following select statement?

string M_Surname="";
string M_GivenName="";
string M_MiddleName="";
int M_StudentID=0
string sSql = "SELECT Top 14 StudentID,Surname,GivenName,MiddleName,ChiName,Remarks FROM Student "

"Order by Surname,GivenName,MiddleName,StudentID "
"Where Pad(Surname,50)+Pad(GivenName,50)+Pad(MiddleName,50)+Cast(StudentID as Char(8))>"
M_Surname.PadRight(50, ' ') + M_GivenName.PadRight(50, ' ') + M_MiddleName.PadRight(50, ' ') + M_StudentID.PadLeft(8, ' ');

TIA

Viorel 112.1K Reputation points

2021-09-20T05:04:22.037+00:00

I think that Order By must be put after Where part and you must use Parameterised Queries (with a single parameter). Or at least put apostrophes around the concatenated value after ‘>’.
Timon Yang-MSFT 9,571 Reputation points

2021-09-20T06:31:22.387+00:00
@Ben Tam
In addition, M_StudentID is an int value, and we cannot directly use PadLeft on it.
This is the sql statement generated by this code:

SELECT Top 14 StudentID,Surname,GivenName,MiddleName,ChiName,Remarks FROM Student Order by Surname,GivenName,MiddleName,StudentID Where Pad(Surname,50)+Pad(GivenName,50)+Pad(MiddleName,50)+Cast(StudentID as Char(8))>TestM_Surname TestM_GivenName TestM_MiddleName 1

This seems a bit confusing. If you copy the generated sql to the database for inspection, what error will be prompted?
Which database do you use? I can't find information about Pad function.
Ben Tam 216 Reputation points

2021-09-20T14:14:37.677+00:00

@Olaf Helper @Timon Yang-MSFT @Viorel

Thanks for your replies and helps. The issue has already been solved.

Accepted answer

Olaf Helper 40,741 Reputation points

2021-09-20T06:38:06.343+00:00

What's wrong with the following select statement?

Nearly everthing. The command order ist wrong, right is SELECT FROM WHERE ORDER.
And PAD isn't a valid Transact SQL function.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

What is wrong with this T-Sql statement

0 additional answers