How to insert system datetime in sql server database

Question

0.00/5 (No votes)

See more:

Hi I have written a program to insert some values in database but i am getting sql formate exception in date. below is my program

SQL

enum RefundStatus
   {
       Dispute = 1,
       DisputeRes = 2
   };
   class Program
   {
       public void InsertRecord(int i)
       {
          int RFStatus= Convert.ToInt16((i%2 ==0)?RefundStatus.Dispute:RefundStatus.DisputeRes);
          Random rnd = new Random();
           long XUID = rnd.Next(1,100);
           DateTime myDateTime = DateTime.Now;


           SqlConnection cnn = new SqlConnection("Data Source=XBLVMTST005;Initial Catalog=RefundsDB;Integrated Security=SSPI");
           cnn.Open();

           String str = string.Format(@"insert into [dbo].[CustomerInfo] (UserXUID,RefundType,RefundDate,
           RefundDeniedReason,DisputeCustData,CustomerEmail,CustomerName,Currency,Country,
           Refund,FraudRuleStatus,RefundStatus,DisputeStatus,AllowComments,LastUpdatedTime)
           values ('{0}','{1}','{2},{3}','{4}','{5},{6}','{7}','{8},{9}','{10}','{11},{12}','{13}','{14}'",
 XUID, "", "", "", "", "", "", "", "", "", "", RFStatus, "", "", myDateTime);
           SqlCommand cmd = new SqlCommand(str, cnn);

           cmd.ExecuteNonQuery();
       }

       static void Main(string[] args)
       {
           Program obj = new Program();
           {
           for (int i = 0; i < 2; i++)
               obj.InsertRecord(i);
               Thread.Sleep(2000);
           }
       }
   }

Here I am getting

SQLException-Incorrect syntax near '6/8/2014 7:14:56 AM'.

Posted 8-Jun-14 4:23am

richa11

Updated 8-Jun-14 4:50am

PIEBALDconsult

v2

Add a Solution

Comments

[no name] 8-Jun-14 10:29am

Use a parameterized query and your problem will most likely resolve itself.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

DamithSL · Answer 1 · 2014-06-08T04:29:00

Solution 1

You better use parameters in SQL statement
sample code:

C#

SqlCommand cmd = new SqlCommand("insert into  [dbo].[CustomerInfo] (UserXUID, LastUpdatedTime) values (@UserXUID, @LastUpdatedTime)");
cmd.Parameters.AddWithValue("@UserXUID", XUID);
cmd.Parameters.AddWithValue("@LastUpdatedTime", DateTime.Now);

This also will save you from SQL injection attacks.

Few additional things, you are calling insert in a loop but not correctly handling the closing of the connection. you better use using block as below, it will close/dispose the object even you get exception middle of the process. When you set null values; use DBNull.Value

C#

using(SqlConnection cnn = new SqlConnection(yourconnectionString))
{
   cnn.Open();
   using(SqlCommand cmd = new SqlCommand("insert into  [dbo].[CustomerInfo] (UserXUID,RefundType, LastUpdatedTime) values (@UserXUID,@RefundType, @LastUpdatedTime)"))
   {
       cmd.Parameters.AddWithValue("@UserXUID", XUID);
       cmd.Parameters.AddWithValue("@RefundType", DBNull.Value); //when you need null value
       cmd.Parameters.AddWithValue("@LastUpdatedTime", DateTime.Now);
       cmd.ExecuteNonQuery();
   }
}

Posted 8-Jun-14 4:29am

DamithSL

Updated 8-Jun-14 5:57am

v3

Comments

richa11 8-Jun-14 11:31am

I used parameterized query also but again exception..

public void InsertRecord(int i)
{
int RFStatus = Convert.ToInt16((i % 2 == 0) ? RefundStatus.Dispute : RefundStatus.DisputeRes);
Random rnd = new Random();
long XUID = rnd.Next(1, 100);
String myDateTime = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.fff");
DateTime LastUpdateTime = DateTime.Parse(myDateTime);

SqlConnection cnn = new SqlConnection("Data Source=WN7-CDDGMV1\\SQLEXPRESS;Initial Catalog=RefundsDB;Integrated Security=SSPI");
cnn.Open();
String str = @"insert into [dbo].[CustomerInfo] (RefundID,UserXUID,RefundType,RefundDate,
RefundDeniedReason,DisputeCustData,CustomerEmail,CustomerName,Currency,Country,
Refund,FraudRuleStatus,RefundStatus,DisputeStatus,AllowComments,LastUpdatedTime) values(@RefundID,@UserXUID,@RefundType,@RefundDate,@RefundDeniedReason,@DisputeCustData,@CustomerEmail,@CustomerName,@Currency,@Country,@Refund,@FraudRuleStatus,@RefundStatus,@DisputeStatus,@AllowComments,@LastUpdatedTime) ";
SqlCommand cmd=new SqlCommand(str,cnn);
cmd.Parameters.AddWithValue("@RefundID", null);
cmd.Parameters.AddWithValue("@UserXUID",XUID);
cmd.Parameters.AddWithValue("@RefundType", null);
cmd.Parameters.AddWithValue("@RefundDate", DateTime.Now);
cmd.Parameters.AddWithValue("@RefundDeniedReason", null);
cmd.Parameters.AddWithValue("@DisputeCustData", null);
cmd.Parameters.AddWithValue("@CustomerEmail", null);
cmd.Parameters.AddWithValue("@CustomerName", null);
cmd.Parameters.AddWithValue("@Currency", null);
cmd.Parameters.AddWithValue("@Country", null);
cmd.Parameters.AddWithValue("@Refund", null);
cmd.Parameters.AddWithValue("@FraudRuleStatus", null);
cmd.Parameters.AddWithValue("@RefundStatus", RFStatus);
cmd.Parameters.AddWithValue("@DisputeStatus", null);
cmd.Parameters.AddWithValue("@AllowComments", null);
cmd.Parameters.AddWithValue("@LastUpdatedTime", LastUpdateTime);
cmd.ExecuteNonQuery();
}

Whats wrong?

DamithSL 8-Jun-14 11:37am

what is the error?
if you have nullable columns ekip all the columns you going to set null values.

[no name] 8-Jun-14 11:40am

Since you did not say what exception you are seeing on your screen and we cannot see your screen, my guess would be that you did not include @LastUpdatedTime in your SQL statement.