data type mismatch in criteria expression

Question

i have a data table like that
 
FIELD NAME                   DATATYPE
Member No                        text  
Nic_number                        text
First_name                         text
Last_name                         text
Middle_intial                       text
Birthdate                           date/time
Adress                              text
City                                  text
Province                            text
Postalcode                         number
Pno                                  number
email                                 text
Exp_date                           date/time
 
this one of the table of my database made in access know if i want to delete a record i get the error which is type mismatch in criteria expression when i use this to delete this record
 
public void onDelete(OleDbDataReader reader, ListView vListview,string sTable)
{
ListView.SelectedListViewItemCollection col = vListview.SelectedItems;
IEnumerator colEnum = col.GetEnumerator();
colEnum.MoveNext();
ListViewItem item = (ListViewItem)colEnum.Current;
ListViewItem.ListViewSubItemCollection subItemCol = item.SubItems;
IEnumerator subEnum=subItemCol.GetEnumerator();
StringEnumerator stringEnum = strCol.GetEnumerator();
StringBuilder builder = new StringBuilder();
builder.Append("DELETE FROM ");
builder.Append( sTable );
builder.Append(" WHERE ");
reader.Read();
bool bFirst = true;
int ncolumn = 0;
string strtype;
while (subEnum.MoveNext() == true && stringEnum.MoveNext() == true)
{
ListViewItem.ListViewSubItem subItem = (ListViewItem.ListViewSubItem)subEnum.Current;
strtype = reader.GetDataTypeName(ncolumn++);
if (subItem.Text != "")
{
if (bFirst == false)
builder.Append(" AND");
else
bFirst = false;
builder.Append(" [");
builder.Append(stringEnum.Current.ToString());
if (strtype == "DBTYPE_14")
builder.Append("] = ");
else
builder.Append("]= '");
builder.Append(subItem.Text);
if (strtype != "DBTYPE_14")
builder.Append("'");
}
}

string deleteStr;
deleteStr=builder.ToString();
dbAccess.dbOpen();
dbAccess.DeleteCommand=deleteStr;
display(dbAccess.GetReader,vListview);
}

in this code strCol is collection of string which i define like this
 
 
StringCollection strCol = new StringCollection();

Answer 1

Unfortunately going through all your code without the data to back it is too complicated.  The problem probably lies in your generated query.  Please post the final value of deleteStr after you've built up the command and we can probably identify the problem.

 

Michael Taylor - 10/15/07

http://p3net.mvps.org

 

 

Answer 2

hi Mr tylor u want my final delete string which is given below

 

DELETE FROM Pinformation

WHERE  [Member_no] = 'A3'

AND [Nic_number] = '42101-4518342-2'

AND [First_name] = 'Sunan'

AND [Last_name] = 'Hashmi'

AND [Middle initial] = 'Miss'

AND [Birth_date] = '11/11/1982 12:00:00 AM'

AND [Adress] = 'A-143' AND [city] = 'karachi'

AND [province] = 'Sindh'

AND [Postal code] = 63400

AND [P-no] = 6314586

AND [email] = '[e-mail]'

AND [Exp_date] = '8/6/2008 12:00:00 AM'

 

i cant understand where is the problem due this i cant update my record when i m going to update my records i got the same error

 

Answer 3

Your column names in your query do not match the schema you originally posted. 

 

Middle_Initial => Middle Initial

PostalCode => Postal Code

PNo => P-no

 

Additionally be aware that you generally shouldn't delete based upon on this criteria.  Presumably the member # is unique.  If so then use that in your query rather than all the column values.  It is faster and cleaner.

 

Michael Taylor - 10/16/07

http://p3net.mvps.org

 

 

Answer 4

know i change all of field but my problem exceed some body told me that i got problem in date type how would i change it to string but the solution u provide didn't work

Answer 5

So once you fixed the column names the problem persists?  The problem probably lies in your DT fields.  SQL will auto-convert from string to DT.  Access will as well.  It might not like the format of your DT value.  Then again your PNo might be too big for the number field that you're storing it in.

 

The easiest solution is to comment out the AND clauses for the DTs and see if it works.  If it does then the DT columns are causing the problem.  If not then you can start commenting out other clauses until you find the problem.  Again, though, it is easier to just compare against the primary key for the table (member #, I assume) and use it instead.  No reason to do more work then needed.  It would also probably eliminate your problem altogether.

 

Michael Taylor - 10/16/07

http://p3net.mvps.org

 

 

Answer 6

Duplicate post. Please refer follow-up responses to the following:

 

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2264811&SiteID=1

 

 

 

Answer 7

This was helpful.  I passed DateTime.Now as my value to a datetime field in Access and got the same error.  Passing DateTime.Now.toShortDateString worked fine.  I don't have the time of the event, but that is not very important.  Thanks for the info.

John

---

Alpha Geek