retrieve value from sql string and store in a variable

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 1951 Views - Last Post: 01 August 2015 - 12:40 PM Rate Topic: -----

#1 peterpiper   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 30
  • Joined: 01-August 15

retrieve value from sql string and store in a variable

Posted 01 August 2015 - 05:20 AM

I have a button on a form in c# that runs the query below and populates a datagridview.
One of the columns in the dgv is called TOTAL. It is that column value (TOTAL) which I
would like to save/store as a variable or in a variable to then use in a new SQL query
that follows. I don't know how to do this. I have no problem in doing it the other way
round. This is the query
sda = new SqlDataAdapter(@"SELECT detail.branch, SUM(CONVERT(DECIMAL(8, 2), detail.total)) AS TOTAL, 
        SUM(CONVERT(DECIMAL(8, 2),detail.typeA / 1.2)) AS TA, 
FROM    detail 


Is This A Good Question/Topic? 0
  • +

Replies To: retrieve value from sql string and store in a variable

#2 tlhIn`toq   User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6535
  • View blog
  • Posts: 14,450
  • Joined: 02-June 10

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 05:24 AM

See FAQ #13 Q:... how to do x,y,z with a database


tlhIn`toq's FAQ list

Learning to debug one's own code is an essential skill. Sadly, one that apparently few college courses teach. Silly if you ask me.
Placing breakpoints and walking through the code line by line allows you to actually WATCH it execute, check the condition of each of variable's run-time value, and watch the logic unfold right before your eyes.
Visualizing what your code does will let you see why it behaves the way it does.
It would be well worth your time to do the tutorials on FAQ 2. A couple hours learning this skill will save you hundreds of hours of confusion in one project alone.

In addition to FAQ 2 in the list below, also check my signature block for a link on how to look at your variables' values at run-time.

TOP most asked:
What does this error message mean?
FAQ 2: How do I debug
FAQ 3: How do I make Class1/Form1 talk to Class2/Form2


FAQ (Frequently Asked Questions - Updated July 2013
Spoiler



Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,271
  • Joined: 12-December 12

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 08:25 AM

What is preventing you from reading a value from a cell of the DGV? Post your code attempt.

If you actually want to sum the column first then you'll need to loop through the rows. Examples for this are easily found. In either case, you could store the value in a variable and pass its value as a parameter to a SQL statement.
Was This Post Helpful? 0
  • +
  • -

#4 peterpiper   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 30
  • Joined: 01-August 15

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 09:00 AM

View Postandrewsw, on 01 August 2015 - 08:25 AM, said:

... store the value in a variable and pass its value as a parameter to a SQL statement.


That's exactly what I need to achieve, store the value of one field in that Sql Statement,
the value of TOTAL.
I know how to pass the value as a parameter to a SQL statement but I don't know how to
get and store the value in the first place
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,271
  • Joined: 12-December 12

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 09:10 AM

It is odd that you have managed to get a DataGridView up and running but haven't discovered a way to read values from it.

Ah well, investigate dataGridView1.Rows[0].Cells[0].Value.

I trust that you are using the MSDN documentation effectively?

DataGridViewCell.Value Property :MSDN

ADDED On re-reading, it seems that you want to retrieve the value from the SQL statement, not from the DGV? If so then I think you could clarify your question further. For example, does your SQL return several rows? In which case, which row are you trying to read?

This post has been edited by andrewsw: 01 August 2015 - 09:17 AM

Was This Post Helpful? 0
  • +
  • -

#6 peterpiper   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 30
  • Joined: 01-August 15

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 10:46 AM

I know how to reference the dgv and get values from it. It's the value
from the SQl that I need to extract. The form contains two dgv's so by
getting the value from the SQL I can then pass that value to a new SQL
statement which in urn will populate the second dgv. Hope that doesn't
sound too confusing.
Unless there is a better what to achieve this ?
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,271
  • Joined: 12-December 12

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 11:01 AM

Again, your SQL will return several rows, unless there happens to be only a single branch, so which row's total are you trying to read? Your statement is also missing GROUP BY detail.branch.

Presumably there is a DataTable involved as well, so it might be a question of reading, or computing, from this table. Your question lacks details.
Was This Post Helpful? 0
  • +
  • -

#8 peterpiper   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 30
  • Joined: 01-August 15

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 11:07 AM

View Postandrewsw, on 01 August 2015 - 09:10 AM, said:

It is odd that you have managed to get a DataGridView up and running but haven't discovered a way to read values from it.

Ah well, investigate dataGridView1.Rows[0].Cells[0].Value.

I trust that you are using the MSDN documentation effectively?

DataGridViewCell.Value Property :MSDN

ADDED On re-reading, it seems that you want to retrieve the value from the SQL statement, not from the DGV? If so then I think you could clarify your question further. For example, does your SQL return several rows? In which case, which row are you trying to read?


Andrew. Didn't spot the addition to your answer.
I may have partly answered your question a few minutes ago.
The SQL only returns one row. There can only be one TOTAL value
If I can get that value then I could pass it to another SQL statement
Was This Post Helpful? 0
  • +
  • -

#9 peterpiper   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 30
  • Joined: 01-August 15

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 11:13 AM

View Postandrewsw, on 01 August 2015 - 11:01 AM, said:

Again, your SQL will return several rows, unless there happens to be only a single branch, so which row's total are you trying to read? Your statement is also missing GROUP BY detail.branch.

Presumably there is a DataTable involved as well, so it might be a question of reading, or computing, from this table. Your question lacks details.


It will only ever return one row. There is a DataTable involved as follows:
            dt = new DataTable();
            sda.Fill(dt);
            dg.DataSource = dt;

Was This Post Helpful? 0
  • +
  • -

#10 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,271
  • Joined: 12-December 12

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 11:20 AM

Then a DataTable contains DataRows, and a DataRow contains

DataRow.Item Property (DataColumn) :MSDN

so it's probably dt.Rows[0][0] to obtain a value.

(If there is only ever one row then there doesn't seem much point in filling a DGV.)
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,271
  • Joined: 12-December 12

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 11:50 AM

You also don't need to aggregate (SUM) if there is a single row.
Was This Post Helpful? 0
  • +
  • -

#12 peterpiper   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 30
  • Joined: 01-August 15

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 11:52 AM

View Postandrewsw, on 01 August 2015 - 11:20 AM, said:

Then a DataTable contains DataRows, and a DataRow contains

DataRow.Item Property (DataColumn) :MSDN

so it's probably dt.Rows[0][0] to obtain a value.

(If there is only ever one row then there doesn't seem much point in filling a DGV.)


Good point. Hadn't thought of that. Is it overkill ? What would you use in its place ?
I started out by using labels to display the data. Thought a Dgv looked better
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,271
  • Joined: 12-December 12

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 11:54 AM

Labels or TextBoxes.
Was This Post Helpful? 0
  • +
  • -

#14 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,271
  • Joined: 12-December 12

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 12:14 PM

For a single row, retrieved once, forget about the DGV and filling a DataTable. You could just use ExecuteReader and read the single row.

Advice would have been a lot easier to give if your question had been clearer :whistling:
Was This Post Helpful? 0
  • +
  • -

#15 peterpiper   User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 30
  • Joined: 01-August 15

Re: retrieve value from sql string and store in a variable

Posted 01 August 2015 - 12:27 PM

I'am a total novice in c#. Learning as I go along
You say, ExecuteReader to read the single row.
I only need one value from that row (TOTAL)

Anyway, will use text boxes as you suggest

But how is my initial question not clear ? I wanted
just one value from that SQL String I posted. Don't
know how else I could have expressed that. Is it to do
with c# terminology ?. As I said, very new to programming

This post has been edited by andrewsw: 01 August 2015 - 12:37 PM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2