Invalid Syntax in my sproc?!?!

Hi I have a gridview that is being populated from a method that gets it's data from a table view.

SELECT dbo.cis_AlumniContact.Street, dbo.cis_AlumniContact.City, dbo.cis_AlumniContact.State, dbo.cis_AlumniContact.Telephone,
dbo.cis_AlumniContact.Occupation, dbo.cis_AlumniContact.Description, dbo.cis_AlumniContact.Zip, dbo.cis_AlumniContact.FirstName,
dbo.cis_AlumniContact.LastName, dbo.cis_AlumniContact.YearGraduate, dbo.cis_AlumniContact.Email, dbo.cis_AlumniContact.Contact,
dbo.aspnet_Users.UserName, dbo.cis_StudentId.UaaStudentId
FROM dbo.aspnet_Users INNER JOIN
dbo.cis_AlumniContact ON dbo.aspnet_Users.UserId = dbo.cis_AlumniContact.UserId INNER JOIN
dbo.cis_StudentId ON dbo.aspnet_Users.UserId = dbo.cis_StudentId.UserId

No big deal, works great. Now when I click update I call this method

<DataObjectMethod(DataObjectMethodType.Update)>

PublicSub UpdateAlumni(ByVal StreetAsString,ByVal CityAsString,ByVal StateAsString,ByVal TelephoneAsString,ByVal OccupationAsString,ByVal DescriptionAsString,ByVal ZipAsString,ByVal FirstNameAsString,ByVal LastNameAsString,ByVal YearGraduateAsString,ByVal EmailAsString,ByVal ContactAsBoolean,ByVal original_UserNameAsString,ByVal UaaStudentIdAsString)TryDim connxAsNew SqlConnection(getConnectionString)

connx.Open()

Dim sqlCmdAsNew SqlCommand("cis_UpdateAlumniContact", connx)

sqlCmd.Parameters.Add(

New SqlParameter("@.UserName", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.UserName").Value = original_UserName

sqlCmd.Parameters.Add(

New SqlParameter("@.FirstName", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.FirstName").Value = FirstName

sqlCmd.Parameters.Add(

New SqlParameter("@.LastName", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.LastName").Value = LastName

sqlCmd.Parameters.Add(

New SqlParameter("@.Email", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.Email").Value = Email

sqlCmd.Parameters.Add(

New SqlParameter("@.Street", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.Street").Value = Street

sqlCmd.Parameters.Add(

New SqlParameter("@.City", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.City").Value = City

sqlCmd.Parameters.Add(

New SqlParameter("@.State", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.State").Value = State

sqlCmd.Parameters.Add(

New SqlParameter("@.Occupation", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.Occupation").Value = Occupation

sqlCmd.Parameters.Add(

New SqlParameter("@.Description", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.Description").Value = Description

sqlCmd.Parameters.Add(

New SqlParameter("@.Telephone", SqlDbType.NChar))

sqlCmd.Parameters(

"@.Telephone").Value = Telephone

sqlCmd.Parameters.Add(

New SqlParameter("@.Zip", SqlDbType.NChar))

sqlCmd.Parameters(

"@.Zip").Value = Zip

sqlCmd.Parameters.Add(

New SqlParameter("@.Contact", SqlDbType.Bit))

sqlCmd.Parameters(

"@.Contact").Value = Contact

sqlCmd.Parameters.Add(

New SqlParameter("@.YearGraduate", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.YearGraduate").Value = YearGraduateDim cmdAs SqlDataReader = sqlCmd.ExecuteReaderCatch exAs ExceptionDim erAsNew cis_ODS_Error

er.InsertError(

"cis_ODS_Alumni - UpdateAlumni: " + ex.Message.ToString)EndTryEndSub

The sproc it calls is:

ALTER PROCEDURE

dbo.cis_UpdateAlumniContact

@.UserName

as nvarchar(50),

@.Street

as nvarchar(50),

@.City

as nvarchar(50),

@.State

as nvarchar(2),

@.Telephone

as nvarchar(50),

@.Occupation

as nvarchar(50),

@.Description

as nvarchar(50),

@.Zip

as nvarchar(50),

@.FirstName

as nvarchar(50),

@.LastName

as nvarchar(50),

@.YearGraduate

as nvarchar(4),

@.Email

as nvarchar(50),

@.Contact

as bit

AS

UPDATEcis_AlumniContact

SETStreet = @.Street, City = @.City, State = @.State, Telephone = @.Telephone, Occupation = @.Occupation, Description = @.Description, Zip = @.Zip, FirstName = @.FirstName, LastName = @.LastName, YearGraduate = @.YearGraduate, Email = @.Email, Contact = @.Contact

FROMaspnet_UsersINNER JOINcis_AlumniContact

ONcis_AlumniContact.UserId = aspnet_Users.UserId

WHERE@.UserName = aspnet_Users.UserName

RETURN

I get this vague error

cis_ODS_Alumni - UpdateAlumni: Incorrect syntax near 'cis_UpdateAlumniContact'

If I execute the SQL from the editor it works fine. The only thing different about this sproc and my other update sprocs is the inner join. Any idea? Thanks

I see two possible problems.

You need to set the command type to:StoredProcedure

You are calling ExecuteReader, I think you need to call ExecuteNonQuery.

|||Holy cow man. Thanks so much. I copied and pasted from another method and didn't include that line. Thanks again.

invalid object name..(my error message)

i'm working on an application using vs 2005, sql server2000, with c# asp.net

i can access many tables in my db that the dbo is the dbowner for them, but when i access few tables that the owner for them is dswebwork, i recieved an error says, invalid object name tbluser...which tbluser is table name...this is the error message in details....

Invalid object name 'tblUsers'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Invalid object name 'tblUsers'.

Source Error:

Line 57: string passWord = txtPassword.Text;Line 58:Line 59: Users users = new Users(Constants.DB_CONNECTION,Line 60: userName, passWord);Line 61:

Source File:e:\web works\Webworks\DSCWebWorks\LoginMaster.master.cs Line:59

Stack Trace:

[SqlException (0x80131904): Invalid object name 'tblUsers'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +52 System.Data.SqlClient.SqlDataReader.get_MetaData() +130 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +371 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +45 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +162 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +35 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +32 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +183 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +307 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +151 WebWorksBO.DBElements.BaseDataSQLClient.FillDataset(DataSet dsToFill) in C:\Development\MyWebWorks20\WebWorksBO\DBElements\BaseDataSQLClient.cs:97 WebWorksBO.DBElements.dbUsers..ctor(String connStr, String loginname, String loginpassword) in C:\Development\MyWebWorks20\WebWorksBO\DBElements\dbUsers.cs:38 WebWorksBO.AppElements.Users..ctor(String connStr, String loginname, String loginpassword) in C:\Development\MyWebWorks20\WebWorksBO\AppElements\Users.cs:370 LoginMaster.LoginUser() in e:\web works\Webworks\DSCWebWorks\LoginMaster.master.cs:59 LoginMaster.imgbtnOK_Click(Object sender, ImageClickEventArgs e) in e:\web works\Webworks\DSCWebWorks\LoginMaster.master.cs:46 System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) +102 System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +141 System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +72 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3840

so..i hope to help me...i need to deploy this project soon...

Append the owner to the table name, when you use it or change the owner to dbo

Select * from dswebwork.tblUsers

Invalid object name dbo.getname ?

someone please help me?

i've made this used-defined function and sql won't let me use it,

CREATE FUNCTION dbo.getname (@.sname varchar(255))
RETURNS TABLE
AS

RETURN
SELECT ISNULL((select (c.firstname + ' ' + c.surname) from contacts.dbo.c_contact as c where c.employ_ref LIKE @.sname), @.sname) as FullName

using it,

select dbo.getname(c.EMPLOY_REF)
from c_contact as c

but everytime i try to use it i get,

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.getname'.

what's wrong with it?, i should have rights, i am set as sysadmingot it to work,

CREATE FUNCTION dbo.getname (@.sname nvarchar(255))
RETURNS nvarchar(255)
AS

BEGIN
DECLARE @.ss as nvarchar(255)
SET @.ss = (
SELECT ISNULL((select (c.firstname + ' ' + c.surname) from contacts.dbo.c_contact as c where c.employ_ref LIKE @.sname), @.sname) as FullName
)
RETURN(@.ss)
END

INVALID LENGTH PARAMETER PASSED....

I have the follwoing stored procedure:

ALTER procedure [dbo].[up_GetExecutionContext](
@.ExecutionGUID int = null
) as
begin
set nocount on

declare@.s varchar(500)
declare @.i int

set @.s = ''
select @.s = @.s + EventType + ','-- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x

set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...'-- If string is too long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'

set nocount off
end --procedure
GO

When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:

Invalid length parameter passed to the SUBSTRING function.

There is no SUBSTRING being used anywhere in the query and the
datatypes look okay to me.

Any suggestions would be greatly appreciated.

Thanks!!On Jun 4, 12:43 pm, ansonee <anso...@.yahoo.comwrote:

Quote:

Originally Posted by

I have the follwoing stored procedure:
>
ALTER procedure [dbo].[up_GetExecutionContext](
@.ExecutionGUID int = null
) as
begin
set nocount on
>
declare @.s varchar(500)
declare @.i int
>
set @.s = ''
select @.s = @.s + EventType + ',' -- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x
>
set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...' -- If string is too long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'
>
set nocount off
end --procedure
GO
>
When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:
>
Invalid length parameter passed to the SUBSTRING function.
>
There is no SUBSTRING being used anywhere in the query and the
datatypes look okay to me.
>
Any suggestions would be greatly appreciated.
>
Thanks!!

increase the value of @.s from 500 to 5000 maybe and test it ?|||ansonee (ansonee@.yahoo.com) writes:

Quote:

Originally Posted by

set @.s = ''
select @.s = @.s + EventType + ',' -- Dynamically build the list of
events
from(
select distinct top 100 percent [event] as EventType
from dbo.PackageStep
where (@.ExecutionGUID is null or PackageStep.packagerunid =
@.ExecutionGUID)
order by 1
) as x

I'm afraid that this relies on undefined behaviour. It may produce what
you want today. It might not tomorrow. If you are on SQL 2000, you
will need to run a cursor. On SQL 2005 there exists an option with
XML. See SQL Server MVP Antith Sen's article on
http://www.projectdmx.com/tsql/rowconcatenate.aspx for more information.

Quote:

Originally Posted by

set @.i = len(@.s)
select case @.i
when 500 then left(@.s, @.i - 3) + '...' -- If string is too
long then
terminate with '...'
else left(@.s, @.i - 1) -- else just remove the final comma
end as 'Context'
>
set nocount off
end --procedure
GO
>
When I run this and pass in a value of NULL, things work fine. When I
pass in an actual value (i.e. 15198), I get the following message:
>
Invalid length parameter passed to the SUBSTRING function.
>
There is no SUBSTRING being used anywhere in the query

No, but there is LEFT, which is just a shortcut for SUBSTRING.

More to the point, you have failed to handle the case that the query
does not find any events, and @.i is the empty string.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Invalid Column Name in SELECT

This select works as I expect:
SELECT exp_AcctNum,
exp_Amount,
CAST(Left(dbo.strat(exp_Amount),2) AS INT) AS [Strat Level],
SUBSTRING(dbo.strat(exp_Amount),3, LEN(dbo.strat(exp_Amount))- 2)
AS [Strata Desc]
FROM Expenses
Go
However, I am concerned about it having to call the function 3 times for
each row. Will SQL Server 2000 be smart enough to know that it is the same
call each time?
I tried using column aliases, but got errors.
For example, in Access I am use to doing something like:
SELECT Amount AS [Amt],
AMT as Amt2
FROM [tbl Expenses];
When I try to do the same thing in SQL Server,
SELECT exp_Amount AS [Amt],
AMT as Amt2
FROM Expenses
Go
it gives me a Invalid column name 'AMT'.
I had wanted to call the function once and create a column alias and then
use that alias for the CAST and SUBSTRING, but cannot get by the column
issue.
Something like
SELECT exp_AcctNum,
exp_Amount,
dbo.strat(exp_Amount) AS 'stvalue',
CAST(Left(stvalue,2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(dbo.strat(exp_Amount))- 2)
AS [Strata Desc]
FROM Expenses
Go
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'stvalue'.
I would really appreciate some guidance on this one!
Thanks.On Thu, 1 Jun 2006 17:40:15 -0500, MikeV06 wrote:

>This select works as I expect:
>SELECT exp_AcctNum,
> exp_Amount,
> CAST(Left(dbo.strat(exp_Amount),2) AS INT) AS [Strat Level],
> SUBSTRING(dbo.strat(exp_Amount),3, LEN(dbo.strat(exp_Amount))- 2)
> AS [Strata Desc]
>FROM Expenses
>Go
>However, I am concerned about it having to call the function 3 times for
>each row. Will SQL Server 2000 be smart enough to know that it is the same
>call each time?
Hi Mike,
Unfortunately, no.
(snip)
>I had wanted to call the function once and create a column alias and then
>use that alias for the CAST and SUBSTRING, but cannot get by the column
>issue.
>Something like
>SELECT exp_AcctNum,
> exp_Amount,
> dbo.strat(exp_Amount) AS 'stvalue',
> CAST(Left(stvalue,2) AS INT) AS [Strat Level],
> SUBSTRING(stvalue, 3, LEN(dbo.strat(exp_Amount))- 2)
> AS [Strata Desc]
>FROM Expenses
>Go
You can't do it this way. A column alias can only be used in the ORDER
BY clause, nowhere else in the query.
You can use a derived table, though:
SELECT exp_AcctNum,
exp_Amount,
stvalue,
CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc]
FROM (SELECT exp_AcctNum,
exp_Amount,
dbo.strat(exp_Amount) AS stvalue
FROM Expenses) AS d
Hugo Kornelis, SQL Server MVP|||On Fri, 02 Jun 2006 01:00:44 +0200, Hugo Kornelis wrote:
[snip]

> You can't do it this way. A column alias can only be used in the ORDER
> BY clause, nowhere else in the query.
> You can use a derived table, though:
> SELECT exp_AcctNum,
> exp_Amount,
> stvalue,
> CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
> SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc]
> FROM (SELECT exp_AcctNum,
> exp_Amount,
> dbo.strat(exp_Amount) AS stvalue
> FROM Expenses) AS d
Thank you very much. I was miles away from this solution and had already
spent 1 day trying several things that did not work. It needs a little
touch up; however, what I finally came up from your template works. What I
really do like is that the call to the function is only made once.
I guess I could put the derived table in a view and have this view call
that view (which ends up being called by another view to get the final
stratification result table). I am not sure I see any benefit in taking
that approach, but may try it just to see what happens. Uhm, maybe another
UDF instead of a View ... too tired to really think at this point.
You have made my day -- I think I will give it up for the day. Thanks.
Mike.
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'prestrat')
DROP VIEW prestrat
GO
CREATE VIEW prestrat
AS
SELECT exp_AcctNum,
exp_Amount,
stvalue,
CAST(LEFT(stvalue, 2) AS INT) AS [Strat Level],
SUBSTRING(stvalue, 3, LEN(stvalue) - 2) AS [Strata Desc],
posrecs,
posdols,
negrecs,
negdols,
[posrecs]+[negrecs] AS totrecs,
[posdols]+[negdols] AS totdols,
exp_VouchNum,
exp_InvoiceNum,
exp_StoreNum,
exp_Date,
exp_SupplierNum,
exp_SupplierName,
exp_RecordType
FROM (SELECT exp_AcctNum,
exp_Amount,
exp_VouchNum,
exp_InvoiceNum,
exp_StoreNum,
exp_Date,
exp_SupplierNum,
exp_SupplierName,
exp_RecordType,
CASE WHEN exp_Amount>=0 THEN 1 ELSE 0 END AS posrecs,
CASE WHEN exp_Amount>=0 THEN exp_Amount ELSE 0 END AS
posdols,
CASE WHEN exp_Amount<0 THEN 0 ELSE 1 END AS negrecs,
CASE WHEN exp_Amount<0 THEN 0 ELSE exp_Amount END AS negdols,
dbo.strat(exp_Amount) AS stvalue
FROM Expenses) AS d
WHERE Exp_RecordType = '2'
Go

Invalid column name for the value of a column

I am having a SQl satement as
If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
Drop table ##TEMPDelete
SET @.Name = (Select replace(@.Name,' ','_'))
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+ @.Name + ' where eid = '+ @.eid
Exec (@.SQL)
Set @. Name = (select @.Name from ##TEMPDelete)
Print AddressTemp
this results back as an error
Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
samay
please adviceI don't see where @.eid is getting valued. Assuming that it is a char
variable - I would think that you want the select statement to be:
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '''+ @.eid +''''
So that it puts quotes around the value of @.eid
--TJTODD
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:EBA43F13-84EB-42A0-9635-3AD2840C78F8@.microsoft.com...
> I am having a SQl satement as
> If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
> Drop table ##TEMPDelete
> SET @.Name = (Select replace(@.Name,' ','_'))
> Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '+ @.eid
> Exec (@.SQL)
> Set @. Name = (select @.Name from ##TEMPDelete)
> Print AddressTemp
>
> this results back as an error
> Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
> samay
> please advice|||Well, where are your single quotes? eid is a string, right? Try
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '''+ @.eid +''''
--
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:EBA43F13-84EB-42A0-9635-3AD2840C78F8@.microsoft.com...
> I am having a SQl satement as
> If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
> Drop table ##TEMPDelete
> SET @.Name = (Select replace(@.Name,' ','_'))
> Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '+ @.eid
> Exec (@.SQL)
> Set @. Name = (select @.Name from ##TEMPDelete)
> Print AddressTemp
>
> this results back as an error
> Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
> samay
> please advice

Invalid column name for the value of a column

I am having a SQl satement as
If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
Drop table ##TEMPDelete
SET @.Name = (Select replace(@.Name,' ','_'))
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+ @.
Name + ' where eid = '+ @.eid
Exec (@.SQL)
Set @. Name = (select @.Name from ##TEMPDelete)
Print AddressTemp
this results back as an error
Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
samay
please adviceI don't see where @.eid is getting valued. Assuming that it is a char
variable - I would think that you want the select statement to be:
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '''+ @.eid +''''
So that it puts quotes around the value of @.eid
--TJTODD
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:EBA43F13-84EB-42A0-9635-3AD2840C78F8@.microsoft.com...
> I am having a SQl satement as
> If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
> Drop table ##TEMPDelete
> SET @.Name = (Select replace(@.Name,' ','_'))
> Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '+ @.eid
> Exec (@.SQL)
> Set @. Name = (select @.Name from ##TEMPDelete)
> Print AddressTemp
>
> this results back as an error
> Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
> samay
> please advice|||Well, where are your single quotes? eid is a string, right? Try
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '''+ @.eid +''''
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:EBA43F13-84EB-42A0-9635-3AD2840C78F8@.microsoft.com...
> I am having a SQl satement as
> If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
> Drop table ##TEMPDelete
> SET @.Name = (Select replace(@.Name,' ','_'))
> Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '+ @.eid
> Exec (@.SQL)
> Set @. Name = (select @.Name from ##TEMPDelete)
> Print AddressTemp
>
> this results back as an error
> Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
> samay
> please advice

Invalid column name for the value of a column

I am having a SQl satement as
If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
Drop table ##TEMPDelete
SET @.Name = (Select replace(@.Name,' ','_'))
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+ @.Name + ' where eid = '+ @.eid
Exec (@.SQL)
Set @. Name = (select @.Name from ##TEMPDelete)
Print AddressTemp
this results back as an error
Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
samay
please advice
I don't see where @.eid is getting valued. Assuming that it is a char
variable - I would think that you want the select statement to be:
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '''+ @.eid +''''
So that it puts quotes around the value of @.eid
--TJTODD
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:EBA43F13-84EB-42A0-9635-3AD2840C78F8@.microsoft.com...
> I am having a SQl satement as
> If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
> Drop table ##TEMPDelete
> SET @.Name = (Select replace(@.Name,' ','_'))
> Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '+ @.eid
> Exec (@.SQL)
> Set @. Name = (select @.Name from ##TEMPDelete)
> Print AddressTemp
>
> this results back as an error
> Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
> samay
> please advice
|||Well, where are your single quotes? eid is a string, right? Try
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '''+ @.eid +''''
http://www.aspfaq.com/
(Reverse address to reply.)
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:EBA43F13-84EB-42A0-9635-3AD2840C78F8@.microsoft.com...
> I am having a SQl satement as
> If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
> Drop table ##TEMPDelete
> SET @.Name = (Select replace(@.Name,' ','_'))
> Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+
@.Name + ' where eid = '+ @.eid
> Exec (@.SQL)
> Set @. Name = (select @.Name from ##TEMPDelete)
> Print AddressTemp
>
> this results back as an error
> Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
> samay
> please advice

Intricate SQL Statement

Hi there at the forum,
I have a table with the following structure
CREATE TABLE [dbo].[Demand] (
[ArtNr] [varchar] (20) NOT NULL ,
[Plandate] [datetime] NOT NULL ,
[Dispo_element] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[AmountReq] [decimal](18, 3) NULL ,
[AmountAvail] [decimal](18, 3) NULL ,
[PlannedDelivery] [decimal](18, 3) NULL ,
[Target_Inventory] [decimal](18, 3) NULL
) ON [PRIMARY]
GO
The table contains data pertaining to supply control.
[ArtNr] designates the article number
[Plandate] shows the date of a movment
[Dispo_element] contains a code that classifies the row in the tabel as bein
g:
- Inventory
- Demand
- Delivery
[AmountReq] is the amount of a demand
[AmountAvail] is the amount available after a demand or a delivery had been
accounted for
[PlannedDelivery] is the amount that is to be delivered
[Target_Inventory] displays the missing amount in order to fulfill the
demands of a given day. Should the available amout be larger than the
demand, this column displays 0.
It is possible to have more than one delivery and more than one demand for
an articel on a any given day. Target
Data Example is
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '1.1.2005', 'inventory', 0, 100, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '2.1.2005', 'demand', 50, 50, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, 0, 0, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'demand', 50, -50, 0, 50)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '4.1.2005', 'supply', 0, 150, 100, 0)
INSERT INTO [dbo].[Demand]([ArtNr], [Plandate], [Dispo_element],
[AmountReq], [AmountAvail], [PlannedDelivery], [Target_Inventory])
VALUES(1, '5.1.2005', 'demand', 200, -50, 0, 50)
At the moment I show this data in a grid which means that for a day with 10
demands and 10 deliveries, 20 rows are shown.
My Question is: Is it possible to show a row per day, displaying the
consolidated data
Date Req Avail Deliv Target Type
1.1.05 0 100 0 0 inventory
2.1.05 50 50 0 0 demand
4.1.05 0 200 150 0 supply
4.1.05 100 100 0 0 demand
5.1.05 200 -100 0 -100 demand
Thank you very much for any help you might provide. I thought at first
about doing this by the means of some cursor and a temp table but the result
were just too slow. I hope
that it is possible to do this using SELECT statements without having to use
a cursor.
Best regardsYour table doesn't have a primary key! Hopefully your intention is to
fix that. Try:
SELECT artnr, plandate,
SUM(amountreq),
SUM(amountavail),
SUM(planneddelivery),
SUM(target_inventory),
dispo_element
FROM Demand
GROUP BY plandate, artnr, dispo_element
David Portas
SQL Server MVP
--