Hi Friends!
It is obvious that connecting to a database using Visual Studio .NET (C# or VB) requires two methods:
1. Design-Time
2. Runtime Objects.
In this case, we would stick to the Runtime Objects using a Data Command and an OleDbConnection.
{
string connString="Provider=Microsoft.ACE.OleDB.12.0;Data Source=C:\\myFolder\\sampledb.accdb";
public OleDbConnection dbConn = new OledbConnection(connString);
try
{
dbConn.Open();
}
Catch (OleDbException e)
{
// errors goes here
}
}
Here is my point, you may get your update or insert queries not working exactly as planned even after checking through your codes. Simply bend the rule. An example is provided below:
{
string cmdString="UPDATE table SET col1=@val1,col2=@val2 WHERE id=@id";
OleDbCommand oleCmd = new OleDbCommand();
oleCmd.Connection =dbConn;
oleCmd.CommandType =CommandType.Text;
oleCmd.CommandText =cmdString;
oleCmd.Parameters.Add("@val1",OleDbType.Char).Value=Textbox1.Text;
oleCmd.Parameters.Add("@val2",OleDbType.Char).Value=Textbox2.Text;
oleCmd.Parameters.Add("@id",OleDbType.Char).Value=id.Text;
try
{
int i=oleCmd.ExecuteNonQuery();
if (int i==0)
{
//failed update statement
MessageBox.Show("Failed Statement");
}
}
finally
{
oleCmd.Dispose()
dbConn.Close()
}
}
First, I would advice you add command parameters in the same order you use them in your command string so as to avoid inserting data into the wrong field. I also find that the WHERE clause does not work when you do not add that field as the last parameter in your command.
An alternative to the above rule is to use the traditional method, say your update failed with the message "Failed Statement" as coded.
string cmdString="UPDATE table SET col1=@val1,col2=@val2 WHERE id=" + refID
...
oleCmd.Parameters.Add("@val1",OleDbType.Char).Value=Textbox1.Text;
oleCmd.Parameters.Add("@val2",OleDbType.Char).Value=Textbox2.Text;
Now I do not use a parameter for the WHERE clause, rather I use a variable which must have been initiated from another event or method.
I find this approached very helpful, especially after debugging my code and I don't seem to discover the error on time.
Sometimes your programm is unable to interpret the value of your WHERE parameter @ID due to data type inconsistency with that defined in your database or program just being funny.
It's better to deliver a working application than to be late.
Hearty Regards,
AdeLeke
