Solved

Unclosed quotation mark before the character string

Posted on 2006-07-17
53
1,270 Views
Last Modified: 2007-12-19
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
Comment
Question by:SirReadAlot
  • 34
  • 19
53 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 17121284
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
 

Author Comment

by:SirReadAlot
ID: 17121287
will do
0
 
LVL 23

Expert Comment

by:apresto
ID: 17121288
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
 

Author Comment

by:SirReadAlot
ID: 17121301
same error still

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

Author Comment

by:SirReadAlot
ID: 17121308
did u do this on purpose
<wbr/>.
0
 
LVL 23

Expert Comment

by:apresto
ID: 17121344
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
 

Author Comment

by:SirReadAlot
ID: 17121351
what about this

<wbr/>.
0
 
LVL 23

Expert Comment

by:apresto
ID: 17121374
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
 
LVL 23

Expert Comment

by:apresto
ID: 17121376
>>>what about this

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

Expert Comment

by:apresto
ID: 17121381
Open it in the browser and it wont be there :o)
0
 

Author Comment

by:SirReadAlot
ID: 17121422
i am using internet exploper
0
 

Author Comment

by:SirReadAlot
ID: 17121469
     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
 
LVL 23

Expert Comment

by:apresto
ID: 17121476
does this work? If not try simply specifying the sheet name
0
 

Author Comment

by:SirReadAlot
ID: 17121490
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
 

Author Comment

by:SirReadAlot
ID: 17121495
the name of the spread sheet is sheet1
0
 

Author Comment

by:SirReadAlot
ID: 17121508
i will originaly following this

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

Author Comment

by:SirReadAlot
ID: 17121548
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
 
LVL 23

Expert Comment

by:apresto
ID: 17121567
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
 
LVL 23

Expert Comment

by:apresto
ID: 17121573
>>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
 

Author Comment

by:SirReadAlot
ID: 17121587
wil try again thanks
0
 

Author Comment

by:SirReadAlot
ID: 17121611
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
 

Author Comment

by:SirReadAlot
ID: 17121619
i don't see what is incorrect
0
 

Author Comment

by:SirReadAlot
ID: 17121629
am just gonna move the qoutes around
0
 

Author Comment

by:SirReadAlot
ID: 17121645
no luck
0
 
LVL 23

Expert Comment

by:apresto
ID: 17121656
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
 

Author Comment

by:SirReadAlot
ID: 17121666
will try it
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

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


wow!!! this is just typical
0
 

Author Comment

by:SirReadAlot
ID: 17121700
everything is closed
0
 

Author Comment

by:SirReadAlot
ID: 17121748
     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
 

Author Comment

by:SirReadAlot
ID: 17121753
     
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
 
LVL 23

Expert Comment

by:apresto
ID: 17121756
"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
 

Author Comment

by:SirReadAlot
ID: 17121767
will c
0
 
LVL 23

Expert Comment

by:apresto
ID: 17121768
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
 

Author Comment

by:SirReadAlot
ID: 17121791
sorry, how did u test it in query analyser?
0
 

Author Comment

by:SirReadAlot
ID: 17121857
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
 
LVL 23

Expert Comment

by:apresto
ID: 17121888
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
 

Author Comment

by:SirReadAlot
ID: 17121902
will try
0
 

Author Comment

by:SirReadAlot
ID: 17121928
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
 

Author Comment

by:SirReadAlot
ID: 17121941
sorry,

this is the error

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

Expert Comment

by:apresto
ID: 17122002
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
 

Author Comment

by:SirReadAlot
ID: 17122059
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
 
LVL 23

Expert Comment

by:apresto
ID: 17122083
>>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
 

Author Comment

by:SirReadAlot
ID: 17122097
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
 

Author Comment

by:SirReadAlot
ID: 17122107
hold on apresto
0
 

Author Comment

by:SirReadAlot
ID: 17122116


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
 
LVL 23

Expert Comment

by:apresto
ID: 17122132
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
 
LVL 23

Expert Comment

by:apresto
ID: 17122136
sorry, didnt refresh, ignore that comment

0
 
LVL 23

Accepted Solution

by:
apresto earned 500 total points
ID: 17122144
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
 

Author Comment

by:SirReadAlot
ID: 17122153
will try
0
 

Author Comment

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

Author Comment

by:SirReadAlot
ID: 17122318

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
 
LVL 23

Expert Comment

by:apresto
ID: 17122347
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
 

Author Comment

by:SirReadAlot
ID: 17122357
me too, i shall search
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now