Having trouble connecting with my datasource

deadEddie

Hey there, I'm sure there is some simple newbie problem with my code, but I have not been able to figure it out and was hoping another set of eyes would help. I'm working on a financial accounting tool that so far has two tables, a Users table and an AccountsTransactions table, The below method is from my DatabaseCommunicator class. It builds fine but crashes with a message that the "Balance" column is not in the AccountsTransactions Table. Since I know that the column is in fact in the table, the only thing I can think of as being the problem is that I'm not actually connecting to the datasource and filling the dataset. Can anyone see where I'm going wrong? Thanks in advance. :confused:

public string[] CreateNewTransaction(int UserID) // note the use of string[] as the method return type
{
// create an array to hold method results
string[] returnArray = new string[2];

        string selectSql = "SELECT Count(\*) FROM AccountTransactions WHERE UserID = @UserID";

        // new SqlConnection
        connect = new SqlConnection("Data Source=.\\\\SQLEXPRESS;AttachDbFilename='C:\\\\Users\\\\Ed\\\\Documents\\\\Visual Studio 2010\\\\Projects\\\\School Projects\\\\Accounts\\\\Accounts\\\\Accounts.mdf';Integrated Security=True;User Instance=True");

        // initialize SqlDataAdapter
        dataAdapter = new SqlDataAdapter();

        SqlCommand cmd = new SqlCommand(selectSql, connect);

        dataAdapter.SelectCommand = cmd;

        // parameters for cmd
        cmd.Parameters.AddWithValue("@UserID", UserID);

        // create a new DataSet
        dataSet = new DataSet();
                    
        dataAdapter.Fill(dataSet, "AccountTransactions");

        // create variable equal to the count of rows in the database and set the DataRow to that row number
        int lastRow = dataSet.Tables\["AccountTransactions"\].Rows.Count-1;
        DataRow dRow = dataSet.Tables\["AccountTransactions"\].Rows\[lastRow\];
                    
        returnArray\[0\] = Convert.ToString(dataSet.Tables\["AccountTransactions"\].Rows\[lastRow\]\["Balance"\]);
        returnArray\[1\] = Convert.ToString(dataSet.Tables\["AccountTransactions"\].Rows\[lastRow\]\["TransactionNo"\]);

        connect.Dispose();

        return returnArray;
    }

Hum Dum

deadEddie wrote:

string selectSql = "SELECT Count(*) FROM AccountTransactions WHERE UserID = @UserID";

You are selecting column which are not present is select statement.

deadEddie wrote:

returnArray[0] = Convert.ToString(dataSet.Tables["AccountTransactions"].Rows[lastRow]["Balance"]); returnArray[1] = Convert.ToString(dataSet.Tables["AccountTransactions"].Rows[lastRow]["TransactionNo"]);

For accessing balance and transaction include them is select statement, like

Select Balance, TransactionNo FROM AccountTransactions WHERE UserID = @UserID;

AnnieCalvert

It may help you .read it. http://www.dapfor.com/en/net-suite/net-grid/tutorial

deadEddie

:) Thanks Hum Dum. Of course that fixed my problem. DE

deadEddie

Thanks Annie. Good read