Trouble with SPROC and VIEW not working together.

Trouble with SPROC and VIEW not working together.

Post by kloeppe » Tue, 16 Dec 2003 16:41:20



I have a SPROC that uses various VIEWs (passed into the SPROC from an ASP.NET dropdownlist), but I'm getting "Invalid Syntax near 'View name' "  errors from the browser (IE) when I execute the dropdownlist.

It appears to me that it should work.  The VIEWs run fine by themselves.

Anyone see where my error could be?

Thanks,

Paul

Here is the SPROC-----------------------------------------------------





return
GO

and an example VIEW-------------------------------------------------------------------------------

CREATE VIEW dbo.Improving_Revenue
AS
select  Name
from Data_01
group by Name
having
max(case when Period = 'Q3_2003' then Revenue else -2000000000 end)

Quote:> max(case when Period = 'Q2_2003' then Revenue else -2000000000 end)

and
max(case when Period = 'Q2_2003' then Revenue else -2000000000 end)

Quote:> max(case when Period = 'Q1_2003' then Revenue else -2000000000 end)


 
 
 

Trouble with SPROC and VIEW not working together.

Post by Aaron Bertrand - MV » Tue, 16 Dec 2003 17:03:12




support, or foreign alphabets, or not?

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


Quote:> I have a SPROC that uses various VIEWs (passed into the SPROC from an

ASP.NET dropdownlist), but I'm getting "Invalid Syntax near 'View name' "
errors from the browser (IE) when I execute the dropdownlist.

> It appears to me that it should work.  The VIEWs run fine by themselves.

> Anyone see where my error could be?

> Thanks,

> Paul

> Here is the SPROC-----------------------------------------------------





> return
> GO

> and an example

VIEW------------------------------------------------------------------------
-------

> CREATE VIEW dbo.Improving_Revenue
> AS
> select  Name
> from Data_01
> group by Name
> having
> max(case when Period = 'Q3_2003' then Revenue else -2000000000 end)
> > max(case when Period = 'Q2_2003' then Revenue else -2000000000 end)
> and
> max(case when Period = 'Q2_2003' then Revenue else -2000000000 end)
> > max(case when Period = 'Q1_2003' then Revenue else -2000000000 end)



from the dropdownlist.

 
 
 

Trouble with SPROC and VIEW not working together.

Post by kloeppe » Tue, 16 Dec 2003 17:31:11


No particular reason for the use of NVarChar vs VarChar.


Paul

 
 
 

Trouble with SPROC and VIEW not working together.

Post by Aaron Bertrand - MV » Tue, 16 Dec 2003 17:35:08



That's right (except no need for parens).  What was the result???

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

 
 
 

Trouble with SPROC and VIEW not working together.

Post by kloeppe » Tue, 16 Dec 2003 18:01:13


Arron:  Here is the error...

Object reference not set to an instance of an object.


also, I have 3 items in the dropdownlist, the 2nd and 3rd items give me similar errors, but the 1st item gives no error (however, it does not return the data from the VIEW either.)

--------------------------------------------------------------------------------------------------------------------
[SqlException: Line 1: Incorrect syntax near 'Improving_Margin_operating'.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +45
   System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
   System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36
   DotNetNuke.Chart_001.Select_Filter_SelectedIndexChanged(Object sender, EventArgs e) +266
   System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e) +108
   System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +26
   System.Web.UI.Page.RaiseChangedEvents() +115
   System.Web.UI.Page.ProcessRequestMain() +1081

 
 
 

Trouble with SPROC and VIEW not working together.

Post by Aaron Bertrand - MV » Tue, 16 Dec 2003 18:42:14


I didn't ask for the error output.  Run the stored procedure from QUERY
ANALYZER instead of your app, and show the result of the PRINT statement.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


Quote:> Arron:  Here is the error...

> Object reference not set to an instance of an object.

> I don't know if this is helpful, but here is the stack trace for the


Quote:

> also, I have 3 items in the dropdownlist, the 2nd and 3rd items give me

similar errors, but the 1st item gives no error (however, it does not return
the data from the VIEW either.)
Quote:

> --------------------------------------------------------------------------

------------------------------------------
Quote:> [SqlException: Line 1: Incorrect syntax near

'Improving_Margin_operating'.]
Quote:>    System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior

cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
Quote:>    System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior) +45

System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior) +5
Quote:>    System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32

startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
Quote:>    System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32

startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +77
Quote:>    System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
+36
>    DotNetNuke.Chart_001.Select_Filter_SelectedIndexChanged(Object sender,
EventArgs e) +266
>    System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs
e) +108

System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.Ra
isePostDataChangedEvent() +26
Quote:>    System.Web.UI.Page.RaiseChangedEvents() +115
>    System.Web.UI.Page.ProcessRequestMain() +1081

 
 
 

Trouble with SPROC and VIEW not working together.

Post by kloeppe » Tue, 16 Dec 2003 20:31:11


Aaron:

It appears that I just lost a long message to you. Here's the jist of it though.

Query Analyzer result-----------------------------------------------------------------------------
Server: Msg 201, Level 16, State 4, Procedure Company_List_by_Filter, Line 0

I completely removed the control and rebuilt it (Using VisualStudio.NET 2003), but still have the same error.
VS builds the code without error.

Here's the code for the event handler:

                private void Select_Filter_SelectedIndexChanged(object sender, System.EventArgs e)
                {
                        SqlConnection myConnection = new SqlConnection("server=AMD;database=imSMART;Trusted_Connection=yes");
                        SqlDataAdapter myCommand = new SqlDataAdapter("Company_List_by_Filter", myConnection);

                        myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;



                        //Arbitrary table name Company_List
                        DataSet ds = new DataSet();
                        myCommand.Fill(ds, "Company_List");

                        Select_Company.DataSource=ds.Tables["Company_List"].DefaultView;
                        Select_Company.DataBind();
                }

Thanks,

Paul

 
 
 

1. Joining two views together ( NOT )

I am trying to inner join two views together. I get the following error
message:-

Msg 4409, Level 20, State 1
The columns in the query definition and the view definition do not
match.

The views themselves contain a union queries than put partitioned data
back together.

I am running on 6.5sp5

Anyone got any bright ideas ???

Tom Walder
London
UK

2. ROSS RDB site

3. MDX: Linkmember and Sum function not working together

4. Best Java Database IDE?

5. top and distinct keyword did not work together

6. MDAC & SQL 7

7. Sproc not working

8. Leftover processes on shutdown - Debian+JDBC

9. Result Set not working with Delete Statement in sproc

10. Q:Transactions and Tbatch components not working together ?

11. HELP: ADO, RDS, and Sybase not working together

12. truncate table works as an sql statement but not in Sproc

13. SQL Server View : NOT LIKE not working for me