I am using PHP to read and write to an Access database via ODBC. I am succefully managing to pull data from the database but my user registration script will not work at all. I won't post the script because I don't think it would help the situation.

Basically, when the script has attempted to write the details to the database I am getting this error:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in C:\Program Files\Abyss Web Server\htdocs\insert_user.php on line 34

I'm not sure how to go about fixing it. I've made sure that all of the fields in my database are Text to allow them to accept the majority of inputs. The only thing that I can think of causing this is the auto-increment 'ID', the password field, the drop down menu for credit card type.

Cheers,
Bailz.

    I think its better to post this peace of code where you insert the data into the database

      Ok, I've got 2 peices of code. 1 that contains the form that the user fills in which then passes the data to the next form which inserts it into the database.

      Script 1:
      <form method="post" action="insert_user.php">
      <table border="0" width="45%">
      <tr>
      <td>
      <input type="hidden" name="ID">
      </td>
      <td>
      &nbsp
      </td>
      </tr>
      <tr>
      <td align="right">
      <font>Username:
      </td>
      <td>
      <input type="text" name="Username">
      </td>
      </tr>
      <tr>
      <td align="right">
      <font>Password:
      </td>
      <td>
      <input type="password" name="Password">
      </td>
      </tr>
      <tr>
      <td align="right">
      <font>Address:
      </td>
      <td>
      <input type="text" name="Address">
      </td>
      </tr>
      <tr>
      <td align="right">
      <font>Credit Card Number:
      </td>
      <td>
      <input type="text" name="Credit_Card_Number">
      </td>
      </tr>
      <tr>
      <td align="right">

      	<font>Credit Card Type:
	</td>
	<td>
		<select name="Credit_Card_Type">
			<option value="Mastercard">Mastercard
			<option value="Switch">Switch
		</select>
	</td>
</tr>
<tr>
	<td align="right">
		<font>Expiry Date:
	</td>
	<td>
		<input type="text" name="Expiry_Date" value="dd/mm/yy">
	</td>
</tr>
<tr>
	<td align="right">
		<font>Name on Card:
	</td>
	<td>
		<input type="text" name="Name_on_Card">
	</td>
</tr>
<tr>
	<td colspan="2">
		<center><input type="Submit" name="submit" value="Register!">&nbsp&nbsp<input type="reset" value="Reset">
	</td>
</tr>

      </table>
      </form>

      And script 2:

      
<h1>Register User Results</h1> 
<?php 
  // create short variable names 
  $id=$_POST['ID'];
  $user=$_POST['Username']; 
  $pass=$_POST['Password']; 
  $address=$_POST['Address']; 
  $credit=$_POST['Credit_Card_Number'];
  $type=$_POST['Credit_Card_Type'];
  $expiry=$_POST['Expiry_Date'];
  $name=$_POST['Name_on_Card'];

  @ $connection = odbc_connect('localhost', '', ''); 

  if (!$connection) 
  { 
     echo 'Error: Could not connect to database.  Please try again later.'; 
     exit; 
  } 

  $query = "INSERT INTO User VALUES ('$id' , '$user', '$pass', '$address', '$credit', '$type', '$expiry', '$name')";  

  $result = odbc_exec($connection, $query); 

  if ($result) 

  echo  ("Thank you for registering!"); 

?>

      Cheers,
      Bailz.

        try this :

        $id=intval($_POST['ID']);

        $query = "INSERT INTO User VALUES ($id , '$user', '$pass', '$address', '$credit', '$type', '$expiry', '$name')";

          Thanks that's got rid of the error. Now it's just putting in '0' for the ID instead of 1, 2, 3, 4 etc ...

          So whenever I try to add another new record it's complaining about data duplication, it's a relational database I'm using.

          Cheers,
          Bailz.

            Looks like ID is not passed correctly via the POST method, or is always set to 0.

            Either fix that, or try using NULL to force the auto-increment.

              What would be the reason for ID not being passed? Seen as though everything else is moving on perfectly!

              Cheers,
              Bailz.

                my mistake.

                It is being passed, but isnt set to anything.
                So my interpretation is that it defaults to 0.
                Since you dont plan to set it to anything, but rather use the auto-increment, try something like:

                ... VALUES (NULL , '$user' ...

                  Alright, I've put in the NULL but now it's returning:

                  Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] You tried to assign the Null value to a variable that is not a Variant data type., SQL state 23000 in SQLExecDirect in C:\Program Files\Abyss Web Server\htdocs\insert_user.php on line 34

                  Cheers,
                  Bailz.

                    Hallo,

                    maybo you should describe your table design a little bit more...

                    You must know that sql is a standard lang and also Access didn't work like each sql-server.

                      Write a Reply...