Access vs oledb SQL Problem

[TheMagician]

Hi

 

I have some code for accessing a MS Access 2000 database. The code returns the error "No value given for one or more required parameters." when the line MyAdapter.Fill(dsGrid) is executed.

 

I know the adapter/connection work because it works on other queries in the same programme and it is identical (except for the SQL) so the connection string is correct. I therefore conclude the SQL is the problem but I thought the SQL was right because I have taken the strSQL value (which it outputs to a label at the end) and pasted it into Access and (after replacing the % with *) it produced the correct values within Access.

 

Any help would be greatly appreciated!!!!

 

Cheers

 

Ian

 

 Private Sub PopDataGrid()

       Dim strSQL, strDim1, strDim2, strFilter, strFilterCriteria, strDisp As String

       Dim strTab = "[National DB 2001-02]"

       'Main filter
       strFilter = cboFilter.SelectedValue
       If optDetail.Checked Then
           strFilterCriteria = cboFilterChoice.SelectedValue
       Else
           strFilterCriteria = ""
       End If

       'Dimensions
       strDim1 = cboDim1.SelectedValue
       strDim2 = cboDim2.SelectedValue

       'Display
       strDisp = cboDisplay.SelectedValue

       'Generate(SQL)
       'Select
       strSQL &= "SELECT " & strDim1 & "." & strDim1 & "_Name, "
       strSQL &= strDim2 & "." & strDim2 & "_Name, " & strTab & "." & strDisp
       ' From
       strSQL &= " FROM " & strFilter & " INNER JOIN (" & strDim1
       strSQL &= " INNER JOIN (" & strDim2 & " INNER JOIN " & strTab
       strSQL &= " ON " & strDim2 & "." & strDim2 & "_Id = " & strTab & "." & strDim2 & ")"
       strSQL &= " ON " & strDim1 & "." & strDim1 & "_Id = " & strTab & "." & strDim1 & ")"
       strSQL &= " ON " & strFilter & "." & strFilter & "_Id = " & strTab & "." & strFilter
       ' Where
       strSQL &= " WHERE " & strFilter & "." & strFilter & "_Name LIKE '%" & strFilterCriteria & "%';"

       Dim MyConnection As New OleDbConnection("provider=Microsoft.Jet.Oledb.4.0; data source=" & strPath)
       Dim MyAdapter As New OleDbDataAdapter(strSQL, MyConnection)
       Dim dsGrid As New DataSet

       Try
           MyAdapter.Fill(dsGrid)
           MyDataGrid.DataSource = dsGrid
           MyDataGrid.DataBind()
       Catch ex As Exception
           lblError.Text = ex.Message
       Finally
           MyConnection.Close()
       End Try

       lblMsg.Text = strSQL

   End Sub

[Robby]

In your cod you still have %, also do a MessageBox/Trace on strSQL and paste it here.

[TheMagician]

In your cod you still have %' date=' also do a MessageBox/Trace on strSQL and paste it here.[/quote']

 

SELECT AggregatedEconomicSector.AggregatedEconomicSector_Name, EndUse.EndUse_Name, [National DB 2001-02].[Delivered Energy (TJ)] FROM FuelType INNER JOIN (AggregatedEconomicSector INNER JOIN (EndUse INNER JOIN [National DB 2001-02] ON EndUse.EndUse_Id = [National DB 2001-02].EndUse) ON AggregatedEconomicSector.AggregatedEconomicSector_Id = [National DB 2001-02].AggregatedEconomicSector) ON FuelType.FuelType_Id = [National DB 2001-02].FuelType WHERE FuelType.FuelType_Name LIKE '**';

 

It doesn't make any difference whether I use * or % on the final result (ie theres still the same error) and I always thought ADO queries used % while access internal queries used *.

 

Something just occured to me - would this error occur if there wasn't a proper relationship between two tables (ie defined in the database)?

[TheMagician]

I figured out the problem - it was in the database structure itself - someone helpfully renamed the table names and inserted a new relationship between two existing ones without telling me! (dont you hate shared drives!)

 

Thanks for looking at it though!