I have made an application that automatically mails some files. The problem now is, that I have the emailaddresses in another database, (sdf-file).
I want to search for the value of "filenaam" which can be something like "q1869". That variable has to be searched in the database (automail.sdf). And when ther is something with the "q1869" in it. Then get the value of that row, in the second field, which is in my case "email".
Now I tried a lot of things. But I stumbled upon a problem right now.
When I run this code, I get an "Object reference not set to an instance of an object." error at string emailaddress = getemail.ExecuteScalar().ToString();
System.Windows.Forms.Timer mytimer = new System.Windows.Forms.Timer();
//Automail mail = new Automail();
public Form1()
{
InitializeComponent();
//timer aanmaken, interval instellen en aanzetten + gegevens in datatable laden
mytimer.Tick +=mytimer_Tick;
mytimer.Interval = 10000;
Methods methods = new Methods();
Populate();
}
public OdbcConnection con = new OdbcConnection("Driver={iSeries Access ODBC Driver};uid=AYISHB;system=NLPROD;dbq=MMASHB MMASHB;dftpkglib=QGPL;languageid=ENU;pkg=QGPL/DEFAULT(IBM),2,0,1,0,512;qrystglmt=-1;signon=1;trace=2");
public iDB2Connection conn = new iDB2Connection("DataSource=NLPROD;UserID=AYISHB;Password=AYI;DataCompression=True;Default Collection=mmashb;");
public SqlCeConnection dbcon = new SqlCeConnection(@"Data Source=E:\Users\Ali\Documents\automail.sdf");
SmtpClient SmtpServer = new SmtpClient("smtp.dsv.com"); // smtp client maken
int aantalmails = 0;
public DataTable dt = new DataTable();
///////////////////////////methodes//////////////////////////
//Vul dataGridView1 methode
public void Populate()
{
dt.Clear();
OdbcCommand cm = new OdbcCommand("SELECT * FROM SELECTIE ORDER BY OMSCH",con); //querycommand om gegevens te openen
OdbcDataAdapter da = new OdbcDataAdapter();
da.SelectCommand = cm; // data adapter command aanmaken
da.Fill(dt); // datatable vullen met de data
}
public void sendMail()
{
while (true)
{
Populate();
if (dt.Rows.Count >= 1)
{
for (int x = dt.Rows.Count - 1; x >= 0; --x)
{
int i = x; //i is aantal rijen
int r = 1;
string query = dt.Rows[i][r].ToString();// zet de querynummer om in een string
string filenaam = query.Trim(); // haal de overtollige spaties uit de naam en filenaam is de querynmr
SqlCeCommand getemail = new SqlCeCommand("SELECT email" + " FROM Emails" + " WHERE (query LIKE @querynaam)" , dbcon);
getemail.Parameters.AddWithValue("@querynaam", filenaam);
string emailaddress = getemail.ExecuteScalar().ToString();
MessageBox.Show(emailaddress);
//try
//{
//}
//catch (Exception ex)
//{
// MessageBox.Show(ex.Message);
//}
System.Diagnostics.Process.Start(@"M:\dtf\" + filenaam + ".dtf"); // start de dtf bestand met de zelfde querynaam op de M schijf
Thread.Sleep(15000); // wacht 15 seconden
try
{
MailMessage mail = new MailMessage();//
mail.From = new MailAddress("[email protected]");
mail.To.Add(emailaddress.ToString());
mail.Subject = "Report " + filenaam + " DSV REPORT";
mail.Body = "Dear Customer,\nthis message is an automated mail sent by an unattended server. \nThe attachment included in this mail is: " + filenaam + "\nPlease do not reply to this email \n \nThis mail has been sent on " + string.Format("{0:HH:mm:ss:tt yyyy-MM-dd}", DateTime.Now);
System.Net.Mail.Attachment attachment;
attachment = new System.Net.Mail.Attachment(@"M:/" + filenaam + ".xls");
mail.Attachments.Add(attachment);
SmtpServer.Credentials = new System.Net.NetworkCredential("[email protected]", "Uran1234");
SmtpServer.Send(mail);
mail.Dispose();
Thread.Sleep(10000);
string sourcefile = @"M:\" + filenaam + ".xls";
string destinationfile = @"M:\verzondenreports\" + filenaam + ".xls";
if (System.IO.File.Exists(@"M:\verzondenreports\" + filenaam + ".xls"))
{
System.IO.File.Delete(@"M:\verzondenreports\" + filenaam + ".xls");
System.IO.File.Move(sourcefile, destinationfile);
}
else
{
System.IO.File.Move(sourcefile, destinationfile);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
Thread.Sleep(2000);
try
{
iDB2DataAdapter data = new iDB2DataAdapter("SELECT FROM SELECTIE ORDER BY OMSCH", conn);
data.DeleteCommand = new iDB2Command("DELETE FROM SELECTIE WHERE" + filenaam, conn);
data.DeleteCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
if (dt.Rows.Count > 0)
{
dt.Rows[i].Delete();
}
else
{
return;
}
dt.AcceptChanges();
filenaam = "";
}
dt.Clear();
}
else
{
dt.Clear();
Populate();
}
}
}
///////////////////////////methodes//////////////////////////
//wanneer form word opgestart
private void Form1_Shown(object sender, EventArgs e)
{
try
{
//open connecties
con.Open();
conn.Open();
dbcon.Open();
//vul de tabel met de gegevens
Populate();
}
catch(iDB2Exception ex)
{
// als connectie niet lukt weergeef foutmelding
MessageBox.Show(ex.Message);
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
con.Close();
conn.Close();
}
private void button1_Click(object sender, EventArgs e)
{
label2.Text = ("Running...");
mytimer.Enabled = true;
Thread t = GetT();
t.Start();
//Populate();
//if (dataGridView1.Rows.Count > 1)
//{
// int count = dataGridView1.Rows.Count;
// for (int x = 0; x <= dataGridView1.Rows.Count; x++)
// {
// int i = 0;
// int r = 1;
// string number = dataGridView1.Rows[i].Cells[r].Value.ToString();
// string filenaam = number.Trim();
// textBox1.Text = filenaam;
// System.Diagnostics.Process.Start(@"M:\dtf\" + filenaam + ".dtf");
// i = i++;
// Thread.Sleep(100);
// }
// //OdbcCommand cm = new OdbcCommand("DELETE FROM SELECTIE WHERE *", con);
// //OdbcDataAdapter da = new OdbcDataAdapter();
// //da.DeleteCommand = cm;
// iDB2DataAdapter data = new iDB2DataAdapter("SELECT FROM SELECTIE ORDER BY OMSCH", conn);
// conn.Open();
// data.DeleteCommand = new iDB2Command("DELETE FROM SELECTIE", conn);
// data.DeleteCommand.ExecuteNonQuery();
//}
//else
//{
// Populate();
//}
//conn.Close();
//Populate();
}
private Thread GetT()
{
Thread t = new Thread(new ThreadStart(sendMail));
return t;
}
void mytimer_Tick(object sender, EventArgs e)
{
//sendMail();
}
private void btnStop_Click(object sender, EventArgs e)
{
Thread t = GetT();
t.Abort();
label2.Text = ("Stopped...");
mytimer.Enabled = false;
}
private void label2_Click(object sender, EventArgs e)
{
}
private void Form1_Load(object sender, EventArgs e)
{
}
getemail.ExecuteScalar() will return null if there are no results, in which case getemail.ExecuteScalar().ToString() will throw a null reference exception.
You will need to check, something like this:
var result = getemail.ExecuteScalar();
if (result != null)
{
string emailaddress = result.ToString();
// Etc