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

x
?
Solved

Object reference not set to an instance of an object.

Posted on 2006-07-14
13
Medium Priority
?
358 Views
Last Modified: 2010-04-16
Since we do not know the amount of columns coming in from our clients
we have decided to create the destination table on the fly.

I have excel save on my pc but I keep getting the above error


and it  highlighted this bit

return sSQL.Replace("'", "''");

Has anyone trird to do this before?

thanks



=================================full code==============
private void Button1_Click(object sender, System.EventArgs e)
            {
            FromExcel();      
            }

            void FromExcel()
            {
                  con.Close();
                  con.Open();
                  //                  conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Server.MapPath("\\AutoDataCapture_2\\Files\\") + Path.GetFileName(postedFile.FileName) +";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();
            }
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
  • 8
  • 5
13 Comments
 
LVL 28

Expert Comment

by:2266180
ID: 17107004
that is probably because sSQL is null. you can check that by putting a break point on that line and see in the watch if it is indeed null. but I don't see that line in the code you posted
0
 

Author Comment

by:SirReadAlot
ID: 17107007
okay i will check the sSql
0
 

Author Comment

by:SirReadAlot
ID: 17107019
yes it does return null,

what esle can i do
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.

 
LVL 28

Expert Comment

by:2266180
ID: 17107027
well, I don't know what you were trying to do. post your code and maybe I can understand that.

I can think of 2 cases:
1) you forgot to initializae it
2) you are using another string and made a confusion with it.
0
 

Author Comment

by:SirReadAlot
ID: 17107046


it is case 2,

but that reurns null


public class WebForm2 : System.Web.UI.Page
      {
            protected System.Web.UI.WebControls.Button Button1;
            SqlConnection con = new SqlConnection("server=10.217.1.85;User ID=sa;password=oarsjove;Database=ADC11");
            string  strg;
            private void Page_Load(object sender, System.EventArgs e)
            {
                  // Put user code to initialize the page here
            }

            

            private string parseStr(string sSQL)
            {
                  return sSQL.Insert(0, "\\\\ukwatdb20\\wwwroot$\\AutoDataCapture\\Files1\\" );
                  //return sSQL.Insert(0, "c:\\code\\" );
            }
            private string parseSQL (string sSQL)
            {
                  return sSQL.Replace("'", "''");
            }
            private string parse_SQL (string sSQL)
            {
                  return sSQL.Replace("-", "");
            }
            private string parse_hash (string sSQL)
            {
                  return sSQL.Replace("#", ".");
            }
      


            #region Web Form Designer generated code
            override protected void OnInit(EventArgs e)
            {
                  //
                  // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                  //
                  InitializeComponent();
                  base.OnInit(e);
            }
            
            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InitializeComponent()
            {    
                  this.Button1.Click += new System.EventHandler(this.Button1_Click);
                  this.Load += new System.EventHandler(this.Page_Load);

            }
            #endregion

            private void Button1_Click(object sender, System.EventArgs e)
            {
            FromExcel();      
            }

            void FromExcel()
            {
                  con.Close();
                  con.Open();
                  //                  conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Server.MapPath("\\AutoDataCapture_2\\Files\\") + Path.GetFileName(postedFile.FileName) +";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 += parseStr(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 28

Expert Comment

by:2266180
ID: 17107054
from the code you have given the parseSQL method is not being called. so there is no way you can get an error on that line
0
 

Author Comment

by:SirReadAlot
ID: 17107075
     private string parseStr(string sSQL)
            {
                  return sSQL.Insert(0, "\\\\ukwatdb20\\wwwroot$\\AutoDataCapture\\Files1\\" );
                  //return sSQL.Insert(0, "c:\\code\\" );
            }

returns null
0
 

Author Comment

by:SirReadAlot
ID: 17107079
I have to create abrowse  button
0
 
LVL 28

Expert Comment

by:2266180
ID: 17107104
instead of
return sSQL.Insert(0, "\\\\ukwatdb20\\wwwroot$\\AutoDataCapture\\Files1\\" );
use
return "\\\\ukwatdb20\\wwwroot$\\AutoDataCapture\\Files1\\"+sSQL;
0
 

Author Comment

by:SirReadAlot
ID: 17107131
will try this
0
 

Author Comment

by:SirReadAlot
ID: 17107185
actually cily

I only gave you a subset of the code not the full version.

the actual problem is that this path

string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Driver={Microsoft Excel Driver (*.xls)};DBQ=";
does not let us save any excel file with any format

but this code below allows so to save with any format b\c it has the
Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");      
incorporated in it
            
      

conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Server.MapPath("\\AutoDataCapture_2\\Files\\") + Path.GetFileName(postedFile.FileName) +";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");      

                                      
                  
0
 
LVL 28

Accepted Solution

by:
2266180 earned 2000 total points
ID: 17107209
I am sorry, but that seems to be another question.
object reference not set to an instance of an object means you got a null pointer exception whcih has nothing to do with the fact that using one string you can do something and usin another string you cannot do the same thing. whichi by the way is absolutly normal since the 2 strings are absolutly differrent (nothing is similar except they both deal with excel)
since you are having another issue here, you should probably open another question.
0
 

Author Comment

by:SirReadAlot
ID: 17107281
yeah
0

Featured Post

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!

Question has a verified solution.

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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

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