Solved

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

Posted on 2012-04-09
2
205 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
[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
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

632 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