Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Unclosed quotation mark before the character string

Posted on 2006-07-17
53
Medium Priority
?
1,301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 2000 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

721 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