I don't understand why this exception crops up All of the fields in the database are of type text and when I load the Dataset, they all load fine. However, when I try to Edit and click on the Save button, I get this message (shortened due to space) when the method attempts to execute the updtCmnd.ExecuteNonQuery statement:
Error trying to edit current record:
(Note: I have displayed all of the fields (13 total) here)
Error: Data type mismatch in criteria expression.
Error Data: System.Collections.ListDictionaryInternal
Error Code: -2147217913
Errors: System.Data.OleDbErrorCollection
Source: Microsoft JET Database Engine
TargetSite: Int32 Execute CommandTextForSingleResult(System.Data.OleDb.tagDBPARAMS, System.Object.ByRef)
Any suggestions (Note: The Exception message is verbose because I'm still testing this app.)
private void buttonModify_Click(object sender, System.EventArgs e)
{
string strEditConnect = "";
if ( buttonModify.Text == "&Edit" )
{
textboxHoldFirstName.Location = new Point( 92, 10 );
textboxHoldLastName.Location = new Point( 200, 10 );
textboxHoldFirstName.Focus();
strAction = "update";
SetTextboxSequence( strAction );
this.userControlAppStatus1.labelCurrentStatus.Text = "Updating . . .";
buttonModify.Text = "&Save";
ResetButtons ( false, false, true, true, false );
}
else
{
VerifyFields();
try
{
strEditConnect = strPhnBkConnect + strDbPath + strDbSecurity;
string strAddrId = this.labelRowId.Text;
string strEditCommand = "UPDATE addressbook SET " +
"lastname = , firstname = , " +
"spousename = , " +
"address = , city = , state = , zipcode = , " +
"emailaddress = , " +
"homephone = , cellphone = , " +
"workphone = , workxtension = , " +
"birthdate = " +
"WHERE (((addressID) = '" + strAddrId + "')) ";
using ( OleDbConnection conn = new OleDbConnection ( strEditConnect ) )
{
using ( OleDbCommand updtCmnd = new OleDbCommand ( strEditCommand, conn ) )
{
updtCmnd.Parameters.Add ( new OleDbParameter ( "lastname", this.textboxHoldLastName.Text ) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "firstname", this.textboxHoldFirstName.Text) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "spousename", this.textBoxSpouse.Text) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "address", this.textBoxAddress.Text) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "city", this.textBoxCity.Text) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "state", this.textBoxState.Text ) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "zipcode", this.textBoxZipCode.Text ) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "emailaddress", this.textBoxEmail.Text ) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "homephone", this.textBoxPhone.Text ) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "cellphone", this.textBoxCellPhone.Text ) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "workphone", this.textBoxWorkPhone.Text ) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "workxtension", this.textBoxExtension.Text ) );
updtCmnd.Parameters.Add ( new OleDbParameter ( "birthdate", this.textBoxBirthdate.Text ) );
conn.Open ( );
updtCmnd.ExecuteNonQuery ( );
conn.Close ( );
}
}
ConnectToDatabase ( );
this.userControlAppStatus1.labelCurrentStatus.Text = "Ready . . .";
buttonModify.Text = "&Edit";
ResetButtons ( true, true, true, true, true );
}
catch (OleDbException ox )
{
MessageBox.Show( "Error trying to edit current record: \n" +
"\nLast Name = '" + this.textboxHoldLastName.Text + "', " +
"\nFirst Name = '" + this.textboxHoldFirstName.Text + "', " +
"\nSpouse Name = '" + this.textBoxSpouse.Text + "', " +
"\nAddress = '" + this.textBoxAddress.Text + "', " +
"\nCity = '" + this.textBoxCity.Text + "', " +
"\nState = '" + this.textBoxState.Text + "', " +
"\nZipcode = '" + this.textBoxZipCode.Text + "', " +
"\nEmail Address = '" + this.textBoxEmail.Text + "', " +
"\nHome Phone = '" + this.textBoxPhone.Text + "', " +
"\nCell Phone = '" + this.textBoxCellPhone.Text + "', " +
"\nWork Phone = '" + this.textBoxWorkPhone.Text + "', " +
"\nWork Extension = '" + this.textBoxExtension.Text + "', " +
"\nBirthdate = '" + this.textBoxBirthdate.Text + "', " +
"\n\n+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+" +
"\n\nError: " + ox.Message +
"\n\nError Data: " + ox.Data +
"\n\nErrorCode: " + ox.ErrorCode +
"\nErrors: " + ox.Errors +
"\nSource: " + ox.Source +
"\nTargetSite: " + ox.TargetSite +
"\n\n+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+" +
"\n\nVerbose Error : " + ox.ToString ( ),
"Database Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
} //* end method: buttonModify_Click
OLEDB Row update error
Debbie M
Sheesh, I thought I had put that info in my message.
Database: Access
RowId: AutoNum
All remaining field types are 'Text'
OS: XP Pro
I've read the help on this and after plowing thru that I found that part of the problem lay in the syntax itself; I was surrounding the ID with single quotes.
That out of the way, thank you for the "kick in the rear", after re-reading I saw my error.
But, I see no difference in VarChar and VarWChar
Xiaobo Gu
I wonder why you are creating a new parameter type for each parameter
eg updtCmnd.Parameters.Add ( new OleDbParameter ( "lastname", this.textboxHoldLastName.Text ) );
The VS docs say you should be using:
updtCmnd.Parameters.Add ("@lastname", OleDbType.VarChar, 255).Value = this.textboxHoldLastName.Text;
The parameter name has to have a @ in front of it, and the number is the max length of the varchar ie your textbox text.
May I suggest you look at the VS docs. This may show you where you are going wrong.
Bye the way, to get a better response next time, you can include a table of info such as the OS you are using, the DataBase, etc.
Good Luck.
Ron.