B
Bajtitou
Guest
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 Ponte2
) AS Num3,
(
SELECT COUNT( Datedéclosion) FROM Ponte2 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 Ponte2 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 ponte2
) as Sum1,
(
Select Sum(IIf(Couvant Is Null,0,Couvant)) from ponte2
)as Sum2,
(
Select Sum(IIf(Eclos Is Null ,0,Eclos))FROM Ponte2
)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", "Ponte2")
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.
Continue reading...
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 Ponte2
) AS Num3,
(
SELECT COUNT( Datedéclosion) FROM Ponte2 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 Ponte2 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 ponte2
) as Sum1,
(
Select Sum(IIf(Couvant Is Null,0,Couvant)) from ponte2
)as Sum2,
(
Select Sum(IIf(Eclos Is Null ,0,Eclos))FROM Ponte2
)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", "Ponte2")
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.
Continue reading...