Problem building SQL text query with parameters / "Syntax error converting datetime from character string" error message

Hi folks,

 I keep looking at these SQL text query and I cannot figure out what I am doing wrong.

If I include wrap the dates with quotes, I get the "Syntax error converting datetime from character string" error message, but I don't include them I don't get anything which is also wrong, because there is some data to be displayed.

 Bottom line, what do you think I am doing wrong when building the SQL query?

1                DateTime dateFrom = txtDateFrom.SelectedDate;
2 DateTime from = new DateTime(dateFrom.Year, dateFrom.Month, dateFrom.Day, 0, 0, 0);
3
4 DateTime dateTo = txtDateTo.SelectedDate;
5 DateTime to = new DateTime(dateTo.Year, dateTo.Month, dateTo.Day, 23, 59, 59);
6
7 string template = lstTemplate.SelectedValue;
8
9 String sql = @"SELECT BATCHES.BATCHID, BATCHDEFS.BATCHDEFNAME, BATCHES.REALPAGECNT, QUEUES.QUEUENAME, BATCHES.CREATIONTIME
10 FROM BATCHES INNER JOIN BATCHDEFS
11 ON BATCHES.BATCHDEFID = BATCHDEFS.BATCHDEFID
12 INNER JOIN QUEUES
13 ON BATCHES.QUEUEID = QUEUES.QUEUEID
14 WHERE (BATCHES.CREATIONTIME)
15 BETWEEN '@from' AND '@to' AND UPPER(BATCHDEFS.BATCHDEFNAME) = '@template' ORDER BY BATCHES.BATCHID;"
;
16
17 SqlCommand command = new SqlCommand(sql);
18
19 command.Parameters.AddWithValue("@from", from);
20 command.Parameters.AddWithValue("@to", to);
21 command.Parameters.AddWithValue("@template", template.ToUpper());
22
23 grd.DataSource = getDataReader(command);
24 grd.DataBind();
 Thank you for your help!

...Alex

 

0
neualex
3/23/2009 9:20:15 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
1080 Views

Similar Articles

[PageSpeed] 31

 String sql = "SELECT BATCHES.BATCHID, BATCHDEFS.BATCHDEFNAME, BATCHES.REALPAGECNT, QUEUES.QUEUENAME, BATCHES.CREATIONTIME
10                               FROM BATCHES INNER JOIN BATCHDEFS
11                               ON BATCHES.BATCHDEFID = BATCHDEFS.BATCHDEFID
12                               INNER JOIN QUEUES
13                               ON BATCHES.QUEUEID = QUEUES.QUEUEID
14                               WHERE (BATCHES.CREATIONTIME)
15                               BETWEEN @from AND @to AND UPPER(BATCHDEFS.BATCHDEFNAME) = @template ORDER BY BATCHES.BATCHID;"
;
................


Limno

0
limno
3/23/2009 10:08:51 PM

 Blind me ;(

Thank you for your prompt response.

...Alex

0
neualex
3/23/2009 11:27:51 PM
Reply:

Similar Artilces:

sql insert "Conversion failed when converting from a character string to uniqueidentifier." error with parameters
Hi,I'm new at asp .net and am having a problem. On a linkbutton click event, I want to insert into my db a row of data which includes two parameters. Param1 is the id of the logged in user, and Param2 is  <%#DataBinder.Eval(Container.DataItem, 'UserId')%> which is the username of a user given through a datalist.When I execute the query:   "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)"I get the error: Conversion failed when converting from a character string to uniqueidentifier.I am trying to insert these into my table aspnet_...

Error Message "Failed to convert parameter value from a String to a Int32." but can't find the problem
Hello,  I'm working on a sample ecommere web application.  So far, I have a datalist that displays departments from a database, which works fine.  Then another datalist shows up when a department is selected with categories, again, works fine.  Then ANOTHER datalist which shows products in a datalist based on the category selected, all works fine. When the products show up, their name shows up as a link to click on to view the product details, which goes to a seperate page and shows a full 2 pictures if available, the full length description, and price.  My big...

SQL Server 2005 error: "Incorrect syntax near "-" "
Hello, I have an application written in visual basic which connects to a SQL Server 2005In DB of SQL Server I have many tables. Table names can contain chars like: "-",  "\",  ".", etc. When I execute the instruction in visual basic sTableName= "Table-3" ' or ""Table-3"" or "Table_1" or sTableName= """" + "Table_1" + """"   -> """" = 4 quates (2 for string identify and two for writing in string a quate chr(34)->" strQu...

SQL Server 2005 error: "Invalid object name" / "Incorrect syntax near"
Hi All,This problem isent strictly to do with ASP.NET but I am creating an ASP.NET web application.Just a quick overview, I have recently had to reinstall windows on my machine becuase of an unrelated matter so before I did so i created an SQL script using the SQL Server Database Publishing Wizard so that the SQL Database could be recreated exaclty as it was.I have just reistalled SQL Server 2005 and the Managment Studio and have attempted to run the script.Once the script is run there are literally thousands of the following two errors:Invalid object nameandIncorrect syntax near ...It is ...

SQL Server Error: "Arithmetic overflow error converting numeric to data type numeric"
I'm still quite new to SQL, and so I may be missing something quite obvious. Apologies in advance if that's the case. I've included my query at the bottom of the message as it is quite long; my problem is that I get "Arithmetic overflow error converting numeric to data type numeric" when the first parameter to my COALESCE calls is not NULL. That is, the COALESCEs and the query work as I expect when SUM(cust_vw_actual.Actual) is NULL (they return 0.00), but if it isn't I get the error. I am also getting "Warning: Null value eliminated from aggregate.&...

Why I don't get "Plain Text" error messages from MS-SQL
Hello, I get more than less jargon error messages from Microsoft SQL, as Interbase can give something sane like "Foreignkey error SomeForeighKey on table SomeWeirdTableName" While with MS-SQL I get something like: "SQL State: 01000, SQL Error Code: 3621 SQL State: 23000, SQL Error Code: 547" which tells absolutely nothing, and takes ages to trace the actual error. I've seen sane error message from MS-SQL with our product maybe once or twice, so it can happen :) . Any ideas where to look at this??? -TP- ...

Error in building SQL query within "LIKE" statement for TableAdapter in Design Section
ASP.net 2.0 (VB), SQL Server 2005:While creating a new TableAdapter in design section, I'm using the query builder and trying to write a query within "LIKE" statement as below -Example1: SELECT * FROM table WHERE field LIKE @'%TextBoxData%'Example2: SELECT * FROM table WHERE field LIKE '%@TextBoxData%' but these query doesn't work...error in building query...any clue to make it work? If I remove "@" sure the query will work with normal but '%TextBoxData%' will become a hardcoding value...this is not I want...I want make the TextBoxData become a flexible value depend on t...

Error Message "Syntax error in FROM clause"
This code doesn't work ( see error message below), help please...         private bool initializeBilling(int billId){            string qry = "Insert into billing (parid,bilid,spcid,bilcurrent,bilcurtrash,bilcursewer,bilcurpet,bilcurothers) " +                         " select [space].parid,200501,[space].spcid,[rent].renrent,[rent].rentrash,[rent].rensewer,[rent].renpet,[rent].renoth...

"connections to SQL server files (*.mdf)" error message in Visual studio when adding a new SQL database
Hope you can help. Running Visual Studio 2005 and SQLServer2005.  I have created a new project and am attempting to add a new SQL database to it.  When I select the SQL database I get an "Connections to SQL server files (*.mdf) requires SQL Server Express to function properly" error message.  I also tried to add an existng database and it errors out as well "The file can not be opened as it is being used by another process.....".  However, I can create a new data source connection to existing SQL databases.  One caveat though.  I can ...

upgrade error:"CONNECTIVITY ERROR:DB-Library: "SQL Server connection timed out."
PLEASE HELP OH WISE SYBASE GURUS! upgrading 11.9.2 to 12.0. unloaded software. rebooted server. selected "upgrade server" in Configure Sybase Servers window. typed in sa password. got "CONNECTIVITY ERROR: DB-Library: "SQL Server connection timed out." -- Chuck Boyce chuck_boyce@operamail.com http://www.geocities.com/chuck_boyce_jr/ Yahoo Messenger ID: chuck_boyce_jr http://messenger.yahoo.com AOL Instant Messenger ID: sg95m476 http://www.aol.com/aim/ ICQ Messenger ID: 41449031 http://www.icq.com ...

Update a date within SQL gives me an error "Can't convert String to Date"
Good afternoon/Morning I am hoping someone can help me with a problem I am having. I am trying to update a date value but I get the convert error. I have tried the following code but with no joy. Can anyone shed some light on what I need to do to enable the converting of a string into Date?private void UpdateSingleItem(string StoredProcedure) { //Connect to stored procedure //Create a DataAdapter, and then provide the name of the stored procedure.SqlDataAdapter MyDataAdapter = new SqlDataAdapter(StoredProcedure, MyConnection); //Set the command type as StoredProcedure.MyData...

Help on Error message: "An error occurred while try to load the string resources (GetModuleHandle failed with error "
Hi I have compiled my project in VS.NET (ASP.NET web application in C#) without any build errors. This was in the process of establishing a local setup using the files from the deployed server. Now after copying all the relevant files and compiling them, when I try to browse from IIS (inetmgr) with an appropriate start page (Login.aspx), I get the following error message: An error occurred while try to load the string resources (GetModuleHandle failed with error -2147023888). Description: An unhandled exception occurred during the execution of the current web request. Please revie...

"Not connected to SQL Database" Error Message
Hello All ! Last night, I left ISQL running on my machine. This morning, when I went to run a query in ISQL, I got the message: "Not connected to SQL Database". I understand the message pretty clearly. But, can anyone tell me at what point is a connection "dropped"? After how long? and lastly, what can I do to prevent or extend the time before I lose the connection? Thanks in advance, Everett In the online help you can look at the connection parameters by searching on "DBSRV50". The parameter you want to modify is "-ti". Here is ...

SQL statement failed: (-305) I/O error Fatal error: A disk read from file "SomeDatabase.db" failed with error code: (38)
SQL Anywhere 10.0.1.3831 E. 2009-09-11 21:55:41. SQL statement failed: (-305) I/O error Fatal error: A disk read from file "D:\FHMSL\iFHMS\Data\Feedlot111.db" failed with error code: (38) -- transaction rolled back E. 2009-09-11 21:55:41. Error while executing hook procedure sp_hook_dbmlsync_end. Received this error while running a proc during sync. Tried to validate the db in Sybase Central resulting in the same error. I saw the post at http://groups.google.com/group/sybase.public.sqlanywhere.general/browse_thread/thread/b5d958904160e576?fwc=1 and am wondering if th...

Web resources about - Problem building SQL text query with parameters / "Syntax error converting datetime from character string" error message - asp.net.sql-datasource

Resources last updated: 1/18/2016 5:54:11 PM