Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1312
  • Last Modified:

Unclosed quotation mark before the character string

Hi experts,

Can you see this

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

                                     

      string strr      = "select * into ADC11.dbo.test1 FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0', 'Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\" ')";
      
I have closed everything!!
0
SirReadAlot
Asked:
SirReadAlot
  • 34
  • 19
1 Solution
 
aprestoCommented:
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
0
 
SirReadAlotAuthor Commented:
will do
0
 
aprestoCommented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SirReadAlotAuthor Commented:
same error still

      Label1.Text      "Error:Line 1: Incorrect syntax near ')'.\r\nUnclosed quotation mark before the character string ')'."      string
0
 
SirReadAlotAuthor Commented:
did u do this on purpose
<wbr/>.
0
 
aprestoCommented:
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
0
 
SirReadAlotAuthor Commented:
what about this

<wbr/>.
0
 
aprestoCommented:
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)
0
 
aprestoCommented:
>>>what about this

sorry, i am using quick post and it puts them in sometimes - just ignore it, sorry
0
 
aprestoCommented:
Open it in the browser and it wont be there :o)
0
 
SirReadAlotAuthor Commented:
i am using internet exploper
0
 
SirReadAlotAuthor Commented:
     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$] ' )";
0
 
aprestoCommented:
does this work? If not try simply specifying the sheet name
0
 
SirReadAlotAuthor Commented:
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
0
 
SirReadAlotAuthor Commented:
the name of the spread sheet is sheet1
0
 
SirReadAlotAuthor Commented:
i will originaly following this

http://hknetug.com/forums/thread/329.aspx
0
 
SirReadAlotAuthor Commented:
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$] ' )";
0
 
aprestoCommented:
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$] ' )";
0
 
aprestoCommented:
>>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$] ' )";
0
 
SirReadAlotAuthor Commented:
wil try again thanks
0
 
SirReadAlotAuthor Commented:
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
0
 
SirReadAlotAuthor Commented:
i don't see what is incorrect
0
 
SirReadAlotAuthor Commented:
am just gonna move the qoutes around
0
 
SirReadAlotAuthor Commented:
no luck
0
 
aprestoCommented:
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$] ' )";
0
 
SirReadAlotAuthor Commented:
will try it
0
 
SirReadAlotAuthor Commented:
     Ex.Message      "Line 1: Incorrect syntax near '\\'.\r\nUnclosed quotation mark before the character string ')'."      string


wow!!! this is just typical
0
 
SirReadAlotAuthor Commented:
everything is closed
0
 
SirReadAlotAuthor Commented:
     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
0
 
SirReadAlotAuthor Commented:
     
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
0
 
aprestoCommented:
"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?
0
 
SirReadAlotAuthor Commented:
will c
0
 
aprestoCommented:
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 ''
0
 
SirReadAlotAuthor Commented:
sorry, how did u test it in query analyser?
0
 
SirReadAlotAuthor Commented:
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
0
 
aprestoCommented:
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'' '  <--

0
 
SirReadAlotAuthor Commented:
will try
0
 
SirReadAlotAuthor Commented:
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

0
 
SirReadAlotAuthor Commented:
sorry,

this is the error

{Text="Error:Line 1: Incorrect syntax near ','."}
0
 
aprestoCommented:
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...
 
 
0
 
SirReadAlotAuthor Commented:
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();
            }
                        
0
 
aprestoCommented:
>>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 ','."}
0
 
SirReadAlotAuthor Commented:
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$])
0
 
SirReadAlotAuthor Commented:
hold on apresto
0
 
SirReadAlotAuthor Commented:


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$])
0
 
aprestoCommented:
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
0
 
aprestoCommented:
sorry, didnt refresh, ignore that comment

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

try that
0
 
SirReadAlotAuthor Commented:
will try
0
 
SirReadAlotAuthor Commented:
same error
Error:Line 1: Incorrect syntax near ','. Unclosed quotation mark before the character string ')'.
0
 
SirReadAlotAuthor Commented:

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.].
0
 
aprestoCommented:
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
0
 
SirReadAlotAuthor Commented:
me too, i shall search
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 34
  • 19
Tackle projects and never again get stuck behind a technical roadblock.
Join Now