Solved

Object reference not set to an instance of an object.

Posted on 2006-07-14
13
328 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.NET HttpWebRequest 12 58
Connecting database in Xamarin? 6 52
Server Error 11 47
Code works but it's slow 24 44
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

18 Experts available now in Live!

Get 1:1 Help Now