question

BenTam-3243 avatar image
0 Votes"
BenTam-3243 asked BenTam-3243 published

What is wrong with this T-Sql statement

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

sql-server-generaldotnet-csharp
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


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 ‘>’.

0 Votes 0 ·

@BenTam-3243
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.

0 Votes 0 ·

@OlafHelper-2800 @TimonYang-MSFT @Viorel-1

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

0 Votes 0 ·

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.