http://www.perlmonks.org?node_id=678961

srikantvm has asked for the wisdom of the Perl Monks concerning the following question:

use strict; use Win32::OLE; use Win32::OLE::Const 'Microsoft ActiveX Data Objects'; # Change these three variables my $database = "TestAccess.mdb"; my $table = "Clients"; my $field1 = "CompanyName"; my $field2 = "ClientID"; my $field3 = "Phone"; my $Conn = Win32::OLE->new("ADODB.Connection"); my $RS = Win32::OLE->new("ADODB.Recordset"); my $DSN = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$database;UID=;PWD=;"; $Conn->Open($DSN); my $SQL = "SELECT $field1, $field2, $field3 FROM $table"; $RS->Open($SQL, $Conn, 1, 1); until ($RS->EOF) { my $value1 = $RS->Fields($field1)->value; my $value2 = $RS->Fields($field2)->value; my $value3 = $RS->Fields($field3)->value; print $value1,"\t",$value2,"\t",$value3,"\n"; $RS->MoveNext; } $RS->Close; $Conn->Close;

Hi All,

Please go through the above code, If i execute the same code with tables and columns having whitespace characters in their names. I am getting the following error (Can't call method "value" on an undefined value). Please help.... Kindly suggest me the solution for this particular scenario.

Thanks & Regards,
Srikant

Replies are listed 'Best First'.
Re: error returned in Win32::OLE module with mdb file's tables and cloumns having whitespace characters in their names.
by strat (Canon) on Apr 09, 2008 at 07:53 UTC

    Try [-braces around the column names, e.g

    my $string = "SELECT [col 1], [col 2], col3 ...";

    and then use "col 1" as hash keys, e.g

    my $value1 = $RS->Fields("col 1")->value;

    Best regards,
    perl -e "s>>*F>e=>y)\*martinF)stronat)=>print,print v8.8.8.32.11.32"

Re: error returned in Win32::OLE module with mdb file's tables and cloumns having whitespace characters in their names.
by apl (Monsignor) on Apr 08, 2008 at 11:36 UTC
    Please bracket your code with <code> and </code>. If your post didn't look readable to you when you Previewed it, you can imagine what it looks like to the rest of us...
Re: error returned in Win32::OLE module with mdb file's tables and cloumns having whitespace characters in their names.
by thundergnat (Deacon) on Apr 08, 2008 at 15:38 UTC

    Please enclose code in <c> ... </c> brackets. It makes it much easier for others to help you.

    As I am not working on a Windows system, I can't test this, but I believe you need to enclose the variable names in double quotes. You'll need to escape them so they get passed through in the SQL statement though.

    my $SQL = "SELECT \"$field1\", \"$field2\", \"$field3\" FROM \"$table\ +"";

    Or such.

    You might be better off using DBIx to handle your SQL so you can use placeholders and avoid the whole headache of trying to figure out how and when to quote the fields.