时间戳

首页 > 解决方案 > odbc_exec()：SQL 错误：[Microsoft][ODBC Microsoft Access Driver] 参数太少。预期 1.，SQLExecDirect 中的 SQL 状态 07001

php - odbc_exec()：SQL 错误：[Microsoft][ODBC Microsoft Access Driver] 参数太少。预期 1.，SQLExecDirect 中的 SQL 状态 07001

问题描述

我在使用 MS Access 数据库的 PHP 中遇到了一些问题，当我在 PHP 中运行查询时显示错误

odbc_exec()：SQL 错误：[Microsoft][ODBC Microsoft Access Driver] 参数太少。预期 1.，SQLExecDirect 中的 SQL 状态 07001

但是当我要回显“$query”变量并在 Microsoft Access 中运行语法时运行良好。

这是我的查询：

<?php

// setup database for your microsoft Access
// you can setup password in you microsoft Access

// this is a variable for your connection in odbc
// "zkConnection" is your ODBC Data Source Administrator
$conn = odbc_connect("zkConnection", "", "");

// create condition for testing conecction
if ($conn) {
    // echo "<br>Connection Established</br>";
} else {
    echo "Koneksi Gagal";
}

$from_date = date('d/m/Y', strtotime($_REQUEST['from_date'])) . ' 00:00:00';
$to_date = date('d/m/Y', strtotime($_REQUEST['to_date'])) . ' 23:59:59';

$query = "INSERT INTO CalculateData(USERID, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, datein, timein, dateout, timeout) SELECT USERID, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, Format(CHECKTIME, \"dd/mm/yyyy\") AS datein, FORMAT(MIN(CHECKTIME), 'h:m:s') AS timein, Format(CHECKTIME, \"dd/mm/yyyy\") AS dateout, FORMAT(MAX(CHECKTIME), 'h:m:s') AS timeout
FROM TransactionLog WHERE CHECKTIME BETWEEN #$from_date# AND #$to_date#
GROUP BY USERID, Name, lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, CHECKTIME ";

if ($query) {
    //  echo $query;
}

$letsgo = odbc_exec($conn, $query);


if ($letsgo === false)
{
    die(print_r( odbc_error(), true));
}else{

}

header("location: index.php");


?>

这是我的表字段CalculateData： CalculateData


这是我的表字段 TransactionLog ： TransactionLog

标签： phpdatabasems-access

解决方案

首先，您的 sql 命令必须评估为如下所示：

CHECKTIME BETWEEN #2021/09/28 00:00:00# AND #2021/09/29 23:59:59#

因此，尝试：

$from_date = date('Y/m/d', strtotime($_REQUEST['from_date'])) . ' 00:00:00';
$to_date = date('Y/m/d', strtotime($_REQUEST['to_date'])) . ' 23:59:59';

$query = "INSERT INTO CalculateData (USERID, [Name], lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, datein, timein, dateout, timeout) SELECT USERID, [Name], lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, Format(CHECKTIME, '\#yyyy/mm/dd\#') AS datein, FORMAT(MIN(CHECKTIME), '\#h:m:s\#') AS timein, Format(CHECKTIME, '\#yyyy/mm/dd\#') AS dateout, FORMAT(MAX(CHECKTIME), '\#h:m:s\#') AS timeout
FROM TransactionLog WHERE CHECKTIME BETWEEN #$from_date# AND #$to_date#
GROUP BY USERID, [Name], lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, CHECKTIME";

您也可以尝试这种更简单的方法：

SELECT USERID, [Name], lastname, CardNo, DEPTID, DEPTNAME, SUPDEPTID, DateValue(CHECKTIME) AS datein, TimeValue(MIN(CHECKTIME)) AS timein, DateValue(CHECKTIME) AS dateout, TimeValue(MAX(CHECKTIME)) AS timeout

推荐阅读