VB.NET/MS Access sql problem

Saint_Mel

Having a bit of bother filling a DataSet.
Background ... VB.NET application, MS Access database

I want to concatenate 2 of the fields in a query so the SQL and have them seperated by a hyphen as so:

SELECT [Field1] & " - " & [Field2] AS [JoinedFields],
SUM([Field3]) AS [TotalField3]
GROUP BY Field1, Field2

When I run this directly in the MS Access query it works fine, results shown as :

[JoinedFields] ... [TotalField3]
[AAA - BBBBB] ........... [ 2 ]
[AAA - CCCCC] ........... [ 5 ]
[AAA - DDDDD] ........... [ 2 ]
[BBB- AAAAAA] ........... [ 1 ]
[BBB - BBBBB ] ........... [ 3 ]
[BBB - DDDDD] ........... [ 7 ]

Problem is when I run it through VB.NET to fill a DataSet

Dim DBCommand = New OdbcCommand(sSQL, DBConnection)
daQueryResults.SelectCommand = DBCommand
daQueryResults.Fill(dsQueryResults, "QueryResultsDataSet")

On the Fill I get :
"ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1."

Any idea what I'm doing wong?

FSL

Why have you got quotation marks around QueryResultsDataSet?

Presumably QueryResultsDataSet is a variable by putting quotation marks around it you are sending the string "QueryResultsDataSet" not the contents of QueryResultsDataSet.

Evil Phil

Can you post the complete code listing? It's difficult to understand what exactly your doing with only 3 lines of actual code.

Saint_Mel

Evil Phil wrote: »

Can you post the complete code listing? It's difficult to understand what exactly your doing with only 3 lines of actual code.

Yep,

Dim sConnectionString As String = "dsn=myConnectionString"
Dim DBConnection As New Odbc.OdbcConnection(sConnectionString)
Dim daQueryResults As New OdbcDataAdapter
Dim dsQueryResults As New DataSet

Try

Dim sSQL As String = ""
sSQL = "SELECT [Field1] & " & """ - """ & " & [Field2] AS [JoinedFields], SUM([Field3]) AS [TotalField3] FROM myTable GROUP BY Field1,Field2"

Dim DBCommand = New OdbcCommand(sSQL, DBConnection)
daQueryResults.SelectCommand = DBCommand
daQueryResults.Fill(dsQueryResults, "QueryResultDataSet")

Catch exODBC As Data.Odbc.OdbcException
...
...
...

stevenmu

Try apostrophes instead of quote in you SQL string

sSQL = "SELECT [Field1] & ' - ' & [Field2] AS [JoinedFields], SUM([Field3]) AS [TotalField3] FROM myTable GROUP BY Field1,Field2"

It's been a while, but from what I remember the .Net Access adapter is a little less flexible with the characters it supports than Access itself, often leading to cryptic errors like this one.

Saint_Mel

stevenmu wrote: »

Try apostrophes instead of quote in you SQL string

sSQL = "SELECT [Field1] & ' - ' & [Field2] AS [JoinedFields], SUM([Field3]) AS [TotalField3] FROM myTable GROUP BY Field1,Field2"

It's been a while, but from what I remember the .Net Access adapter is a little less flexible with the characters it supports than Access itself, often leading to cryptic errors like this one.

Fantastic, that worked perfect. Thanks a million for your help