0

Here is my code. please help me. i'm not able to add records into database.

XML:

<?xml version="1.0" encoding="UTF-8"?>
<!--OrderFlow will have varchar Type M-Mail in Art, B- Bring Art To Store, N- Normal Orders-->
<ns:orders xmlns:ns="http://www.demandware.com/xml/impex/order/2006-10-31" Version="17.2.1.0" StoreNumber="9291" EventName="OrderCreation_Ecom_to_FP" Source="Ecom" IsReady="0" OrderFlow="M">
    <ns:order xmlns="http://www.demandware.com/xml/impex/order/2006-10-31" OrderNumber="00008310W01" Encoding="ISO-8859-1" CartOrderNumber="00008310" PhysicalStoreNumber="7383" OriginalOrderNumber="00008210W01">
        <ns:CreateDate>2018-03-06T08:08:15</ns:CreateDate>
        <ns:DueDate>2018-03-06T08:08:15</ns:DueDate>
        <ns:Quantity>1</ns:Quantity>
        <ns:RetailSubTotal>159</ns:RetailSubTotal>
        <ns:DiscountedSubTotal>143.1</ns:DiscountedSubTotal>
        <ns:Tax>9.3</ns:Tax>
        <ns:TotalPrice>152.4</ns:TotalPrice>
        <ns:DiscountAmount>-15.9</ns:DiscountAmount>
        <ns:TotalPriceLessTax>143.1</ns:TotalPriceLessTax>
        <ns:TaxableSubtotal>143.1</ns:TaxableSubtotal>
        <ns:NonTaxableSubtotal>0.0</ns:NonTaxableSubtotal>
        <ns:UseStretcherBar>0</ns:UseStretcherBar>
        <ns:Notes>Framed Photo Print</ns:Notes>
        <ns:OrderType>Custom</ns:OrderType>
        <ns:OrderStatus>Audited</ns:OrderStatus>
        <ns:Framewidth>38.00</ns:Framewidth>
        <ns:FrameHeight>29.50</ns:FrameHeight>
        <ns:TotalOutsideWidth>39.54</ns:TotalOutsideWidth>
        <ns:TotalOutSideHeight>31.04</ns:TotalOutSideHeight>
        <ns:previewImageURL>https://previews.framerspointe.com/image/upload/bo_0px_solid_rgb:ffffff,c_crop,h_2412,w_3228,x_18,y_18/c_scale,h_2448,w_3264/h_2796,u_Mats:B8260,w_3612/fl_layer_apply/h_99,l_Frames:2951261TL,w_3810/fl_layer_apply,g_north,y_-99/a_180,h_99,l_Frames:2951261BR,w_3810/fl_layer_apply,g_south,y_-99/h_99,l_Frames:2951261BR,w_2994/c_lpad,g_north,h_3810,w_2994/a_90/l_triangle,w_99/a_180/e_cut_out,fl_layer_apply,g_north_east/l_triangle,w_99/a_90/e_cut_out,fl_layer_apply,g_south_east/fl_layer_apply/h_99,l_Frames:2951261TL,w_2994/c_lpad,g_north,h_3810,w_2994/a_-90/l_triangle,w_99/a_-90/e_cut_out,fl_layer_apply,g_north_west/l_triangle,w_99/e_cut_out,fl_layer_apply,g_south_west/fl_layer_apply/c_scale,w_400/pqqiethajvrz3lstvdqs</ns:previewImageURL>
        <ns:ExpediteShippingPrice IsExpedite="0" CartQty="1">
            <ns:RetailPrice>0</ns:RetailPrice>
            <ns:SalePrice>0</ns:SalePrice>
            <ns:Tax>0</ns:Tax>
            <ns:DiscountedAmount>0</ns:DiscountedAmount>
        </ns:ExpediteShippingPrice>
        <ns:MailInArt-address>
            <ns:customer-no>29832996</ns:customer-no>
            <ns:customer-email>Amberry1208@gmail.com</ns:customer-email>
            <ns:FirstName>Angela</ns:FirstName>
            <ns:LastName>Berry</ns:LastName>
            <ns:StreetA>7111 W 56TH ST</ns:StreetA>
            <ns:StreetB>#40</ns:StreetB>
            <ns:City>SIOUX FALLS</ns:City>
            <ns:PostalCode>57106-7593</ns:PostalCode>
            <ns:State_id>SD</ns:State_id>
            <ns:country-code>us</ns:country-code>
            <ns:phone>605-254-5931</ns:phone>
            <ns:ShippingType>001</ns:ShippingType>
        </ns:MailInArt-address>
        <ns:shipping-address>
            <ns:customer-no>29832996</ns:customer-no>
            <ns:customer-email>Amberry1208@gmail.com</ns:customer-email>
            <ns:FirstName>Angela</ns:FirstName>
            <ns:LastName>Berry</ns:LastName>
            <ns:StreetA>8607 Allisonville Rd</ns:StreetA>
            <ns:StreetB>Store #7383</ns:StreetB>
            <ns:City>Indianapolis</ns:City>
            <ns:PostalCode>46250</ns:PostalCode>
            <ns:State_id>IN</ns:State_id>
            <ns:country-code>us</ns:country-code>
            <ns:phone>605-254-5931</ns:phone>
            <ns:ShippingType>001</ns:ShippingType>
        </ns:shipping-address>
        <ns:Backing>
            <ns:Sku>SFC</ns:Sku>
            <ns:Layer />
            <ns:RetailPrice>0</ns:RetailPrice>
            <ns:SalePrice>0</ns:SalePrice>
            <ns:DiscountedAmount>0</ns:DiscountedAmount>
            <ns:Width>38.00</ns:Width>
            <ns:Height>29.50</ns:Height>
        </ns:Backing>
        <ns:Fitting>
            <ns:Sku>BFIT</ns:Sku>
            <ns:Layer />
            <ns:RetailPrice>0</ns:RetailPrice>
            <ns:SalePrice>0</ns:SalePrice>
            <ns:DiscountedAmount>0</ns:DiscountedAmount>
            <ns:Width>38.00</ns:Width>
            <ns:Height>29.50</ns:Height>
        </ns:Fitting>
        <ns:Glass>
            <ns:Sku>AC</ns:Sku>
            <ns:Layer />
            <ns:RetailPrice>0</ns:RetailPrice>
            <ns:SalePrice>0</ns:SalePrice>
            <ns:DiscountedAmount>0</ns:DiscountedAmount>
            <ns:Width>38.00</ns:Width>
            <ns:Height>29.50</ns:Height>
        </ns:Glass>
        <ns:Images>
            <ns:Image ShapeType="Rectangle" DesignNumber="101">
                <ns:DPI>96</ns:DPI>
                <ns:Width>34</ns:Width>
                <ns:Height>25.5</ns:Height>
                <ns:RetailPrice>39</ns:RetailPrice>
                <ns:SalePrice>35.1</ns:SalePrice>
                <ns:DiscountedAmount>3.9</ns:DiscountedAmount>
                <ns:LeftOffset>1.813</ns:LeftOffset>
                <ns:TopOffset>1.813</ns:TopOffset>
                <ns:RightOffset>35.81</ns:RightOffset>
                <ns:BottomOffset>27.31</ns:BottomOffset>
                <ns:RightBorder>1.8125</ns:RightBorder>
                <ns:BottomBorder>1.8125</ns:BottomBorder>
                <ns:LeftBorder>1.8125</ns:LeftBorder>
                <ns:TopBorder>1.8125</ns:TopBorder>
                <ns:sourceImageURL>https://previews.framerspointe.com/image/upload/pqqiethajvrz3lstvdqs</ns:sourceImageURL>
                <ns:croppedImageURL>https://previews.framerspointe.com/image/upload/q_100/c_crop,dn_96,h_2448,w_3264,x_0,y_0/pqqiethajvrz3lstvdqs</ns:croppedImageURL>
                <ns:Description>Framed Photo Print</ns:Description>
                <ns:LibraryImageUsed>0</ns:LibraryImageUsed>
                <ns:SubstrateType>Paper</ns:SubstrateType>
                <ns:Mount>
                    <ns:Sku>Dry Mount</ns:Sku>
                    <ns:RetailPrice>0</ns:RetailPrice>
                    <ns:SalePrice>0</ns:SalePrice>
                    <ns:DiscountedAmount>0</ns:DiscountedAmount>
                    <ns:Width>34</ns:Width>
                    <ns:Height>25.5</ns:Height>
                </ns:Mount>
            </ns:Image>
        </ns:Images>
        <ns:Frames>
            <ns:Frame>
                <ns:Sku>2951261</ns:Sku>
                <ns:Layer />
                <ns:RetailPrice>75</ns:RetailPrice>
                <ns:SalePrice>67.5</ns:SalePrice>
                <ns:DiscountedAmount>7.5</ns:DiscountedAmount>
                <ns:Width>38.00</ns:Width>
                <ns:Height>29.50</ns:Height>
            </ns:Frame>
        </ns:Frames>
        <ns:Mats>
            <ns:Mat>
                <ns:Sku>B8260</ns:Sku>
                <ns:Layer>1</ns:Layer>
                <ns:RetailPrice>45</ns:RetailPrice>
                <ns:SalePrice>40.5</ns:SalePrice>
                <ns:DiscountedAmount>4.5</ns:DiscountedAmount>
                <ns:Width>38.00</ns:Width>
                <ns:Height>29.50</ns:Height>
                <ns:RightBorder>2.188</ns:RightBorder>
                <ns:BottomBorder>2.188</ns:BottomBorder>
                <ns:LeftBorder>2.188</ns:LeftBorder>
                <ns:TopBorder>2.188</ns:TopBorder>
                <ns:Opening Height="25.13" Width="33.63" />
            </ns:Mat>
        </ns:Mats>
    </ns:order>
</ns:orders>

C#:

DateTime? date=null;
DateTime? DueDate = null;
string OriginalOrderNumber = null;
string FirstName=null;
string LastName = null;
string email = null;
string StreetA = null;
string StreetB = null;
string PostalCode = null;
string City = null;
string State_id = null;
string country_code = null;
string phone = null;

XmlTextReader reader = new XmlTextReader("ecom_MailInArt_Store.xml");

while (reader.Read())
{

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:order"))
    {
        if (reader.HasAttributes)
        {
            OriginalOrderNumber = reader.GetAttribute("OriginalOrderNumber");

        }

    }

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:CreateDate"))
    {
        reader.Read();
        date = Convert.ToDateTime(reader.Value);

    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:DueDate"))
    {
        reader.Read();
        DueDate = Convert.ToDateTime(reader.Value);
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:FirstName"))
    {
        reader.Read();
        FirstName = reader.Value;

    }

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:LastName"))
    {
        reader.Read();
        LastName = reader.Value;
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:customer-email"))
    {
        reader.Read();
        email = reader.Value;
    }

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:phone"))
    {
        reader.Read();
        phone = reader.Value;
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:StreetA"))
    {
        reader.Read();
        StreetA = reader.Value;
    }

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:StreetB"))
    {
        reader.Read();
        StreetB = reader.Value;
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:PostalCode"))
    {
        reader.Read();
        PostalCode = reader.Value;
    }

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:City"))
    {
        reader.Read();
        City = reader.Value;
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:State_id"))
    {
        reader.Read();
        State_id = reader.Value;
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:country-code"))
    {
        reader.Read();
        country_code = reader.Value;
    }

    SqlConnection con = new SqlConnection("Data Source=MNGNET320543D;Initial Catalog=excercise1;User id=sa;Password=Infosys123;");
    SqlCommand cmd = new SqlCommand("Insert into MailInArtOrder(OrderNumber,OrderCreateDate,DueDate,FirstName,LastName,EmailAddress,PhoneNumber,StreetA,StreetB,PostalCode,City,State,CountryCode) values(@OrderNumber,@OrderCreateDate,@DueDate,@FirstName,@LastName,@EmailAddress,@PhoneNumber,@StreetA,@StreetB,@PostalCode,@City,@State,@CountryCode)", con);

    cmd.Parameters.AddWithValue("@OrderNumber", OriginalOrderNumber);
    cmd.Parameters["@OrderNumber"].Value = OriginalOrderNumber;
    cmd.Parameters.AddWithValue("@OrderCreateDate", date);
    cmd.Parameters.AddWithValue("@DueDate", DueDate);
    cmd.Parameters.AddWithValue("@FirstName", FirstName);
    cmd.Parameters.AddWithValue("@LastName", LastName);
    cmd.Parameters.AddWithValue("@EmailAddress", email);
    cmd.Parameters.AddWithValue("@PhoneNumber", phone);
    cmd.Parameters.AddWithValue("@StreetA", StreetA);
    cmd.Parameters.AddWithValue("@StreetB", StreetB);
    cmd.Parameters.AddWithValue("@PostalCode", PostalCode);
    cmd.Parameters.AddWithValue("@City", City);
    cmd.Parameters.AddWithValue("@State", State_id);
    cmd.Parameters.AddWithValue("@CountryCode", country_code);

    try
    {
        con.Open();
        SqlDataReader reader1 = cmd.ExecuteReader();
        Console.WriteLine("Record inserted");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Please help me. I want these records added to my database.

I get this error:

The parameterized query '(@OrderNumber nvarchar(11),@OrderCreateDate datetime,@DueDate da' expects the parameter '@PhoneNumber', which was not supplied

If I try to insert data after while loop. it gives one record inserted.

I want all records.

Please help me.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Veerendra L Nayak
  • 17
  • 6
  • IF you need to do multiple inserts at once, you should be passing an object of type XML to your query and skip loops with one insert at a time. Possible duplicate of (https://stackoverflow.com/questions/15128999/passing-xml-string-parameter-to-sql-server-stored-procedure) – Ryan Wilson Mar 09 '18 at 13:15
  • What happened to the idea of the [short self-contained example](http://sscce.org/)? Spraying all of your code and data into a question is unlikely to get the result you want. – spender Mar 09 '18 at 13:15
  • i don't have knowledge in xml. please help me. how to do that – Veerendra L Nayak Mar 09 '18 at 13:19
  • 1
    Often that error means the parameter value is null. You should check for null conditions and use `DBNull.Value` when the variable is null. – Crowcoder Mar 09 '18 at 13:24
  • First, connection should be opened once, i.e. before while. See good example of this at https://stackoverflow.com/questions/36815927/inserting-multiple-records-into-sql-server-database-using-for-loop. Maybe you should validate all your data from XML (as Crowcoder said) before passing them as argument to further processing outside of your code. – Yarl Mar 09 '18 at 13:26
  • You are passing as a query parameter `@PhoneNumber` but you did not add it to `SqlCommand` object, so when executing the query it does not know where to get this parameter – JuanDM Mar 09 '18 at 13:28
  • `ExecuteNonQuery` is more appropriate than `ExecuteReader` for inserts. Read over all of the `Execute...` methods to get an idea of their intended uses. – Crowcoder Mar 09 '18 at 13:34

1 Answers1

0

In Your while, for each loop you'll get only one information. But you are trying to insert in Database for each loop. You have to get all information and after insert all og them in your Database.

try this:

DateTime? date=null;
DateTime? DueDate = null;
string OriginalOrderNumber = null;
string FirstName=null;
string LastName = null;
string email = null;
string StreetA = null;
string StreetB = null;
string PostalCode = null;
string City = null;
string State_id = null;
string country_code = null;
string phone = null;

XmlTextReader reader = new XmlTextReader("ecom_MailInArt_Store.xml");

while (reader.Read())
{

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:order"))
    {
        if (reader.HasAttributes)
        {
            OriginalOrderNumber = reader.GetAttribute("OriginalOrderNumber");

        }

    }

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:CreateDate"))
    {
        reader.Read();
        date = Convert.ToDateTime(reader.Value);

    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:DueDate"))
    {
        reader.Read();
        DueDate = Convert.ToDateTime(reader.Value);
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:FirstName"))
    {
        reader.Read();
        FirstName = reader.Value;

    }

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:LastName"))
    {
        reader.Read();
        LastName = reader.Value;
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:customer-email"))
    {
        reader.Read();
        email = reader.Value;
    }

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:phone"))
    {
        reader.Read();
        phone = reader.Value;
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:StreetA"))
    {
        reader.Read();
        StreetA = reader.Value;
    }

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:StreetB"))
    {
        reader.Read();
        StreetB = reader.Value;
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:PostalCode"))
    {
        reader.Read();
        PostalCode = reader.Value;
    }

    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:City"))
    {
        reader.Read();
        City = reader.Value;
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:State_id"))
    {
        reader.Read();
        State_id = reader.Value;
    }
    if ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "ns:country-code"))
    {
        reader.Read();
        country_code = reader.Value;
    }
}
SqlConnection con = new SqlConnection("Data Source=MNGNET320543D;Initial Catalog=excercise1;User id=sa;Password=Infosys123;");
SqlCommand cmd = new SqlCommand("Insert into MailInArtOrder(OrderNumber,OrderCreateDate,DueDate,FirstName,LastName,EmailAddress,PhoneNumber,StreetA,StreetB,PostalCode,City,State,CountryCode) values(@OrderNumber,@OrderCreateDate,@DueDate,@FirstName,@LastName,@EmailAddress,@PhoneNumber,@StreetA,@StreetB,@PostalCode,@City,@State,@CountryCode)", con);

cmd.Parameters.AddWithValue("@OrderNumber", OriginalOrderNumber);
cmd.Parameters["@OrderNumber"].Value = OriginalOrderNumber;
cmd.Parameters.AddWithValue("@OrderCreateDate", date);
cmd.Parameters.AddWithValue("@DueDate", DueDate);
cmd.Parameters.AddWithValue("@FirstName", FirstName);
cmd.Parameters.AddWithValue("@LastName", LastName);
cmd.Parameters.AddWithValue("@EmailAddress", email);
cmd.Parameters.AddWithValue("@PhoneNumber", phone);
cmd.Parameters.AddWithValue("@StreetA", StreetA);
cmd.Parameters.AddWithValue("@StreetB", StreetB);
cmd.Parameters.AddWithValue("@PostalCode", PostalCode);
cmd.Parameters.AddWithValue("@City", City);
cmd.Parameters.AddWithValue("@State", State_id);
cmd.Parameters.AddWithValue("@CountryCode", country_code);

try
{
    con.Open();
    SqlDataReader reader1 = cmd.ExecuteReader();
    Console.WriteLine("Record inserted");
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}

Hope helps.

Pacheco
  • 950
  • 11
  • 18