problem with SQL
-
HI, i am trying to retreive records for order with in a specified range of dates. I have created sql string as follows : //create the sql sql = "SELECT * FROM vShipments WHERE DateOrdered BETWEEN'" + txtOrderDateFrom.Text + ", '" + "AND '" + txtOrderDateTo.Text + "'"; ------------------------------------------- On execution, i am getting following error(s) : System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. at System.Data.SqlClient.SqlDataReader.Read() at InventoryControl.frmShipments.PopulateListView(String filter) in d:\shirtuniverse\inventorycontrol\frmshipments.cs:line 786 ------------------------------------------------------------ Any idea(s) : what i am doing wrong Thanx in advance
-
HI, i am trying to retreive records for order with in a specified range of dates. I have created sql string as follows : //create the sql sql = "SELECT * FROM vShipments WHERE DateOrdered BETWEEN'" + txtOrderDateFrom.Text + ", '" + "AND '" + txtOrderDateTo.Text + "'"; ------------------------------------------- On execution, i am getting following error(s) : System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. at System.Data.SqlClient.SqlDataReader.Read() at InventoryControl.frmShipments.PopulateListView(String filter) in d:\shirtuniverse\inventorycontrol\frmshipments.cs:line 786 ------------------------------------------------------------ Any idea(s) : what i am doing wrong Thanx in advance
ronin1770 wrote: //create the sql sql = "SELECT * FROM vShipments WHERE DateOrdered BETWEEN'" + txtOrderDateFrom.Text + ", '" + "AND '" + txtOrderDateTo.Text + "'"; Looking this over are you sure you need the comma after the first date. Its inside the single quote. You might try: sql = "SELECT * FROM vShipments WHERE DateOrdered BETWEEN '" + txtOrderDateFrom.Text + "' AND '" + txtOrderDateTo.Text + "'"; Steve Maier, MCSD MCAD
-
HI, i am trying to retreive records for order with in a specified range of dates. I have created sql string as follows : //create the sql sql = "SELECT * FROM vShipments WHERE DateOrdered BETWEEN'" + txtOrderDateFrom.Text + ", '" + "AND '" + txtOrderDateTo.Text + "'"; ------------------------------------------- On execution, i am getting following error(s) : System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. at System.Data.SqlClient.SqlDataReader.Read() at InventoryControl.frmShipments.PopulateListView(String filter) in d:\shirtuniverse\inventorycontrol\frmshipments.cs:line 786 ------------------------------------------------------------ Any idea(s) : what i am doing wrong Thanx in advance
ronin1770 wrote: Any idea(s) : what i am doing wrong This probably isn't what you were looking for, but I feel that I'd be remiss if I didn't point it out anyway. Since you're simply echoing user input (
txtOrderDateTo.Text) into a SQL statement, you have left yourself wide open to a SQL injection attack. Google will yield you a good set of results on a search. For convenience, here is an article[^] to get you started. Hope that helps a bit. :) --Jesse -
HI, i am trying to retreive records for order with in a specified range of dates. I have created sql string as follows : //create the sql sql = "SELECT * FROM vShipments WHERE DateOrdered BETWEEN'" + txtOrderDateFrom.Text + ", '" + "AND '" + txtOrderDateTo.Text + "'"; ------------------------------------------- On execution, i am getting following error(s) : System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. at System.Data.SqlClient.SqlDataReader.Read() at InventoryControl.frmShipments.PopulateListView(String filter) in d:\shirtuniverse\inventorycontrol\frmshipments.cs:line 786 ------------------------------------------------------------ Any idea(s) : what i am doing wrong Thanx in advance
ronin1770 wrote: Any idea(s) : what i am doing wrong So many things, it's almost funny! 1) You're using string concatentation to build an SQL statement. The gods around here will smite you for such foolishness. Like the other posters pointed out, you have opened yourself up to many an SQL injection attack. 2) There is no space between the keyword BETWEEN and the single quote next to it. There should be... 3) A comma between your first date and the keyword AND will cause an error in the SQL statement. 4) Your completely at the mercy of the user to type in the date in the EXACT format that is required by the SQL statement. We've said this a thousand times in the forums: CONVERT THIS STATEMET INTO A PARAMETERIZED QUERY! If you were to release code like this into a production application and sell it, your customers will hang you by your nads when they find out about it! The SqlParameter objects will handle filtering for SQL Injection attacks for you and handle converting the DateTime objects you supply into the correct SQL format. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome