Unclosed quotation mark before the character string

Hi SirReadAlot,

try this:

string strr     = "select * into ADC11.dbo.test1 FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB<wbr/>.4.0', 'Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1\'' ' )";

Apresto

will do

i used 2 single quoted, you used double quoted.

In sql text is represented with single quotes, by using double quotes you were breaking the c# code and the code was expecting a variable

same error still

      Label1.Text      "Error:Line 1: Incorrect syntax near ')'.\r\nUnclosed quotation mark before the character string ')'."      string

did u do this on purpose
<wbr/>.

ah i see, its basically because the strin gis being broken, here:

', 'Extended <-- Where the comma is because of the quotes wither side, just trying to work out way around it

what about this

<wbr/>.

actually, should you not be specifying a source table/sheet and or username and password (which could be blank) in the OPENROWSET function:

from Books ONline:
...FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb'; 'admin'; 'mypwd', Orders)

>>>what about this

sorry, i am using quick post and it puts them in sometimes - just ignore it, sorry

Open it in the browser and it wont be there :o)

i am using internet exploper

     string strr  = "select * into ADC11.dbo.test1 FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Extended Properties=\''Excel 8.0;HDR=Yes;IMEX=1\'','SELECT * FROM [Sheet1$] ' )";

does this work? If not try simply specifying the sheet name

it doesnt work,

can u check to see if i got the quotation right?

      string strr  = "select * into ADC11.dbo.test1 FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Extended Properties=\''Excel 8.0;HDR=Yes;IMEX=1\'','SELECT * FROM [Sheet1$] ' )";


thanks

the name of the spread sheet is sheet1

i will originaly following this

http://hknetug.com/forums/thread/329.aspx

with this
                        string strr  = "select * into ADC11.dbo.test1 FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Extended Properties=\''Excel 8.0;HDR=Yes;IMEX=1\'','SELECT * FROM [Sheet1$] ' )";

i get incorrect syntax the KEYWORD SELECT

'SELECT * FROM [Sheet1$] ' )";

thats because the string is broken again:

 string strr  = "select * into ADC11.dbo.test1 FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB<wbr/>.4.0', 'Extended Properties=\''Excel 8.0;HDR=Yes;IMEX=1\'' ','SELECT * FROM [Sheet1$] ' )";

>>sorry, posted the <wbr/> again:

 string strr  = "select * into ADC11.dbo.test1 FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Extended Properties=\''Excel 8.0;HDR=Yes;IMEX=1\'' ','SELECT * FROM [Sheet1$] ' )";

wil try again thanks

i have tried that, it seems to be geting better, it say
+      Label1      {Text=@"Error:Line 1: Incorrect syntax near '\'."}      System.Web.UI.WebControls.Label

i don't see what is incorrect

am just gonna move the qoutes around

no luck

ok, remove the / from the string, they are no longer necessary as you ar enot using double quotes:

string strr  = "select * into ADC11.dbo.test1 FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1'' ','SELECT * FROM [Sheet1$] ' )";

will try it

     Ex.Message      "Line 1: Incorrect syntax near '\\'.\r\nUnclosed quotation mark before the character string ')'."      string


wow!!! this is just typical

everything is closed

     void FromExcel(string strg)
            {
                  con.Close();
                  con.Open();

      
            
             string strr  = "select * into ADC11.dbo.test1 FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Extended Properties=' 'Excel 8.0;HDR=Yes;IMEX=1')";




           //  string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};DBQ=";
            strr += parseSQL(strg);
             strr += "','SELECT * FROM [Sheet1$]')";
                  SqlCommand SqlCmd= new SqlCommand(strr,con);
                  SqlCmd.ExecuteNonQuery();
                  //Importing the generic Excel file in to Master SQL Database
                  con.Close();
            }


i have even made some adjustments but same error

     
which is

Label1.Text      "Error:Line 1: Incorrect syntax near 'Excel 8.0;HDR=Yes;IMEX=1'.\r\nUnclosed quotation mark before the character string ')'."      string

"select * into ADC11.dbo.test1 FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1'' ','SELECT * FROM [Sheet1$] ' )";

the above works for me...

what if you run it in query analyser?

will c

copy and paste the above EXACTLY

when you have '' they need to be together, this represents a single quote in sql, this ' ' means nothing! they need to be side by side ''

sorry, how did u test it in query analyser?

this works
string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};DBQ=";

but I just want to add 'Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1''

so that i can import any data format

if you add it in:

this wont work:

'Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1''

you need to close the quote at the end:

'Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1'' '  <--

will try

this is what I am currently trying
        string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1'' ' ,'Driver={Microsoft Excel Driver (*.xls)};DBQ=";
 
will let you know

sorry,

this is the error

{Text="Error:Line 1: Incorrect syntax near ','."}

ok, let me just take a moment to expain

In C# when you want to represent a string you use double quotes:

" something "

in sql you need to use single quotes ', if you want to pass a single quote within an sql string you would do this by using 2 single quotes 'my mother''s maiden name is...'

in the above string you are not closing your SQL string properly:

string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1'' ' ,'Driver={Microsoft Excel Driver (*.xls)};DBQ="; <-- THERE SHOULD BE ' HERE

string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1'' ' ,'Driver={Microsoft Excel Driver (*.xls)};DBQ=' ";  LIKE THIS

now... what do you need to append to the string?

lets forget the quotes and double quotes, remove them and post how it is that the WHOLE string should look like, as if you wee typing it into sql manually...
 
 

Hi

i don't want to append any thing to the string.

I am trying to insert what ever data type is in my excel to the database,  right now it works with this code

string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};DBQ=";

but there are other datatype in the excel so i have to use imex

void FromExcel(string strg)
            {
                  con.Close();
                  con.Open();

string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1'' ' ,'Driver={Microsoft Excel Driver (*.xls)};DBQ=' ";
 

//          string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};DBQ=";
          strr += parseSQL(strg);
           strr += "','SELECT * FROM [Sheet1$]')";
                  SqlCommand SqlCmd= new SqlCommand(strr,con);
                  SqlCmd.ExecuteNonQuery();
                  //Importing the generic Excel file in to Master SQL Database
                  con.Close();
            }
                        

>>but I just want to add 'Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1''

i thought you said you needed to add something on:

ok, so what line is it erroring on, is it still this one:

{Text="Error:Line 1: Incorrect syntax near ','."}

okay

lets forget the quotes and double quotes, remove them and post how it is that the WHOLE string should look like, as if you wee typing it into sql manually...
 

SELECT * INTO db1.dbo.table1 FROM OPENROWSET(MSDASQL,Driver={Microsoft Excel Driver (*.xls)};DBQ=, SELECT * FROM [sheet1$])

hold on apresto

lets forget the quotes and double quotes, remove them and post how it is that the WHOLE string should look like, as if you wee typing it into sql manually...




SELECT * INTO db1.dbo.table1 FROM OPENROWSET(MSDASQL, Extended Properties= Excel 8.0;HDR=Yes;IMEX=1, Driver={Microsoft Excel Driver (*.xls)};DBQ=, SELECT * FROM [sheet1$])

string strr = "SELECT * INTO db1.dbo.table1 FROM OPENROWSET('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};DBQ=', [sheet1$])"

ok, does this work, i know its hasnt got a select at the end but try it out:

http://www.codecomments.com/archive352-2005-9-626923.html

sorry, didnt refresh, ignore that comment

will try

same error
Error:Line 1: Incorrect syntax near ','. Unclosed quotation mark before the character string ')'.

with this code

  string strr  = "select * into ADC11.dbo.test1 FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Extended Properties=''Excel 8.0;HDR=Yes;IMEX=1'' ','SELECT * FROM [Sheet1$] ' )";
      

i got this, looks good, its a diff error!!!!!!!
Error:Could not locate registry entry for OLE DB provider 'Provider=Microsoft.Jet.OLEDB.4.0'. OLE DB error trace [Non-interface error: Provider not registered.].

hhuummm....not sure.

atleast we got past the old one - you might not have the driver installed. I'll try and find out what the error means and how to fix it

me too, i shall search