help forms

The below stored procedure works. However, I am trying to use a text box
from a temp form for the where clause.
WHERE Transactions.TransactionID = [forms]![form1]![text0]
I even tried changing first line:
Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
And changed last line to:
WHERE Transactions.TransactionID = @.TID
So far, I haven't been able to have this parameter work from a temp form
with the parameter typed into the text0 box.
any help here? I get ado error near "!" or something to that affect.

this below is what does work fine:

Alter PROCEDURE S3 @.TID varchar (255)
AS
SELECT Transactions.TransactionID, Transactions.AccountID,
Transactions.TransactionNumber, Transactions.TransactionDate,
Transactions.TransactionDescription, Transactions.WithdrawalAmount,
Transactions.DepositAmount
FROM Transactions
WHERE Transactions.TransactionID = @.TID"JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
news:sHfGc.196809$Gx4.122482@.bgtnsc04-news.ops.worldnet.att.net...
> The below stored procedure works. However, I am trying to use a text box
> from a temp form for the where clause.
> WHERE Transactions.TransactionID = [forms]![form1]![text0]
> I even tried changing first line:
> Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
> And changed last line to:
> WHERE Transactions.TransactionID = @.TID
> So far, I haven't been able to have this parameter work from a temp form
> with the parameter typed into the text0 box.
> any help here? I get ado error near "!" or something to that affect.
> this below is what does work fine:
> Alter PROCEDURE S3 @.TID varchar (255)
> AS
> SELECT Transactions.TransactionID, Transactions.AccountID,
> Transactions.TransactionNumber, Transactions.TransactionDate,
> Transactions.TransactionDescription, Transactions.WithdrawalAmount,
> Transactions.DepositAmount
> FROM Transactions
> WHERE Transactions.TransactionID = @.TID

SQL Server is a pure server application - it doesn't know anything about
forms or other presentation of data. You need to capture the parameter value
in your client application (Access?), then pass it to the stored procedure
using a client library such as ADO. If you're unsure how to go about this,
you might want to post in a forum for whichever client application you have.

Simon|||I thought sql server was about sql. You're saying there's no way to pass
data from a form to it as a parameter?
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40e99857$1_2@.news.bluewin.ch...
> "JIMMIE WHITAKER" <kpsklab@.worldnet.att.net> wrote in message
> news:sHfGc.196809$Gx4.122482@.bgtnsc04-news.ops.worldnet.att.net...
> > The below stored procedure works. However, I am trying to use a text
box
> > from a temp form for the where clause.
> > WHERE Transactions.TransactionID = [forms]![form1]![text0]
> > I even tried changing first line:
> > Alter PROCEDURE S3 @.TID varchar (255) = [forms]![form1]![text0]
> > And changed last line to:
> > WHERE Transactions.TransactionID = @.TID
> > So far, I haven't been able to have this parameter work from a temp form
> > with the parameter typed into the text0 box.
> > any help here? I get ado error near "!" or something to that affect.
> > this below is what does work fine:
> > Alter PROCEDURE S3 @.TID varchar (255)
> > AS
> > SELECT Transactions.TransactionID, Transactions.AccountID,
> > Transactions.TransactionNumber, Transactions.TransactionDate,
> > Transactions.TransactionDescription, Transactions.WithdrawalAmount,
> > Transactions.DepositAmount
> > FROM Transactions
> > WHERE Transactions.TransactionID = @.TID
> SQL Server is a pure server application - it doesn't know anything about
> forms or other presentation of data. You need to capture the parameter
value
> in your client application (Access?), then pass it to the stored procedure
> using a client library such as ADO. If you're unsure how to go about this,
> you might want to post in a forum for whichever client application you
have.
> Simon|||JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> I thought sql server was about sql. You're saying there's no way to pass
> data from a form to it as a parameter?

You are right SQL Server is about SQL, but forms are not SQL per se.

The difference between Access and SQL Server is that Access is a one-tier
application. That is, the query language knows about the GUI and can
interact with it directly. This makes it very easy to write application,
but it comes with a price: it does not scale well, and is best suited for
single-user applications. Also, since the application accesses the database
directly, that means a lot of network traffic if the database is on the
other machine.

SQL Server is intended for applications with two or more tiers. That is,
there is a client on one machine, which passes queries or remote procedure
calls to the server which sends data back. Since machine that hosts the
database does not have do care about drawing GUI:s it can concentrate
on managing the data. And since the server program works on the server,
only the data that the user actually requests has to travel the network.

There are of course several ways to pass a parameter from a form to SQL
Server, but the point is that once the parameter reaches SQL Server, SQL
Server has no idea where it came from. It could be from an input form, it
could have been a read from a file, or it could be a constant in the calling
client program.

To talk to SQL Server from a client, there is a whole range of client
libraries to choose from. From Access the most commonly used today is
ADO. But really how you this best in Access, is probably a question
for comp.databases.ms-access.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help building a #temp table

I have the following query:

--build query for #temp table
select 'L' as RECTYPE, identity(int, 1, 1) as JELINENO, max(i.jeno) + 1 as JENO, 'AJE' as JECODE,
--build the fiscal year, when month = October, November or December add 1 to actual year
FY = case
when datepart(month, GetDate()) = '10' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '11' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '12' then datepart(year, GetDate()) + 1
else datepart(year, GetDate())
end,
-- The PD starts in April and increases by 1 each month
PD = case
when datepart(month, GetDate()) = '1' then '10'
when datepart(month, GetDate()) = '2' then '11'
when datepart(month, GetDate()) = '3' then '12'
when datepart(month, GetDate()) = '4' then '1'
when datepart(month, GetDate()) = '5' then '2'
when datepart(month, GetDate()) = '6' then '3'
when datepart(month, GetDate()) = '7' then '4'
when datepart(month, GetDate()) = '8' then '5'
when datepart(month, GetDate()) = '9' then '6'
when datepart(month, GetDate()) = '10' then '7'
when datepart(month, GetDate()) = '11' then '8'
when datepart(month, GetDate()) = '12' then '9'
else 'error'
end,
'1' as SUBPD, 'N' as REVFLAG, 'Journal Entry from MAXIMO' as HEADDESC,
--If the issuetype is a return the AMOUNT is a negative value, multiply by -1
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
go
select * from #temp
drop table #temp

When I run this query, I get the following error:

Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#temp'.

I can find no reason for this error. If I run the query without the AMOUNT = case statement, it works fine. Can anyone help?

Thanks,

Dave

The mistake only arises in the AMOUNT=CASE statement since it's the only one (except from the MAX(i.jeno) column) that's accesing a real column in the database. The rest of the columns are calculated.

So, as you already have an aggregate function (the MAX) the rest of the columns from real tables must either have an aggregate function or the SELECT clause must contain a GROUP BY clause.

You can't form a SELECT with only one MAX column and the rest of them normal columns without telling SQL how it should group the results from where to choose the MAX value from.

If you really want to achieve that you have to calculate the MAX column either before in a variable or in the same SELECT with a sub-SELECT query.

IE: SELECT var1, var2, var3, (SELECT MAX(othervar) FROM table)

Hope i was clear.
Good luck,
Alan.

|||

First of all I would compress the code and rewrite it as following.

Please post DDLs as it's not clear what do you want to do. the problem is clear as you have a reference to the column m.linecost that is not in a group by list. Just to get rid of the error you can add a group by abs(m.linecost), but it doesn't look like what you want to do.

select
'L' as RECTYPE,
identity(int, 1, 1) as JELINENO,
max(i.jeno) + 1 as JENO,
'AJE' as JECODE,

--build the fiscal year, when month = October, November or December add 1 to actual year

FY = datepart(year, GetDate()) + (datepart(month, GetDate()) + 2) / 12,

-- The PD starts in April and increases by 1 each month

PD = (datepart(month, GetDate()) + 8) % 12 + 1,

'1' as SUBPD,

'N' as REVFLAG,

'Journal Entry from MAXIMO' as HEADDESC,

--If the issuetype is a return the AMOUNT is a negative value, multiply by -1

AMOUNT = abs(m.linecost)

into #temp

from matusetrans m, issuesreturns i

|||Dave -

You can't use an Aggregate Function on only one column without using a Group By for the other columns

So you either need to group by m.linecost OR put an Agrregate around it - like max() or min().

To simpify your query

select
max(i.jeno) + 1 as JENO, -- YOU USED MAX HERE
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
-- SO YOU EITHER NEED TO USE AN AGRREGRATE HERE OR
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
-- OR GROUP BY m.linecost HERE

Hope that helps

AWAL

help

I have two temp table name #table1 and #table
when I run the following update, I get an error
" Cannot use the column prefix 't'. This must match the object in the UPDATE clause '#test1'.
update #test
set t.name=tt.nam
from #test1 t, #test2 t
where t.last=tt.las
This in SQL 2000. Why would this happend
Thanks
Jasontry this
update t
set t.name=tt.name
from #test1 t, #test2 tt
where t.last=tt.last
>--Original Message--
>I have two temp table name #table1 and #table2
>when I run the following update, I get an error
>" Cannot use the column prefix 't'. This must match the
object in the UPDATE clause '#test1'. "
>update #test1
>set t.name=tt.name
>from #test1 t, #test2 tt
>where t.last=tt.last
>
>This in SQL 2000. Why would this happend.
>Thanks,
>Jason
>.
>|||Or, better still,
UPDATE
t
SET
t.name = tt.name
FROM
#test1 AS t
INNER JOIN #test2 AS tt ON
tt.last = t.last
James Hokes
> update t
> set t.name=tt.name
> from #test1 t, #test2 tt
> where t.last=tt.last
"Scott Dexter" <anonymous@.discussions.microsoft.com> wrote in message
news:033501c3c5ad$e9b73d20$a401280a@.phx.gbl...
> try this
> update t
> set t.name=tt.name
> from #test1 t, #test2 tt
> where t.last=tt.last
>
>
> >--Original Message--
> >I have two temp table name #table1 and #table2
> >
> >when I run the following update, I get an error
> >" Cannot use the column prefix 't'. This must match the
> object in the UPDATE clause '#test1'. "
> >
> >update #test1
> >set t.name=tt.name
> >from #test1 t, #test2 tt
> >where t.last=tt.last
> >
> >
> >This in SQL 2000. Why would this happend.
> >
> >Thanks,
> >Jason
> >
> >.
> >