Retrieve the previous record and store to text boxes

Question

I am working on a project in Visual Studio 2017 using C# which shows and saves test results as well as compare the current and previous records, although I created a trial solution file to simplify the tables.

One of the functions of it is similar to this question except I have this table in my service-based database dtbTrial:

CREATE TABLE [dbo].[tblTest1]
(
    [TestNumber] NUMERIC(8) NOT NULL PRIMARY KEY, 
    [ParameterA] REAL NULL, 
    [ParameterB] REAL NULL, 
    CONSTRAINT [FK_tblTest1_tblTestMain] FOREIGN KEY ([TestNumber]) REFERENCES [tblTestMain]([TestNumber])
)

and intended to retrieve the previous record and put ParameterA and ParameterB of the previous record to its respective text boxes. The concerned table currently has three rows.

Using the linked question above, I tried to replicate it and run to my code on button click. I have tried two query strings based on the answers:

WITH dbo AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY TestNumber ORDER BY TestNumber) row_num FROM tblTest1)
SELECT a.TestNumber,
a.ParameterA, (SELECT MAX(b.ParameterA) FROM dbo b WHERE b.TestNumber = a.TestNumber AND b.row_num < a.row_num) AS PrevParameterA,
a.ParameterB, (SELECT MAX (b.ParameterB) FROM dbo b WHERE b.TestNumber = a.TestNumber AND b.row_num < a.row_num) AS PrevParameterB
FROM dbo a  WHERE TestNumber = @testnumber"

and

SELECT TestNumber, ParameterA, LAG(ParameterA) OVER (PARTITION BY TestNumber ORDER BY TestNumber) PrevParameterA, ParameterB, LAG(ParameterB) OVER (PARTITION BY TestNumber ORDER BY TestNumber) PrevParameterB FROM tblTest1 WHERE TestNumber = @testnumber

then run the following code:

using (SqlConnection con = new SqlConnection(constring))
            {
                using(SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@testnumber", testNumberTextBox.Text);
                    con.Open();

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    textBox2.Text = (dr["PrevParameterA"].ToString());
                    textBox1.Text = (dr["PrevParameterB"].ToString());
                }

                con.Close();
            }
        }

The intended output did not actuate i.e. the text boxes remain null or of no value. I also tried running both to the dataset of the database and the following error appears:

The OVER SQL construct or statement is not supported.

A beginner as I am, I'm at loss as how to fix it. What did I miss or what should I do to rectify this?

score 0 · Answer 1 · answered Jan 16 '18 at 07:26

0

Based on this post there is a chance that including alias should fix your problem. Thus, your subquery will be looking something like this:

SELECT t.*, 
    ROW_NUMBER() OVER (PARTITION BY TestNumber ORDER BY TestNumber) row_num 
FROM tblTest1 t

answered Jan 16 '18 at 07:26

Rufi

2,529
1
20
41

1

Using an alias still gives null text boxes but I might take note of using aliases when needed. Thanks. – eleu Jan 16 '18 at 07:59

score 0 · Accepted Answer · answered Jan 16 '18 at 07:32

According to your table definition and code, you want to get the previous record of given testnumber. Then you don't need to use over or other any complex queries.

This query enough for you

SELECT TOP 1 * FROM [tblTest1] WHERE TestNumber < @testnumber ORDER BY TestNumber DESC

Retrieve the previous record and store to text boxes

2 Answers2