Object reference not set to an instance of an object.

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();
            }
SirReadAlotAsked:
Who is Participating?
 
2266180Connect With a Mentor Commented:
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
 
2266180Commented:
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
 
SirReadAlotAuthor Commented:
okay i will check the sSql
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
SirReadAlotAuthor Commented:
yes it does return null,

what esle can i do
0
 
2266180Commented:
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
 
SirReadAlotAuthor Commented:


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
 
2266180Commented:
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
 
SirReadAlotAuthor Commented:
     private string parseStr(string sSQL)
            {
                  return sSQL.Insert(0, "\\\\ukwatdb20\\wwwroot$\\AutoDataCapture\\Files1\\" );
                  //return sSQL.Insert(0, "c:\\code\\" );
            }

returns null
0
 
SirReadAlotAuthor Commented:
I have to create abrowse  button
0
 
2266180Commented:
instead of
return sSQL.Insert(0, "\\\\ukwatdb20\\wwwroot$\\AutoDataCapture\\Files1\\" );
use
return "\\\\ukwatdb20\\wwwroot$\\AutoDataCapture\\Files1\\"+sSQL;
0
 
SirReadAlotAuthor Commented:
will try this
0
 
SirReadAlotAuthor Commented:
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
 
SirReadAlotAuthor Commented:
yeah
0
All Courses

From novice to tech pro — start learning today.