Solved

Object reference not set to an instance of an object.

Posted on 2006-07-14
13
336 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: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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Loop not working 29 53
Reset asp.net login password 4 42
converto datetime always changes format 9 35
how to check to see if datatable has headers or not 2 26
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…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

806 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