Page 1 of 1

Inserting data into a MS Access DB

Posted: Sun Feb 08, 2004 3:47 am
by bianster
Hi all,

i'm having some problems running a SQL insert for an Access DB. I echoed the SQL string to the screen:

INSERT INTO member values ('bianster@aserver.com','72bKD5BpMVi4k','Doug',12345678,12345678,false,false)

and it checks out alrite but I keep getting:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect

Code: Select all

<?php

$emailAddr = $HTTP_POST_VARS&#1111;emailAddr];
$password = $HTTP_POST_VARS&#1111;password];
$name = $HTTP_POST_VARS&#1111;name];
$contact = $HTTP_POST_VARS&#1111;contact];
$altContact = $HTTP_POST_VARS&#1111;altContact];
$vehno = $HTTP_POST_VARS&#1111;vehno];
$vehmodel = $HTTP_POST_VARS&#1111;vehmodel];

settype($contact, "double");
settype($altContact, "double");
$status = add_member($emailAddr, $password, $name, $contact, $altContact, $vehno, $vehmodel);

function add_member($emailAddr, $password, $name, $contact, $altContact, $vehno, $vehmodel)&#123;
	require 'dbconfig.dat';
	$status_code = 2;
	$approved = false;
	$admin = false;
	
	$salt = substr($password, 0, 2);
	$encrypted_password = crypt($password, $salt);
	$db_conn = odbc_connect($db, $db_server, $db_user, $db_password) or die ("Could not connect to server...");
	$member_insert = "INSERT INTO member values (";
	$member_insert .= "'".$emailAddr."',";
	$member_insert .= "'".$encrypted_password."',";
	$member_insert .= "'".$name."',";
	$member_insert .= $contact . ",";
	$member_insert .= $altContact . ",";
	$member_insert .= "false,";
	$member_insert .= "false)";
	print $member_insert;
	$member_add_result = odbc_exec($db_conn, $member_insert) or die ("Unable to execute query for the MEMBER table...");
	odbc_close($db_conn);
	
	return $status_code;
&#125;
?>
halp!! i can't figure out why this is happening...from the error message, something's wrong with the datatypes but I have already cast 2 variables into double integers....

Posted: Sun Feb 08, 2004 3:56 am
by microthick
In your access database, are you SURE that the fields are listed in that same order you are inserting? If not, then the sql command may fail.

You could always try the more common insert method, in case this is the problem.

INSERT INTO tbl_table (field1, field2, field3) VALUES (value1, value2, value3)

Posted: Sun Feb 08, 2004 4:16 am
by bianster
no dice, I tried that method before and the same thing happened...

Posted: Sun Feb 08, 2004 10:43 am
by microthick
Specifically, what type of fields are the last two?

Posted: Mon Feb 09, 2004 4:36 am
by bianster
in Access, they're yes/no fields formatted as true/false....

Posted: Tue Feb 10, 2004 7:31 am
by bianster
i think i know where the problem is occuring now...I'm trying to insert boolean data into a BIT column in the table. But is there a way to convert/cast the boolean into a BIT value??

the functions i found return strings...etc decbin()...