Convert data to save in database

Question

I can save those information when SQL Server table column datatype is nvarchar(MAX) but I need to save data with another data types.

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

    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);
            }
        }
    }

I'm using this part to save in to databaseas,

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

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();
        }
    }

when i save in to table with data type nvarchar(MAX) , i can take message from this point Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true); but when i change the data type in the table (http://i.stack.imgur.com/8FLfH.jpg), output is string msg = "Insert Error:"; — Dilan Wickramarathna, Sep 20 '13 at 05:41
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 — Dilan Wickramarathna, Sep 20 '13 at 06:27
Some value is exceeding its limit in table 2..IF you paste your stack trace then we can identify that column.. — Sasidharan, Sep 20 '13 at 06:39
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. — Dilan Wickramarathna, Sep 20 '13 at 06:59
Yep,the only thing is date column datatype..Try replacing it with datetime. — Sasidharan, Sep 20 '13 at 07:05
already Column data type is datetime in the [Table 2](http://i.stack.imgur.com/8FLfH.jpg) ,, but output from my code is not date time format it is varchar so how to change it in code, please help me .. — Dilan Wickramarathna, Sep 20 '13 at 07:14
you should create a method,pass the varchar value to that method,convert it to datetime ,then insert.. — Sasidharan, Sep 20 '13 at 07:16
can you helo me to do it ,, with small sample with this code... some modification — Dilan Wickramarathna, Sep 20 '13 at 08:28
http://stackoverflow.com/questions/14653859/convert-string-to-datetime-in-sql-server — Sasidharan, Sep 20 '13 at 08:44
@dilan3gb Please let us know the format of `InsertedDate` which you needed to change, I mean show us Nvarchar datetime format . — Suraj Singh, Sep 20 '13 at 08:54
one advice try to use parameterized query, this insert query of yours is very error prone . — Suraj Singh, Sep 20 '13 at 08:59

score 0 · Answer 1 · answered Sep 20 '13 at 08:45

pass ur nvarchar value to this method..

public static DateTime DateParse(string date)
        {
            date = date.Trim();
            if (!string.IsNullOrEmpty(date))
            return DateTime.Parse(date, new System.Globalization.CultureInfo("en-GB"));
            return new DateTime();
        }

Suraj Singh · Accepted Answer · 2013-09-20T10:09:46.880

You can apply convert.ToDateTime at the time you are fetching data from Datatable , However i don't feel you need to do all this split functions to fetch values for your Insert query you can directly apply the stringCollection sc string to values field in your insert query no transformation needed to done here .

sc.Add(dtDealerCode + "," + dtInvoiceNo + "," + Convert.ToDateTime(dtInvoiceDate) + "," + dtItemIdentityCode.Text + "," + dtPurchasingPrice.Text + "," + dtDiscountRate + "," + dtDiscount + "," + Convert.ToDateTime(dtIssueMode) + "," + dtQty.Text + "," + dtTotal.Text + "," + Convert.ToDateTime(dtExpireDate) + "," + dtBatchNumber.Text + "," + dtUploadedStatus + "," + dtInsertedDate + "," + Convert.ToDateTime(dtUploadedDate) + "," + dtForce + "," + dtPrinciple + "," + dtPrinciple + "," + NewTotal.Text);

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"(yourstringCollection);

One more thing here you are fetching only controls not their values to fetch values from control use .Text .

 var dtExpireDate = (Label)GridView1.Rows[rowIndex].Cells[5].FindControl("ExpiaryDate").text;

Convert data to save in database

2 Answers2