Re: CREATE VIEW error

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

We are planing-to-consider SQL server indeed.
Hopefully it will go better + i'll learn more of SQLServer, if i have more
time i really would like to understand stored procedures and such (Seems
awesome).

Thanks,


"Jeff Dillon" <jeffdillon@xxxxxxxxxxx> schreef in bericht
news:uBCTCzNgGHA.2456@xxxxxxxxxxxxxxxxxxxxxxx
Use SQL Express instead. It's free

"Edwin Knoppert" <info@xxxxxxxxxxxxxxxxxxx> wrote in message
news:e56ai7$m1b$1@xxxxxxxxxxxxxxxxxx
Actually, I'm done.
Me too, afaik i already mentioned the ORDER was the issue, yes the field
exists, yes the query works in access development, asking more is time
consuming unless you are willing to run the silly app and see for
yourself.


"Jeff Dillon" <jeffdillon@xxxxxxxxxxx> schreef in bericht
news:Oqo5PWEgGHA.4004@xxxxxxxxxxxxxxxxxxxxxxx
No, my questions are NOT answered.

Once again (3rd time?) WHERE does it break? Do other Views work ok??

I'm not gonna install this app just to help you. You want help, then
answer my questions

Actually, I'm done.


"Edwin Knoppert" <info@xxxxxxxxxxxxxxxxxxx> wrote in message
news:e556a4$n0l$1@xxxxxxxxxxxxxxxxxx
Why didn't you try the example?
All your questions would then be answered and you'll see this is a
weird thing ok?



"Jeff Dillon" <jeffdillon@xxxxxxxxxxx> schreef in bericht
news:%23OvPTIBgGHA.3572@xxxxxxxxxxxxxxxxxxxxxxx
So exactly where does it break?? If you remove the Order by? Can you
order by on other fields? Is Field3 really your field name? Not very
descriptive.

"Edwin Knoppert" <news@xxxxxxxxxxxxxx> wrote in message
news:447406e7$0$2030$ba620dc5@xxxxxxxxxxxxxxxxxxxxxx
CREATE VIEW RESULT AS
SELECT *
FROM Table1
ORDER BY Field3;

Same result (error)

"Edwin Knoppert" <news@xxxxxxxxxxxxxx> schreef in bericht
news:447331f0$0$2017$ba620dc5@xxxxxxxxxxxxxxxxxxxxxx
I have not, will try tomorrow (Holland :) )
I'm using year and such in expressions, these go well though.


"Jeff Dillon" <jeffdillon@xxxxxxxxxxx> schreef in bericht
news:Oa5xfBofGHA.4276@xxxxxxxxxxxxxxxxxxxxxxx
I assume you've tried other ORDER BY clauses that work ok? I believe
YEAR is a built-in Access function that you can't use. Did you try
ORDER BY Field3?

Jeff
"Edwin Knoppert" <news@xxxxxxxxxxxxxx> wrote in message
news:4472c64f$0$2030$ba620dc5@xxxxxxxxxxxxxxxxxxxxxx
OK Jeff, i worked it out, at the end it was easy to prepare an
example.

Just create a table with a few fields + datefield (whatever).
When i add the ORDER BY clause it will not accept it..

CREATE VIEW RESULT AS
SELECT *
FROM Table1
ORDER BY Year(Field3);

I have posted the working example here:
http://www.hellobasic.com/trials/dbtest1.zip

I'm sure there are workarounds for this by making use of
additional querydefs but that's not really a goal is it?
ACCESS runs the select statement fine!

Extract the zip and use open website, run.
The EXECUTE accepts an execute query which will be used with a
tranaction and rollback.




"Jeff Dillon" <jeffdillon@xxxxxxxxxxx> schreef in bericht
news:eI4Si8bfGHA.4892@xxxxxxxxxxxxxxxxxxxxxxx
What? You create a simple example that works, and add your linked
tables until it works. Basic troubleshooting. Of course you need
everything, but you don't get there all at once!

Again, "where" does it break? Um, don't "forget" this time? That
is exactly what it causing your error (?? obviously?)


"Edwin Knoppert" <info@xxxxxxxxxxxxxxxxxxx> wrote in message
news:e4l918$dfc$1@xxxxxxxxxxxxxxxxxx
I already tried that (and forgot) but of course i can not have
less.
Iow, it should accept 100%, maybe there is a know issue.
But on inet i found very little.


"Jeff Dillon" <jeffdillon@xxxxxxxxxxx> schreef in bericht
news:OioUd73eGHA.356@xxxxxxxxxxxxxxxxxxxxxxx
Reduce the complexity until you find where it's breaking

"Edwin Knoppert" <info@xxxxxxxxxxxxxxxxxxx> wrote in message
news:e4l7d4$97b$1@xxxxxxxxxxxxxxxxxx
To answer the responses so far:

This is an access MDB used in ASP.NET v2.0
The website is hosted by a webhosting company, we only have
access through ftp.

Yes, we have the project locally, i'm testing this create view
stuff locally.
The idea is that i need to write me complex queries, to
complex for me unless i prepare multiple querydefs (views)
using each other.

To avoid downloading the database for maintanance i wanted to
insert new queries remote.
I'm just taking existing querydef's from the access database
which work(!) and try to create them using Execute() stuff.
'Simple' queries do get inserted, the example i shown does
not.. ???

I simply use an ASPX having a textbox being passed to the
execute command.
Liek i said, it works until it's getting complicated according
the error message i get.




"Edwin Knoppert" <news@xxxxxxxxxxxxxx> schreef in bericht
news:446dbb14$0$2021$ba620dc5@xxxxxxxxxxxxxxxxxxxxxx
I have serious issue by inserting a query using (names are
fake):

CREATE VIEW Query1 AS
SELECT Count(Table1.ID) AS CountOfID, Day([DatumAanmaak]) AS
Dag, Month([DatumAanmaak]) AS Maand, Year([DatumAanmaak]) AS
Jaar
FROM Table1
GROUP BY Day([DatumAanmaak]), Month([DatumAanmaak]),
Year([DatumAanmaak]), Day([DatumAanmaak]),
Month([DatumAanmaak]), Year([DatumAanmaak])
HAVING (((Year([DatumAanmaak]))=Year(Date())))
ORDER BY Year([DatumAanmaak]) DESC , Month([DatumAanmaak])
DESC , Day([DatumAanmaak]) DESC;

"Only simple SELECT queries are allowed in VIEWS."

Can this be solved?






























.

Relevant Pages

  • Re: CREATE VIEW error
    ... Is Field3 really your field name? ... The EXECUTE accepts an execute query which will be used with a ... insert new queries remote. ... I'm just taking existing querydef's from the access database which ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: CREATE VIEW error
    ... Is Field3 really your field name? ... The EXECUTE accepts an execute query which will be used with a ... The idea is that i need to write me complex queries, ... I'm just taking existing querydef's from the access database which ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: CREATE VIEW error
    ... exists, yes the query works in access development, asking more is time ... Is Field3 really your field name? ... The EXECUTE accepts an execute query which will be used with a ... The idea is that i need to write me complex queries, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: CREATE VIEW error
    ... exists, yes the query works in access development, asking more is time ... Is Field3 really your field name? ... The EXECUTE accepts an execute query which will be used with a ... The idea is that i need to write me complex queries, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: CREATE VIEW error
    ... Is Field3 really your field name? ... The EXECUTE accepts an execute query which will be used with a ... The idea is that i need to write me complex queries, ... I'm just taking existing querydef's from the access database ...
    (microsoft.public.dotnet.framework.adonet)