Solved

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

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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