Problem using command.parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Johnny - 23 Jul 2007 02:26 GMT

I need to get data from a sql server database and insert records into a sqlce
database if they do not already exist in the sqlce database. I have written
the code below but I'm getting an error message when the cmd.Prepare() line
is executed. The error message is - "The conversion is not supported. [ Type
to convert from (if known) = int, Type to convert to (if known) =
uniqueidentifier ]"}

I have compared the ordinal position of each parameter to the SqlDbType and
they all match. I don't have any idea why I'm getting the error message or
how to debug it at this point. Any help would be much appreciated.

cmd.CommandText =
"Insert VMI_CUST (CUST_ID, SHIP2_ID, CUST_NO,
QUICK_SCAN, ALLOW_DUPLICATE_SCANS, SIGNATURE_REQUIRED, " +
"VALIDATE_MANDATORY_ITEMS, PO_TYPE, CUST_PO,
LAST_CHANGE_DATE, RELEASE_NO, LAST_SYNC_DATE, COMPANY_ID) " +
"Select " +
"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
"'" + Global.CompanyID + "' " +
"FROM vmi_controls " +
"WHERE vmi_controls.company_id = '" +
Global.CompanyID + "'"; //+
" AND NOT EXISTS " +
"(SELECT cust_id FROM vmi_cust WHERE
cust_id = '" + drCustomers[x]["CUST_ID"] + "'" +
" and ship2_id =
'" + drCustomers[x]["SHIP2_ID"] + "'" +
" and company_id =
'" + Global.CompanyID + "')";

cmd.Parameters.Clear();

cmd.Parameters.Add(new SqlCeParameter("CUST_ID",
SqlDbType.UniqueIdentifier));
cmd.Parameters.Add(new SqlCeParameter("SHIP2_ID",
SqlDbType.UniqueIdentifier));
cmd.Parameters.Add(new SqlCeParameter("CUST_NO",
SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlCeParameter("QUICK_SCAN",
SqlDbType.NVarChar));
cmd.Parameters.Add(new
SqlCeParameter("ALLOW_DUPLICATE_SCANS", SqlDbType.NVarChar));
cmd.Parameters.Add(new
SqlCeParameter("SIGNATURE_REQUIRED", SqlDbType.NVarChar));
cmd.Parameters.Add(new
SqlCeParameter("VALIDATE_MANDATORY_ITEMS", SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlCeParameter("PO_TYPE",
SqlDbType.NVarChar));
cmd.Parameters.Add(new SqlCeParameter("CUST_PO",
SqlDbType.NVarChar));
cmd.Parameters.Add(new
SqlCeParameter("LAST_CHANGE_DATE", SqlDbType.DateTime));
cmd.Parameters.Add(new SqlCeParameter("RELEASE_NO",
SqlDbType.NVarChar));
cmd.Parameters.Add(new
SqlCeParameter("LAST_SYNC_DATE", SqlDbType.DateTime));

cmd.Prepare();

Reply to this Message

Nicholas Paldino [.NET/C# MVP] - 23 Jul 2007 02:34 GMT

Johnny,

Assuming the order is correct, I would look at what the following is
returning:

drCustomers[x]["SHIP2_ID"]

As you are just appending it in the string, when in reality, you really
should be parameterizing that as well.

Signature

- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

>I need to get data from a sql server database and insert records into a
>sqlce
[quoted text clipped - 62 lines]
>
> cmd.Prepare();

Reply to this Message

Johnny - 23 Jul 2007 02:56 GMT

I changed the command text to the following and it works great but I'm not
getting the results I need because I'm not filtering out values that already
exist like the orginal statement does. The thing I don't get is that I'm
using the exact same order of things with the select statement as with the
statement that uses the insert into with values parameter. I just don't get
it????

cmd.CommandText =
"Insert into VMI_CUST (CUST_ID, SHIP2_ID,
CUST_NO, QUICK_SCAN, ALLOW_DUPLICATE_SCANS, SIGNATURE_REQUIRED, " +
"VALIDATE_MANDATORY_ITEMS, PO_TYPE, CUST_PO,
LAST_CHANGE_DATE, RELEASE_NO, LAST_SYNC_DATE, COMPANY_ID) " +
"Values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '"
+ Global.CompanyID + "')";

By the way, why do I need to have the drCustomers[x]["SHIP2_ID"] parametized
when it's part of the where clause?

> Johnny,
>
[quoted text clipped - 72 lines]
> >
> > cmd.Prepare();

Reply to this Message

Problem using command.parameters

Free Magazines