SQL help in .NET WPF to retrieve multiple rows into DataGrid

1
votes

I'm making a program at work for people to retrieve gift card info.

I had a look around and there appears to be a few ways of doing this but I've not got one working yet. I'm trying to get some data for the history of a card (Multiple rows) out of a database and display it on a WPF form. I've changed the connection string and it used a variable cardNumber for the where clause. At the moment I've got the code posted below.

Edit: to clarify ResultsGrid is the Data Grid View on the UI

        if (CardNumber.TextLength == 14)
        {
            SqlConnection sqlConnection1 = new SqlConnection("Data Source=XXX;Initial Catalog=USICOAL;Integrated Security=True");
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.CommandText = "SELECT STORE_NO, WORKSTATION_NO, RTL_TRN_NO, AMOUNT, ACCT_TRN_TYPE_CODE, ACCT_TRN_DATETIME, OLD_BALANCE, NEW_BALANCE FROM ACCOUNT_TRANSACTION WHERE ACCOUNT_NO = " + cardNumber;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();

            reader = cmd.ExecuteReader();
            ResultsGrid.DataSource = reader;
            ResultsGrid.Refresh();

            sqlConnection1.Close();
        }
        //Else do nothing
        ...

        private void CardNumber_TextChanged(object sender, EventArgs e)
        {
            // Try converting Value to int
            try
            {
                cardNumber = long.Parse(CardNumber.Text);
            } catch // Catch Exception thrown
            {
                Console.WriteLine("Unable To Convert to long");
                CardNumber.Text = cardNumber.ToString();
            }
        }
2
c#sql.netwpfvisual-studio
What is the error? - Shon
There is no error the datagrid is just blank. ran debug and put a break in and can see that the reader has data in it. - Delo1987
remove the refresh line or do applcation.doevents() - Shon
Forgive my ignorance i'm new to .net could you elaborate on the doevent solution please. - Delo1987
it is the line of code which is ran automatically when ever a block of code is finished and returns to waiting on another event. in some cases you want to basically have changes which are made to the to the ui happen before then. eg if you update a textbox and then try to read from it before the control is returned to the ui then it will have the old value. i suggested it for testing purposes - Shon

2 Answers

0
votes

Simply try as below, (between sqlConnection1.Open(); and close();)

SqlDataAdapter da=new SqlDataAdapter(cmd);
DataTable dt=new DataTable("ACCOUNT_TRANSACTION");
da.Fill(dt);
ResultsGrid.ItemsSource=dt.Defaultview;

I recommend to write codes manually with automatic intellisense support by visual studio meaning don't just copy above code because it is case sensitive.

and you don't need 3 lines,

reader = cmd.ExecuteReader();
ResultsGrid.DataSource = reader;
ResultsGrid.Refresh();
0
votes

Guys thanks for all your help it's working now. i'm new so my upvote doesn't display yet but you've goten them.

It now looks like this

SqlConnection sqlConnection1 = new SqlConnection();
sqlConnection1.ConnectionString = @"Data Source=###; Initial Catalog =USICOAL; Integrated Security=True";

SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection1;
cmd.CommandText = "SELECT STORE_NO, WORKSTATION_NO, RTL_TRN_NO, AMOUNT, ACCT_TRN_TYPE_CODE, ACCT_TRN_DATETIME, OLD_BALANCE, NEW_BALANCE FROM ACCOUNT_TRANSACTION WHERE ACCOUNT_NO = " + cardNumber + "order by ACCT_TRN_DATETIME";

sqlConnection1.Open();

DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
ResultsGrid.DataSource = dt;