need help in following syntax

Question

public static DataTable LoadGrid(string SelectedItem,string yearSelected)

{

 DataTable tbl;

string a = SelectedItem;

string b = yearSelected;

string query =" SELECT top 10 SalesOrderID, RevisionNumber, OrderDate,DueDate, 
                            "ShipDate, Status,OnlineOrderFlag,SalesOrderNumber,PurchaseOrderNumber,AccountNumber, CustomerID, SalesPersonID, st.Name AS TerritoryName,BillToAddressID, ShipToAddressID,ShipMethodID, CreditCardID, CreditCardApprovalCode,CurrencyRateID, SubTotal, TaxAmt, Freight,TotalDue,Comment, soh.rowguid, soh.ModifiedDate FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID"+
WHERE  st.Name = "+a+"AND Datepart(year,OrderDate) = "+b;

tbl=DataAccess.cmd(query);
return (tbl);

}

the data passed to a is a territory and to b is b is year from 2 different dropdown list but the query is giving error saying invalid column name canada (suppose if i choose canada from the dropdown list) and canada is an item of a table in territory table in Name column

well i guess you must be using single quotes in the where clause — Nitin Singh, Jan 12 '15 at 03:58

score 0 · Answer 1 · answered Jan 12 '15 at 03:56

0

The reason for the error is because of missing quotes. The query is currently WHERE st.Name = Candada while it should be WHERE st.Name = 'Canada' instead.

Please consider using a stored procedure instead of using inline SQL queries though.

answered Jan 12 '15 at 03:56

sudheeshix

1,541
2
17
28

1

and st.TerritoryId is missing quotes, the string concatenation is wrong. – prospector Jan 12 '15 at 03:57
1

Give a man a fish, and he will eat for a day. Give a fish a man, and it will eats for weeks. – sudheeshix Jan 12 '15 at 04:07

score 0 · Answer 2 · edited Jan 12 '15 at 14:21

0

well to be more specific you should use the following syntax

string query = string.Format(
            "SELECT top 10 SalesOrderID, RevisionNumber, OrderDate,"
           +" DueDate,ShipDate, Status,OnlineOrderFlag,SalesOrderNumber,"
        +"PurchaseOrderNumber,AccountNumber, CustomerID,SalesPersonID, st.Name AS TerritoryName,"
        +"BillToAddressID, ShipToAddressID,ShipMethodID,CreditCardID,"
        +"CreditCardApprovalCode,CurrencyRateID, SubTotal, TaxAmt, Freight,TotalDue,Comment,"
        +"soh.rowguid, soh.ModifiedDate FROM Sales.SalesOrderHeader soh "
        +"INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID "
       +" WHERE st.Name='{0}' AND Datepart(year,OrderDate) = '{1}'",a,b);

but use sp in these kind of work...:)

edited Jan 12 '15 at 14:21

siride

200,666
4
41
62

answered Jan 12 '15 at 04:09

Nitin Singh

159
2
3
14

...I tried the above code ...it gave error saying incorrect syntax near st – Sumeet Hiremath Jan 12 '15 at 04:49
@SumeetHiremath: add a space after `soh"` (so it becomes `soh "`) on the third line from the bottom. I'll fix the code in the answer as well. – siride Jan 12 '15 at 14:21

score 0 · Accepted Answer · answered Jan 12 '15 at 05:25

public static DataTable LoadGrid(string selectedItem,string yearSelected)
{
    DataTable tbl;
    string query = string.Format( @" SELECT top 10 SalesOrderID, RevisionNumber, OrderDate,DueDate,ShipDate, Status,OnlineOrderFlag,SalesOrderNumber,PurchaseOrderNumber,AccountNumber, CustomerID, SalesPersonID, st.Name AS TerritoryName,BillToAddressID, ShipToAddressID,ShipMethodID, CreditCardID, CreditCardApprovalCode,CurrencyRateID, SubTotal, TaxAmt, Freight,TotalDue,Comment, soh.rowguid, soh.ModifiedDate 
                FROM Sales.SalesOrderHeader as soh 
                INNER JOIN Sales.SalesTerritory as st 
                    ON soh.TerritoryID = st.TerritoryID
                WHERE  st.Name = '{0}' AND Datepart(year,OrderDate) = '{1}'", selectedItem, yearSelected);

    tbl=DataAccess.cmd(query);
    return (tbl);
}

This might improve code readability thus makes writing queries more readable and less prone to syntax errors.

score -1 · Answer 4 · answered Jan 12 '15 at 04:16

SELECT top 10 SalesOrderID, RevisionNumber, OrderDate,DueDate, 
                            "ShipDate, Status,OnlineOrderFlag,SalesOrderNumber,PurchaseOrderNumber,AccountNumber, CustomerID, SalesPersonID, st.Name AS TerritoryName,BillToAddressID, ShipToAddressID,ShipMethodID, CreditCardID, CreditCardApprovalCode,CurrencyRateID, SubTotal, TaxAmt, Freight,TotalDue,Comment, soh.rowguid, soh.ModifiedDate FROM Sales.SalesOrderHeader soh INNER JOIN Sales.SalesTerritory st ON soh.TerritoryID = st.TerritoryID"+
WHERE  st.Name = "+a+"AND Datepart(year,OrderDate) = "+b;

why use + operator to compose the string again and again, very low efficient and hard to read , why not use "{0}".format()?

The `+` operator is going to be irrelevant here. It only really matters for large strings built with lots of operations. See: http://stackoverflow.com/questions/11133185/stringbuilder-versus-string-concat. — siride, Jan 12 '15 at 04:25
Also, this snippet won't even compile as you have the quotes all wrong. — siride, Jan 12 '15 at 04:26

need help in following syntax

4 Answers4