Solved

How to correct a C# console application to execute a stored procedure?

Posted on 2012-04-09
2
176 Views
Last Modified: 2012-06-21
I have written the following C# console appliation to execute a stored procedure but no output is generated. Can you tell me where I went wrong? I don't have a SQL table to write to at the present time.

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Globalization;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.Odbc;
using System.Runtime.InteropServices;

namespace ENA
{
    class Program
    {
       
        static OdbcConnection cn1 = new System.Data.Odbc.OdbcConnection
                ("DSN=1;UID=2;PWD=7;");

        static void Main(string[] args)
        {
            //   using (StreamReader sr = new StreamReader("NameAddr.txt"))
            cn1.Open();
            try
            {
                //     test
                string strAct = "";
                //string strLastName = "";
                strAct = "019172436";
                               
                ///////  call StoredProced //////
                OdbcCommand cmd = new OdbcCommand("getTATGATCDSAByAccount", cn1);
                                cmd.CommandType = CommandType.StoredProcedure;
             
                cmd.CommandText = "exec getTATGATCDSAByAccount @ApplnNm='test', @accountNumber='" + strAct + "'";
                cmd.CommandText = cmd.CommandText + ",@accountSystem=1435,@selectFields='accountNumber, accountStatus";
                cmd.CommandText = cmd.CommandText + ", fullName1, address1, city, stateCode, postalCode, taxID, accountClassification";
                cmd.CommandText = cmd.CommandText + ", fullName2, countryCode, countryOfResidence, countryOfCitizenship, accountCategory";
                cmd.CommandText = cmd.CommandText + ", cashIndicator, marginIndicator, codIndicator'";
               
                OdbcDataReader reader = cmd.ExecuteReader();
               
                Console.WriteLine();

            }
            catch (IOException e)
            {
                Console.WriteLine(e.Message);
                return;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return;
            }
        }
    }
}
0
Comment
Question by:zimmer9
2 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 37825820
You are specifying a stored procedure to call but then are specifying commandtext where you need to specify parameters see the linked articles odbc section for an example of how to do this :

http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.71).aspx
0
 
LVL 10

Expert Comment

by:himanshut
ID: 37825841
Put a debugger at this line -->   cmd.CommandText = cmd.CommandText + ", cashIndicator, marginIndicator, codIndicator'";


and see what text do you have.

Copy that text and execute it in database itself to see if your stored procedure returns any values in SQL?
I think it would be more to do with filters and hence you are not getting results.

TEST IT BY:
reduce filters something like in stored procedure: just keep select * from tblname where id = @ID

and pass @ID from .net to SQL as parameter and see if you get any results there.

Cheers
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now