Trying the read data from one table then write it to another table

Question

I am try to inset data into another table from the first table. I pass 2 things from the Website to the [WebMethod].

[WebMethod]
public string purchaseInfo(int itemID, string logedInEmail)

When i run this an exception is thrown saying

"Data type mismatch in criteria expression".

 try
       {
           OleDbConnection conn = new OleDbConnection(connString);
           conn.Open();

           OleDbCommand cmd = conn.CreateCommand();
           cmd.CommandText = "SELECT ItemID, itemName, description, price FROM ItemInfo WHERE (ItemID = '" + itemID + "')"; //ItemID is a AutoNumber field.
           OleDbDataReader reader = cmd.ExecuteReader();

           while (reader.Read()) // Reading the data selected
           {
               int currentItemID = (int)reader["ItemID"];                   
               string currentItemName = (string)reader["itemName"];
               string currentdescription = (string)reader["description"];
               string currentPrice = (string)reader["price"];

               cmd.CommandText = @"INSERT INTO PurchaseInfo (itemID, buyerIDEmail, itemName, description, price)
                                 VALUES ('" + currentItemID + "', '" + logedInEmail + "', '" + currentItemName + "', '" + currentdescription + "', '" + currentPrice + "')";
               cmd.ExecuteNonQuery();                   
           }

           conn.Close();
           return "Successful";

       }
       catch (Exception ex)
       {
           return ex.Message;
       }

I have checked if all the data fields are the correct type in the database, and as far as I can tell they are. currentItemID is inserted into Number field. and the rest is all inserted into a Short Text field.

Don't know if it matter but there is also an Auto number field in PurchaseInfo.

Oh no! Do not create SQL statements like that. Guard yourself against [SQL injection](http://stackoverflow.com/questions/4892166/how-does-sqlparameter-prevent-sql-injection) — venerik, Nov 11 '15 at 08:49
Why two steps? Why not create a query that selects from ItemInfo and inserts in purchaseinfo with any extra data as parameters? — Fionnuala, Nov 11 '15 at 09:20
ok figured it out, thanx for the help. Never new i could just do that XD. — Gene Smith, Nov 11 '15 at 09:41

Soner Gönül · Answer 1 · 2015-11-11T09:06:39.860

currentItemID is inserted into Number field.

I think that's the problem since you try to insert it with single quotes as '" + currentItemID + "'. Remove single quotes between them.

Also ItemID = '" + itemID + "' looks suspicious to me. Based on it's name, this should be some numerical values as well. So, you might need to remove these single quotes as well.

But more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

Use using statement to dispose your connection, command and reader automatically instead of calling Close or Dispose methods manually.

using(var connection = new OleDbConnection(connString))
using(var command = connection.CreateCommand())
{
      // Set your CommandText property.
      // Add parameters with `Add` method
      using(var reader = cmd.ExecuteReader())
      {
          while (reader.Read())
          {
               // Get your values.
               // Clear your parameters with `Parameters.Clear()` method.
               // Add new parameters to `cmd`
               // Execute your query.
          }
      }
}

score 0 · Answer 2 · answered Nov 11 '15 at 08:57

Use parameterize queries like so:

try
{
   OleDbConnection conn = new OleDbConnection(connString);
   conn.Open();

   OleDbCommand cmd = conn.CreateCommand();
   cmd.CommandText = "SELECT ItemID, itemName, description, price FROM ItemInfo WHERE (ItemID = @itemId)";
   cmd.Parameters.Add(new OleDbParameter("@itemId", itemId)
   OleDbDataReader reader = cmd.ExecuteReader();

   while (reader.Read()) // Reading the data selected
   {
       int currentItemID = (int)reader["ItemID"];                   
       string currentItemName = (string)reader["itemName"];
       string currentDescription = (string)reader["description"];
       string currentPrice = (string)reader["price"];

       cmd.CommandText = @"INSERT INTO PurchaseInfo (itemID, buyerIDEmail, itemName, description, price)
                         VALUES (@currentItemId, @loggedInEmail, @currentItemName, @currentDescription,@currentPrice)";
       cmd.Parameters.Add(new OleDbParameter("@currentItemId", currentItemId);
       cmd.Parameters.Add(new OleDbParameter("@loggedInEmail", loggedInEmail);
       cmd.Parameters.Add(new OleDbParameter("@currentItemName", currentItemName);
       cmd.Parameters.Add(new OleDbParameter("@currentDescription", currentDescription);
       cmd.Parameters.Add(new OleDbParameter("@currentPrice", currentPrice);
       cmd.ExecuteNonQuery();                   
   }

   conn.Close();
   return "Successful";

}
catch (Exception ex)
{
    return ex.Message;
}

I tried changing it to that but it still throws the Exception — Gene Smith, Nov 11 '15 at 09:07
ok wait got it to not throw that exception. but it now says, "There is already an open DataReader associated with this Command which must be closed first." — Gene Smith, Nov 11 '15 at 09:11

Trying the read data from one table then write it to another table

2 Answers2