Problem using command.parameters

G

Guest

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();
 
N

Nicholas Paldino [.NET/C# MVP]

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.
 
G

Guest

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?



Nicholas Paldino said:
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.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Johnny said:
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();
Click to expand...
Click to expand...
 
Top