string literal with sql

[Jimbo]

Hi, why is the @ not doing it's job here?

string sql = @"SELECT InvoiceLine.InvoiceLineItemRefFullName AS
["Product"],
Sum(InvoiceLine.InvoiceLineQuantity) AS ["Quantity"] FROM Invoice,
InvoiceLine
WHERE (((Invoice.TxnID)=[InvoiceLine].[TxnID])
AND ((InvoiceLine.InvoiceLineItemRefFullName)>'')
AND ((InvoiceLine.InvoiceLineQuantity)>0)
AND ((Invoice.TimeCreated)>#1/1/2004 11:20:6#
And (Invoice.TimeCreated)<#3/1/2004 11:20:6#))
GROUP BY InvoiceLine.InvoiceLineItemRefFullName;";

 

[Jon Skeet [C# MVP]]

Hi, why is the @ not doing it's job here?

string sql = @"SELECT InvoiceLine.InvoiceLineItemRefFullName AS
["Product"],
Sum(InvoiceLine.InvoiceLineQuantity) AS ["Quantity"] FROM Invoice,
InvoiceLine
WHERE (((Invoice.TxnID)=[InvoiceLine].[TxnID])
AND ((InvoiceLine.InvoiceLineItemRefFullName)>'')
AND ((InvoiceLine.InvoiceLineQuantity)>0)
AND ((Invoice.TimeCreated)>#1/1/2004 11:20:6#
And (Invoice.TimeCreated)<#3/1/2004 11:20:6#))
GROUP BY InvoiceLine.InvoiceLineItemRefFullName;";

What do you think it ought to be doing which it's not? You need to
double up on " when using verbatim string literals though.

 

[Jimbo]

Hi, why is the @ not doing it's job here?

string sql = @"SELECT InvoiceLine.InvoiceLineItemRefFullName AS
["Product"],
Sum(InvoiceLine.InvoiceLineQuantity) AS ["Quantity"] FROM Invoice,
InvoiceLine
WHERE (((Invoice.TxnID)=[InvoiceLine].[TxnID])
AND ((InvoiceLine.InvoiceLineItemRefFullName)>'')
AND ((InvoiceLine.InvoiceLineQuantity)>0)
AND ((Invoice.TimeCreated)>#1/1/2004 11:20:6#
And (Invoice.TimeCreated)<#3/1/2004 11:20:6#))
GROUP BY InvoiceLine.InvoiceLineItemRefFullName;";

What do you think it ought to be doing which it's not? You need to
double up on " when using verbatim string literals though.

Ah the old double quotes. I spend too much time not programming these days,
takes me a day or so to get back into the swing of things.

That's my excuse.

Thanks Jon!

 

[Jimbo]

Thanks Jon!

Gah, this query works in access but not in code:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll

string sql = @"SELECT InvoiceLine.InvoiceLineItemRefFullName AS
[""blah1""],
Sum(InvoiceLine.InvoiceLineQuantity) AS [""blah2""]
FROM Invoice,
InvoiceLine
WHERE Invoice.TxnID = [InvoiceLine].[TxnID]
AND InvoiceLine.InvoiceLineQuantity > 0
AND Invoice.TimeCreated >#1/1/2003 11:20:6#
And Invoice.TimeCreated<#3/1/2004 11:20:6#
AND InvoiceLine.InvoiceLineItemRefFullName like '%RIHT/150/50/'
GROUP BY InvoiceLine.InvoiceLineItemRefFullName;";

DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sql,DB.Connection());
da.Fill(ds);
return ds;

Any ideas?

 

[Nicholas Paldino [.NET/C# MVP]]

Jimbo,

You should be creating a parameterized query and then passing the
parameters for the query yourself. This way, you won't get hung up on the
formatting of the different types in Access (or any other provider, for that
matter).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Thanks Jon!

Gah, this query works in access but not in code:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll

string sql = @"SELECT InvoiceLine.InvoiceLineItemRefFullName AS
[""blah1""],
Sum(InvoiceLine.InvoiceLineQuantity) AS [""blah2""]
FROM Invoice,
InvoiceLine
WHERE Invoice.TxnID = [InvoiceLine].[TxnID]
AND InvoiceLine.InvoiceLineQuantity > 0
AND Invoice.TimeCreated >#1/1/2003 11:20:6#
And Invoice.TimeCreated<#3/1/2004 11:20:6#
AND InvoiceLine.InvoiceLineItemRefFullName like '%RIHT/150/50/'
GROUP BY InvoiceLine.InvoiceLineItemRefFullName;";

DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sql,DB.Connection());
da.Fill(ds);
return ds;

Any ideas?

 

[Jimbo]

Thanks Jon!

Gah, this query works in access but not in code:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred
in system.data.dll

string sql = @"SELECT InvoiceLine.InvoiceLineItemRefFullName AS
[""blah1""],
Sum(InvoiceLine.InvoiceLineQuantity) AS [""blah2""]
FROM Invoice,
InvoiceLine
WHERE Invoice.TxnID = [InvoiceLine].[TxnID]
AND InvoiceLine.InvoiceLineQuantity > 0
AND Invoice.TimeCreated >#1/1/2003 11:20:6#
And Invoice.TimeCreated<#3/1/2004 11:20:6#
AND InvoiceLine.InvoiceLineItemRefFullName like '%RIHT/150/50/'
GROUP BY InvoiceLine.InvoiceLineItemRefFullName;";

DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sql,DB.Connection());
da.Fill(ds);
return ds;

Any ideas?

IDIOT!

Always use try and catch! I had the wrong db path.

 

[Jimbo]

Jimbo,

You should be creating a parameterized query and then passing the
parameters for the query yourself. This way, you won't get hung up on the
formatting of the different types in Access (or any other provider, for that
matter).

Hope this helps.

Can Access do stored procedures....?

 

[Nicholas Paldino [.NET/C# MVP]]

Jimbo,

I believe it can. I think that when you open up Access, and add a
"Query" it is exposed as a stored procedure.

 

[Jon Skeet [C# MVP]]

Can Access do stored procedures....?

You don't need to use stored procedures to do parameterised queries.