Reusing SQL statements

  • Thread starter Thread starter jfbaro
  • Start date Start date
J

jfbaro

Hi,

I am new to C# and SQLite and I am having problems using
Paramaters.Add...

I prepare the "INSERT" statement and inside a loop I set the values for
each parameter and call ExecuteNonQuery();
Something similar to that:

checkCmd.CommandText = "SELECT userId FROM users WHERE
userId = @userId";

IDbDataParameter userIdPar =
checkCmd.CreateParameter();

userIdPar.ParameterName = "@userId";

userIdPar.DbType = DbType.String;
checkCmd.Parameters.Add(userIdPar);

updateCmd = conn.CreateCommand();
updateCmd.CommandText = "UPDATE users SET userName =
@userName, userPin = @userPin WHERE userId = @userId";
IDbDataParameter userIdUp =
updateCmd.CreateParameter();
userIdUp.ParameterName = "@userId";
userIdUp.DbType = DbType.Int32;
IDbDataParameter userNameUp =
updateCmd.CreateParameter();
userNameUp.ParameterName = "@userName";
userNameUp.DbType = DbType.String;
IDbDataParameter userPinUp =
updateCmd.CreateParameter();
userPinUp.ParameterName = "@userPin";
userPinUp.DbType = DbType.String;
updateCmd.Parameters.Add(userIdUp);
updateCmd.Parameters.Add(userNameUp);
updateCmd.Parameters.Add(userPinUp);


insertCmd = conn.CreateCommand();
insertCmd.CommandText = "INSERT INTO users (userId,
userName, userPin) VALUES (@userId, @userName, @userPin)";
IDbDataParameter userIdIn =
insertCmd.CreateParameter();
userIdIn.ParameterName = "@userId";
userIdIn.DbType = DbType.Int32;
IDbDataParameter userNameIn =
insertCmd.CreateParameter();
userNameIn.ParameterName = "@userName";
userNameIn.DbType = DbType.String;
IDbDataParameter userPinIn =
insertCmd.CreateParameter();
userPinIn.ParameterName = "@userPin";
userPinIn.DbType = DbType.String;
insertCmd.Parameters.Add(userIdIn);
insertCmd.Parameters.Add(userNameIn);
insertCmd.Parameters.Add(userPinIn);


while (objBinaryReaderRx.BaseStream.Position <
PacketSize)
{

int userId = objBinaryReaderRx.ReadInt32();
string userName = objBinaryReaderRx.ReadString();
string userPIN = objBinaryReaderRx.ReadString();

userIdPar.Value = userId;

readerCheck = checkCmd.ExecuteReader();

if (readerCheck.Read())
{
userIdUp.Value = userId;
userNameUp.Value = userName;
userPinUp.Value = userPIN;

updateCmd.ExecuteNonQuery();
}
else
{
userIdIn.Value = userId;
userNameIn.Value = userName;
userPinIn.Value = userPIN;

insertCmd.ExecuteNonQuery();

}

readerCheck.Close();
}


transaction.Commit();

return true;

catch{...}
finally{...}

The first "INSERT" works fine, but the second time I get: "Operation is
not valid due to the current state of the object."

I am using .NET 1.1, Finisar 1.1(as Database Driver) and SQLite 3

Thanks in advance
 
Back
Top