none
Pushing the limits of table adapters - Creating temp table in stored procedure RRS feed

  • Question

  • I'm a bit new to this environment so please forgive me in advance for my "newness".

    I created a new Table adapter based on a stored procedure in my SQL Express 2008 database file. Here's where I think the problem lies; As part of the SP a temp table is created so that I can join some data and have it come in as part or the returning recordset. All this works fine when tested and executed in SS Management Studio. When I create the table adapter it immediately complains about the temp table. I'm not sure if there is a workaround for my problem but in the days of my VB6 programming, all I had to do was create an ADO command, pass in the name of my SP and the input parameters and voila' I had a recordset. Is this what I should do in VS10 in order to populate a datagrid?

    Sunday, September 19, 2010 4:18 PM

Answers

  • Hi Peak,

    In VS10, you are still able to use a command, pass in the name of the SP and the input parameters and get the result. In ADO.NET, you can use a DataAdapter to retrieve the records and store them in dataset or datatable (set the command type of select command of the DataAdapter to StoredProcedure, and set the command text to the name of that store procedure), then bind the datatable to the datagridfor example, you can do something like:

    Public Class Publishers
    Private cnPubs As SqlConnection
    Private daPubs As SqlDataAdapter
    Private cmdSelPubInfo As SqlCommand
    Private dtPubs As DataTable
    
    'Connection
    cnPubs = New SqlConnection _("server=localhost;integrated security=true;database=pubs")
    'select command
    cmdSelPubInfo = New SqlCommand
    cmdSelPubInfo.Connection = cnPubs
    cmdSelPubInfo.CommandType = CommandType.StoredProcedure
    cmdSelPubInfo.CommandText = "up_GetPubInfo"
    'DataApapter
    daPubs = New SqlDataAdapter
    daPubs.SelectCommand = cmdSelPubInfo
    'DataTable
    dtPubs = New DataTable
    ....
    
    'Fill the data table
    daPubs.Fill(dtPubs)
    
    'Bind the data table to datagridview
    Me.DataGridView1.DataSource = dtPubs 
    


    So I am wondering do you really need to use a TableAdapter ? 

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Peak Creek Monday, September 20, 2010 4:13 PM
    Monday, September 20, 2010 9:20 AM
    Moderator

All replies

  • Hi Peak,

    In VS10, you are still able to use a command, pass in the name of the SP and the input parameters and get the result. In ADO.NET, you can use a DataAdapter to retrieve the records and store them in dataset or datatable (set the command type of select command of the DataAdapter to StoredProcedure, and set the command text to the name of that store procedure), then bind the datatable to the datagridfor example, you can do something like:

    Public Class Publishers
    Private cnPubs As SqlConnection
    Private daPubs As SqlDataAdapter
    Private cmdSelPubInfo As SqlCommand
    Private dtPubs As DataTable
    
    'Connection
    cnPubs = New SqlConnection _("server=localhost;integrated security=true;database=pubs")
    'select command
    cmdSelPubInfo = New SqlCommand
    cmdSelPubInfo.Connection = cnPubs
    cmdSelPubInfo.CommandType = CommandType.StoredProcedure
    cmdSelPubInfo.CommandText = "up_GetPubInfo"
    'DataApapter
    daPubs = New SqlDataAdapter
    daPubs.SelectCommand = cmdSelPubInfo
    'DataTable
    dtPubs = New DataTable
    ....
    
    'Fill the data table
    daPubs.Fill(dtPubs)
    
    'Bind the data table to datagridview
    Me.DataGridView1.DataSource = dtPubs 
    


    So I am wondering do you really need to use a TableAdapter ? 

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Peak Creek Monday, September 20, 2010 4:13 PM
    Monday, September 20, 2010 9:20 AM
    Moderator
  • Thanks Alex. You're right, there's not a good reason to have a table adapter for this stored procedure. It was basically my lack of understanding of what a table adapter does. I've given this some thought and may not use the SP after all. For presentation purposes I may provide a "header" datagrid and a sub grid for detail and that will show the user the information in better fashion, instead of having to scroll across long records. I appreciate your response and help. I'm sure you'll be getting more questions from me. :)
    Monday, September 20, 2010 4:13 PM