Solved

dataadapter and sql procedure

Posted on 2004-09-07
12
386 Views
Last Modified: 2010-04-23
Hi,
I create a VB.Net program which get data from SQL server. My DataAdapter link to a SQL store procedure (I use DataAdapter Configuration Wizard), but it does not understand the table with "#" (#test). This is my store proc:
create  PROC category_get_test
AS
BEGIN
      SET NOCOUNT ON
      create table #test
      (
            id char(2),
            name char(50)
      )
      
      insert into #test
      select id, name
      from category

      select id, name from #test
      SET NOCOUNT OFF
END
How can I fix this? Please help.
Thanks.
0
Comment
Question by:khanhna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
12 Comments
 
LVL 8

Expert Comment

by:razo
ID: 12003884
when u run the stored procedure does it give the error or only when u run it through the adapter
0
 

Author Comment

by:khanhna
ID: 12004565
I use DataAdapter Configuration Wizard to set the sqlcommand for the adapter (at design time). At the step "Bind Command to Existing stored procedure", I select the stored then go to next step, the error msg is: Invalid object name "#test".
0
 
LVL 8

Expert Comment

by:razo
ID: 12004691
i meant wheen u run ur stored procedure from the enterprise manager does it run correctly without errors?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:khanhna
ID: 12004854
yes, it is correct.
0
 
LVL 7

Expert Comment

by:natloz
ID: 12007460
I think it is because you are using a temporary table...the # is a temporary table that is dropped at the end of the user connection...You may need to create a permenant table and then run the following stored proc...this should work.
I would use a TEMP table for holding values within the stored proc for calculation/manipulation purposes and either return an @ReturnID or a Query from a permenant table...

eg...
create table tblTest
     (
          id char(2),
          name char(50)
     )


create  PROC category_get_test
AS
BEGIN
     SET NOCOUNT ON
         
     insert into tblTest
     select id, name
     from category

     select id, name from tblTest
     SET NOCOUNT OFF
END
0
 

Author Comment

by:khanhna
ID: 12012326
Of course using a permanent table is ok but i would like a temporary table. We have a lot of users running this store, so it will create a lot of tables.
Is there other way?
0
 
LVL 7

Expert Comment

by:natloz
ID: 12017259
So you have tried my approach to make sure that you are able to successfully connect and use a stored procedure in this way? I am just making sure that it is not another problem other then the temporary table.

I think the problem is that you are using a local temporary table that gets destroyed when the user connection closes.

Why could you not just return the results

create  PROC category_get_test
AS
BEGIN
     SET NOCOUNT ON
     
     select id, name
     from category

     SET NOCOUNT OFF
END
0
 

Author Comment

by:khanhna
ID: 12024391
of course we can do like this. this is just an example. I ask for the case that we need to calculate within the stote proc so we need to user temporary table.
0
 
LVL 7

Accepted Solution

by:
natloz earned 200 total points
ID: 12027050
Well, since a temp local table is scoped to a single SQL connection, the table will be destroyed. I think you need to approach this differently to try and obtain your results. For instance, bringing back a dataset of real values and manipulating them in code from your front end, or designing queries that return result sets from your actual tables. Local temp tables can be used to do calculating within your stored proc, but can not be returned to VB.net since they are scoped to the connection and destroyed upon leaving the procedure.

Here is another person with a similar problem...

http://www.gotdotnet.com/Community/MessageBoard/Thread.aspx?id=251029
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question