Click here to Skip to main content
15,120,078 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
can save those information when SQL table column data types is nvarchar(MAX) but i need to save data to the .

i need to know , how to save to this table ,

this is my database and i need to save data in to the table, but i cannot use my code do do it , but i can save in to the database using nvarchar(MAX) data format

database image http://i.stack.imgur.com/8FLfH.jpg
C#
protected void btnSaave_Click(object sender, EventArgs e)
{
    int rowIndex = 0;
    StringCollection sc = new StringCollection();
    if (ViewState["CurrentData"] != null)
    {
        DataTable dtCurrentTable = (DataTable)ViewState["CurrentData"];
        DataRow drCurrentRow = null;
        if (dtCurrentTable.Rows.Count > 0)
        {
            for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
            {
                var dtDealerCode = txtIDealerCode.Text;
                var dtInvoiceNo = txtInvoiceNumber.Text;
                var dtInvoiceDate = txtInvoiceDate.Text;
                var dtItemIdentityCode = (Label)GridView1.Rows[rowIndex].Cells[1].FindControl("ItemCode");
                var dtPurchasingPrice = (Label)GridView1.Rows[rowIndex].Cells[3].FindControl("UnitPrice");
                var dtDiscountRate = txtDiscount.Text;
                var dtDiscount = txtProductDiscount.Text;
                var dtIssueMode = ddlIssueMode.SelectedValue;
                var dtQty = (Label)GridView1.Rows[rowIndex].Cells[6].FindControl("Quantity");
                var dtTotal = (Label)GridView1.FooterRow.FindControl("GetTotal");
                var dtExpireDate = (Label)GridView1.Rows[rowIndex].Cells[5].FindControl("ExpiaryDate");
                var dtBatchNumber = (Label)GridView1.Rows[rowIndex].Cells[4].FindControl("Batch");
                var dtUploadedStatus = txtInvoiceDate.Text;
                var dtInsertedDate = "1";
                var dtUploadedDate = txtInvoiceDate.Text;
                var dtForce = txtForce.Text;
                var dtPrinciple = txtPrinciple.Text;
                var NewTotal = (Label)GridView1.FooterRow.FindControl("GetQuantity");
                // (Label)GridView1.Rows[rowIndex].Cells[7].FindControl("Total");
                //(Label)GridView1.Rows[rowIndex].Cells[2].FindControl("Product")
                sc.Add(dtDealerCode + "," + dtInvoiceNo + "," + dtInvoiceDate + "," + dtItemIdentityCode.Text + "," + dtPurchasingPrice.Text + "," + dtDiscountRate + "," + dtDiscount + "," + dtIssueMode + "," + dtQty.Text + "," + dtTotal.Text + "," + dtExpireDate + "," + dtBatchNumber.Text + "," + dtUploadedStatus + "," + dtInsertedDate + "," + dtUploadedDate + "," + dtForce + "," + dtPrinciple + "," + dtPrinciple + "," + NewTotal.Text);
                rowIndex++;
            }

            InsertRec(sc);
        }
    }
}

im using this part to save in to databases. I need to save data in to the table, but i cannot use my code do do it , but i can save in to the database using nvarchar(MAX) data format
C#
private void InsertRec(StringCollection sc)
    {
        var conn = new SqlConnection(GetConnectionString());
        var sb = new StringBuilder(string.Empty);
        var splitItems = (string[])null;
        foreach (string item in sc)
        {
            const string sqlStatement =
                "INSERT INTO DEL_PurchasesLines1 (DealerCode,InvoiceNo,InvoiceDate,ItemIdentityCode,PurchasingPrice,DiscountRate,Discount,IssueMode,Qty,Total,ExpireDate,BatchNumber,UploadedStatus,InsertedDate,UploadedDate,Force,Principle,NewTotal) VALUES";

            if (item.Contains(","))
            {
                splitItems = item.Split(",".ToCharArray());
                sb.AppendFormat("{0}('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2], splitItems[3], splitItems[4], splitItems[5], splitItems[6], splitItems[7], splitItems[8], splitItems[9], splitItems[10], splitItems[11], splitItems[12], splitItems[13], splitItems[14], splitItems[15], splitItems[16], splitItems[17]);
            }
        }

        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sb.ToString(), conn) { CommandType = CommandType.Text };
            cmd.ExecuteNonQuery();

            Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);

        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            conn.Close();
        }
    }
Posted
Updated 19-Sep-13 20:52pm
v2

1 solution

Are you getting any errors or its not showing any error? Run the SQL Profiler and see what insert statement it is generating and try running that query in the query window
   
Comments
DevB001 20-Sep-13 3:08am
   
i can use this table to save my data using that code.
Table 1- http://i.imgur.com/nVwsmWT.jpg

but i cannot save to this table
Table 2 - http://i.stack.imgur.com/8FLfH.jpg

i know their is a problem with data types .. so how to fix it in my code

output error-
Additional information: Insert Error:The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.
Madhu Nair 20-Sep-13 3:22am
   
This error normally occurs when you are specifying wrong value to the column. Please provide with the sql statement you are using for inserting data along with the table structure

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900