Update table using check null value from data reader

Question

My two columne email and status on tableemail i had use a data reader for count mail address and send mail:

>  OleDbCommand cmd = null;
>             OleDbCommand cmd2 = null;
>             
>             string queryString = "select email,status from tableemail";
>             using (OleDbConnection connection = new OleDbConnection("Provider = OraOLEDB.Oracle.1; Data Source = xe;
> Password=eppspps;User ID = xpress; unicode=true"))
>             {
>                 OleDbCommand command= new OleDbCommand(queryString, connection);
>                 connection.Open();
>                 cmd = new OleDbCommand(queryString);
>                 cmd.Connection = connection;
>                 OleDbDataReader reader = cmd.ExecuteReader();
>                
>                 while (reader.Read())
>                     
>                 {
>                     MailAddress to = new MailAddress(reader[0].ToString());
>                     message.To.Add(to);     
>                 }

this code is work good, but
i need update status data to tablemail after check null value on data reader

> if (!reader.IsDBNull(1))
>                 {
>                     cmd2 = new OleDbCommand(" UPDATE tableemail set status=1 where status is null", connection);
>                     cmd2.ExecuteNonQuery();
>                 }

Unrelated tip: the command and reader are both IDisposable so each should be in a `using` block. — Richardissimo, Oct 28 '18 at 10:21
You haven't explained the problem, but I suspect that you need to have completed the first command before you do others. Just keep a list of the things you want to update and do it after the command finishes. — Richardissimo, Oct 28 '18 at 10:24
On re-reading, your update does not use the value from the reader, so it's easier: you don't need to keep track of anything. Just put in the `using` blocks that I mentioned, and put `cmd2` after the `using` block for `cmd`. Failing that, please [edit] the question to clarify what the problem is. — Richardissimo, Oct 28 '18 at 10:48
You already have a `using` block for the connection - what do you need me to explain? — Richardissimo, Oct 28 '18 at 20:57

D-Shih · Accepted Answer · 2018-10-28T12:02:35.643

I think you can try to only use cmd to do select and update, and remove if If(!reader.IsDBNull(1)) and move update code to outer loop.

OleDbCommand cmd = null;

string queryString = "select email,status from tableemail";
using (OleDbConnection connection = new OleDbConnection("Provider = OraOLEDB.Oracle.1; Data Source = xe;
 Password=eppspps;User ID = xpress; unicode=true"))
{
 OleDbCommand command= new OleDbCommand(queryString, connection);
 connection.Open();
 cmd = new OleDbCommand(queryString);
 cmd.Connection = connection;
 OleDbDataReader reader = cmd.ExecuteReader();

 while (reader.Read()) 
 {
     MailAddress to = new MailAddress(reader[0].ToString());
     message.To.Add(to);     
 }
  cmd.CommandText = " UPDATE tableemail set status=1 where status is null";
  cmd.ExecuteNonQuery();
}

EDIT

if you want to use update in the while-loop you can try to use another OleDbCommand and execute update in if

string queryString = "select email,status from tableemail";
string updateString = "UPDATE tableemail set status=1 where email = @email";
using (OleDbConnection connection = new OleDbConnection("Provider = OraOLEDB.Oracle.1; Data Source = xe;
 Password=eppspps;User ID = xpress; unicode=true"))
{
 connection.Open();
 OleDbCommand cmd = new OleDbCommand(queryString,Connection);
 OleDbCommand cmd2 = new OleDbCommand(updateString,Connection);
 OleDbDataReader reader = cmd.ExecuteReader();

 while (reader.Read()) 
 {
     MailAddress to = new MailAddress(reader[0].ToString());
     message.To.Add(to);     
     if(!reader.IsDBNull(1)){
         cmd2.Parameters.Clear();
         cmd2.Parameters.Add(new OleDbParameter("email", reader["email"]));
         cmd2.ExecuteNonQuery();
     }
  }
}

it possible check with data reader values during update processs?? — brids, Oct 28 '18 at 11:36
Yes but is there any column is `PK` you might update data by PK. — D-Shih, Oct 28 '18 at 11:38
You just need to use a column for `update` and make sure this data won't duplicate. If your e-mail isn't duplicate in your table you can use it. — D-Shih, Oct 28 '18 at 11:49
actually update required for send auto mail when insert new information under the email address, i want to be update status which mail all ready sent i am sending mail using data reader and i want to be update data reader values — brids, Oct 28 '18 at 12:03

Update table using check null value from data reader

1 Answers1