Click here to Skip to main content
15,031,361 members
Please Sign up or sign in to vote.
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
Updated 30-Jul-13 10:18am
v3
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?

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.
   
In your StrQuery variable, try replacing this....
C#
this.dtpStockdate.Value

with this...
C#
this.dtpStockdate.Value.ToShortDateString();
   
v2
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()
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900