Thread: ODBC SQL Error

HI Community,

I'm getting this error when performing a query using ODBC command:-

ERROR [07002] [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error.

Here's my code :-

Code:

Private Sub sb_bindInventory()
        Dim conn As OdbcConnection = Nothing
        Dim adp As New OdbcDataAdapter
        Dim dset As New DataSet




        Try
            If pfnBln_ODBC_Connect(conn, "PPS", False) Then
                '{call GetNodeID(?,?)}
                adp.SelectCommand = New OdbcCommand("{call Proc_Rpt_InventoryCount(?,?)}", conn)


                With adp.SelectCommand
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.AddWithValue("@CycleCountNo", IIf(txtSearch.Text.Trim = "", 0, txtSearch.Text.Trim))
                End With


                adp.Fill(dset, AppSettings("StockCount")) 'The error hits here






                If dset.Tables(AppSettings("StockCount")).Rows.Count <= 0 Then
                    molGeneral.psb_Err(lblErrMsg, "No record available to generate report", "")
                    Session(AppSettings("Session_Rpt")) = Nothing
                    Exit Sub
                Else
                    Session(AppSettings("Session_Rpt")) = dset.Copy
                    Session(AppSettings("RPT_ID")) = AppSettings("RPT_StockCount")
                End If


            End If
        Catch ex As Exception
            Throw New Exception("sb_bindNewParts - " & ex.Message)
        Finally
            pfnBln_ODBC_Connect(conn, "", True)
            psb_Dispose_Data_Adapter(adp)
            psb_Dispose_Data_Set(dset)
        End Try
    End Sub

And this is the stored procedure i'm calling :-

Code:

ALTER PROCEDURE [dbo].[Proc_Rpt_InventoryCount] 
@CycleCountNo bigint


AS
BEGIN


set nocount on;
   IF @CycleCountNo=0 
   BEGIN
      SELECT Cycle_Count_No, Item_Code, Item_Desc,Cost_Center, Location, Count_Qty, 
      ISNULL(CountBy,'') AS Real_Name
      FROM SC_Details
      /* get cycle count no */
      INNER JOIN SC_Header ON SC_Header.SCH_ID=SC_Details.SCH_ID 
      INNER JOIN SC_Trans ON SC_Details.SCD_ID=SC_Trans.SCD_ID
      GROUP BY Cycle_Count_No, Item_Code, Item_Desc,Cost_Center, Location, Count_Qty, CountBy
   END


   IF @CycleCountNo>0 
   BEGIN
      SELECT Cycle_Count_No, Item_Code, Item_Desc,Cost_Center, Location, Count_Qty, 
      ISNULL(CountBy,'') AS Real_Name
      FROM SC_Details
      /* get cycle count no */
      INNER JOIN SC_Header ON SC_Header.SCH_ID=SC_Details.SCH_ID 
      INNER JOIN SC_Trans ON SC_Details.SCD_ID=SC_Trans.SCD_ID
      /* get real name */
      WHERE Cycle_Count_No=@CycleCountNo
      GROUP BY Cycle_Count_No, Item_Code, Item_Desc,Cost_Center, Location, Count_Qty, CountBy
   END


END

Looks like a group by clause is clashing with isnull function

does your sp gives any return if you execute it at management studio with test data?