Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-04-09
2
Medium Priority
?
229 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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Loops Section Overview

886 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