C# datatable not reading from proc!

[lemunk]

Hi guys,

Im writing a windows c# form at the moment and after all the headaches of gettin my stored procedure to combine 2 tables from 2 databases from 2 servers that had different collations(.....yeh MASSIVE headache)ive hit another snag!

here's my form1 code, no where near completed, missing my try/catchs, methods etc.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace RepSalesNetAnalysis
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent(); 
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string myConn = "Server=fake;" +
                        "Database=shaftdata;" +
                        "uid=fake;" +
                        "pwd=fake;" +
                        "Connect Timeout=300;";
            SqlConnection conn = new SqlConnection(myConn);
            conn.Open();

            SqlCommand Pareto = new SqlCommand();
            Pareto.Connection = conn;
            Pareto.CommandType = CommandType.StoredProcedure;
            Pareto.CommandText = "AAlinkParetoOK";

            BindingSource bindme = new BindingSource();
            DataTable mytable = new DataTable();
            
            SqlDataAdapter table1 = new SqlDataAdapter(Pareto);
            //SqlDataReader read = Pareto.ExecuteReader();
            
            table1.Fill(mytable);
            TESTER.Text = "ok it worked";
            
            conn.Close();
            //bindme.DataSource = table1;
            //dataGridView1.DataSource = bindme; 
        }
    }
}

i seem to connect no problem, i seem to create my adapters no problem but when i call my stored procedure no data is put into my table(ignore my commented out lines, they are part of my debugging).
ive double checked my sql proc and it does work. so there somthing about me calling it that doesnt.
Any help would be excellent!

[lemunk]

bump

[lemunk]

re bump(is it ok to bump posts btw?)

[Antenka]

Hello, lemunk.
Take a look at this: How to: Execute a Stored Procedure that Returns Rows . And at DbDataAdapter.Fill Method (DataTable).

The DataAdapter uses SelectCommand property to retrieve a data from DataBase. Which is not specified in your code.

P.S. bumping, at least, is not appreciated ;)

[lemunk]

Hi again, thanks for the advice.
i tried a number of different methods but all with no results.
Ive checked and it seems i am connecting and running the procedure (used execute scalar).
I have a really stange feeling it could be my query as its not a simple select all query.
here it is.

USE [ShaftData]
GO
/****** Object:  StoredProcedure [dbo].[ztester1]    Script Date: 11/22/2011 08:06:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ztester1]
AS
SELECT 
	A.Acct, 
	A.Name, 
	A.Document, 
	A.Part,
	B.Pareto, 
	A.PG,   
	A.Qty, 
	A.Unit, 
	A.[datetime], 
	A.Year_1, 
	A.Month_1 
FROM 
	[NEWPareto] B 
INNER JOIN 
OPENQUERY(sacbtest, 'SELECT Acct,Name,Document,Part,PG,Qty,Unit,[DateTime],year_1,Month_1 FROM AutoPart.Dbo.vSpends') A
ON 
	A.part collate SQL_Latin1_General_CP1_CI_AS = B.part;

as you can see my procedure is calling from 2 different servers (they dont even have same collation which was a nightmare to figure out) and joins them to get my results i need. This query does work and execute which is great.

Is there something about joins that interferes with the adapter? does it have to be a table your connecting too and not a query?
i would post all the variations in my code but that would take half a day. lets just say ive tried almost everything. Even the VB programmer took a look and couldnt figure out why it would display any data at all.

p.s ignore the name of the procedure and what i called, in my code i am calling it.

[lemunk]

oh WOW, its finally working.....you know why?

SQLadapter already opens a connection and closes it. in my code im opening also and closing via code conn.Open(); etc.
For some reason this seems to interfear with the retrieval of data......
This one has drove me to the edge of insanity!!
thanks for the help anyway guys.