bulk insert fails

Hi

I have a page that bulkinsert data to my sql server, I build up the bulk insert part like this...

1 sb.Append("Exec p_BulkInsertPDI'<ROOT><PROT>")23 sb.Append("<PDI NID=""" & HiddenField1.Value & """ AID=""" & HiddenField1a.Value & """ MID="" GID="" UID=""" & UserID & """/>")45 sb.Append("</PROT></ROOT>'")

The problem I have here is that sometimes the AID value doesn't have any value beacuse on the previous page haven't sent any value to that hiddenfield.
So when I try to run this, I get a error message like this... "Conversion failed when converting the nvarchar value 'AID=' to data type int".

It would be the best if I could insert Null values if no value have been provided. Is this possible to do?

Regards

You could use the IF and Only If , e.g

sb.append("...." & _

IIF(hiddenvalue.value = "", "NULL", hiddenvalue.value) & _

".........")

EDIT: Got the syntax slightly wrong - it has been corrected.

|||Ahhh.. This sounds very promising, I'll try it and get back...|||

Hi Again

I replaced it so it look like this ...

sb.Append("<PD NID=""" & HiddenField7.Value &""" AID=""" & HiddenField7a.Value &""" FID=""" & (IIf(HiddenField7b.Value ="","NULL", HiddenField7b.Value)) &""" GID=""" & (IIf(HiddenField7d.Value ="","NULL", HiddenField7d.Value)) &""" MID=""" & (IIf(HiddenField7c.Value ="","NULL", HiddenField7c.Value)) &""" PID=""" & PID &"""/>")sb.Append("<PD NID=""" & HiddenField12.Value &""" AID=""" & HiddenField12a.Value &""" FID=""NULL"" GID=""NULL"" MID=""NULL"" UID=""" & UserID &"""/>")

And this is my sp...
 
p_BulkInsertPDI (@.FormData ntext)ASDECLARE @.hDoc int exec sp_xml_preparedocument @.hDoc OUTPUT,@.FormData BEGINSET NOCOUNT ON;INSERT INTO tbl_Form_Answers(NodeID, AprovalID, FaultID, Grade, MeasureID, ProtocolID)SELECT * FROM OPENXML(@.hDoc,'ROOT/PROT/PD',1)WITH ( NIDInteger , AIDInteger , FIDInteger , GID integer, MID integer, PID integer ) XMLEmpEXEC sp_xml_removedocument @.hDocEND
 
 

But now I get this errror message.. "Conversion failed when converting the nvarchar value 'NULL' to data type int"

How can I get this to work? I really don't want to make x number of insertation to the db, I would prefer to just do one.

Regards

|||

Ahhh, I didn't see that one coming. First thing you can try is use the dbnull.value instead:

sb.append("...." (IIf(HiddenField7d.Value ="",DBNull.value, HiddenField7d.Value)) )

Not sure if this will work. Otherwise your best bet is to temporarily assign a number as null. e.g. use a value you know will never occur (such as -1). After the inserts, you can then update the database and change all the -1 to NULL. Not the best method, but it should work.:

sb.append("...." (IIf(HiddenField7d.Value ="",-1, HiddenField7d.Value)) )

 
p_BulkInsertPDI
(@.FormData ntext)
AS
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.FormData
BEGIN
SET NOCOUNT ON;
INSERT INTO tbl_Form_Answers(NodeID, AprovalID, FaultID, Grade, MeasureID, ProtocolID)
SELECT *
FROM OPENXML(@.hDoc,'ROOT/PROT/PD',1)
WITH ( NIDInteger , AIDInteger , FIDInteger , GID integer, MID integer, PID integer ) XMLEmp
UPDATE tbl_Form_Answers SET Grade = NULL WHERE Grade = -1
EXEC sp_xml_removedocument @.hDoc
END

|||

Hi

The DBNull.Value part didn't work so I ended up with your second proposal wich worked fine, I guess I have to live with the update part. It must be better than to make 20 database insert, which would have been the case if this wouldnt work.

Thanks for all your help

Best Regards