Here is an example using a ComboBox and a TextBox. I've used a ComboBox with fixed entries for the user to choose from. I don't want them to enter the column name for a few reasons: They might misspell it, they might introduce other errors and because I'm using string concatenation to get the column name (only the name, NOT the value) it provides a further protection against SQL Injection.
privatevoid button1_Click(object sender, EventArgs e)
if (cmbColumns.SelectedIndex == -1)
MessageBox.Show("You must select a column");
MessageBox.Show("You must enter a value to search for");
// This bit would NOT normally be within the UI layervar conString = ConfigurationManager.ConnectionStrings["ConnectToDB"].ConnectionString;
using (SqlConnection conn = new SqlConnection(conString))
using (var myCommand = new SqlCommand())
myCommand.Connection = conn;
var sb = new StringBuilder(
"SELECT First_Name, Last_Name, Grid_Square, Country, State, Call_Sign, Date_Time, Mode, Power ");
sb.Append("FROM clouddata WHERE ");
myCommand.CommandText = sb.ToString();
using (var adapter = new SqlDataAdapter(myCommand))
var myTable = new DataTable();
dataGridView1.DataSource = myTable;
Hi, I have a problem generating SQL to return a single row for a client. My table structure is the following...
Clients - ClientID, ClientName
Statuses - StatusID, StatusDescription, ClientID
StatusMappings - StatusID, ClientID
The statuses can be different depending on the client, so for example, clients table:
I'm not sure about the example you are presenting ... Are the following statements true?
- The Statuses table shows the possible statuses for each Client?
- The StatusMapping table shows the actual statuses for each client.
If that is the case then with the data you presented you cannot possibly get the results because for Client "Halifax" Status 'Draft Letter' is Id 3 and there is nothing on the StatusMappings table for that Id. Similarly 'Awaiting Response' would be Id 4.
Assuming you get the data sorted out then something like the following will work
SELECT * FROM
SELECT C.ClientID, ClientName, StatusDescription,
CASEWHEN SM.StatusID ISNOTNULLTHEN'Y'ELSE'N'ENDAS Actual
LEFTOUTERJOIN@Statuses S ON C.ClientID = S.ClientID
LEFTOUTERJOIN@StatusMappings SM on SM.StatusID = S.StatusID
MAX(Actual) FOR StatusDescription in ([Draft Letter],[Awaiting Response],[Complete])
WHERE ClientName = 'Barclays'
If you want to use it in an SSRS report then put the query into a Stored Procedure, passing the Client name or id as a parameter.
If the Status Descriptions are not standard or consistent (i.e. not the ones listed) then you will have to use some dynamic sql.
There are articles here on CodeProject on all the topics above
I achieved this in the end by dynamically creating strings to pass in to a SQL statement I was building up. I used MAX and Case statements to achieve this. I finished it an EXEC (@MyString). Thanks for the advice!
All you need to do is to use [SUM()](https://www.techonthenet.com/oracle/functions/sum.php) - one of [aggragate functions](https://docs.oracle.com/database/121/SQLRF/functions003.htm#SQLRF20035):
SELECT SUM(VISIT_FEE) AS FEE,
EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,
EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_,
EXTRACT(DAY FROM VISIT_DATE) AS DAY_
WHERE IS_PAY = 1
GROUP BY EXTRACT(YEAR FROM VISIT_DATE),
EXTRACT(MONTH FROM VISIT_DATE) ,
EXTRACT(DAY FROM VISIT_DATE)