SirReadAlot
asked on
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
========================== =======ful l 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(" \\AutoData Capture_2\ \Files\\") + Path.GetFileName(postedFil e.FileName ) +";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Driv er={Micros oft 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();
}
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
==========================
private void Button1_Click(object sender, System.EventArgs e)
{
FromExcel();
}
void FromExcel()
{
con.Close();
con.Open();
// conn = new OleDbConnection("Provider=
string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Driv
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();
}
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
ASKER
okay i will check the sSql
ASKER
yes it does return null,
what esle can i do
what esle can i do
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.
I can think of 2 cases:
1) you forgot to initializae it
2) you are using another string and made a confusion with it.
ASKER
it is case 2,
but that reurns null
public class WebForm2 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.
SqlConnection con = new SqlConnection("server=10.2
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$\\
//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.B
this.Load += new System.EventHandler(this.P
}
#endregion
private void Button1_Click(object sender, System.EventArgs e)
{
FromExcel();
}
void FromExcel()
{
con.Close();
con.Open();
// conn = new OleDbConnection("Provider=
string strr ="SELECT * INTO ADC11.dbo.test1 FROM OPENROWSET('MSDASQL','Driv
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();
}
}
}
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
ASKER
private string parseStr(string sSQL)
{
return sSQL.Insert(0, "\\\\ukwatdb20\\wwwroot$\\ AutoDataCa pture\\Fil es1\\" );
//return sSQL.Insert(0, "c:\\code\\" );
}
returns null
{
return sSQL.Insert(0, "\\\\ukwatdb20\\wwwroot$\\
//return sSQL.Insert(0, "c:\\code\\" );
}
returns null
ASKER
I have to create abrowse button
instead of
return sSQL.Insert(0, "\\\\ukwatdb20\\wwwroot$\\ AutoDataCa pture\\Fil es1\\" );
use
return "\\\\ukwatdb20\\wwwroot$\\ AutoDataCa pture\\Fil es1\\"+sSQ L;
return sSQL.Insert(0, "\\\\ukwatdb20\\wwwroot$\\
use
return "\\\\ukwatdb20\\wwwroot$\\
ASKER
will try this
ASKER
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','Driv er={Micros oft 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(" \\AutoData Capture_2\ \Files\\") + Path.GetFileName(postedFil e.FileName ) +";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
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','Driv
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=
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yeah