saving datetimepicker and datagridview data into db

Question

0.00/5 (No votes)

See more:

my form contains one dateTimePicker and a datagridview and i want to save data into sql db
i am using the codes below but keeps given an error. the error msg "conversion of a varchar data type to a datetime data type resulted in an out of range value"
please help me out

C#

for (int i = 0; i < dgvSales.Rows.Count -1; i++)
                        {
                            ;
                            string StrQuery = null;
                            StrQuery = @"INSERT INTO tblstock VALUES ('" + dgvSales.Rows[i].Cells["Product ID"].Value + "', '" + this.dtpStockdate.Value + "', '" + dgvSales.Rows[i].Cells["Product Name"].Value + "', '" + dgvSales.Rows[i].Cells["Quantity"].Value + "')";
                            comm.CommandText = StrQuery;
                            conn.Open();
                            comm.ExecuteNonQuery();
                            conn.Close();
                        }

Posted 30-Jul-13 9:52am

mikeoabban

Updated 30-Jul-13 10:18am

v3

Add a Solution

Comments

[no name] 30-Jul-13 15:57pm

You would think that after being a member for this long and asking 45 questions, you would know by now that we cannot read your mind to know what "keeps given an error" means.

ridoy 30-Jul-13 16:07pm

what error?

4 solutions

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)

Adarsh chauhan · Answer 1 · 2013-07-30T21:32:00

Hi

I would suggest to use parametrized query. It will prevent SQL injection.

try this...

C#

comm.CommandText = "INSERT INTO tblstock VALUES(@ProductID,@Stockdate,@ProductName,@Quantity)";
comm.Parameters.Add(new SqlParameter("@ProductID", dgvSales.Rows[i].Cells["Product ID"].Value));
comm.Parameters.Add(new SqlParameter("@Stockdate", dtpStockdate.Value.date));
comm.Parameters.Add(new SqlParameter("@ProductName", dgvSales.Rows[i].Cells["Product Name"].Value));
comm.Parameters.Add(new SqlParameter("@Quantity", dgvSales.Rows[i].Cells["Quantity"].Value));
Comm.Connection=conn;
conn.Open();
comm.ExecuteNonQuery();
conn.Close()

idenizeni · Answer 2 · 2013-07-30T10:43:00

Solution 3

In your StrQuery variable, try replacing this....

C#

this.dtpStockdate.Value

with this...

C#

this.dtpStockdate.Value.ToShortDateString();

Posted 30-Jul-13 10:43am

idenizeni

Updated 30-Jul-13 10:44am

v2

ridoy · Answer 3 · 2013-07-30T10:12:00

Explore these and then try yourself..
How to INSERT date into sql db date column using dateTimePicker?[^]
http://stackoverflow.com/questions/3147258/insert-datetimepicker-text-in-sql-database[^]
http://stackoverflow.com/questions/12776618/read-data-from-datagridview-and-save-to-database[^]
http://stackoverflow.com/questions/3723646/how-to-save-data-from-a-datagridview-into-database-c-sharp-winforms[^]

Kschuler · Answer 4 · 2013-07-30T10:07:00

Without knowing the exact error message...it's hard to help you.

My guess it that you're getting a data type mismatch type error. I would guess that the Quantity field in your table is numeric and you are passing it in surrounded by single quotes.

You really should look into using parameters. It will help with problems like this and will prevent SQL injection if users are able to manually type into the grid.

saving datetimepicker and datagridview data into db

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0