none
Query input must contain at least one table or query. RRS feed

  • Question

  • Hi,

    I am Using a Ms-access data base and Oledbconnection,

    Trying to run this code I get the Message error :

    " query input must contain at least one table or query".

    Private Sub Summ2() Try con = New OleDbConnection(cs) con.Open() Dim sql As String = "SELECT (SELECT COUNT( Cage) FROM Ponte1 ) AS Num3, ( SELECT COUNT( Datedéclosion) FROM Ponte1 WHERE ( Datedéclosion >=DATEADD(day, DATEDIFF(day,0,DATE()),0) AND Datedéclosion < DATEADD(day, DATEDIFF(day,0,DATE())+1,0)) ) AS Num1, ( SELECT Count( DatedeMirage)FROM Ponte1 WHERE (DateDeMirage >=DATEADD(day, DATEDIFF(day,0,DATE()),0) AND DateDeMirage < DATEADD(day, DATEDIFF(day,0,DATE())+1,0)) ) as Num2, ( SELECT Sum(IIf(EnPonte Is Null ,0,EnPonte)) from ponte1 ) as Sum1, ( Select Sum(IIf(Couvant Is Null,0,Couvant)) from ponte1 )as Sum2, ( Select Sum(IIf(Eclos Is Null ,0,Eclos))FROM Ponte1 )as Sum3" cmd = New OleDbCommand(sql) cmd.Connection = con rdr = cmd.ExecuteReader() If rdr.Read Then Dim Num3 As Integer = rdr.Item("Num3") Dim sum1 As Double = rdr.Item("sum1") Dim sum2 As Double = rdr.Item("sum2") Dim sum3 As Double = rdr.Item("sum3") Dim Num1 As Integer = rdr.Item("Num1") Dim Num2 As Integer = rdr.Item("Num2") con = New OleDbConnection(cs) con.Open() Dim cb2 As String = "Update DbJournal Set NombredeCouples=@d2,CouplesEnPonte=@d3,

    CouplesCouvant=@d4,CouplesEnEclosion=@d5,CagesaMirés=@d6,

    CagesEclos=@d7 Where Pontes=@d1"

    cmd = New OleDbCommand(cb2) cmd.Parameters.AddWithValue("@d1", "Ponte1") cmd.Parameters.AddWithValue("@d2", Num3) cmd.Parameters.AddWithValue("@d3", sum1) cmd.Parameters.AddWithValue("@d4", sum2) cmd.Parameters.AddWithValue("@d5", sum3) cmd.Parameters.AddWithValue("@d6", Num2) cmd.Parameters.AddWithValue("@d7", Num1) cmd.Connection = con cmd.ExecuteNonQuery() con.Close() If Not rdr Is Nothing Then rdr.Close() End If Exit Sub End If con.Close() Catch ex As Exception MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub

    So Thank you very Much .

    Best Regards.




    • Edited by Bajtitou Thursday, February 28, 2019 6:25 PM
    Thursday, February 28, 2019 9:43 AM

Answers

  • Also get rid of the space.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Bajtitou Thursday, February 28, 2019 8:30 PM
    Thursday, February 28, 2019 7:34 PM
    Moderator

All replies

  • Hello,

    Did you try running the query (assuming the first one) in MS-Access?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, February 28, 2019 10:33 AM
    Moderator
  • Hello,

    Did you try running the query (assuming the first one) in MS-Access?

    Hi,  

    The query can't run in Ms-Access, Error With 'Date' or 'GetDate' Functions

    But This One Run Fine Using Sql Mode.

    SELECT (SELECT COUNT( Cage) FROM Ponte1
      ) AS Num3,
      (
       SELECT COUNT( Datedéclosion) FROM Ponte1 WHERE ( Datedéclosion >=DATEADD(day, DATEDIFF(day,0,GETDATE()),0) 
       AND Datedéclosion < DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0))
     ) AS Num1, 
     (
        SELECT Count( DatedeMirage)FROM Ponte1 WHERE (DateDeMirage >=DATEADD(day, DATEDIFF(day,0,GETDATE()),0) 
        AND DateDeMirage < DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0)) 
      ) as Num2,
      (
       SELECT Sum(isnull(EnPonte,0)) from ponte1
      ) as Sum1,
      (
       Select Sum(isnull(Couvant,0)) from ponte1
       )as Sum2,
       (
        Select Sum(isnull(Eclos,0))FROM Ponte1
        )as Sum3
    So Thank Very much for You answer .


    • Edited by Bajtitou Thursday, February 28, 2019 6:26 PM
    Thursday, February 28, 2019 1:31 PM
  • Hello,

    First thing than is GETDATE() is not available with MS-Access, use DATE() function instead.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, February 28, 2019 1:39 PM
    Moderator
  • Hello,

    First thing than is GETDATE() is not available with MS-Access, use DATE() function instead.

    Hi,

    Trying only this piece of query I get missing operator.

    SELECT COUNT([ Datedéclosion]) FROM Ponte1  WHERE ([Datedéclosion] >=DATEADD(day, DATEDIFF(day,0,DATE()),0)
      AND ([Datedéclosion] < (DATEADD(day, DATEDIFF(day,0,DATE())+1,0)) AS Num1

    Thank you.


    • Edited by Bajtitou Thursday, February 28, 2019 6:26 PM
    Thursday, February 28, 2019 6:13 PM
  • Hello,

    First thing than is GETDATE() is not available with MS-Access, use DATE() function instead.

    Hi,

    Trying only this piece of query I get missing operator.

    SELECT COUNT([ Datedéclosion]) FROM Ponte1  WHERE ([Datedéclosion] >=DATEADD(day, DATEDIFF(day,0,DATE()),0)
      AND ([Datedéclosion] < (DATEADD(day, DATEDIFF(day,0,DATE())+1,0)) AS Num1

    Thank you.


    Perhaps

    SELECT 
    	COUNT([ Datedéclosion]) AS Num1
    	FROM Ponte1  
    	WHERE 
    		[Datedéclosion] >= DATEADD(day, DATEDIFF(day,0,DATE()),0) AND 
    		([Datedéclosion] < (DATEADD(day, DATEDIFF(day,0,DATE())+1,0))) 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, February 28, 2019 6:52 PM
    Moderator

  • Perhaps

    SELECT 
    	COUNT([ Datedéclosion]) AS Num1
    	FROM Ponte1  
    	WHERE 
    		[Datedéclosion] >= DATEADD(day, DATEDIFF(day,0,DATE()),0) AND 
    		([Datedéclosion] < (DATEADD(day, DATEDIFF(day,0,DATE())+1,0))) 


        Hi,

    Access displays the Enter Parameter Value dialog box.

    Thank you Very much.

    Thursday, February 28, 2019 7:12 PM
  • This may be happening because of the brackets being used, remove all of these [ and ] and try again.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, February 28, 2019 7:33 PM
    Moderator
  • Also get rid of the space.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Bajtitou Thursday, February 28, 2019 8:30 PM
    Thursday, February 28, 2019 7:34 PM
    Moderator
  • Also get rid of the space.


    Hi,

    Finally This work very well.

    SELECT COUNT([Datedéclosion]) AS Num1 FROM Ponte1 WHERE [Datedéclosion] >= DATEADD("d", DATEDIFF("d",0,DATE()),0)

    AND([Datedéclosion] < (DATEADD("d", DATEDIFF("d",0,DATE())+1,0)))

    The error was here : this synthax  "day" is wrong

    Thank you for your assistances ,

    hope if it will work with the whole code block up.

    Thanks.


    • Edited by Bajtitou Thursday, February 28, 2019 8:45 PM
    Thursday, February 28, 2019 8:39 PM