Pros and cons with table lock

I use an ole db destination which I simply use to insert data into database.
If it's configured to use Fast Load as access mode, then it is possible to switch on/off the Lock table property, which is set to true as default.

Are there any drawbacks with switching off the Lock table property? What do I gain when using it?

Thanks for your help!

Its a performance issue. Having a table lock means that the load process doesn't have to worry about lower level locks, and so the load should be quicker.
But it does mean no one else can use that table

Proper syntax for bulk insert?

Hi,

I'm working in vb.net and want to use a stored procedure to insert all employees from one db into my db. I can insert one by one, but I would like to get them all in without looping.

How would I do this? I've tried bulk insert, but I keep getting syntax errors; I've read the books online, but don't quite understand what they mean. I don't want to use DTS, should I?

Here is what I'm doing so far:

CREATE Procedure Insert_From_Personnel
@.emp_num char(10),
@.Frst_Name char(10),
@.Last_Name char(10),
@.DivisionID char (4)

as

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

VALUES (@.emp_num,@.Frst_Name,@.Last_Name,@.DivisionID)
GO

Thanks for any help,SELECT * INTO myDB..Table FROM oneDB..Table|||Thanks Brett,

Now it's giving me an error that says 'object Individual is already in the database.'

Have I placed the select * in the right place?

CREATE Procedure Insert_From_Personnel
@.emp_num char(10),
@.Frst_Name char(10),
@.Last_Name char(10),
@.DivisionID char (4)

as

select * into individual from FROMGDPersonnelByDivision

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

VALUES (@.emp_num,@.Frst_Name,@.Last_Name,@.DivisionID)
GO

Thanks,|||insert into db1..Individual (fields)
select fields
from db2..Individual

This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?|||Itmay take forever though. If 'select into' is enabled on db1 I'd use Brett's approach by dropping Individual from db1 and using select * into... from ...|||insert into db1..Individual (fields)
select fields
from db2..Individual

This will bring over all of the records, so be careful about primary/unique key constraints. Sounds like you may be doing a refresh of data for a testing database?

fields...oye...|||OK, OK. I am being lazy. I admit it. ;-)

Still, it is slightly better than

insert into table
select *
from other_table|||Thanks guys, I DO need all the help I can get...

MCrowley, I am creating an appliction for two users. Once a week, or whenever, I want the user to be able to import people and info from the Personnel db into the Individuals table. The field names are different.

At this point, I don't mind slow, I just want to see it work. So now I've got:

CREATE Procedure Insert_From_Personnel

as

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
select emp_num,frst_name,last_name,[division id]
from FROMGDPersonnelByDivision

GO

I wiped out everything to start from scratch. I ran the SP and got the 'string or binary data would be truncated' error. Ran it again and got the 'violation of PK_Individual' error. There isn't any data in the table yet, how can it be violated?

Just to see if I've got it right, I took the PK off and it worked! It was pretty speedy, too. I'll try to write something on the vb.net side to Update if it sees the same emp_num and Insert if that emp_num doesn't exist.

One last thing... how can I say if it DOESN'T exist INSERT in the SP?

Thanks for your help!|||if not exists (select...)|||I'm getting closer, I hope. I've tried the if exists all over the place and weeded it down to only one 'incorrect syntax' near from FROMGDPersonnelByDivision

What the heck am I missing?!

CREATE Procedure Insert_From_Personnel
as
if not exists (select emp_num,frst_name,last_name,[division id])

INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

from FROMGDPersonnelByDivision
GO

Can I have one more hint, please?|||Yeah, I agree, the combination is finite and you're close ;)

CREATE Procedure Insert_From_Personnel
as
if not exists (select 1 from FROMGDPersonnelByDivision where <something> = <something>) INSERT into individual (IndividualID,FirstName,LastName,DivisionID)
else
print 'Record already exists'
return (0)
GO|||rdjabarov,

Help me out here. Why isn't this working?

When I use your example (simplified):
CREATE Procedure Insert_From_Personnel

as

if not exists (select emp_num,frst_name, last_name, [division id] from FROMGDPersonnelByDivision where emp_num = emp_num)
INSERT into individual (IndividualID,FirstName,LastName,DivisionID)

GO

It gives me the 'incorrect syntax near ')' after the last DivisionID, as if it needs something more.

After looking around on the web and in my Murach book, I tried putting values back in and using 'default values'. But then it has problems with the 'as'. As if it does not want VALUES using the 'if not exists'. What does it need? I'm not finding much info about combining 'if not' with 'insert into'.

Thanks for any help!|||The INSERT INTO requires either a VALUES clause or a SELECT statement to provide the data that you want inserted.

-PatP|||Hmmmm... OK,

The select is there. It makes sense in english;

if this doesn't exist: (the data from these columns, from THAT table) then INSERT it into THIS table

>> (select emp_num,frst_name, last_name, [division id] from >>FROMGDPersonnelByDivision where emp_num = emp_num)

Will I have better luck using 'WHERE NOT EXISTS'? Is there a difference?

Thanks,|||How about:INSERT into individual (
IndividualID, FirstName, LastName
, DivisionID
) SELECT
emp_num, frst_name, last_name
, [division id]
FROM FROMGDPersonnelByDivision
WHERE NOT EXSISTS (SELECT *
FROM individual AS b
WHERE b.InidividualID = FROMGDPersonnelByDivision.emp_num)-PatP|||Pat! Pat! It worked!

I was just trying to manipulate the code you gave for the 'Create Trigger?' post. I knew that the a.xxx and b.xxx sounded like where I should go. I didn't know I could just use a 'b'.

Thank you SO much! I'll be using this all over the place at work, so it's very important I get this concept down.|||Well, I am glad someone has a better eye sight than me ;)

Propagate fields from elements 2 or 3 levels above current element

Hi,
I am currently trying to insert multilevel data to 5 tables from one xml
source file. It inserts without errors, however, I noticed that I can only
propagate element values to the child only if the element value is from a
hierarchy above it.
For example, I have the following hierarchies/entities within a file:
<CONTROL>
<ORDER> (repeating)
<ORDER_ITEM> (repeating)
<FEATURES> (repeating)
</FEATURES>
</ORDER_ITEM>
<WORK_INSTRUCTIONS> (repeating)
</WORK_INSTRUCTIONS>
</ORDER>
</CONTROL>
In the table destined to hold fields for <FEATURES>, I also included one
field from <CONTROL> and another from <ORDER>. However, both remain blank
when I look at this table. Instead, only the the line number field from
<ORDER_ITEM> show up in addition to the fields from <FEATURES>. Is there
another way I could structure my annotated schema or tables to be able to
hold these needed fields?
I can post or send my table DDL, my annotated schema, and the sample xml
file upon request.
Regards,
Tristan
I had forgotten to bring up earlier that I am using SQLXMLBulkLoad object
from SQLXML 3 sp3 to upload the xml document to the 5 tables described.
5 tables described are as follows:
[DF_TempOrder_Control] ([XctlMsgNum],[Transaction],[Schema], [Plant],
[ParNum], [Date],[Time], [Valid])
[DF_TempOrder_Header] ([XctlMsgNum],[OrdMsgNum],[Action],[Floor_Order],
[Order_Type],[Due_Date],[Cra_Date],[AddrNumber],[Country],
[Zipcode], [Master_Order], [Ship_Tie] [nvarchar],
[RPQ], [Valid])
[DF_TempOrder_Details] ([XctlMsgNum], [Floor_Order], [MATNR],
[MATNR_DESC], [KDMAT], [KDMAT_DESC], [REVISION],
[SERIAL_YN], [REVISION_YN], [UPC_PART_YN],
[CONSIGNED_YN],[MAC_YN], [QTY],
[FLOOR_POSNR], [PARENT_POSNR], [BUNDLE_POSNR],
[VBAP_POSNR], [LIPS_POSNR], [VBAP_POSEX],
[SALES_ORDER],[SOLD_CUST_PO],[DELIVERY_NUM],
[SHIP_CUST_PO],[MACHINE_MODEL], [Valid])
[DF_TempOrder_ItemFeatures] ([XctlMsgNum],
[Floor_Order], [FLOOR_POSNR],
[Feature], [Valid])
[DF_TempOrder_Work_Instructions] ([XctlMsgNum],
[Floor_Order], [Instruction], [Valid])
My annotated schema is as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Order_Control" parent="DF_TempOrder_Control"
parent-key="XCTLMSGNUM" child="DF_TempOrder_Header"
child-key="XCTLMSGNUM" />
<sql:relationship name="Order_Detail" parent="DF_TempOrder_Header"
parent-key="FLOOR_ORDER" child="DF_TempOrder_Details"
child-key="FLOOR_ORDER" />
<sql:relationship name="OrderWI" parent="DF_TempOrder_Header"
parent-key="FLOOR_ORDER" child="DF_TempOrder_Work_Instructions"
child-key="FLOOR_ORDER" />
<sql:relationship name="OrderItemFeature" parent="DF_TempOrder_Details"
parent-key="FLOOR_POSNR" child="DF_TempOrder_Features"
child-key="FLOOR_POSNR" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="CONTROL" sql:relation="DF_TempOrder_Control">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="XCTLMSGNUM" type="xsd:string" />
<xsd:element name="TRANSACTION" type="xsd:string" />
<xsd:element name="SCHEMA" type="xsd:string" />
<xsd:element name="PLANT" type="xsd:string" />
<xsd:element name="PARNUM" type="xsd:string" />
<xsd:element name="DATE" type="xsd:string" />
<xsd:element name="TIME" type="xsd:string" />
<xsd:element name="ORDER" sql:relation="DF_TempOrder_Header"
sql:relationship="Order_Control">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FLOOR_ORDER" type="xsd:string" />
<xsd:element name="ORDMSGNUM" type="xsd:string" />
<xsd:element name="ACTION" type="xsd:string" />
<xsd:element name="ORDER_TYPE" type="xsd:string" />
<xsd:element name="DUE_DATE" type="xsd:string" />
<xsd:element name="CRA_DATE" type="xsd:string" />
<xsd:element name="ADDRNUMBER" type="xsd:integer" />
<xsd:element name="COUNTRY" type="xsd:string" />
<xsd:element name="ZIPCODE" type="xsd:string" />
<xsd:element name="MASTER_ORDER" type="xsd:string" />
<xsd:element name="SHIP_TIE" type="xsd:string" />
<xsd:element name="RPQ" type="xsd:string" />
<xsd:element name="ORDER_ITEM" sql:relation="DF_TempOrder_Details"
sql:relationship="Order_Detail">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MATNR" type="xsd:string" />
<xsd:element name="MATNR_DESC" type="xsd:string" />
<xsd:element name="KDMAT" type="xsd:string" />
<xsd:element name="KDMAT_DESC" type="xsd:string" />
<xsd:element name="REVISION" type="xsd:string" />
<xsd:element name="SERIAL_YN" type="xsd:string" />
<xsd:element name="REVISION_YN" type="xsd:string" />
<xsd:element name="UPC_PART_YN" type="xsd:string" />
<xsd:element name="CONSIGNED_YN" type="xsd:string" />
<xsd:element name="MAC_YN" type="xsd:string" />
<xsd:element name="QTY" type="xsd:int" />
<xsd:element name="FLOOR_POSNR" type="xsd:string" />
<xsd:element name="PARENT_POSNR" type="xsd:string" />
<xsd:element name="BUNDLE_POSNR" type="xsd:string" />
<xsd:element name="VBAP_POSNR" type="xsd:string" />
<xsd:element name="LIPS_POSNR" type="xsd:string" />
<xsd:element name="VBAP_POSEX" type="xsd:string" />
<xsd:element name="SALES_ORDER" type="xsd:string" />
<xsd:element name="SOLD_CUST_PO" type="xsd:string" />
<xsd:element name="DELIVERY_NUM" type="xsd:string" />
<xsd:element name="SHIP_CUST_PO" type="xsd:string" />
<xsd:element name="MACHINE_MODEL" type="xsd:string" />
<xsd:element name="FEATURES" sql:relation="DF_TempOrder_Features"
sql:relationship="OrderItemFeature">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FEATURE" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="WORK_INSTRUCTIONS"
sql:relation="DF_TempOrder_Work_Instructions" sql:relationship="OrderWI">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INSTRUCTION" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My sample xml file is as follows:
<MESSAGE>
<CONTROL>
<XCTLMSGNUM>123456789</XCTLMSGNUM>
<TRANSACTION>ORDER FOR FLOOR</TRANSACTION>
<SCHEMA>ORDER CONTENT</SCHEMA>
<PLANT>TN01</PLANT>
<PARNUM>0011</PARNUM>
<DATE>20050427</DATE>
<TIME>1237</TIME>
<ORDER>
<ORDMSGNUM>123456</ORDMSGNUM>
<ACTION>ADD</ACTION>
<FLOOR_ORDER>SAP123456</FLOOR_ORDER>
<ORDER_TYPE>ZDBO</ORDER_TYPE>
<ADDRNUMBER>123456</ADDRNUMBER>
<COUNTRY>US</COUNTRY>
<ZIPCODE>12345</ZIPCODE>
<MASTER_ORDER>DOC12345</MASTER_ORDER>
<SHIP_TIE>10823741</SHIP_TIE>
<DUE_DATE>05302005</DUE_DATE>
<CRA_DATE>06302005</CRA_DATE>
<RPQ>12345</RPQ>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION</INSTRUCTION>
</WORK_INSTRUCTIONS>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION1</INSTRUCTION>
</WORK_INSTRUCTIONS>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION2</INSTRUCTION>
</WORK_INSTRUCTIONS>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
</ORDER>
</CONTROL>
</MESSAGE>
|||Unfortunately, there is no possible mapping to perform the below action for
the given input Xml.
However, you may apply XSLT or wrap the SAX reader to inject fields which
you would like to appear in different levels. This is the one possible
workaround I can think.
"Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:ED8CDC51-FB41-4867-B77E-E92855DF11FB@.microsoft.com...
>I had forgotten to bring up earlier that I am using SQLXMLBulkLoad object
> from SQLXML 3 sp3 to upload the xml document to the 5 tables described.
> 5 tables described are as follows:
> [DF_TempOrder_Control] ([XctlMsgNum],[Transaction],[Schema], [Plant],
> [ParNum], [Date],[Time], [Valid])
> [DF_TempOrder_Header] ([XctlMsgNum],[OrdMsgNum],[Action],[Floor_Order],
> [Order_Type],[Due_Date],[Cra_Date],[AddrNumber],[Country],
> [Zipcode], [Master_Order], [Ship_Tie] [nvarchar],
> [RPQ], [Valid])
> [DF_TempOrder_Details] ([XctlMsgNum], [Floor_Order], [MATNR],
> [MATNR_DESC], [KDMAT], [KDMAT_DESC], [REVISION],
> [SERIAL_YN], [REVISION_YN], [UPC_PART_YN],
> [CONSIGNED_YN], [MAC_YN], [QTY],
> [FLOOR_POSNR], [PARENT_POSNR], [BUNDLE_POSNR],
> [VBAP_POSNR], [LIPS_POSNR], [VBAP_POSEX],
> [SALES_ORDER], [SOLD_CUST_PO], [DELIVERY_NUM],
> [SHIP_CUST_PO], [MACHINE_MODEL], [Valid])
> [DF_TempOrder_ItemFeatures] ([XctlMsgNum],
> [Floor_Order], [FLOOR_POSNR],
> [Feature], [Valid])
> [DF_TempOrder_Work_Instructions] ([XctlMsgNum],
> [Floor_Order], [Instruction], [Valid])
> My annotated schema is as follows:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="Order_Control" parent="DF_TempOrder_Control"
> parent-key="XCTLMSGNUM" child="DF_TempOrder_Header"
> child-key="XCTLMSGNUM" />
> <sql:relationship name="Order_Detail" parent="DF_TempOrder_Header"
> parent-key="FLOOR_ORDER" child="DF_TempOrder_Details"
> child-key="FLOOR_ORDER" />
> <sql:relationship name="OrderWI" parent="DF_TempOrder_Header"
> parent-key="FLOOR_ORDER" child="DF_TempOrder_Work_Instructions"
> child-key="FLOOR_ORDER" />
> <sql:relationship name="OrderItemFeature" parent="DF_TempOrder_Details"
> parent-key="FLOOR_POSNR" child="DF_TempOrder_Features"
> child-key="FLOOR_POSNR" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="CONTROL" sql:relation="DF_TempOrder_Control">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="XCTLMSGNUM" type="xsd:string" />
> <xsd:element name="TRANSACTION" type="xsd:string" />
> <xsd:element name="SCHEMA" type="xsd:string" />
> <xsd:element name="PLANT" type="xsd:string" />
> <xsd:element name="PARNUM" type="xsd:string" />
> <xsd:element name="DATE" type="xsd:string" />
> <xsd:element name="TIME" type="xsd:string" />
> <xsd:element name="ORDER" sql:relation="DF_TempOrder_Header"
> sql:relationship="Order_Control">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="FLOOR_ORDER" type="xsd:string" />
> <xsd:element name="ORDMSGNUM" type="xsd:string" />
> <xsd:element name="ACTION" type="xsd:string" />
> <xsd:element name="ORDER_TYPE" type="xsd:string" />
> <xsd:element name="DUE_DATE" type="xsd:string" />
> <xsd:element name="CRA_DATE" type="xsd:string" />
> <xsd:element name="ADDRNUMBER" type="xsd:integer" />
> <xsd:element name="COUNTRY" type="xsd:string" />
> <xsd:element name="ZIPCODE" type="xsd:string" />
> <xsd:element name="MASTER_ORDER" type="xsd:string" />
> <xsd:element name="SHIP_TIE" type="xsd:string" />
> <xsd:element name="RPQ" type="xsd:string" />
> <xsd:element name="ORDER_ITEM" sql:relation="DF_TempOrder_Details"
> sql:relationship="Order_Detail">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="MATNR" type="xsd:string" />
> <xsd:element name="MATNR_DESC" type="xsd:string" />
> <xsd:element name="KDMAT" type="xsd:string" />
> <xsd:element name="KDMAT_DESC" type="xsd:string" />
> <xsd:element name="REVISION" type="xsd:string" />
> <xsd:element name="SERIAL_YN" type="xsd:string" />
> <xsd:element name="REVISION_YN" type="xsd:string" />
> <xsd:element name="UPC_PART_YN" type="xsd:string" />
> <xsd:element name="CONSIGNED_YN" type="xsd:string" />
> <xsd:element name="MAC_YN" type="xsd:string" />
> <xsd:element name="QTY" type="xsd:int" />
> <xsd:element name="FLOOR_POSNR" type="xsd:string" />
> <xsd:element name="PARENT_POSNR" type="xsd:string" />
> <xsd:element name="BUNDLE_POSNR" type="xsd:string" />
> <xsd:element name="VBAP_POSNR" type="xsd:string" />
> <xsd:element name="LIPS_POSNR" type="xsd:string" />
> <xsd:element name="VBAP_POSEX" type="xsd:string" />
> <xsd:element name="SALES_ORDER" type="xsd:string" />
> <xsd:element name="SOLD_CUST_PO" type="xsd:string" />
> <xsd:element name="DELIVERY_NUM" type="xsd:string" />
> <xsd:element name="SHIP_CUST_PO" type="xsd:string" />
> <xsd:element name="MACHINE_MODEL" type="xsd:string" />
> <xsd:element name="FEATURES" sql:relation="DF_TempOrder_Features"
> sql:relationship="OrderItemFeature">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="FEATURE" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="WORK_INSTRUCTIONS"
> sql:relation="DF_TempOrder_Work_Instructions" sql:relationship="OrderWI">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INSTRUCTION" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> My sample xml file is as follows:
> <MESSAGE>
> <CONTROL>
> <XCTLMSGNUM>123456789</XCTLMSGNUM>
> <TRANSACTION>ORDER FOR FLOOR</TRANSACTION>
> <SCHEMA>ORDER CONTENT</SCHEMA>
> <PLANT>TN01</PLANT>
> <PARNUM>0011</PARNUM>
> <DATE>20050427</DATE>
> <TIME>1237</TIME>
> <ORDER>
> <ORDMSGNUM>123456</ORDMSGNUM>
> <ACTION>ADD</ACTION>
> <FLOOR_ORDER>SAP123456</FLOOR_ORDER>
> <ORDER_TYPE>ZDBO</ORDER_TYPE>
> <ADDRNUMBER>123456</ADDRNUMBER>
> <COUNTRY>US</COUNTRY>
> <ZIPCODE>12345</ZIPCODE>
> <MASTER_ORDER>DOC12345</MASTER_ORDER>
> <SHIP_TIE>10823741</SHIP_TIE>
> <DUE_DATE>05302005</DUE_DATE>
> <CRA_DATE>06302005</CRA_DATE>
> <RPQ>12345</RPQ>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION1</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION2</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> </ORDER>
> </CONTROL>
> </MESSAGE>
|||Other solutions include:
Write your own shredder on the midtier.
Pass the data to the database and use OpenXML in SS2000 (or nodes() in
SS2005) to get at the parent.
Best regards
Michael
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:uUX%23VvRbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Unfortunately, there is no possible mapping to perform the below action
> for the given input Xml.
> However, you may apply XSLT or wrap the SAX reader to inject fields which
> you would like to appear in different levels. This is the one possible
> workaround I can think.
>
> "Tristan" <Tristan@.discussions.microsoft.com> wrote in message
> news:ED8CDC51-FB41-4867-B77E-E92855DF11FB@.microsoft.com...
>

Propagate fields from elements 2 or 3 levels above current element

Hi,
I am currently trying to insert multilevel data to 5 tables from one xml
source file. It inserts without errors, however, I noticed that I can only
propagate element values to the child only if the element value is from a
hierarchy above it.
For example, I have the following hierarchies/entities within a file:
<CONTROL>
<ORDER> (repeating)
<ORDER_ITEM> (repeating)
<FEATURES> (repeating)
</FEATURES>
</ORDER_ITEM>
<WORK_INSTRUCTIONS> (repeating)
</WORK_INSTRUCTIONS>
</ORDER>
</CONTROL>
In the table destined to hold fields for <FEATURES>, I also included one
field from <CONTROL> and another from <ORDER>. However, both remain blank
when I look at this table. Instead, only the the line number field from
<ORDER_ITEM> show up in addition to the fields from <FEATURES>. Is there
another way I could structure my annotated schema or tables to be able to
hold these needed fields?
I can post or send my table DDL, my annotated schema, and the sample xml
file upon request.
Regards,
TristanI had forgotten to bring up earlier that I am using SQLXMLBulkLoad object
from SQLXML 3 sp3 to upload the xml document to the 5 tables described.
5 tables described are as follows:
[DF_TempOrder_Control] ([XctlMsgNum],[Transaction],[Schema], [Plant],
[ParNum], [Date],[Time], [Valid])
[DF_TempOrder_Header] ([XctlMsgNum],[OrdMsgNum],[Action],[Floo
r_Order],
[Order_Type],[Due_Date],[Cra_Date],[Addr
Number],[Country],
[Zipcode], [Master_Order], [Ship_Tie] [nvarchar],
[RPQ], [Valid])
[DF_TempOrder_Details] ([XctlMsgNum], [Floor_Order], [MATNR],
[MATNR_DESC], [KDMAT], [KDMAT_DESC], [REVISION],
[SERIAL_YN], [REVISION_YN], [UPC_PART_YN],
[CONSIGNED_YN], [MAC_YN], [QTY],
[FLOOR_POSNR], [PARENT_POSNR], [BUNDLE_POSNR],
[VBAP_POSNR], [LIPS_POSNR], [VBAP_POSEX],
[SALES_ORDER], [SOLD_CUST_PO], [DELIVERY
_NUM],
[SHIP_CUST_PO], [MACHINE_MODEL], [Valid])
[DF_TempOrder_ItemFeatures] ([XctlMsgNum],
[Floor_Order], [FLOOR_POSNR],
[Feature], [Valid])
[DF_TempOrder_Work_Instructions] ([XctlMsgNum],
[Floor_Order], [Instruction], [Valid])
My annotated schema is as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Order_Control" parent="DF_TempOrder_Control"
parent-key="XCTLMSGNUM" child="DF_TempOrder_Header"
child-key="XCTLMSGNUM" />
<sql:relationship name="Order_Detail" parent="DF_TempOrder_Header"
parent-key="FLOOR_ORDER" child="DF_TempOrder_Details"
child-key="FLOOR_ORDER" />
<sql:relationship name="OrderWI" parent="DF_TempOrder_Header"
parent-key="FLOOR_ORDER" child="DF_TempOrder_Work_Instructions"
child-key="FLOOR_ORDER" />
<sql:relationship name="OrderItemFeature" parent="DF_TempOrder_Details"
parent-key="FLOOR_POSNR" child="DF_TempOrder_Features"
child-key="FLOOR_POSNR" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="CONTROL" sql:relation="DF_TempOrder_Control">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="XCTLMSGNUM" type="xsd:string" />
<xsd:element name="TRANSACTION" type="xsd:string" />
<xsd:element name="SCHEMA" type="xsd:string" />
<xsd:element name="PLANT" type="xsd:string" />
<xsd:element name="PARNUM" type="xsd:string" />
<xsd:element name="DATE" type="xsd:string" />
<xsd:element name="TIME" type="xsd:string" />
<xsd:element name="ORDER" sql:relation="DF_TempOrder_Header"
sql:relationship="Order_Control">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FLOOR_ORDER" type="xsd:string" />
<xsd:element name="ORDMSGNUM" type="xsd:string" />
<xsd:element name="ACTION" type="xsd:string" />
<xsd:element name="ORDER_TYPE" type="xsd:string" />
<xsd:element name="DUE_DATE" type="xsd:string" />
<xsd:element name="CRA_DATE" type="xsd:string" />
<xsd:element name="ADDRNUMBER" type="xsd:integer" />
<xsd:element name="COUNTRY" type="xsd:string" />
<xsd:element name="ZIPCODE" type="xsd:string" />
<xsd:element name="MASTER_ORDER" type="xsd:string" />
<xsd:element name="SHIP_TIE" type="xsd:string" />
<xsd:element name="RPQ" type="xsd:string" />
<xsd:element name="ORDER_ITEM" sql:relation="DF_TempOrder_Details"
sql:relationship="Order_Detail">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MATNR" type="xsd:string" />
<xsd:element name="MATNR_DESC" type="xsd:string" />
<xsd:element name="KDMAT" type="xsd:string" />
<xsd:element name="KDMAT_DESC" type="xsd:string" />
<xsd:element name="REVISION" type="xsd:string" />
<xsd:element name="SERIAL_YN" type="xsd:string" />
<xsd:element name="REVISION_YN" type="xsd:string" />
<xsd:element name="UPC_PART_YN" type="xsd:string" />
<xsd:element name="CONSIGNED_YN" type="xsd:string" />
<xsd:element name="MAC_YN" type="xsd:string" />
<xsd:element name="QTY" type="xsd:int" />
<xsd:element name="FLOOR_POSNR" type="xsd:string" />
<xsd:element name="PARENT_POSNR" type="xsd:string" />
<xsd:element name="BUNDLE_POSNR" type="xsd:string" />
<xsd:element name="VBAP_POSNR" type="xsd:string" />
<xsd:element name="LIPS_POSNR" type="xsd:string" />
<xsd:element name="VBAP_POSEX" type="xsd:string" />
<xsd:element name="SALES_ORDER" type="xsd:string" />
<xsd:element name="SOLD_CUST_PO" type="xsd:string" />
<xsd:element name="DELIVERY_NUM" type="xsd:string" />
<xsd:element name="SHIP_CUST_PO" type="xsd:string" />
<xsd:element name="MACHINE_MODEL" type="xsd:string" />
<xsd:element name="FEATURES" sql:relation="DF_TempOrder_Features"
sql:relationship="OrderItemFeature">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="FEATURE" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="WORK_INSTRUCTIONS"
sql:relation="DF_TempOrder_Work_Instructions" sql:relationship="OrderWI">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="INSTRUCTION" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My sample xml file is as follows:
<MESSAGE>
<CONTROL>
<XCTLMSGNUM>123456789</XCTLMSGNUM>
<TRANSACTION>ORDER FOR FLOOR</TRANSACTION>
<SCHEMA>ORDER CONTENT</SCHEMA>
<PLANT>TN01</PLANT>
<PARNUM>0011</PARNUM>
<DATE>20050427</DATE>
<TIME>1237</TIME>
<ORDER>
<ORDMSGNUM>123456</ORDMSGNUM>
<ACTION>ADD</ACTION>
<FLOOR_ORDER>SAP123456</FLOOR_ORDER>
<ORDER_TYPE>ZDBO</ORDER_TYPE>
<ADDRNUMBER>123456</ADDRNUMBER>
<COUNTRY>US</COUNTRY>
<ZIPCODE>12345</ZIPCODE>
<MASTER_ORDER>DOC12345</MASTER_ORDER>
<SHIP_TIE>10823741</SHIP_TIE>
<DUE_DATE>05302005</DUE_DATE>
<CRA_DATE>06302005</CRA_DATE>
<RPQ>12345</RPQ>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION</INSTRUCTION>
</WORK_INSTRUCTIONS>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION1</INSTRUCTION>
</WORK_INSTRUCTIONS>
<WORK_INSTRUCTIONS>
<INSTRUCTION>THIS IS TEST INSTRUCTION2</INSTRUCTION>
</WORK_INSTRUCTIONS>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
<ORDER_ITEM>
<MATNR>PART A</MATNR>
<MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
<KDMAT>CUST PART A</KDMAT>
<KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
<REVISION>N</REVISION>
<SERIAL_YN>Y</SERIAL_YN>
<REVISION_YN>N</REVISION_YN>
<UPC_PART_YN>N</UPC_PART_YN>
<CONSIGNED_YN>Y</CONSIGNED_YN>
<MAC_YN>Y</MAC_YN>
<QTY>1</QTY>
<FLOOR_POSNR>12345</FLOOR_POSNR>
<PARENT_POSNR>0000</PARENT_POSNR>
<BUNDLE_POSNR>0000</BUNDLE_POSNR>
<VBAP_POSNR>1000</VBAP_POSNR>
<LIPS_POSNR>0001</LIPS_POSNR>
<VBAP_POSEX>0001</VBAP_POSEX>
<FEATURES>
<FEATURE>PRINT ON BOX</FEATURE>
</FEATURES>
<FEATURES>
<FEATURE>PRINT ON BOX 2</FEATURE>
</FEATURES>
<SALES_ORDER>Used at line level when</SALES_ORDER>
<SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
<DELIVERY_NUM>DOC12345</DELIVERY_NUM>
<SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
<MACHINE_MODEL>6400-800</MACHINE_MODEL>
</ORDER_ITEM>
</ORDER>
</CONTROL>
</MESSAGE>|||Unfortunately, there is no possible mapping to perform the below action for
the given input Xml.
However, you may apply XSLT or wrap the SAX reader to inject fields which
you would like to appear in different levels. This is the one possible
workaround I can think.
"Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:ED8CDC51-FB41-4867-B77E-E92855DF11FB@.microsoft.com...
>I had forgotten to bring up earlier that I am using SQLXMLBulkLoad object
> from SQLXML 3 sp3 to upload the xml document to the 5 tables described.
> 5 tables described are as follows:
> [DF_TempOrder_Control] ([XctlMsgNum],[Transaction],[Schema], [Plant],
> [ParNum], [Date],[Time], [Valid])
> [DF_TempOrder_Header] ([XctlMsgNum],[OrdMsgNum],[Action],[Floo
r_Order],
> [Order_Type],[Due_Date],[Cra_Date],[Addr
Number],[Country],
> [Zipcode], [Master_Order], [Ship_Tie] [nvarchar],
> [RPQ], [Valid])
> [DF_TempOrder_Details] ([XctlMsgNum], [Floor_Order], [MATNR],
> [MATNR_DESC], [KDMAT], [KDMAT_DESC], [REVISION],
> [SERIAL_YN], [REVISION_YN], [UPC_PART_YN],
> [CONSIGNED_YN], [MAC_YN], [QTY],
> [FLOOR_POSNR], [PARENT_POSNR], [BUNDLE_POSNR],
> [VBAP_POSNR], [LIPS_POSNR], [VBAP_POSEX],
> [SALES_ORDER], [SOLD_CUST_PO], [DELIVERY_NUM],
> [SHIP_CUST_PO], [MACHINE_MODEL], [Valid])
> [DF_TempOrder_ItemFeatures] ([XctlMsgNum],
> [Floor_Order], [FLOOR_POSNR],
> [Feature], [Valid])
> [DF_TempOrder_Work_Instructions] ([XctlMsgNum],
> [Floor_Order], [Instruction], [Valid])
> My annotated schema is as follows:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="Order_Control" parent="DF_TempOrder_Control"
> parent-key="XCTLMSGNUM" child="DF_TempOrder_Header"
> child-key="XCTLMSGNUM" />
> <sql:relationship name="Order_Detail" parent="DF_TempOrder_Header"
> parent-key="FLOOR_ORDER" child="DF_TempOrder_Details"
> child-key="FLOOR_ORDER" />
> <sql:relationship name="OrderWI" parent="DF_TempOrder_Header"
> parent-key="FLOOR_ORDER" child="DF_TempOrder_Work_Instructions"
> child-key="FLOOR_ORDER" />
> <sql:relationship name="OrderItemFeature" parent="DF_TempOrder_Details"
> parent-key="FLOOR_POSNR" child="DF_TempOrder_Features"
> child-key="FLOOR_POSNR" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="CONTROL" sql:relation="DF_TempOrder_Control">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="XCTLMSGNUM" type="xsd:string" />
> <xsd:element name="TRANSACTION" type="xsd:string" />
> <xsd:element name="SCHEMA" type="xsd:string" />
> <xsd:element name="PLANT" type="xsd:string" />
> <xsd:element name="PARNUM" type="xsd:string" />
> <xsd:element name="DATE" type="xsd:string" />
> <xsd:element name="TIME" type="xsd:string" />
> <xsd:element name="ORDER" sql:relation="DF_TempOrder_Header"
> sql:relationship="Order_Control">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="FLOOR_ORDER" type="xsd:string" />
> <xsd:element name="ORDMSGNUM" type="xsd:string" />
> <xsd:element name="ACTION" type="xsd:string" />
> <xsd:element name="ORDER_TYPE" type="xsd:string" />
> <xsd:element name="DUE_DATE" type="xsd:string" />
> <xsd:element name="CRA_DATE" type="xsd:string" />
> <xsd:element name="ADDRNUMBER" type="xsd:integer" />
> <xsd:element name="COUNTRY" type="xsd:string" />
> <xsd:element name="ZIPCODE" type="xsd:string" />
> <xsd:element name="MASTER_ORDER" type="xsd:string" />
> <xsd:element name="SHIP_TIE" type="xsd:string" />
> <xsd:element name="RPQ" type="xsd:string" />
> <xsd:element name="ORDER_ITEM" sql:relation="DF_TempOrder_Details"
> sql:relationship="Order_Detail">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="MATNR" type="xsd:string" />
> <xsd:element name="MATNR_DESC" type="xsd:string" />
> <xsd:element name="KDMAT" type="xsd:string" />
> <xsd:element name="KDMAT_DESC" type="xsd:string" />
> <xsd:element name="REVISION" type="xsd:string" />
> <xsd:element name="SERIAL_YN" type="xsd:string" />
> <xsd:element name="REVISION_YN" type="xsd:string" />
> <xsd:element name="UPC_PART_YN" type="xsd:string" />
> <xsd:element name="CONSIGNED_YN" type="xsd:string" />
> <xsd:element name="MAC_YN" type="xsd:string" />
> <xsd:element name="QTY" type="xsd:int" />
> <xsd:element name="FLOOR_POSNR" type="xsd:string" />
> <xsd:element name="PARENT_POSNR" type="xsd:string" />
> <xsd:element name="BUNDLE_POSNR" type="xsd:string" />
> <xsd:element name="VBAP_POSNR" type="xsd:string" />
> <xsd:element name="LIPS_POSNR" type="xsd:string" />
> <xsd:element name="VBAP_POSEX" type="xsd:string" />
> <xsd:element name="SALES_ORDER" type="xsd:string" />
> <xsd:element name="SOLD_CUST_PO" type="xsd:string" />
> <xsd:element name="DELIVERY_NUM" type="xsd:string" />
> <xsd:element name="SHIP_CUST_PO" type="xsd:string" />
> <xsd:element name="MACHINE_MODEL" type="xsd:string" />
> <xsd:element name="FEATURES" sql:relation="DF_TempOrder_Features"
> sql:relationship="OrderItemFeature">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="FEATURE" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="WORK_INSTRUCTIONS"
> sql:relation="DF_TempOrder_Work_Instructions" sql:relationship="OrderWI">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="INSTRUCTION" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> My sample xml file is as follows:
> <MESSAGE>
> <CONTROL>
> <XCTLMSGNUM>123456789</XCTLMSGNUM>
> <TRANSACTION>ORDER FOR FLOOR</TRANSACTION>
> <SCHEMA>ORDER CONTENT</SCHEMA>
> <PLANT>TN01</PLANT>
> <PARNUM>0011</PARNUM>
> <DATE>20050427</DATE>
> <TIME>1237</TIME>
> <ORDER>
> <ORDMSGNUM>123456</ORDMSGNUM>
> <ACTION>ADD</ACTION>
> <FLOOR_ORDER>SAP123456</FLOOR_ORDER>
> <ORDER_TYPE>ZDBO</ORDER_TYPE>
> <ADDRNUMBER>123456</ADDRNUMBER>
> <COUNTRY>US</COUNTRY>
> <ZIPCODE>12345</ZIPCODE>
> <MASTER_ORDER>DOC12345</MASTER_ORDER>
> <SHIP_TIE>10823741</SHIP_TIE>
> <DUE_DATE>05302005</DUE_DATE>
> <CRA_DATE>06302005</CRA_DATE>
> <RPQ>12345</RPQ>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION1</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <WORK_INSTRUCTIONS>
> <INSTRUCTION>THIS IS TEST INSTRUCTION2</INSTRUCTION>
> </WORK_INSTRUCTIONS>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> <ORDER_ITEM>
> <MATNR>PART A</MATNR>
> <MATNR_DESC>PART DESCRIPTION</MATNR_DESC>
> <KDMAT>CUST PART A</KDMAT>
> <KDMAT_DESC>CUST PART DESCRIPTION</KDMAT_DESC>
> <REVISION>N</REVISION>
> <SERIAL_YN>Y</SERIAL_YN>
> <REVISION_YN>N</REVISION_YN>
> <UPC_PART_YN>N</UPC_PART_YN>
> <CONSIGNED_YN>Y</CONSIGNED_YN>
> <MAC_YN>Y</MAC_YN>
> <QTY>1</QTY>
> <FLOOR_POSNR>12345</FLOOR_POSNR>
> <PARENT_POSNR>0000</PARENT_POSNR>
> <BUNDLE_POSNR>0000</BUNDLE_POSNR>
> <VBAP_POSNR>1000</VBAP_POSNR>
> <LIPS_POSNR>0001</LIPS_POSNR>
> <VBAP_POSEX>0001</VBAP_POSEX>
> <FEATURES>
> <FEATURE>PRINT ON BOX</FEATURE>
> </FEATURES>
> <FEATURES>
> <FEATURE>PRINT ON BOX 2</FEATURE>
> </FEATURES>
> <SALES_ORDER>Used at line level when</SALES_ORDER>
> <SOLD_CUST_PO>PO12345</SOLD_CUST_PO>
> <DELIVERY_NUM>DOC12345</DELIVERY_NUM>
> <SHIP_CUST_PO>PO12345</SHIP_CUST_PO>
> <MACHINE_MODEL>6400-800</MACHINE_MODEL>
> </ORDER_ITEM>
> </ORDER>
> </CONTROL>
> </MESSAGE>sql

Prooblem inserting into a table

Hi! I am currently trying to insert some values into a table. I have a very strange problem. I get the error message that i am trying to insert a null value into one of my colums, the problem is that when I write the value to a label control before I runs the insert method on my SqlDataSource control the value is not NULL. If i push the insert button on my webpage once again everything is ok.

What am I doing wrong? Anyone seen this kind of problem before?

regards,

Lumsen

Hi,

You can use the SQL Profiler to see which sql statement runs on the database after you click the button. If the error message is an sql error then you can see the statement by using the profiler.

Eralper

|||Can't be sure without more details. It could depends on what data control you're using, if any, and posting your code may help people to get better idea.|||without more info, its hard to tell. One thing you can do is add a watch to that variable and see if it is NULL at the wrong time.|||

Thanks for trying to help me out!

Here is the code from my asp page:

<%@. Page Language="VB" AutoEventWireup="false" CodeFile="newassesment.aspx.vb" Inherits="newassesment" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Add a new assesment<br />
<br />
<asp:Label ID="Label1" runat="server" Text="assesment id"></asp:Label>
<asp:TextBox ID="text_assesment_id" runat="server"></asp:TextBox><br />
<br />
<asp:Label ID="Label2" runat="server" Text="primary language"></asp:Label>
<asp:DropDownList ID="prim_lang_DropDownList" runat="server" DataSourceID="SqlDataSource2"
DataTextField="language_id" DataValueField="language_id">
</asp:DropDownList>
<br />
<br />
<asp:Label ID="Label3" runat="server" Text="# of answer alternatives"></asp:Label>
<asp:TextBox ID="text_ans_alt" runat="server"></asp:TextBox><br />
<br />
<asp:Label ID="Label4" runat="server" Text="Dont know allowed"></asp:Label>
<asp:CheckBox ID="checkBox_dont_know_allowed" runat="server" /><br />
<br />
<asp:Label ID="Label5" runat="server" Text="Light level"></asp:Label>
<asp:CheckBox ID="checkBox_light_level" runat="server" /><br />
<br />
<asp:Label ID="Label6" runat="server" Text="Medium level"></asp:Label>
<asp:CheckBox ID="checkBox_medium_level" runat="server" /><br />
<br />
<asp:Label ID="Label7" runat="server" Text="Extensive level"></asp:Label>
<asp:CheckBox ID="checkBox_extensive_level" runat="server" /><br />
<br />
<asp:Button ID="button_submit" runat="server" Text="Submit" />
<asp:Label ID="label_error" runat="server" ForeColor="Red"></asp:Label>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AssesmentConnectionString2 %>"
DeleteCommand="DELETE FROM [assesment_definition] WHERE [assesment_id] = @.assesment_id"
InsertCommand="INSERT INTO [assesment_definition] ([assesment_id], [primary_language_id], [number_of_answer_alternatives], [dont_know_answer_accepted], [light_level], [medium_level], [extensive_level]) VALUES (@.assesment_id, @.primary_language_id, @.number_of_answer_alternatives, @.dont_know_answer_accepted, @.light_level, @.medium_level, @.extensive_level)"
SelectCommand="SELECT * FROM [assesment_definition]" UpdateCommand="UPDATE [assesment_definition] SET [primary_language_id] = @.primary_language_id, [number_of_answer_alternatives] = @.number_of_answer_alternatives, [dont_know_answer_accepted] = @.dont_know_answer_accepted, [light_level] = @.light_level, [medium_level] = @.medium_level, [extensive_level] = @.extensive_level WHERE [assesment_id] = @.assesment_id">
<DeleteParameters>
<asp:Parameter Name="assesment_id" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="primary_language_id" Type="String" />
<asp:Parameter Name="number_of_answer_alternatives" Type="Int32" />
<asp:Parameter Name="dont_know_answer_accepted" Type="Boolean" />
<asp:Parameter Name="light_level" Type="Boolean" />
<asp:Parameter Name="medium_level" Type="Boolean" />
<asp:Parameter Name="extensive_level" Type="Boolean" />
<asp:Parameter Name="assesment_id" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:FormParameter FormField="text_assesment_id" Name="assesment_id" Type="String" />
<asp:FormParameter FormField="prim_lang_DropDownList" Name="primary_language_id"
Type="String" />
<asp:FormParameter FormField="text_ans_alt" Name="number_of_answer_alternatives"
Type="Int32" />
<asp:FormParameter FormField="text_dontknowallowed" Name="dont_know_answer_accepted"
Type="Boolean" DefaultValue="" />
<asp:FormParameter FormField="text_lightlevel" Name="light_level" Type="Boolean" DefaultValue="false" />
<asp:FormParameter FormField="text_mediumlevel" Name="medium_level" Type="Boolean" DefaultValue="false" />
<asp:FormParameter FormField="text_extensivelevel" Name="extensive_level" Type="Boolean" DefaultValue="false" />
</InsertParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AssesmentConnectionString2 %>"
SelectCommand="SELECT [language_id] FROM [language]"></asp:SqlDataSource>
<br />
<br />
<br />
<asp:TextBox ID="text_dontknowallowed" runat="server" CausesValidation="True"></asp:TextBox><br />
<asp:TextBox ID="text_lightlevel" runat="server"></asp:TextBox><br />
<asp:TextBox ID="text_mediumlevel" runat="server"></asp:TextBox><br />
<asp:TextBox ID="text_extensivelevel" runat="server"></asp:TextBox><br />
<br />
<br />
<asp:Label ID="testLabel" runat="server" ForeColor="Blue"></asp:Label></div>
</form>
</body>
</html>

And the code when the submit button is clicked:

Partial Class newassesment
Inherits System.Web.UI.Page

Protected Sub button_submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button_submit.Click

text_dontknowallowed.Text = checkBox_dont_know_allowed.Checked
text_lightlevel.Text = checkBox_light_level.Checked
text_mediumlevel.Text = checkBox_medium_level.Checked
text_extensivelevel.Text = checkBox_extensive_level.Checked

testLabel.Text = SqlDataSource1.InsertCommand()

Try

Dim recAff As String

recAff = SqlDataSource1.Insert()

label_error.Text = (recAff & " record inserted successfully!")

Catch SQLExp As System.Data.SqlClient.SqlException

label_error.Text = "An SQL Server Error Occurred: " & SQLExp.Message()

Catch except As Exception

label_error.Text = except.ToString()

End Try

End Sub

Protected Sub checkBox_dont_know_allowed_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles checkBox_dont_know_allowed.CheckedChanged

testLabel.Text = "HALLO"

End Sub
End Class

Do this help?

Thanks

Lumsen

|||

Why aren't you using one of the data controls (DetailsView or FormView) along with your SqlDataSource? They were meant to be used together. I suppose you could somehow make your code work properly, but it may prove difficult to get everything right. Also, FormView/DetailsView allow paging, edit, delete, insert to take place automatically and does data binding for you. By not using these controls, you're choosing to manually handle everything (sort of reinventing the wheel). Finally, I'm not sure if anyone will be willing to help you implement something so out of the ordinary.

So, my advice is for you to go with FormView control for Insert, Edit, Delete of your data. It'll make your project far easier.

|||You shouldn't be using formparameters to pull from ASP.NET controls to start. Either make them Parameters, and set them manually, or make them controlparameters to pull from an ASP.NET control, or change all your textboxes from ASP.NET controls to form controls "<INPUT TYPE=...>" and use formparameters.|||Thanks for guiding me! Works a lot better with the formview control!

Just one problem remains.. I want to make one of the fields in the formview a dropdownlist, where the items are selected from another table than the table associated with the formview. Is that possible?

Thanks!

Lumsen|||Using a DropDownList control within a FormView is possible. That topic has been covered many times before, so rather than describing the whole process again, try searching the forum for the keywords "formview dropdownlist".

Programmatic insert into SQL database

I have a page with over 20 Textbox and DDL controls and an upload in various Divs and Panels (Ajax enabled) that are used for gathering user data. Some of the fields are mandatory and some optional.

In the Code behind (VB- I am a complete novice) On the submit button click event, I iterate through the controls in the page and build an array with information from the controls that have data in them, (filtering out the non-filled textboxes, and DDLs).

All this works well, and I get an array called 'myInfo' with the columns with the control ID, and control values 'rvalue' (as string), with the number of rows equal to the filled textboxes and DDLs.

I then step through the array and build a string with 'name=values' of all the rows in the myinfo array and email this as a message:

ThisMessage = ""
NoOfControls = myInfo.GetLength(0)
For i = 0 To NoOfControls - 1
ThisMessage = ThisMessage & myInfo(i).ID.ToString & "=" & myInfo(i).rvalue.ToString & "; "
Next

SendMail(email address, ThisMessage)

I also want to add this information to a database, appended by the IP address and datetime.now.

Dim evdoDataSource As New SqlDataSource()
evdoDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("SQLConnectionStringCK").ToString
evdoDataSource.InsertCommandType = SqlDataSourceCommandType.Text

Dim InsertMessage As String = """INSERT INTO evdoData ("
NoOfControls = myInfo.GetLength(0)

Dim k As Integer
For k = 0 To NoOfControls - 1
InsertMessage = InsertMessage & myInfo(k).ID.ToString & ", "
Next

InsertMessage = InsertMessage & "IPNo, DateEntered) VALUES ("

For k = 0 To NoOfControls - 1
InsertMessage = InsertMessage & "@." & myInfo(k).ID.ToString & ", "
Next
InsertMessage = InsertMessage & ", @.IPNo, @.DateEntered" & ")"""

evdoDataSource.InsertCommand = InsertMessage

I then similarly iterate through and do the insertparameters.

Now here is the rub- (My all too often DUH moment!)

When I look at the insertmessage in debug (and to be sure- I also show the insert string on a temporary debug label on the page), The insertmessage looks fine:

"INSERT INTO EvdData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@.FirstName, @.Age, @.Email, @.Phone, @.Country, @.City, , @.IPNo, @.DateEntered)"

However when the above code(evdoDataSource.InsertCommand = InsertMessage) is run, I get an error - the message with the error is:

The identifier that starts with 'INSERT INTO ModelData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@.FirsteName, @.Age, @.Email, @.Phone,' is too long. Maximum length is 128

EH? When I actually copy the InsertMessage from the debug window and paste it manually after the command "evdoDataSource.InsertCommand = " It works, and I get the data inserted into the table..

-It would seem that I am probably missing something obvious in my complete "noviceness" . HELP! (oh and thanks a bunch in anticipation)

_____________________________________________________________________________________________________________________

Its Easy --------When you know How. Meanwhile Aaaaaaaaah .

Remove the double-quotes around the insert statement.

Dim InsertMessageAs String ="INSERT INTO evdoData (" NoOfControls = myInfo.GetLength(0)Dim kAs Integer For k = 0To NoOfControls - 1 InsertMessage = InsertMessage & myInfo(k).ID.ToString &", "Next InsertMessage = InsertMessage &"IPNo, DateEntered) VALUES ("For k = 0To NoOfControls - 1 InsertMessage = InsertMessage &"@." & myInfo(k).ID.ToString &", "Next InsertMessage = InsertMessage &", @.IPNo, @.DateEntered)"

|||

In the post the you have an extra comma in your insert statement after @.City and before @.IPNo

INSERT INTO EvdData (FirstName, Age, Email, Phone,Country, City, IPNo, DateEntered) VALUES (@.FirstName, @.Age, @.Email,@.Phone, @.Country, @.City, , @.IPNo, @.DateEntered)

You may want to change

InsertMessage = InsertMessage & ", @.IPNo, @.DateEntered" & ")"""

to

InsertMessage = InsertMessage & "@.IPNo, @.DateEntered" & ")"""

|||

First, don't use " (double quotes) in your query, instead use ' (single quote) if and when needed. If at all you have to use double quotes then prefer setting QUOTED_IDENTIFIER OFF for the connection object. This sounds bit tricky, right?

kj@.zqtech.com:

"INSERT INTO EvdData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@.FirstName, @.Age, @.Email, @.Phone, @.Country, @.City, , @.IPNo, @.DateEntered)"

Second, did you notice that there are 2 , (comma) between @.city and @.IPNO or this is just by mistake when you were pasting your code in the post ?

Hope this will help.

|||

Thanks Guys. It was the combination of both things. No quotations required and also the extra comma. (Silly look on my face at this point).

Moral of the story:

1. Coding problems are like magician's tricks: one problem is a slight of hand to take your attention away from another. 2. Beware of taking error messages too literally. -

programatically get values from sqldatasource

Hi All,

I am having problems getting values out of an sqldatasource.

I have 2 on the page, i am using one to insert into a table and the other to get a couple of values from another table for the insert.

the datasource i am trying to get the values from does a select with a querystring filter i need to grab a couple of fields out of it trhen do the insert on the other datasiource with a button click.

The insert is fine, i just don't know how to get the values out of the first 'select source'

Any pointers or suggestions most appreciated.

Cheers

I have figured this out.

I am doing the whole lot in the page load event handler and assigning the values to sessions then inserting the sesion variables in the iteminserting event handler for the formview

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim dvAs DataView = _

CType(get_LocData.Select(DataSourceSelectArguments.Empty), DataView)

ForEach drAs DataRowIn dv.Table.Rows

Session("LocMan") = dr("LocManID").ToString()

Session("LocID") = dr("ID").ToString()

Session("Status") ="New"

Next

EndSub

ProtectedSub FormView1_ItemInserting(ByVal SenderAsObject,ByVal eAs FormViewInsertEventArgs)Handles FormView1.ItemInserting

e.Values("LocMan") = (Session("LocMan"))

e.Values("Status") ="new"

e.Values("LocationID") = (Session("LocID"))

EndSub

|||

Don't use an SqlDataSource for that.

using (SqlConnection conn = new SqlConnection("your connectionstring"))
{
SqlCommand cmd = new SqlCommand("SELECT ... FROM ... WHERE someColumn = @.someParameter", conn);
cmd.Parameters.AddWithValue("@.someParameter", Request.QueryString["yaddayadda"]);
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
col1=reader["col1"]; //Here you need to cast the value to the proper type, or use e.g reader.GetString(0) if it's a string
col2=reader["col2"];
}
}
}

But if you're selecting values just to insert them, you should be able to do it without retrieving the values in the first place. Please give us more details.

program to insert the csv file to SQL server

Dear professional
I am writing a program to insert the csv file to SQL server.
However, the data source contains different charater like that:
ABC-001
ABC-002
BDE_001
BDE_002
have there is any SQL to convert the data as below:
ABC-001-->ABC001
ABC-002-->ABC002
BDE_001-->BDE001
BDE_002-->BDE002
ThanksBasically you need to concatenate. like
left(fieldname, 3) + right(fieldname,3)
Or you can use substring also
Amarnath
"Anton" wrote:
> Dear professional
> I am writing a program to insert the csv file to SQL server.
> However, the data source contains different charater like that:
> ABC-001
> ABC-002
> BDE_001
> BDE_002
> have there is any SQL to convert the data as below:
> ABC-001-->ABC001
> ABC-002-->ABC002
> BDE_001-->BDE001
> BDE_002-->BDE002
> Thanks
>
>|||"Amarnath" wrote:
> Basically you need to concatenate. like
> left(fieldname, 3) + right(fieldname,3)
> Or you can use substring also
> Amarnath
> "Anton" wrote:
> > Dear professional
> >
> > I am writing a program to insert the csv file to SQL server.
> > However, the data source contains different charater like that:
> >
> > ABC-001
> > ABC-002
> > BDE_001
> > BDE_002
> >
> > have there is any SQL to convert the data as below:
> >
> > ABC-001-->ABC001
> > ABC-002-->ABC002
> > BDE_001-->BDE001
> > BDE_002-->BDE002
> >
> > Thanks
> >
> >
> >
> >|||YOu can use Replace function. Replace '-' with ''