CODEWITHSUNDEEP
c#sqlwinformsms-access
Sanjeev4evr
Sanjeev4evr

Reputation: 413

Getting Error while Inserting the Query

Please help me to find the error in the below statements where i am getting error in only from code.

Create table Statement

cmmd.CommandText = "CREATE TABLE Users([User_ID] AUTOINCREMENT PRIMARY KEY, UserName Text(50), UserPwdText(200), [IsActive] YesNo, [ModBy] Long REFERENCES Users (User_ID), [ModDate] Date)";
cmmd.ExecuteNonQuery();

In the database table is created successfully.
Here I am Inserting values through code.

cmmd.CommandText = @"INSERT INTO Users ([UserName], [UserPwd], [IsActive], [ModBy],
           [ModDate])VALUES('Admin','kov1ozyKAjas8awoej3oijhrqoi6q=', 'true', '1', 
           'Datetime.Now.Date')";
cmmd.ExecuteNonQuery();

The exception is

Data type mismatch in criteria expression

Upvotes: 0

Views: 100

Answers (6)

Shell
Shell

Reputation: 6849

There are two errors in your query first is double bracket and second one is DateTime.Now.Date. it is a .Net feature that you cannot execute it in string. And here you don't need to add @ at the beginning of the string. Also change 'True' to Yes

try this

cmmd.CommandText = "INSERT INTO Users ([UserName], [UserPwd], [IsActive], 
            [ModBy],[ModDate])VALUES('Admin','kov1ozyKAjas8awoej3oijhrqoi6q=',
            Yes, '1', Date())";
cmmd.ExecuteNonQuery();

Upvotes: 4

Mihai Hantea
Mihai Hantea

Reputation: 1743

UPDATE:

The recommended way is to use parameters. Using a parameterized query not only steps around SQL injection issues, but solves SQL Server date localization issues as well.

SqlCommand cmd = new SqlCommand(@"INSERT INTO Users ([UserName], [UserPwd], [IsActive], [ModBy], [ModDate])VALUES('Admin','kov1ozyKAjas8awoej3oijhrqoi6q=', 'true', '1', @dateValue)");
cmd.Parameters.AddWithValue("@dateValue", DateTime.Now.Date);
.......
// do same for the other.

You have 2 parentheses after VALUES in your code

Check this :

string format = "yyyy-mm-dd"; //or with date yyyy-mm-dd hh:mm:ss
cmmd.CommandText = string.Format(@"INSERT INTO Users ([UserName], [UserPwd], [IsActive], [ModBy], [ModDate])VALUES('Admin','kov1ozyKAjas8awoej3oijhrqoi6q=', 'true', '1', '{0}')", DateTime.Now.Date.ToString(format));

Also you should use parameters.

Upvotes: 2

user3484362
user3484362

Reputation:

Try this, The datetime is a string if you put inside of the double quote or single quote, So you need the query look like. 

cmmd.CommandText = @"INSERT INTO Users ([UserName], [UserPwd], [IsActive], 
            [ModBy],[ModDate])VALUES('Admin','kov1ozyKAjas8awoej3oijhrqoi6q=',
            'true', '1', '" + DateTime.Now.Date + "')";
                    cmmd.ExecuteNonQuery();

I hopes my query is does not return any errors.

Upvotes: 1

Sefa
Sefa

Reputation: 8992

With DateTime.Now.Date, are u trying to get the date?

Try this:

cmmd.CommandText = @"INSERT INTO Users ([UserName], [UserPwd], [IsActive], [ModBy], [ModDate]) VALUES ('Admin', 'kov1ozyKAjas8awoej3oijhrqoi6q=', 'true', '1', Datetime.Now.Date)";
cmmd.ExecuteNonQuery();

Upvotes: 2

The Hungry Dictator
The Hungry Dictator

Reputation: 3484

First of all check for the DataTypes of each Fields. And make sure that you have used appropriate DataTypes as per the tables. and if everythings ok then just try below query.

cmmd.CommandText = @"INSERT INTO Users ([UserName], [UserPwd], [IsActive], 
            [ModBy],[ModDate])VALUES('Admin','kov1ozyKAjas8awoej3oijhrqoi6q=',
            'true', '1', 'Datetime.Now.Date')";
cmmd.ExecuteNonQuery();

Upvotes: 5

John Saunders
John Saunders

Reputation: 161773

You have a typo:

cmmd.CommandText = @"INSERT INTO Users ([UserName], [UserPwd], [IsActive], [ModBy], [ModDate])VALUES('Admin','kov1ozyKAjas8awoej3oijhrqoi6q=', 'true', '1', 'Datetime.Now.Date')";

One parenthesis, not two.

Upvotes: 2

Related Questions