UPDATE SQL query using WHERE and a date/time data type... Multiple changes...

[Chris Walter]

I'm using the LabView Database Connectivity Toolset and am using the following query...

UPDATE IndexStation
SET Signal_Size=200
WHERE 'StartTime=12:05:23'

Now the problem is that this command seems to update all rows in the table IndexStation... Not just specifically the row where StartTime=12:05:23

I have tries all sorts of {} [] / ' " around certain characters and column names but it always seems to update all rows...

I've begun to use the SQL query tab in Access to try and narrow down as to why this happens, but no luck!

Any ideas!?

Thanks,

Chris.

[Matthew_Williams]

Did you try WHERE StartTime='12:05:23' ?

(quotes around time only); just a guess.

Matt

[Chris Walter]

Yeah, I've tried that and it works perfectly IF the field in Access is set to accept a 'Text' string.

But I have the StartTime as a 'Date/Time' format in the Table Design View.

This is what seems to cause the problem.

As when I save the query like this....

UPDATE IndexStation
SET Signal_Size=200
WHERE StartTime='12:05:23'

It says there is a missing operator and the 'StartTime='12:05:23'' should be surrounded by ' ' .

When this is done the query then updates all fields in the Table! Almost as if the WHERE statement isn't even there...

Chris.

[Chris Walter]

Well if anyone ever reads this again...

Access will only recognise Date and Time fields when they are explicitly defined with ## marks around them...

So what worked for me...

UPDATE IndexStation
SET Signal_Size = 200
WHERE StartDate = #12/05/2007#

And the same for Time...

WHERE StartDate = #12/05/2007# AND #12:34:43#

Argh! half the day wasted on that when NI's blurb says they should be surrounded with {} marks....

Chris.

[Matthew_Williams]

Thanks for the follow up, might save me some frustration.  Glad you got it figured out.

Matt

[rolfk]

---

@Chris Walter wrote:
Well if anyone ever reads this again...

Access will only recognise Date and Time fields when they are explicitly defined with ## marks around them...

So what worked for me...

UPDATE IndexStation
SET Signal_Size = 200
WHERE StartDate = #12/05/2007#

And the same for Time...

WHERE StartDate = #12/05/2007# AND #12:34:43#

Argh! half the day wasted on that when NI's blurb says they should be surrounded with {} marks....

Chris.

---

Well Access is not really a database. It is just a glorified spreadsheet application, sorry. And the fact that Acess has its own ideas of how SQL should work is really not NIs fault.

Rolf Kalbermatter

[Chris Walter]

I completely agree about the Microsoft issue.

But it seems no SQL based manual states that { } will provide a Date/Time constant.

Is this an NI only implementation? Because I can't seem to get it to function correctly within LabView or in any SQL query.

Chris.

[rolfk]

---

@Chris Walter wrote:
I completely agree about the Microsoft issue.

But it seems no SQL based manual states that { } will provide a Date/Time constant.

Is this an NI only implementation? Because I can't seem to get it to function correctly within LabView or in any SQL query.

Chris.

---

There is nothing about the database toolkit in terms of SQL syntax that would be NI specific. The database Toolkit simply interfaces to MS ADO/DAO and the actual SQL syntax is usually implemented in the database driver or database itself although I wouldn't be surprised if ADO/DAO does at times munch a bit with that too.

The Database Toolkit definitely does not. So this might be a documentation error indeed. My understanding of SQL syntax is in fact rather limited so not sure which databases might use what delimiters to format date/time values. I know that SQL Server is rather tricky thanks to MS catering for the local date/time format in all their tools and the so called universal date/time format has borked on me on several occasions.

Rolf Kalbermatter

[Chris Walter]

Thanks for all the help people! Going great guns now... 🙂

Chris.

[nitthya]

Well, I have a similar problem.... the UPDATE query seems to be not working for me at all. I'm using the ADO toolkit to perform the SQL queries. the error is...

"Exception occured in Microsoft JET Database Engine: No value given for one or more required parameters. in ADO Connection Execute.vi->SQL Execute.vi->SQL.vi"

However I can perform SELECT quesries to the same database without problem. Does any one out there could help?

The query is simple

UPDATE IshaKriya11Apr12 SET Status = Check WHERE Num = '100'

This is what I typed in and tried with few different combinations:(

Nitty