Upload Excel files and update mssql db

[help_lucky]

Hi All,

I wish you a Happy new year!!!..

My requirement is to upload an excel file using PHP and to update the data in MSSQL. For which i have designed a screen so that the user can browse 2 input files and update the database. html code for the same

<table  cellspacing="3" cellpadding="3" style="color:#0000b9; background-color:#d7deec; ">
 <tbody>
    <tr>
          <td>
             <b> <label for="babmpath">BA-BM status file:</label> </b>
          </td>
           <td><input type="file" name="babmpath" id="babmpath"  />
         </td>
    </tr>
    <tr>
          <td>
             <b> <label for="eskal">Escalation file:</label> </b></td>
           <td><input type="file" name="eskal" id="eskal"  /></td>
     </tr>
     <tr>
           <td >
              <b><input type="submit" name="update"
                          value="Update Database"
                          onclick="return confirm('Are you sure that you want  to upload to the database?\n\ \nPlease close the excel file that you want to upload to the database.');" /> </b>
          </td>
       </tr>
      </tbody>
  </table>

The logic that i used to upload the files was:-- Once the user browse for these files and press update button. I am saving the path of the files in another 2 hidden input fields. I am passing the path to the stored procedure in MSSQL.

The code in stored procedure:-

ALTER PROCEDURE [dbo].[update_db] 
	-- Add the parameters for the stored procedure here
	@ba_bm_status	VARCHAR(100), 
	@eskal VARCHAR(100),
	@error VARCHAR(MAX) OUTPUT
AS
BEGIN
	
	SET NOCOUNT ON;
	
BEGIN TRANSACTION update_database
SET XACT_ABORT ON


--Importing data using distributed queries
   
SET @error = ''
IF(@ba_bm_status IS NOT NULL)
BEGIN
EXEC('SELECT * INTO temp FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=' + @ba_bm_status + ''',' +
 '''SELECT * FROM [qry_BA_Controlling (Report)$]'')');

Here @ba_bm_status is the path of the excel that i am sending from PHP. qry_BA_Controlling (Report) worksheet name in the excel.

Till now it is working fine. Uploading the data from excel and updating it on the server. (Testing is done on the server itself :-p). Now once the code has been deployed i mean made it online. Whenever the user tries to update the database. It is giving me an error message.

The reason behind it was:- As i am just passing the path instead of uploading the files. The path from where are the files are browsed is on the client side and i am searching for the files in the server side(I mean in MSSQL when i am uploading the files).
For example path of the file:- C:\Documents and Settings\l.varada\Desktop\Files\old\eroom_status.xls

and on execution in MSSQL stored procedure(refer to the code above). It was not able to find the file in this path on the server.

Then i thought for an alternative solution as:- Upload the excel files on to the server and from the server to update the database.

With the little knoweldge i have and after browsing net i have found only method to uplaod file(with input type = file). I am using xampp server and mssql installed with odbc drivers. I have no idea where these files are getting saved on the server.

I would be glad If you can help me further in solving this further or suggest me an alternative solution :-)

Anyways thanks for your patience in reading this...

Regards,
Lakshmi.

[chrishea]

I am not familiar with mssql or its stored procedures but I believe that you need to upload the file to the server first. That means a form and an input statement with the appropriate Enctype"

<form  action=\"$curr_form\" method=post ENCTYPE=\"multipart/form-data\" name=admin>
...
<input type=file enctype=\"multipart/form-data\" name=\"userfile\" >
...
<input type=submit...
</form>

After the form is processed and the file has been uploaded, $userfile (or whatever name you give the file in the form) will be an array that can be accessed either of the ways shown below:

$_FILES["userfile"]["name"] - the name of the uploaded file
$_FILES["userfile"]["type"] - the type of the uploaded file
$_FILES["usefile"]["size"] - the size in bytes of the uploaded file
$_FILES["userfile"]["tmp_name"] - the name of the temporary copy of the file stored on the server
$_FILES["userfile"]["error"] - the error code resulting from the file upload

You can also access the array as $userfile and so forth.

For more info see: http://www.w3schools.com/PHP/php_file_upload.asp

[helpplss]

I have the same requirement, to upload an excel file using PHP and to update the data in MSSQL. i tried your code but i have this error when I excute the sql in MSSQL ,

 Msg 102, Level 15, State 1, Procedure UPLOADSTATUSUPDATE, Line 22
 Incorrect syntax near ';'.

why can it be??
plss reply immediately thanks :)