0

I've been trying to update my data according to the user session (UserLogin) but it kept saying: Data type mismatch in criteria expression. The print_r is just for testing purposes.

Thanks in advance,

Z

function Employee1_BeforeShow(& $sender)
{
    $Employee1_BeforeShow = true;
    $Component = & $sender;
    $Container = & CCGetParentContainer($sender);
    global $Employee1; //Compatibility


$Page = CCGetParentPage($sender);

$db = $Page->Connections["PettyCashMDB"];

$sql1 = "UPDATE Employee SET Employee.LastActive = Date() WHERE Employee.[EmpID] = ". $_SESSION['UserLogin'];
$db->query($sql1);

print_r($_SESSION['UserLogin']);



$db->close();
Employee1_BeforeShow @67-67106FAD
return $Employee1_BeforeShow;
}

EDIT: I've tried @NanaPartykar 's method and by accident I've noticed that it does get the value from $_SESSION['UserLogin'], just that somehow the datatype is different.

EDIT: It displays the error Data type mismatch but both of them are string and returns string.

Z. Zeff
  • 1
  • 2
  • is UserLogin type same as your datatype of EmpID field in DB? – apomene May 27 '16 at 09:40
  • check your where clause in your sql query, type are differents – B.Kevin May 27 '16 at 09:40
  • i think one is a string and the other is a int, try to convert the $_SESSION['UserLogin'] – B.Kevin May 27 '16 at 09:41
  • I think problem is here `Employee.[EmpID]`. Instead use `Employee.EmpID` or use ` (backtick to enclose column name) – Nana Partykar May 27 '16 at 09:43
  • both EmpID & UserLogin returns string, EmpID contains alphabets and UserLogin gets the value from EmpID on login, so both should have the same type and value, I'm having problem catching $_SESSION @apomene B.Kevin – Z. Zeff May 28 '16 at 13:14
  • @NanaPartykar, 3 of those have no difference in CodeCharge, all 3 are accepted, thanks. – Z. Zeff May 30 '16 at 00:36

4 Answers4

0

Instead of Employee.[EmpID], use Employee.EmpID

Nana Partykar
  • 10,556
  • 10
  • 48
  • 77
jophab
  • 5,356
  • 14
  • 41
  • 60
0

You need some quotes:

$sql1 = "UPDATE Employee SET Employee.LastActive = Date() WHERE Employee.[EmpID] = \'". $_SESSION['UserLogin'] . "\'";
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Thanks for the reply, it displays `Parse error: syntax error, unexpected '"'"' (T_CONSTANT_ENCAPSED_STRING)`. @Gustav – Z. Zeff May 30 '16 at 00:32
  • Sorry, you have to escape those single quotes. See edit. – Gustav May 30 '16 at 06:05
  • What does "not working" mean? What is the string of `$sql1`? – Gustav May 30 '16 at 09:00
  • I mean that adding \ to the code doesn't work, not sure if this is what you're asking for `UPDATE Employee SET Employee.LastActive = Date() WHERE Employee.EmpID = ABC123 ` This is the output of `echo $sql1` – Z. Zeff May 31 '16 at 00:36
  • The result should be: `UPDATE Employee SET Employee.LastActive = Date() WHERE Employee.EmpID = 'ABC123'` so you need to add `\'`not just the backslash "\". – Gustav May 31 '16 at 16:01
0

Z - There are a bunch of built-in Codecharge functions to assist with getting values from querystring, sessions and controls.

eg: CCGetSession("UserLogin", "default");

http://docs.codecharge.com/studio50/html/index.html?http://docs.codecharge.com/studio50/html/Components/Functions/PHP/Overview.html

and executing SQL with some validating (from 'Execute Custom SQL' help topic):

$db = new clsDBConnection1();
$SQL = "INSERT INTO report (report_task_id,report_creator) ". 
     "VALUES (". $db->ToSQL(CCGetFromGet("task_id",0),ccsInteger) .",". $db->ToSQL(CCGetUserID(),ccsInteger) .")";

$db->query($SQL);
$db->close();

The $db->ToSQL (and CCToSQL) functions convert and add quotes for relevant data types (ccsText, ccsDate).

There are many examples in the Manual under 'Examples' and 'Programming Reference' for PHP (and ASP, .NET, etc)

http://support.codecharge.com/tutorials.asp

I strongly suggest looking at some of the examples, as Codecharge will handle a lot of the 'plumbing' and adding a lot of custom code will causing problems with the generation of code. In your example, you should add a 'Custom Code' action to the Record's 'Before Show' Event and add your code there. If you add code just anywhere, the entire section of code (eg: Before Show) will change colour and no longer be updated if you change something.

For example, if you manually edited the 'Update' function to change a default value, then no changes through the IDE/Properties will change the 'Update' function (such as adding a new field to the Record).

eratech
  • 81
  • 4
  • thanks for the reply, I'm currently using the before show, custom code as you mentioned, but the problem is not that it can't get the value, but the datatype, this is the error that keeps popping up `SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect ` And for some reason I'm unable to access the codecharge website **page not working** – Z. Zeff May 30 '16 at 08:45
0

Finally got it to work, this is the code $sql1 = "UPDATE Employee SET LastActive = Date() WHERE EmpID = '$_SESSION[UserLogin]' "; Thanks to everyone that helped out.

Z. Zeff
  • 1
  • 2