Page 322 of 695« First...102030...320321322323324...330340350...Last »

Error message when you run the Check Links procedure on the POP_Receiptline table in Microsoft Dynamics GP: "A get/change next operation on table POP_Receiptline failed, record was already locked"

When you run the Check Links procedure on the POP_Receiptline table (also known as the POP10500 table) in Microsoft Dynamics GP or in Microsoft Business Solutions – Great Plains, you receive the following error message:

A get/change next operation on table POP_Receiptline failed, record was already locked.

Additionally, purchase orders may not appear in Microsoft Dynamics GP.

This problem occurs because a record in one of the POP Transaction tables is locked.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

To resolve this problem, follow these steps:

  1. Run the Check Links procedure.

    WARNING If you use Microsoft Dynamics GP 9.0, you must install Service Pack 2 before you follow these steps. The “Check Links” process may incorrectly remove applied records if you run “Check Links” in Microsoft Dynamics GP 9.0 or in Microsoft Dynamics GP 9.0 Service Pack 1.

    To run the Check Links procedure, follow these steps:

    1. Use the appropriate method:
      • In Microsoft Dynamics GP 10.0, point to Maintenance on the Microsoft Dynamics GP menu, and then click Check Links.
      • In Microsoft Dynamics GP 9.0 and in earlier versions, point to Maintenance on the File menu, and then click Check Links.
    2. In the Check Links window, click Purchasing in the Series list, and then click All.

    If purchase orders do not appear in Microsoft Dynamics GP, go to step 2. If the purchase orders appear, the problem is resolved.

  2. In SQL Query Analyzer, use the following script to delete the records in the Activity file in the Dynamics database.
    delete ACTIVITY
    delete SY00800
    delete SY00801
    

  3. Run the following script against the tempdb database to remove the inactive session.
    delete DEX_LOCK
    delete DEX_SESSION
    

  4. Run the following script against the Company database to find the purchase order number that caused the problem. Note the results.
  5. Run the following script to delete the purchase order that caused the problem.
    delete from POP10500 where PONUMBER='NNNNN'.

    Note In the script, replace the NNNNN placeholder with the purchase order number that you noted in step 4.

See more here:
Error message when you run the Check Links procedure on the POP_Receiptline table in Microsoft Dynamics GP: "A get/change next operation on table POP_Receiptline failed, record was already locked"

Error message when you generate collection letters or when you log on to Microsoft Dynamics GP: "CN_Printer_Setting has an incorrect record length" or "CN_Printer_Setting Path does not exist"

When you generate collection letters, or when you log on to Microsoft Dynamics GP, you receive one of the following error messages.

Error message 1

CN_Printer_Setting has an incorrect record length.

Error message 2

CN_Printer_Setting Path does not exist.

This problem may occur for one of the following reasons:
  • There is a damaged CN40102 table.
  • No printer is set up for Collections Management in Microsoft Dynamics GP.

Microsoft Dynamics GP 10.0

Resolution 1

Make sure that a printer is set up. To do this, follow these steps:

  1. Click Microsoft Dynamics GP, point to Tools, point to Setup, point to Sales, and then click Collection Local Setup.
  2. Click the printer icon, and then make sure that a printer is set up.

If a printer is set up, and you still experience the problem, use Resolution 2.

Resolution 2

  1. On the computer on which you receive the error message, locate the folder where the CN40102.dat file and the CN40102.idx file are stored.

    For example, the files may be in the following location:

    C:Program FilesMicrosoft DynamicsGP

  2. Make sure that you have exited Microsoft Dynamics GP.
  3. Copy the CN40102.dat file and the CN40102.idx file, and then save a backup of these two files.
  4. Delete the CN40102.dat file and the CN40102.idx file.
  5. Start Microsoft Dynamics GP 10.0.
  6. Click Microsoft Dynamics GP, point to Tools, point to Setup, point to Sales, and then click Collection Local Setup.
  7. Save the printer.
    This step regenerates a CN40102.dat file and a CN40102.idx file.
  8. Verify that you do not receive the error message.

Microsoft Dynamics GP 9.0

Resolution 1

Make sure that a printer is set up. To do this, follow these steps:

  1. On the Tools menu, point to Setup, point to Sales, and then click Collection Local Setup.
  2. Click the printer icon, and then make sure that a printer is set up.

If a printer is set up, and you still experience the problem, use Resolution 2.

Resolution 2

  1. On the computer on which you receive the error message, locate the folder where the CN40102.dat file and the CN40102.idx file are stored.

    For example, the files may be in the following location:

    C:Program FilesMicrosoft DynamicsGP

  2. Make sure that you have exited Microsoft Dynamics GP.
  3. Copy the CN40102.dat file and the CN40102.idx file, and then save a backup of these two files.
  4. Delete the CN40102.dat file and the CN40102.idx file.
  5. Start Microsoft Dynamics GP 9.0.
  6. On the Tools menu, point to Setup, point to Sales, and then click Collection Local Setup.
  7. Save the printer.
    This step regenerates a CN40102.dat file and a CN40102.idx file.
  8. Verify that you do not receive the error message.

Microsoft Business Solutions – Great Plains 8.0

Resolution 1

Make sure that a printer is set up. To do this, follow these steps:

  1. On the Tools menu, point to Setup, point to Sales, and then click Collection Local Setup.
  2. Click the printer icon, and then make sure that a printer is set up.

If a printer is set up, and you still experience the problem, use Resolution 2.

Resolution 2

  1. On the computer on which you receive the error message, locate the folder where the CN40102.dat file and the CN40102.idx file are stored.

    For example, the files may be in the following location:

    C:Program FilesMicrosoft Business SolutionsGreat Plains

  2. Make sure that you have exited Microsoft Business Solutions – Great Plains.
  3. Copy the CN40102.dat file and the CN40102.idx file, and then save a backup of these two files.
  4. Delete the CN40102.dat file and the CN40102.idx file.
  5. Start Microsoft Business Solutions – Great Plains 8.0.
  6. On the Tools menu, point to Setup, point to Sales, and then click Collection Local Setup.
  7. Save the printer.
    This step regenerates a CN40102.dat file and a CN40102.idx file.
  8. Verify that you do not receive the error message.

Link:
Error message when you generate collection letters or when you log on to Microsoft Dynamics GP: "CN_Printer_Setting has an incorrect record length" or "CN_Printer_Setting Path does not exist"

Error message when you try to print financial statements in General Ledger in Microsoft Dynamics GP: "FP: Bad component offset. form: -207 wind: 0 fld: 3131"

When you try to print financial statements in General Ledger in Microsoft Dynamics GP or in Microsoft Business Solutions – Great Plains, you receive the following error message:

FP: Bad component offset. form: -207 wind: 0 fld: 3131

illegal address

To resolve this problem, follow these steps:

  1. Use the appropriate step:
    • In Microsoft Dynamics GP 10.0, point to Tools on the Microsoft Dynamics GP menu, point to Setup, point to Company, and then click Account Format.

    • In Microsoft Dynamics GP 9.0 or in earlier versions, point to Setup on the Tools menu, point to Company, and then click Account Format.
  2. In the Account Format Setup window, verify that the information is accurate. Make any necessary changes, and then click OK.
  3. Use the appropriate step:
    • In Microsoft Dynamics GP 10.0, point to Tools on the Microsoft Dynamics GP menu, point to Utilities, point to Financial, and then click Reconcile.
    • In Microsoft Dynamics GP 9.0 or in earlier versions, point to Utilities on the Tools menu, point to Financial, and then click Reconcile.
  4. Click to select the Year check box.
  5. For each open year and for each historical year, follow these steps:
    1. Click the year.
    2. Click Reconcile.

Visit site:
Error message when you try to print financial statements in General Ledger in Microsoft Dynamics GP: "FP: Bad component offset. form: -207 wind: 0 fld: 3131"

Error message when you try to log on to a company in Microsoft Great Plains or in Microsoft Dynamics GP: "Stored Procedure smCleanupFilesBeforeLogin returned the following results"

Article ID: 849708 – Last Review: June 12, 2012 – Revision: 6.1

When you try to log on to a company in Microsoft Dynamics GP or in Microsoft Business Solutions – Great Plains, you receive the following error message:

Stored Procedure smCleanupFilesBeforeLogin returned the following results: DBMS: XXX, Great Plains: XXX

This problem may occur for any one of the following causes:
  • Cause 1: The smCleanupFilesBeforeLogin stored procedure is damaged or missing. See Resolution 1.
  • Cause 2: The DYNSA user is not the database owner of the company database. See Resolution 2.
  • Cause 3: The permissions for Microsoft Great Plains users who are part of the DYNGRP role in Microsoft SQL Server are incorrect. See Resolution 3.
  • Cause 4: Records are stuck in the ACTIVITY table or in the SY00800 table. See Resolution 4.

Resolution 1

Note Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.

  1. Start SQL Server Management Studio or Query Analyzer. To do this, follow the steps for the program that you use.
    • Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
      1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

        Note The Connect to Server window opens.

      2. In the Server name box, type the name of the instance of SQL Server.
      3. In the Authentication list, click SQL Authentication.
      4. In the User name box, type sa.
      5. In the Password box, type the password for the sa user, and then click Connect.
    • Microsoft SQL Server 2000
      1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
      2. Type the password for the sa user, and then click OK.
  2. On the File menu, click New.
  3. Click Database Engine Query.
  4. Re-create the stored procedure. Then, run the script against the company database. The following files are available for download from the Microsoft Dynamics File Exchange Server.
    Microsoft scanned these files for viruses. Microsoft used the most current virus-detection software that was available on the date that these files were posted. The files are stored on security-enhanced servers that help prevent any unauthorized changes to the files.

Resolution 2

Note Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.

  1. Start SQL Server Management Studio or Query Analyzer. To do this, follow the steps for the program that you use.
    • Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
      1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

        Note The Connect to Server window opens.

      2. In the Server name box, type the name of the instance of SQL Server.
      3. In the Authentication list, click SQL Authentication.
      4. In the User name box, type sa.
      5. In the Password box, type the password for the sa user, and then click Connect.
    • Microsoft SQL Server 2000
      1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
      2. Type the password for the sa user, and then click OK.
  2. On the File menu, click New.
  3. Click Database Engine Query.
  4. Use the following script to determine the database owner.If the DYNSA user is not listed as the database owner (db_owner) in the results, make the DYNSA user the owner of the database. To do this, run the following script against the desired database.

Resolution 3

Note Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.

  1. Start SQL Server Management Studio or Query Analyzer. To do this, follow the steps for the program that you use.
    • Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
      1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

        Note The Connect to Server window opens.

      2. In the Server name box, type the name of the instance of SQL Server.
      3. In the Authentication list, click SQL Authentication.
      4. In the User name box, type sa.
      5. In the Password box, type the password for the sa user, and then click Connect.
    • Microsoft SQL Server 2000
      1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
      2. Type the password for the sa user, and then click OK.
  2. On the File menu, click New.
  3. Click Database Engine Query.
  4. Run the following Grant.sql script against the DYNAMICS database and against all the company databases. This script gives the appropriate permissions to all Microsoft Great Plains users who are part of the DYNGRP role in SQL Server.
    /*Count : 1 */
    
    declare @cStatement varchar(255)
    
    declare G_cursor CURSOR for select 'grant select,update,insert,delete on ' + convert(varchar(64),name) + ' to DYNGRP' from sysobjects
    	where (type = 'U' or type = 'V') and uid = 1
    
    set nocount on
    OPEN G_cursor
    FETCH NEXT FROM G_cursor INTO @cStatement
    WHILE (@@FETCH_STATUS <> -1)
    begin
    	EXEC (@cStatement)
    	FETCH NEXT FROM G_cursor INTO @cStatement
    end
    DEALLOCATE G_cursor
    
    declare G_cursor CURSOR for select 'grant execute on ' + convert(varchar(64),name) + ' to DYNGRP' from sysobjects
    	where type = 'P'  
    
    set nocount on
    OPEN G_cursor
    FETCH NEXT FROM G_cursor INTO @cStatement
    WHILE (@@FETCH_STATUS <> -1)
    begin
    	EXEC (@cStatement)
    	FETCH NEXT FROM G_cursor INTO @cStatement
    end
    DEALLOCATE G_cursor
    

Resolution 4

Note Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.

  1. Start SQL Server Management Studio or Query Analyzer. To do this, follow the steps for the program that you use.
    • Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
      1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

        Note The Connect to Server window opens.

      2. In the Server name box, type the name of the instance of SQL Server.
      3. In the Authentication list, click SQL Authentication.
      4. In the User name box, type sa.
      5. In the Password box, type the password for the sa user, and then click Connect.
    • Microsoft SQL Server 2000
      1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
      2. Type the password for the sa user, and then click OK.
  2. On the File menu, click New.
  3. Click Database Engine Query.
  4. Run the following script to remove any records that are stuck in the ACTIVITY table and in the BATCH ACTIVITY table.
    DELETE DYNAMICS..ACTIVITY
    DELETE DYNAMICS..SY00800
    



APPLIES TO
  • Microsoft Dynamics GP 2010
  • Microsoft Dynamics GP 10.0
  • Microsoft Dynamics GP 9.0
  • Microsoft Business Solutions–Great Plains 8.0
  • System Manager
kbprb kbmbsmigrate KB849708

Other Resources

Other Support Sites

Community

Get Help Now

Article Translations

More here:
Error message when you try to log on to a company in Microsoft Great Plains or in Microsoft Dynamics GP: "Stored Procedure smCleanupFilesBeforeLogin returned the following results"

You cannot view a shared note in Microsoft Dynamics GP

When you try to view a shared note in Microsoft Dynamics GP, the note is not displayed.
This problem occurs because the Microsoft Dynamics GP installation on your computer does not point to the shared OLE Notes path.
To grant all users access to the same OLE Notes, change the “OLEPath=” line in the dex.ini file on each workstation. The “OLEPath=” line must list the shared location.

To edit the “OLEPath=” line, follow these steps:

  1. Create a shared folder in a shared location. Grant all users minimum permissions of Change to the shared folder.

    Note The following is an example of the path of a shared folder:

    ServerNameSharedOLENotes

  2. In Windows Explorer, double-click the dex.ini file that is located in the Microsoft Dynamics GP code folder on each client workstation.

    The following locations are the default locations of this code folder:

    • Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 2010

      C:Program FilesMicrosoft DynamicsGPData

    • Microsoft Dynamics GP 9.0

      C:Program FilesMicrosoft DynamicsGP

    • Microsoft Business Solutions – Great Plains 8.0

      C:Program FilesMicrosoft Business SolutionsGreat Plains

  3. In the “OLEPath=” line, type the path of the shared folder. For an example of the path of a shared folder, see the note in step 4.

    Note If you type a UNC path, follow these steps so that the appropriate folders are created in the shared folder:

    1. In the shared folder, create a folder for each company database. The folder name must match the company database ID that is displayed in Enterprise Manager or in SQL Management Studio. For example, the folder name for the Fabrikam sample company must be TWO.
    2. In the company folder, create a folder that is named OLENotes.

      Note If the workstation already contains some OLE Notes, move these OLE Notes to the shared directory on the server so that these OLE Notes can be read by Microsoft Dynamics GP. To determine whether the workstation already contains some OLE Notes, examine the “OLEpath=” line in the dex.ini file that is mentioned in step 2. In the folder that is listed in the “OLEpath=” line, look for a folder that has a name that matches the company database ID. This folder contains an OLENotes folder if the workstation already contains OLE Notes. Copy these OLE Notes from the OLENotes folder to the shared folder on the server.

  4. Make sure that there is a backslash at the end of the path. If there is no backslash, the OLE path may not work, and the OLE Notes may not display.

    Note The following are examples of a path that you would add to the Dex.ini file.

    The full UNC path for a shared OLENotes folder on a server may resemble the following:

    OLEPath=ServerGP ShareGPOLE Notes

    The full path for a shared OLENotes folder on your local computer or on a mapped drive may resemble the following:

    OLEPath=C:Program FilesMicrosoft DynamicsGPSharedOLENotes

The OLE Notes feature provides the option to track additional information about key
master records and windows throughout the Microsoft Dynamics GP application.

Excerpt from:
You cannot view a shared note in Microsoft Dynamics GP

You receive error messages when you update to Microsoft Dynamics GP 10.0 Service Pack 4

When you update to Microsoft Dynamics GP 10.0 Service Pack 4, you receive the following error messages:

MicrosoftSQL Native ClientSQL ServerIncorrect syntax near ‘)’

MicrosoftSQL Native ClientSQL ServerCannot find the object ‘taRequesterModifiedItem1′, because it does not exist or you do not have permission.

Specifically, this error message occurs when the update process creates the taRequesterModifieditem1 stored procedure.

This problem occurs because of an invalid entry in the DATACNT field of the eConnect_Out_Setup table.

Web Services is installed during the update to Microsoft Dynamics GP 10.0 Service Pack 4. During this procedure, new records are created in the eConnect_Out_Setup table. However, one or more records in the eConnect_Out_Setup table contain the invalid value 73 in the DATACNT field. This invalid value causes the errors that are displayed in the “Symptoms” section.

To prevent this problem

You can prevent this problem if you have not already installed Microsoft Dynamics GP 10.0 Service Pack 4 or later versions. To prevent this problem, install the latest hotfix for Web Services before you install Microsoft Dynamics GP 10.0 Service Pack 4 or later versions. To do this, download the latest hotfix from one of the following sites, depending on whether you are a customer or a partner:

Customer
https://mbs.microsoft.com/customersource/downloads/servicepacks/webservicesformicrosoft+dynamicsgp10.htm?
(https://mbs.microsoft.com/customersource/downloads/servicepacks/webservicesformicrosoft+dynamicsgp10.htm?)

Partner
https://mbs.microsoft.com/partnersource/downloads/servicepack/webservicesformicrosoft+dynamicsgp10.htm
(https://mbs.microsoft.com/partnersource/downloads/servicepack/webservicesformicrosoft+dynamicsgp10.htm)

To resolve this problem

To resolve this problem, correct the invalid value in the DATACNT field in the eConnect_Out_Setup table. To do this, follow these steps:

  1. Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.
  2. Start the Support Administrator Console, Microsoft SQL Query Analyzer, or SQL Server Management Studio. To do this, use one of the following methods depending on the program that you are using.

    Method 1: For SQL Server Desktop Engine

    If you are using SQL Server Desktop Engine (also known as MSDE 2000), start the Support Administrator Console. To do this, click Start, point to All Programs, point to Microsoft Administrator Console, and then click Support Administrator Console.

    Method 2: For SQL Server 2000

    If you are using SQL Server 2000, start SQL Query Analyzer. To do this, click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.

    Method 3: For SQL Server 2005

    If you are using SQL Server 2005, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

    Method 4: For SQL Server 2008

    If you are using SQL Server 2008, start SQL Server Management Studio. To do this, click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.

  3. Run the following statement against the company database:
    Update eConnect_Out_Setup set DATACNT = 72 where DOCTYPE = 'ModifiedItem'

  4. Start Microsoft Dynamics GP Utilities to continue the update.

Follow this link:
You receive error messages when you update to Microsoft Dynamics GP 10.0 Service Pack 4

How To Display Hierarchical Data by Using Nested Repeater Controls and Visual C# .NET

Article ID: 306154 – Last Review: June 12, 2012 – Revision: 5.0

This article was previously published under Q306154

This article describes how to use nested Repeater controls to display hierarchical data. You can apply this concept
to other list-bound controls.

Bind to the Parent Table

  1. Start Microsoft Visual Studio .NET.
  2. On the File menu, point to New, and then click Project.
  3. Click Visual C# Projects under Project Types, and then click ASP.NET Web Application under Templates.
  4. In the Location box, delete the WebApplication#, and
    then type NestedRepeater. If you use the local server,
    leave the server name as http://localhost. The following path appears in the Location box:

    http://localhost/ NestedRepeater

    Click OK.

  5. In Solution Explorer, right-click the NestedRepeater project name node, point to Add, and then click Add Web Form.
  6. To name the Web Form, type
    NestedRepeater, and click Open.
  7. The new Web Form is created. It opens in Design View in the
    Integrated Development Environment (IDE) of Microsoft Visual Studio .NET. From
    the Toolbox, select the Repeater control, and then drag it to the Web Form page.
  8. Change the ID property of this Repeater control to parentRepeater.
  9. Switch to the HTML view for this Web Form. To do so, click
    the HTML tab in the lower-left corner of the Designer. The Repeater control generates the following HTML code:
    
    					

  10. Add the following code in the Repeater tags: After you do that, the HTML code for the Repeater is as follows:
    
    	
    	     <%# DataBinder.Eval(Container.DataItem, "au_id") %>	
          
    
    					

  11. In Solution Explorer, right-click NestedRepeater.aspx, and then click View Code to switch to the NestedRepeater.aspx.cs code-behind
    file.
  12. Add the following namespace declaration to the top of the
    file:
    using System.Data;
    using System.Data.SqlClient;
    					

  13. Add the following code to the Page_Load event to create a connection to the Pubs database, and then to bind the Authors table to the Repeater control:
          public void Page_Load(object sender, EventArgs e)
    
             //Create the connection and DataAdapter for the Authors table.
             SqlConnection cnn = new SqlConnection("server=(local);database=pubs; Integrated Security=SSPI");
             SqlDataAdapter cmd1 = new SqlDataAdapter("select * from authors",cnn);
    
             //Create and fill the DataSet.
             DataSet ds = new DataSet();
             cmd1.Fill(ds,"authors");
             //Insert code in step 4 of the next section here.
             //Bind the Authors table to the parent Repeater control, and call DataBind.
             parentRepeater.DataSource = ds.Tables"authors";
             Page.DataBind();
    
             //Close the connection.
             cnn.Close();
    

    NOTE: You may have to modify the database connection string as
    appropriate for your environment.

  14. Save all of the files.
  15. In Solution Explorer, right-click the
    NestedRepeater.aspx, and then click Set As Start Page.
  16. On the Build menu click Build Solution to compile the project.
  17. View the .aspx page in the browser, and then verify that
    the page works thus far.

    The output should appear as follows:

    • 172-32-1176
    • 213-46-8915
    • 238-95-7766
    • 267-41-2394

Bind to the Child Table

  1. In the HTML view of the NestedRepeater.aspx page, locate
    the following line of code:
    <%# DataBinder.Eval(Container.DataItem, "au_id") %>
    						

    Add the following code after this code:

    
    		
    	            <%# DataBinder.Eval(Container.DataItem, ""title_id"")%>
    		
    
    						

    This new code adds a second Repeater control to the ItemTemplate property of the parent Repeater control.

  2. Set the DataSource property for the child Repeater control as follows:
    
    					

    After you set the DataSource property for the child Repeater control, the HTML code for the two Repeater controls (parent and child) appears as follows:

    
    	
    		
    		 <%# DataBinder.Eval(Container.DataItem, "au_id") %>
    		
    		
    		
    			
    				<%# DataBinder.Eval(Container.DataItem, ""title_id"")%>	
    			
    		
    	
    
    					

  3. Add the following page directive to the top of the page:
    <%@ Import Namespace="System.Data" %>
    					

  4. In the code-behind page, replace the following line in the Page_Load event
    //Insert code in step 4 of the next section here.
    						

    with the following code:

             //Create a second DataAdapter for the Titles table.
             SqlDataAdapter cmd2 = new SqlDataAdapter("select * from titleauthor",cnn);
             cmd2.Fill(ds,"titles");
    
             //Create the relation between the Authors and Titles tables.
             ds.Relations.Add("myrelation",
             ds.Tables"authors".Columns"au_id",
             ds.Tables"titles".Columns"au_id");
    

    This adds the Titles table to the DataSet, and then adds the relationships between the Authors and Titles
    tables.

  5. Save and compile the application.
  6. View the page in the browser, and then verify that the page
    works so far. The output should appear as follows:

    172-32-1176
    PS3333
    213-46-8915
    BU1032
    BU2075
    238-95-7766
    PC1035
    267-41-2394
    BU1111
    TC7777

Complete Code List

Nestedrepeater.aspx

<%@ Page language="c#" Codebehind="NestedRepeater.aspx.cs" AutoEventWireup="false" Inherits="NestedRepeater.NestedRepeater" %>
<%@ Import Namespace="System.Data" %>



<%# DataBinder.Eval(Container.DataItem,"au_id") %> <%# DataBinder.Eval(Container.DataItem, ""title_id"")%>

Nestedrepeater.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace NestedRepeater

   public class NestedRepeater : System.Web.UI.Page

      protected System.Web.UI.WebControls.Repeater parentRepeater;
      public NestedRepeater()

         Page.Init += new System.EventHandler(Page_Init);

      public void Page_Load(object sender, EventArgs e)

         //Create the connection and DataAdapter for the Authors table.
         SqlConnection cnn = new SqlConnection("server=(local);database=pubs; Integrated Security=SSPI ;");
         SqlDataAdapter cmd1 = new SqlDataAdapter("select * from authors",cnn);

         //Create and fill the DataSet.
         DataSet ds = new DataSet();
         cmd1.Fill(ds,"authors");

         //Create a second DataAdapter for the Titles table.
         SqlDataAdapter cmd2 = new SqlDataAdapter("select * from titleauthor",cnn);
         cmd2.Fill(ds,"titles");

         //Create the relation bewtween the Authors and Titles tables.
         ds.Relations.Add("myrelation",
         ds.Tables"authors".Columns"au_id",
         ds.Tables"titles".Columns"au_id");

         //Bind the Authors table to the parent Repeater control, and call DataBind.
         parentRepeater.DataSource = ds.Tables"authors";
         Page.DataBind();

         //Close the connection.
         cnn.Close();

      private void Page_Init(object sender, EventArgs e)

         InitializeComponent();

      private void InitializeComponent()

         this.Load += new System.EventHandler(this.Page_Load);

   }
}
				

For more information, refer to the following topics in the
Microsoft .NET Framework Software Development Kit (SDK):


APPLIES TO
  • Microsoft ASP.NET 1.0
  • Microsoft ASP.NET 1.1
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# .NET 2003 Standard Edition
kbdatabinding kbhowtomaster kbservercontrols KB306154

Other Resources

Other Support Sites

Community

Get Help Now

Article Translations

Read More:
How To Display Hierarchical Data by Using Nested Repeater Controls and Visual C# .NET

How To Write Binary Files to the Browser Using ASP.NET and Visual C# .NET

This step-by-step article creates a sample page that
demonstrates how to retrieve binary data from a file and then how to write the
data out to the browser using ASP.NET and Visual C#. Although this
demonstration uses an Adobe Acrobat (.pdf) file (which Web application
developers commonly use), you can apply this procedure to other binary file
formats.

Requirements

  • Microsoft .NET Framework
  • Microsoft Windows 2000 or Windows XP
  • Microsoft Internet Information Server (IIS)

Create an ASP.NET Web Application Using Visual C#

This section demonstrates how to create a new ASP.NET Web
Application named BinaryDemo:

  1. Open Microsoft Visual Studio .NET
  2. From the File menu, point to New, and then click Project.
  3. Under Project Types, click Visual C# Projects. Under Templates, click ASP.NET Web Application.
  4. In the Name text box, type BinaryDemo. In the Location text box, type the server name. If you are using the local
    server, leave the location as http://localhost.

Add the PDF File to the Project

To set up your project so that you can add and run the code in
the Create an ASPX Page section to
follow, you must first add an Adobe Acrobat (.pdf) file to your current
project. To do this in Visual Studio .NET, follow these steps:

  1. In Solution Explorer, right-click the project node, click Add, and then click Add Existing Item.
  2. Browse to the location of a .pdf file on your
    system.
  3. Click to highlight the file, and then click Open.
  4. In Visual Studio .NET Solution Explorer, right-click the
    file, and then click Rename. Rename the .pdf file so that it matches the file name
    Acrobat.pdf that is used in the code that follows.

In addition, ensure that Adobe Acrobat Reader is installed on
the client computer from which the .aspx page is viewed so that the browser can
properly read and render the binary data. You can download the Adobe Acrobat
Reader from the following Adobe Web site:

Create the ASPX Page

  1. Add a new .aspx page named BinaryData.aspx to the current
    project as follows:

    1. In Solution Explorer, right-click the project node,
      click Add, and then click Add Web Form.
    2. Name the page BinaryData.aspx, and then click Open.

      NOTE: Make sure that your page is added to the project at the same
      level as the .pdf file that you added in the previous section. This is very
      important because the code uses the relative path to initially reference the
      .pdf file.

  2. In the editor, right-click BinaryData.aspx, and then click View Code.
  3. Highlight the following code, right-click the code, and
    then click Copy. In the Page_Load event in the code-behind page, click Paste on the Edit menu to paste the code:
    private void Page_Load(object sender, System.EventArgs e)
    
                 //Set the appropriate ContentType.
    	    Response.ContentType = "Application/pdf";
                 //Get the physical path to the file.
    	    string FilePath = MapPath("acrobat.pdf");
                 //Write the file directly to the HTTP content output stream.
    	    Response.WriteFile(FilePath);
                Response.End();
    

  4. On the File menu, click Save All.
  5. On the Build menu, click Build.
  6. To run the code, right-click BinaryData.aspx in Solution Explorer, and then click View In Browser. If you are prompted, click Open to open and render the file in the browser.

NOTE: If you want to use the preceding code to support other binary
file types, you must modify the value in the ContentType string so that it specifies the appropriate file format. The
syntax of this string is usually formatted as “type/subtype,” where “type” is
the general content category and “subtype” is the specific content type. For a
full list of supported content types, refer to your Web browser documentation
or the current HTTP specification. The following list outlines some common ContentType values:

  • “text/HTML”
  • “image/GIF”
  • “image/JPEG”
  • “text/plain”
  • “Application/msword” (for Microsoft Word files)
  • “Application/x-msexcel” (for Microsoft Excel
    files)

For more information, refer to the following topics in the
.NET Framework Software Development Kit (SDK) documentation: For additional information about how to accomplish this task
using Microsoft Active Server Pages (ASP), click the article number below to
view the article in the Microsoft Knowledge Base:
276488 

(http://support.microsoft.com/kb/276488/EN-US/
)
How To Use the ADODB.Stream Object to Send Binary Files to the Browser through ASP

Microsoft provides third-party contact information to help you find
technical support. This contact information may change without notice.
Microsoft does not guarantee the accuracy of this third-party contact
information.

The third-party products that are
discussed in this article are manufactured by companies that are independent of
Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the
performance or reliability of these products.

See original article:
How To Write Binary Files to the Browser Using ASP.NET and Visual C# .NET

How To Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual C# .NET

This article demonstrates how to use the GetSchemaTable method of the DataReader object in ADO.NET to retrieve column schema information. Another
name for a column’s schema is its field properties. Column schema information
includes the following information about the column:

  • Name
  • Data type
  • Size
  • Whether the column is a primary key field
  • Whether the column is an autonumber (AutoIncrement)
    field

The GetSchemaTable method returns a DataTable property that contains the column schema for a DataReader. The DataTable contains one row for each field in the resultset. Each column
maps to a property of the field in the resultset. The ColumnName property of the DataTable column is the name of the field’s property, such as the ColumnName, DataType, ColumnSize, IsKeyColumn, or IsAutoIncrement property. The value of the DataTable column is the value of the field’s property, such as the FirstName value for the ColumnName property.
Note To get the primary key information, including whether a field is
part of a primary key and whether it is an AutoIncrement field, you must set the CommandBehavior value of the DataReader to CommandBehavior.KeyInfo.

You can use the GetSchemaTable method with either the OLE DB .NET Provider or the SQL .NET
Provider. The OleDbDataReader.GetSchemaTable method maps to the OLE DB IColumnsRowset::GetColumnsRowset method. The SqlDataReader.GetSchemaTable method does not use an OLE DB Provider layer.

Of note,
unless you explicitly use the GetSchemaTable method, the DataReader does not return column schema. Also, if you use GetSchemaTable to retrieve the column schema, you cannot update the DataReader. The DataReader always retrieves a read-only, forward-only stream of data from a
database.

When to Use the GetSchemaTable Method

  • The SqlConnection object does not support retrieving SQL Server schema information
    in a method analogous to the OleDbConnection object’s GetOleDbSchemaTable method. The GetSchemaTable method of the SqlDataReader class provides a straightforward way to obtain column schema
    information from SQL Server.
  • Although the OleDbConnection object’s GetOleDbSchemaTable method can return database, table, and column schema information,
    you may find that the GetSchemaTable method of the DataReader object is easier to use if you want to retrieve only column
    schema information.
  • You can use the GetSchemaTable method to create a new DataTable based on an existing DataTable property’s schema while you customize the column names and other
    column attributes. For sample code that demonstrates how to use GetSchemaTable to define a new table, refer to the “Visual Studio Samples: Fitch
    and Mather 7.0 Run SQL Query” topic in the Microsoft Visual Studio .NET Online
    Help documentation.

Retrieve Column Schema with the OLE DB .NET Provider

This sample lists the schema information for the columns (field
properties) of the Employees table in the SQL Server Northwind
database.

Note that when you use the OLE DB .NET Provider, you use
the GetSchemaTable method of the OleDbDataReader object.

  1. Start Visual Studio .NET, and then create a new Visual C#
    Console Application project. Class1.cs is created by default.
  2. Open the Code window for Class1. Paste the following code
    at the top of the Code window above the namespace declaration:
    using System.Data;
    using System.Data.OleDb;
    					

  3. In the Code window, paste the following code in the Main function:
    OleDbConnection cn = new OleDbConnection();
    OleDbCommand cmd = new OleDbCommand();
    DataTable schemaTable;
    OleDbDataReader myReader; 
    
    //Open a connection to the SQL Server Northwind database.
    cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=login;
                           Password=password;Initial Catalog=Northwind";
    cn.Open();
    
    //Retrieve records from the Employees table into a DataReader.
    cmd.Connection = cn;
    cmd.CommandText = "SELECT * FROM Employees";
    myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo); 
    
    //Retrieve column schema into a DataTable.
    schemaTable = myReader.GetSchemaTable();
    
    //For each field in the table...
    foreach (DataRow myField in schemaTable.Rows)
        //For each property of the field...
        foreach (DataColumn myProperty in schemaTable.Columns)
    	//Display the field name and value.
    	Console.WriteLine(myProperty.ColumnName + " = " + myFieldmyProperty.ToString());
    
        Console.WriteLine();
    
        //Pause.
        Console.ReadLine();
    }
    
    //Always close the DataReader and connection.
    myReader.Close();
    cn.Close();
    					

  4. Modify the parameters of the ConnectionString property to properly connect to your SQL Server
    computer.
  5. Press the F5 key to compile and to run the project. Notice
    that the properties of each field are listed in the Console window.
  6. Press ENTER to scroll through the list, end the console
    application, and return to the Integrated Development Environment
    (IDE).

Retrieve Column Schema with the SQL .NET Provider

This sample lists the schema information for the columns (field
properties) of the Employees table in the SQL Server Northwind
database.

Note that when you use the SQL .NET Provider, you use the GetSchemaTable method of the SqlDataReader object.

  1. Start Visual Studio .NET, and create a new Visual C#
    Console Application project. Class1.cs is created by default.
  2. Open the Code window for Class1. Paste the following code
    at the top of the Code window above the namespace declaration:
    using System.Data;
    using System.Data.SqlClient;
    					

  3. In the Code window, paste the following code in the Main function:
    SqlConnection cn = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    DataTable schemaTable;
    SqlDataReader myReader; 
    
    //Open a connection to the SQL Server Northwind database.
    cn.ConnectionString = "Data Source=server;User ID=login;
                           Password=password;Initial Catalog=Northwind";
    cn.Open();
    
    //Retrieve records from the Employees table into a DataReader.
    cmd.Connection = cn;
    cmd.CommandText = "SELECT * FROM Employees";
    myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
    
    //Retrieve column schema into a DataTable.
    schemaTable = myReader.GetSchemaTable();
    
    //For each field in the table...
    foreach (DataRow myField in schemaTable.Rows)
        //For each property of the field...
        foreach (DataColumn myProperty in schemaTable.Columns)
    	//Display the field name and value.
    	Console.WriteLine(myProperty.ColumnName + " = " + myFieldmyProperty.ToString());
    
        Console.WriteLine();
    
        //Pause.
        Console.ReadLine();
    }
    
    //Always close the DataReader and connection.
    myReader.Close();
    cn.Close();
    					

  4. Modify the parameters of the ConnectionString property to properly connect to your SQL Server
    computer.
  5. Press F5 to compile and to run the project. Notice that the
    properties of each field are listed in the Console window.
  6. Press ENTER to scroll through the list, end the console
    application, and return to the IDE.

For more information about the DataReader and the GetSchemaTable method, see the following topics in the Visual Studio .NET Online
Help documentation:
For additional information about using the OleDbConnection object’s GetOleDbSchemaTable method to retrieve schema information, click the article number
below to view the article in the Microsoft Knowledge Base:

309681 

(http://support.microsoft.com/kb/309681/EN-US/
)
How To Retrieve Schema Information by Using GetOleDbSchemaTable and Visual C# .NET

Read More:
How To Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual C# .NET

HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET

There are several ways to use ADO.NET to call a stored procedure and to get back return values and return parameters, including:

  • Use a DataSet object to gather the returned rows and to work with these rows in addition to the return values and the return parameters.
  • Use a DataReader object to gather the returned rows, to move through these rows, and then to gather return values and return parameters.
  • Use the ExecuteScalar method to return the value from the first column of the results’ first row with the return values and the return parameters. This is most useful with aggregate functions.
  • Use the ExecuteNonQuery method to return only the return parameters and the return values. Any returned rows are discarded. This is most useful for executing action queries.

This article demonstrates the last three methods and uses both the SqlCommand and the OleDbCommand objects. Make sure that you copy only the code for the managed provider that you are using. If you are not sure which managed provider you should use, visit the following Microsoft Developer Network Web site:

In each of the samples in this article, the parameters are added to the Parameters collection of the Command object. When you use the SqlCommand object, you do not have add the parameters in any particular order, but the parameters must have the correct name. When you use the OleDbCommand object, you must add the parameters in the correct order, and you cannot use the parameters by name.

Use DataReader to Return Rows and Parameters

You can use the DataReader object to return a read-only, forward-only stream of data. The information that the DataReader contains can come from a stored procedure. This example uses the DataReader object to run a stored procedure that has an input and an output parameter and then moves through the returned records to view the return parameters.

  1. Create the following stored procedure on the server that is running Microsoft SQL Server:
    Create Procedure TestProcedure
    (
      @au_idIN varchar (11),
      @numTitlesOUT Integer OUTPUT
    )
    AS 
    
    select A.au_fname, A.au_lname, T.title
    from authors as A join titleauthor as TA on
    A.au_id=TA.au_id
    join titles as T
    on T.title_id=TA.title_id
    where A.au_id=@au_idIN
    set @numTitlesOUT = @@Rowcount
    return (5)
    					

  2. Create a new Visual C# .NET Windows Application project.
  3. Use the using statement on the System and the System.Data namespaces so that you do not have to qualify declarations in those namespaces later in your code. Add this code to the top of the Form code module. Make sure to copy only the code for the provider that you have chosen.SQL Client
    using System.Data.SqlClient;
    					

    OLE DB Data Provider

  4. Replace the code in the private Form_Load event with the following code:SQL Client
    SqlConnection PubsConn = new SqlConnection
    ("Data Source=server;integrated " +
    "Security=sspi;initial catalog=pubs;");
    SqlCommand testCMD = new SqlCommand
    ("TestProcedure", PubsConn);
    
    testCMD.CommandType = CommandType.StoredProcedure;
    
    SqlParameter RetVal = testCMD.Parameters.Add
       ("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;
    SqlParameter IdIn = testCMD.Parameters.Add
      ("@au_idIN", SqlDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    SqlParameter NumTitles = testCMD.Parameters.Add
       ("@numtitlesout", SqlDbType.VarChar, 11);
    NumTitles.Direction = ParameterDirection.Output ;
    
    IdIn.Value = "213-46-8915";
    PubsConn.Open();
    
    SqlDataReader myReader = testCMD.ExecuteReader();
    Console.WriteLine ("Book Titles for this Author:");
    while (myReader.Read()) 
    
         Console.WriteLine ("0", myReader.GetString (2));
       };
    myReader.Close() ;
    Console.WriteLine("Number of Rows: " + NumTitles.Value );
    Console.WriteLine("Return Value: " + RetVal.Value);
    					

    OLE DB Data Provider

    OleDbConnection PubsConn = new OleDbConnection
       ("Provider=SQLOLEDB;Data Source=server;" +
       "integrated Security=sspi;initial catalog=pubs;");
    OleDbCommand testCMD = new OleDbCommand
       ("TestProcedure", PubsConn);
    
    testCMD.CommandType = CommandType.StoredProcedure;
    
    OleDbParameter RetVal = testCMD.Parameters.Add
       ("RetVal", OleDbType.Integer);RetVal.Direction = ParameterDirection.ReturnValue;
    OleDbParameter IdIn = testCMD.Parameters.Add
       ("@au_idIN", OleDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    OleDbParameter NumTitles = testCMD.Parameters.Add
       ("@numtitlesout", OleDbType.VarChar, 11);
    NumTitles.Direction = ParameterDirection.Output;
    
    IdIn.Value = "213-46-8915";
    
    PubsConn.Open();
    
    OleDbDataReader myReader = testCMD.ExecuteReader();
    Console.WriteLine ("Book Titles for this Author:");
    while (myReader.Read()) 
    
         Console.WriteLine ("0", myReader.GetString (2));
       };
    myReader.Close() ;
    Console.WriteLine("Number of Rows: " + NumTitles.Value );
    Console.WriteLine("Return Value: " + RetVal.Value);
    					

  5. Modify the connection string for the Connection object to point to the computer that is running SQL Server.
  6. Run the code. Notice that the DataReader retrieves the records and then returns the parameter values. You can use the Read method of the DataReader object to move through the returned records.

    The Output window displays the titles of two books, the return value of 5, and the output parameter, which contains the number of records (2). Notice that you must close the DataReader in the code to see the parameter values. Additionally, note that you do not have to move through all of the records to see the return parameters if the DataReader is closed.

Use the ExecuteScalar Method of the Command Object

You can use the ExecuteScalar method of the Command object to retrieve parameter values. Additionally, ExecuteScalar returns the first column of the first row of the stored procedure. This is most useful for aggregate functions as in the following example.

  1. Create the following stored procedure on the server that is running SQL Server:
    Create Procedure TestProcedure2
    (
      @au_idIN varchar (11)
    )
    As
    /* set nocount on */
    select count (T.title)
    from authors as A join titleauthor as TA on
    A.au_id=TA.au_id
    join titles as T
    on T.title_id=TA.title_id
    where A.au_id=@au_idIN
    Return(5)
    					

  2. Create a new Visual C# .NET Windows Application project.
  3. Use the using statement on the System and the System.Data namespaces so that you do not have to qualify declarations in those namespaces later in your code. Add this code to the top of the Form code module. Make sure that you copy only the code for the provider that you have chosen.SQL Client
    using System.Data.SqlClient;
    					

    OLE DB Data Provider

  4. Add the following code to the Form_Load event:SQL Client
    string strCount;
    SqlConnection PubsConn = new SqlConnection
       ("Data Source=server;integrated " +
       "Security=sspi;initial catalog=pubs;");
    SqlCommand testCMD = new SqlCommand
       ("TestProcedure2", PubsConn);
    
    testCMD.CommandType = CommandType.StoredProcedure;
    
    SqlParameter RetVal = testCMD.Parameters.Add
       ("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;
    SqlParameter IdIn = testCMD.Parameters.Add
       ("@au_idIN", SqlDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    
    IdIn.Value = "213-46-8915";
    
    PubsConn.Open();
    
    strCount =testCMD.ExecuteScalar ().ToString() ;
    
    Console.WriteLine("Number of Rows: " + strCount );
    Console.WriteLine("Return Value: " + RetVal.Value);
    					

    OLE DB Data Provider

    string strCount;
    OleDbConnection PubsConn = new OleDbConnection
       ("Provider=SQLOLEDB;Data Source=server;" +
       "integrated Security=sspi;initial catalog=pubs;");
    OleDbCommand testCMD = new OleDbCommand
       ("TestProcedure2", PubsConn);
    
    testCMD.CommandType = CommandType.StoredProcedure;
    
    OleDbParameter RetVal = testCMD.Parameters.Add
       ("RetVal", OleDbType.Integer);
    RetVal.Direction = ParameterDirection.ReturnValue;
    OleDbParameter IdIn = testCMD.Parameters.Add
       ("@au_idIN", OleDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    
    IdIn.Value = "213-46-8915";
    
    PubsConn.Open();
    
    strCount = testCMD.ExecuteScalar().ToString() ;
    
    Console.WriteLine("Number of Rows: " + strCount);
    Console.WriteLine("Return Value: " + RetVal.Value);
    					

  5. Modify the connection string for the Connection object to point to the computer that is running SQL Server.
  6. Run the code. Notice that the ExecuteScalar method of the Command object returns the parameters. ExecuteScalar also returns the value of column 1, row 1 of the returned rowset. Therefore, the value of intCount is the result of the count function from the stored procedure.

Use the ExecuteNonQuery Method of the Command Object

This sample uses the ExecuteNonQuery method to run the query and to return the parameter values. ExecuteNonQuery also returns the number of records that are affected after the query runs. However, ExecuteNonQuery does not return any rows or columns from the stored procedure.

The ExecuteNonQuery method is most useful when you use INSERT, UPDATE, or DELETE statements if you only have to know how many rows are changed. In a stored procedure in which you are using only a SELECT statement, you receive -1 because no rows are affected by the query.

  1. Create the following stored procedure on the computer that is running SQL Server:
    Create Procedure TestProcedure3
    (
      @au_idIN varchar (11),
      @au_fnam varchar (30)
    )
    
    As
    /* set nocount on */
    Update authors set au_fname = @au_fnam
    where au_id = @au_idin
    return (5)
    					

  2. Create a new Visual C# .NET Windows Application project.
  3. Use the using statement on the System and the System.Data namespaces so that you do not have to qualify declarations in those namespaces later in your code. Add this code to the top of the Form code module. Make sure that you copy only the code for the provider that you have chosen.SQL Client
    using System.Data.SqlClient;
    					

    OLE DB Data Provider

  4. Replace the code below the private Form1_Load event in the Form1 code module with the following code:SQL Client
    string strRowAffect;
    SqlConnection PubsConn = new SqlConnection
       ("Data Source=server;integrated Security=sspi;" +
       "initial catalog=pubs;");
    SqlCommand testCMD = new SqlCommand
       ("TestProcedure3", PubsConn);
    
    testCMD.CommandType = CommandType.StoredProcedure;
    
    SqlParameter RetVal = testCMD.Parameters.Add
       ("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;
    SqlParameter IdIn = testCMD.Parameters.Add
       ("@au_idIN", SqlDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    SqlParameter FnameIn = testCMD.Parameters.Add
       ("@au_fnam", SqlDbType.VarChar, 30);
    FnameIn.Direction = ParameterDirection.Input;
    
    IdIn.Value = "213-46-8915";
    FnameIn.Value = "Marjorie";
    
    PubsConn.Open();
    
    strRowAffect =testCMD.ExecuteNonQuery ().ToString() ;
    
    Console.WriteLine("Number of Rows: " + strRowAffect );
    Console.WriteLine("Return Value: " + RetVal.Value);
    					

    OLE DB Data Provider

    int intRowAffected;
    OleDbConnection PubsConn = new OleDbConnection
       ("Provider=SQLOLEDB;Data Source=server;" +
       "integrated Security=sspi;initial catalog=pubs;");
    OleDbCommand testCMD = new OleDbCommand
       ("TestProcedure3", PubsConn);
    
    testCMD.CommandType = CommandType.StoredProcedure;
    
    OleDbParameter RetVal = testCMD.Parameters.Add
       ("RetVal", OleDbType.Integer);
    RetVal.Direction = ParameterDirection.ReturnValue;
    OleDbParameter IdIn = testCMD.Parameters.Add
       ("@au_idIN", OleDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    OleDbParameter FnameIn = testCMD.Parameters.Add
       ("@au_fname", OleDbType.VarChar, 30);
    FnameIn.Direction = ParameterDirection.Input;
    
    IdIn.Value = "213-46-8915";
    FnameIn.Value = "Marjorie";
    
    PubsConn.Open();
    intRowAffected = testCMD.ExecuteNonQuery();
    
    Console.WriteLine("Number of Rows affected: " + intRowAffected);
    Console.WriteLine(RetVal.Value);
    					

  5. Modify the connection string for the Connection object to point to the computer that is running SQL Server.
  6. Run the code. The Output window displays the number of affected rows (intRowAffect) and the value of the return parameter.

For additional information, visit the following MSDN Web sites:

View article:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET

Page 322 of 695« First...102030...320321322323324...330340350...Last »

Recent Comments

    Archives

    Categories