varchar data type to a smalldatetime out-of-range exception

Question

i am working on my college project.based on asp.net and sql server.the website is about the dead stock management in a computer laboratory.when i am adding new stock to database a runtime exception " The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value " occurs

pankaj kumbhar · Answer

cn = New System.Data.SqlClient.SqlConnection() cn.ConnectionString = "Data Source=PANKAJ\PANKAJSQL;Initial Catalog=Database;Integrated Security=True" str = "insert into dsr_main values (" + i.ToString + "," str = str + "'" + Location.Text + "'," str = str + "'" + Remarks.Text + "'," str = str + "'" + Date.Today.ToShortDateString + "'," str = str + "'" + Application("username") + "'," str = str + Val(dsr_no.Text).ToString + ")" i = i + 1 cmd = New SqlCommand(str, cn) cn.Open() cmd.ExecuteNonQuery() cn.Close() End While cn = New SqlConnection() cn = New System.Data.SqlClient.SqlConnection() cn.ConnectionString = "Data Source=PANKAJ\PANKAJSQL;Initial Catalog=Database;Integrated Security=True" cn = New SqlConnection(Application("constr")) str = "insert into Main values (" + dsr_no.Text + "," str = str + "'" + dd.Text + "-" + mm.Text + "-" + YYYY.Text + "'," str = str + Quantity.Text + "," str = str + "'" + cmpnt.Text + "'," str = str + "'" + Component_Detail.Text + "'," str = str + "'" + Supplier_Name.Text + "'," str = str + "'" + Supplier_Address.Text + "'," str = str + "'" + Invoice_Bill_no.Text + "'," str = str + "'" + dd0.Text + "-" + mm0.Text + "-" + YYYY0.Text + "'," str = str + Val(Rate.Text).ToString + "," str = str + Val(Excise_duty.Text).ToString + "," str = str + Val(Sales_Tac.Text).ToString + "," str = str + Val(Extra_Charges.Text).ToString + "," str = str + Val(cost.Text).ToString + "," str = str + Val(Total_cost.Text).ToString + "," If (Not Application("dept") = "ADMIN") Then str = str + "'" + dept.Text + "'," Else str = str + "'" + Dept_name.Text + "'," End If str = str + "'" + Now.Day.ToString + "-" + Now.Month.ToString + "-" + Now.Year.ToString + "'," str = str + "'" + Application("username") + "')" cmd = New SqlCommand(str, cn) cn.Open() cmd.ExecuteNonQuery() cn.Close() Application("msg") = "RECORD ADDED" Response.Redirect("~/welcome.aspx") End If End Sub

Ramesh Palanivel · Answer

Hi Pankaj, Is your column also varchar datatype or its datetime type?

pankaj kumbhar · Answer

str = str + "'" + dd.Text + "-" + mm.Text + "-" + YYYY.Text + "',"

Tapan Patel · Answer

what date(string) you are providing for the input?

Ramesh Palanivel · Answer

Hi Pankaj, If you want to convert in c# use Convert.DateTime() method If you want convert in sql server try below code Cast ( '7/7/2011' as datetime ) method

pankaj kumbhar · Answer

how toconvert the format to datetime

Ramesh Palanivel · Answer

Hi Pankaj, The issue is from , you are trying to store string format date in date time field.. Kindly convert the format to datetime before you insert.