Fixed query issue

[Former Member]

Hi,

I have a query to Insert Bom as below

INSERT INTO BOMS

(

OrderID,

Phase,

PositionNumber,

MaterialNumber,

MaterialShortText,

Batch,

ReservationNumber,

ReservationItem,

Quantity,

UOM

) VALUES (

[Param.1],

'[Param.2],

'[Param.3]',

'[Param.4]',

'[Param.5]',

'[Param.6]',

'[Param.7]',

'[Param.8]',

'[Param.9]',

[Param.10]

)

When I execute it, I am getting error as incorrect syntax near ','

I have a confusionusing single quotes around the Param, can someone help me to solve this and make me understand when to use quotes and when not.

Thanks,

Jai

[Former Member]

Hi Jai,

If you are using string datatype like varchar,nvarchar then you should use quotes.

with numbers it is not required.

but even if you will use quotes with numbers and date, sql will convert it to its assigned type.

Try this query

INSERT INTO BOMS
(
OrderID,
Phase,
PositionNumber,
MaterialNumber,
MaterialShortText,
Batch,
ReservationNumber,
ReservationItem,
Quantity,
UOM
) VALUES (
'[Param.1]',
'[Param.2]',
'[Param.3]',
'[Param.4]',
'[Param.5]',
'[Param.6]',
'[Param.7]',
'[Param.8]',
'[Param.9]',
'[Param.10]'
)

Edited by: Anshul Sahu on Jul 14, 2011 10:47 AM

[Former Member]

Hi Anshul,

I get now the error saying " Error converting data type varchar to numeric". I have TYPE_NAME defined as below

ID-int identity

OrderID - bigint

positionnumber- varchar

materialnumber - varchar

materialshorttext - varchar

batch - varhar

reservationnumber - varchar

reservationitem - varchar

Quantity - decimal

UOM - varchar

phase - varchar

"ID" is a primary key

Thanks,

Jai

Edited by: jaimalhotra on Jul 14, 2011 10:59 AM

[Former Member]

Please chcek values of quantity and orderid.

I guess there is something wrong with these values

Thanks

Anshul

[agentry_src]

Jai,

Please remove the single quotes around the fields which are numeric. Anshul was just trying to illustrate the syntax and did not mean that you should do all the Param.X with the single quotes.

Regards,

Mike

[Former Member]

Hi Mike,

I even tried it, according to my data types I removed single quotes for Order ID and Quantity but no luck.

Thanks,

Jai

[Former Member]

Hi All,

If OrderID is an Identity, and correct me if I am wrong, you should not need to include a parameter for it, as it is automatically generated.

Kind Regards,

Diana Hoppe

[Former Member]

Hello Diana,

I defined Order ID as bigint and ID as Identity ( Primary key ). I am have not included ID in the fixed query.

Thanks,

Jai

[Former Member]

Hi Jai,

Sorry I did not read your post carefully enough! Using MSSQL 2008, I created a table with a column of type bigint and decimal, and an ID column that is the identity. I had no problem inserting into MII using

[Param.X]

(unquoted).

Have you verified against the database that the datatypes are as you indicated? I assume you are using Command mode inside of MII. On the Parameters screen, are you using any quotes there (as these would be unnecessary)?

The only datatype I can see might be a little tricky is the decimal type - is it possible to try changing the Quantity to another type for experimental purposes?

- Diana

Not sure how to escape the brackets so they show correctly!

Edited by: Diana Hoppe on Jul 14, 2011 10:01 AM

[Former Member]

Hello Diana,

I changed the Quantity type to numeric but I dont see any difference. I am using the command mode and I dont have any values in the Parameters screen.

Thanks,

Jai

[Former Member]

Jai,

Have you added parameters to the Parameters screen and then tested the query? It sounds like everything is in place and should work (sometimes it is difficult to determine the cause of an issue over the SDN).

Kind Regards,

Diana