CODEWITHSUNDEEP
c#asp.netsql-server-2008ado.net
jeneous
jeneous

Reputation: 45

Error While inserting date in asp.net

On inserting the date via asp.net to SQL Server 2008 an error is happening.

Insert statement

Insert into comment(CommentFrom, CommentTo, Comment, Cmntonscrap, Cmnttime) 
Values('" + Session["UserName"] + "','" + email.Text.ToString() + "','"  + 
       txt.Text.ToString().Trim() + "','" + cscrap.Text.ToString().Trim()  + 
       "','" + DateTime.Now.ToString().Trim() + "')";

Error:

string or binary data would be truncated,Statement is terminating

Upvotes: 1

Views: 505

Answers (3)

SidAhmed
SidAhmed

Reputation: 2352

Concatinate an sql statement is a bad approch (Security problem), your database will be an easy target of the Sql injections.

I suggest you use a stored procedure to add the or modify the data you want, and use SqlParameters to send the inputs from the user interfaces.

May help : How to create stored procedure

Here's a code example to show you how to call the stored procedure with parameters using C#

//The method that call the stored procedure
public void AddComment()
{
    using(var connection = new SqlConnection("ConnectionString"))
    {
        connection.Open();
        using(var cmd = new SqlCommand("storedProcedure_Name", connection) { CommandType = CommandType.StoredProcedure })
        {
            cmd.Parameters.AddWithValue("@CommentFrom", commandFrom);
            cmd.Parameters.AddWithValue("@CommentTo", commentTo);
            //...And  so on

            cmd.ExecuteNonQuery();
        }
    }
}

A example on how to create a stored procedure

CREATE PROCEDURE storedProcedure_Name
    -- Add the parameters for the stored procedure here
    @CommentFrom nvarchar(255) = NULL,
    @CommentTo nvarchar(255) = NULL
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO Comments (CommentFrom, CommentTo) VALUES(@CommentFrom, @CommentTo)
END
GO

Upvotes: 1

nunespascal
nunespascal

Reputation: 17724

Your problem is not with the date.
Its with the string data being too large for a column(probably a varchar), that you are trying to insert the data into.

I would check the length of your Comment and txt.Text and see that the data can be inserted.

Use a sql type datetime for your DateTime. Storing dates in a text column is inviting a lot of trouble.

  • It takes more storage space.
  • Searching through that data will be really difficult.
  • You will never be able to change timezones.

Upvotes: 3

Shiridish
Shiridish

Reputation: 4962

It means that the data type of the field you have given for the date column or any other column(assuming varchar()) in your database table is less than the size your trying to insert. Increase the size you have assigned

Upvotes: 1

Related Questions