Solved

Object reference not set to an instance of an object.

Posted on 2006-07-14
13
319 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
  • 8
  • 5
13 Comments
 
LVL 28

Expert Comment

by:ciuly
Comment Utility
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
Comment Utility
okay i will check the sSql
0
 

Author Comment

by:SirReadAlot
Comment Utility
yes it does return null,

what esle can i do
0
 
LVL 28

Expert Comment

by:ciuly
Comment Utility
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
Comment Utility


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:ciuly
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:SirReadAlot
Comment Utility
     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
Comment Utility
I have to create abrowse  button
0
 
LVL 28

Expert Comment

by:ciuly
Comment Utility
instead of
return sSQL.Insert(0, "\\\\ukwatdb20\\wwwroot$\\AutoDataCapture\\Files1\\" );
use
return "\\\\ukwatdb20\\wwwroot$\\AutoDataCapture\\Files1\\"+sSQL;
0
 

Author Comment

by:SirReadAlot
Comment Utility
will try this
0
 

Author Comment

by:SirReadAlot
Comment Utility
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:
ciuly earned 500 total points
Comment Utility
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
Comment Utility
yeah
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
Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 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

15 Experts available now in Live!

Get 1:1 Help Now