INSERT, UPDATE and DELETE

Hello Peter, and thank you for the wonderful class.

I was trying to use the execute() function to Insert a record into an address book database. Here is my syntax:

Server:
=======
Windows 2003 Server
Apache 2.0.45
PHP 4.3.11

DB Table:
====================
|id | AutoNumber |
|fname| Text |
|lname| Text |
| ... | ... |
--------------------

$mdb = new mdb('data.mdb');
$mdb->open();
$mdb->execute("INSERT INTO [address] ([fname],[lname]) VALUES ('earl','johnson')";
$mdb->close();

However I am not able to insert the data into the database. Is there anything else I should know about the execute function? Is that how you usually insert data?

Thanks,
Pete

P.S. I am able to query the database ok. Just not INSERT, UPDATE or DELETE.

Sorry, I never tried this before. From my point of view MDB was only ment to show records, not manipulate them.
But anyway, I found something at a Microsoft page http://www.microsoft.com/windows2000/en/server/iis/htm/asp/iiwadata.htm

When executing a query which does not return a result set (such as insert, update and delete) then there need to be given additional parameters.

Here is an example from this MS page:

...
'Define SQL SELECT statement.
strSQL = "INSERT INTO Customers (FirstName, LastName) VALUES ('Jose','Lugo')"

'Use the Execute method to issue a SQL query to database.
cnn.Execute strSQL,,adCmdText + adExecuteNoRecords
%>

1.) As you see, execute takes two more parameters. One which is NUL and the sum of adCmdText + adExecuteNoRecords

If you know the value of this sum (I do not know this) then maybe a successful call could be:

$mdb->ADODB->execute( $strSQL, '', $sumwhichIdontknow );

But I did not succeed in googling the correct value for $sumwhichIdontknow.

2.) Maybe there is also a possibility to use the prepare() command from the adodb-object before actually executing the query.

3.) Another issue could be, that the service account of the IIS is not allowed to write to the mdb. Look for the security tab in the mdb's properties.

regards,
Peter

Hello Peter,

Your suggestion "almost" worked. Here is what I did to make it work.

1- I renamed your former "execute()" function to "query()".
2- I created a new "execute()" function for INSERT, UPDATE and DELETE statements. Here is the code:

function query( $strSQL )
{
$this->RS = $this->ADODB->execute( $strSQL );
} // eof query()

function execute( $strSQL )
{
$RecordsAffected=new VARIANT();
$this->ADODB->execute($strSQL,&$RecordsAffected ,1);
} // eof execute()

Again, thank you very much for your help. Feel free to add this to your class if you want to allow users to INSERT, UPDATE and DELETE using your class in addition to query.


Pete

This works too for the new execute() function:


function execute( $strSQL )
{
$this->ADODB->execute($strSQL,1);
} // eof execute()



Pete

Thank you very much for the code.
That was a great job.
You will see it soon in the class!

Regards,
Peter

Thank you very much for the code.
This is a great job!
You will soon see it in the class.

regards,
Peter

Thank you very much for this code.
Great job!
I will soon add this to the class.

Regards,
Peter

Hey thats great!
Now both my retries to respond which did not succeed yesterday show up.

Ok.

Seems the forum was locked up for me.

lol..... Glad I could help. :)