How to read 1 record from a sql server database into a c# textbox?

Question

I want to do this (on Form3):

sqlcon.Open();
            string selectQuery = "SELECT * FROM InvoiceTable where id = @id";
            SqlCommand sqlcmd = new SqlCommand(selectQuery, sqlcon);
            sqldr = sqlcmd.ExecuteReader();
            while(sqldr.Read())
            {
                textbox1 = Max value of TotalCostColumn.value
                or
                textbox1 = Latest value of TotalCostColumn.value
                or
                textbox1 = 2nd row of TotalCostColumn.value
            }

Im not having any datagridview on this form. I do have a datagridview on Form1 which is the actual invoice form. Form3 is the confrimation form which lets the user confirm how much customer should pay eventually. I want the TotalCostColumn's value from my database to be transfered in the related textbox in Form3.

Don't execute a SqlReader if you only need one value. Use ExecuteScalar. Also do yourself a favour and call your forms and text boxes some meaningful. — Palle Due
– Palle Due, Commented Nov 19, 2018 at 9:02
My comment is out of topic, but I strongly advise you to correctly name your forms elements. What if you don't write any code for your application for 6 monthes and want to modify it. Will you remember what textbox1, Form1 button42 etc... refers to? — Cid
– Cid, Commented Nov 19, 2018 at 9:05
@PalleDue thank you for your advice I appreciate it. actually I named them in a meaningfull my self but I thought I have to change those names to these for simplicity — Daniel_Ranjbar
– Daniel_Ranjbar, Commented Nov 19, 2018 at 15:00
@Cid thank you for your advice and your care. I actually changed my real names to these in order them to be simple... I guess I was wrong... thank you — Daniel_Ranjbar
– Daniel_Ranjbar, Commented Nov 19, 2018 at 15:01

Rahul · Accepted Answer · 2018-11-19 09:02:19Z

2

textbox1 = Max value of TotalCostColumn.value

If that's your requirement then why not build the query as per like

SELECT max(TotalCostColumn) FROM InvoiceTable where id = @id

And then instead of using ExecuteReader() you should call ExecuteScalar() which would return a single value

answered Nov 19, 2018 at 9:02

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Robin Bennett · Accepted Answer · 2018-11-20 08:56:45Z

1

This is how you'd get the Max value with ExecuteScalar:

string selectQuery = "SELECT MAX(TotalCostColumn) FROM InvoiceTable where id = @id";
var sqlcmd = new SqlCommand(selectQuery, sqlcon);
var result = sqlcmd.ExecuteScalar();
return int.Parse(result.ToString());

MAX will ensure you only get one row from the table. To get the latest value, you'd have to sort the table (I assume there's a transaction date) and add TOP 1 to get only the first row. (Although ExecuteScalar will ignore everything except the first field of the top row but there's no point returning data you don't need)

There are two ways to get the second row. One is to use a datareader and skip over the first row. The other is with SQL:

SELECT TOP 1 FROM 
(
  SELECT TOP 2 TotalCost FROM InvoiceTable where id = @id ORDER BY TotalCost DESC
) ORDER BY TotalCost ASC

Here the inner query gets two rows of data sorted with the largest item first, and the outer query picks the lower value of the two.

edited Nov 20, 2018 at 8:56

answered Nov 19, 2018 at 9:29

Robin Bennett

3,2411 gold badge11 silver badges22 bronze badges

2 Comments

Daniel_Ranjbar Over a year ago

it underlined the result in Parse(result);and threw this error cannot convert from object to string when I used your first code block

Robin Bennett Over a year ago

@absolute455 - sorry, I've updated my answer to 'int.Parse(result.ToString());' Also, I'm only guessing that you want an 'int' - a cost could be 'decimal'.

nikhil kumar · Accepted Answer · 2018-11-19 09:43:59Z

0

You need to write a stored procedure which accepts the value of the id

stored procedure is defined as follows

create proc selectmax(@id int)
as begin
select max(TotalCostColumn) FROM InvoiceTable where id = @id
end

and call it as below using Disconnected Model so sqlcon.open() is not Required

SqlCommand sqlcmd = new SqlCommand(selectmax, sqlcon);
sqlcmd.CommandType=CommandType.StoredProcedure
SqlDataAdapter adapter= new SqlDataAdapter(sqlcmd);
      DataSet value=new DataSet();
adapter.Fill(value);

This is the implementation using Ado.net Disconnected Model

answered Nov 19, 2018 at 9:43

nikhil kumar

651 silver badge10 bronze badges

Collectives™ on Stack Overflow

How to read 1 record from a sql server database into a c# textbox?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related