|
Hi, I have a small program that is for my own use that has a small database. In my program i have a DataGrid to display the information. Right now when i search i have to search via columns, what i want is to search for data contained in the column rows and only display the data searched. Below is the code i have that will return information when a "Column" is entered into the textbox "tbSearch" but I need to be able to search the column rows. I have nine columns that will contain data and i need to search those column rows.
private void button_Click(object sender, RoutedEventArgs e)
{
using (SqlConnection conn = new SqlConnection())
{
int result = 0;
string searchOut = tbSearch.Text;
conn.ConnectionString = " Data Source=(LocalDB)\\MSSQLLocalDB;Database=cloudyhamdb.mdf;Trusted_Connection=True;AttachDbFilename =|DataDirectory|cloudyhamdb.mdf; Integrated Security = True;";
SqlCommand MyCommand = new SqlCommand("INSERT INTO clouddata " + " (First_Name, Last_Name, Grid_Square, Country, State, Call_Sign, Date_Time, Mode, Power)" + " Values (@First_Name, @Last_Name, @Grid_Square, @Country, @State, @Call_Sign, @Date_Time, @Mode, @Power)", conn);
MyCommand.Parameters.Add("@First_Name", System.Data.SqlDbType.Text);
MyCommand.Parameters.Add("@Last_Name", System.Data.SqlDbType.Text);
MyCommand.Parameters.Add("@Grid_Square", System.Data.SqlDbType.Text);
MyCommand.Parameters.Add("@Country", System.Data.SqlDbType.Text);
MyCommand.Parameters.Add("@State", System.Data.SqlDbType.Text);
MyCommand.Parameters.Add("@Call_Sign", System.Data.SqlDbType.Text);
MyCommand.Parameters.Add("@Date_Time", System.Data.SqlDbType.SmallDateTime);
MyCommand.Parameters.Add("@Power", System.Data.SqlDbType.Text);
MyCommand.Parameters.Add("@Mode", System.Data.SqlDbType.Text);
MyCommand.Parameters["@First_Name"].Value = Convert.ToString(tbFirstName.Text);
MyCommand.Parameters["@Last_Name"].Value = Convert.ToString(tbLastName.Text);
MyCommand.Parameters["@Grid_Square"].Value = Convert.ToString(tbGridSquare.Text);
MyCommand.Parameters["@Country"].Value = Convert.ToString(tbCountry.Text);
MyCommand.Parameters["@State"].Value = Convert.ToString(tbState.Text);
MyCommand.Parameters["@Call_Sign"].Value = Convert.ToString(tbCallSign.Text);
MyCommand.Parameters["@Date_Time"].Value = Convert.ToDateTime(DateTime.Now);
MyCommand.Parameters["@Power"].Value = Convert.ToString(tbPower.Text);
MyCommand.Parameters["@Mode"].Value = Convert.ToString(tbMode.Text);
conn.Open();
MyCommand.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da = new SqlDataAdapter("Select * FROM clouddata", conn);
DataSet ds = new DataSet();
if (tbSearch.Text.Length >= 1)
{
da.Fill(ds, "MyDataBinding");
DataRow[] returnRows = ds.Tables[0].Select("Last_Name" + searchOut);
returnRows = ds.Tables[0].Select("First_Name=" + searchOut);
result = returnRows.Length;
dataGrid2.DataContext = ds.Tables[0];
int results = MyCommand.ExecuteNonQuery();
}
conn.Close();
}
}
modified 23-May-17 13:26pm.
|
|
|
|
|
I may have missed something, but that is an INSERT statement. Surely a search would involve a SELECT from a pre-existing table. Something along the lines of
SqlCommand MyCommand = new SqlCommand("SELECT First_Name, Last_Name, Grid_Square, Country, State, Call_Sign, Date_Time, Mode, Power FROM clouddata WHERE First_Name = @First_Name", conn);
MyCommand.Parameters.AddWithValue("@First_Name", searchOut);
If the column you are going to search on can differ then you will need two textboxes or similar, one to name the column to search on and one to give the value to search on.
|
|
|
|
|
Can you provide an example for using 2 textboxes?
|
|
|
|
|
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.
private void button1_Click(object sender, EventArgs e)
{
if (cmbColumns.SelectedIndex == -1)
{
MessageBox.Show("You must select a column");
return;
}
if (string.IsNullOrEmpty(tbSearch.Text))
{
MessageBox.Show("You must enter a value to search for");
return;
}
var conString = ConfigurationManager.ConnectionStrings["ConnectToDB"].ConnectionString;
using (SqlConnection conn = new SqlConnection(conString))
{
conn.Open();
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 ");
sb.Append(cmbColumns.SelectedItem);
sb.Append("=@searchValue");
myCommand.CommandText = sb.ToString();
myCommand.Parameters.AddWithValue("@searchValue", tbSearch.Text);
using (var adapter = new SqlDataAdapter(myCommand))
{
var myTable = new DataTable();
adapter.Fill(myTable);
dataGridView1.DataSource = myTable;
}
}
}
}
|
|
|
|
|
Team need help on this.
I have an SSRS Report:
In the matrix table:
I have a column name called [DER_CW_FLAG] which provides output in 3 columns named: CW,Other_Week,PW.
The outputs for these columns are obtained using expression "=sum(Fields!TotalApproved.Value)/SUM(Fields!Total.Value)"
I Need to calculate the difference between PW and CW in another column.
|
|
|
|
|
You can't work out that you need to create an expression in a new field that calculates the difference? Give it up and look for another career.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
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:
1, Barclays
2, Halifax
Statuses table:
1, Draft Letter, 1
2, Complete, 1
3, Draft Letter, 2
4, Awaiting Response, 2
5, Complete, 2
StatusMappings table:
1, 1 (Draft Letter for Barclays)
3, 2 (Draft Letter for Halifax)
4, 2 (Awaiting Response for Halifax)
How I want to display the data in a report (based on client selected), for example :
Client | Draft Letter | Awaiting Response | Complete |
-------------------------------------------------------------
Halifax Y Y
I'm not sure how I would best write the SQL for this. Also I would like to produce SSRS reports based on it. Can anybody please advise? Thanks
modified 22-May-17 10:30am.
|
|
|
|
|
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.
|
|
|
|
|
You're quite right sorry, I've amended the data. Thanks!
|
|
|
|
|
Assuming you get the data sorted out then something like the following will work
SELECT * FROM
(
SELECT C.ClientID, ClientName, StatusDescription,
CASE WHEN SM.StatusID IS NOT NULL THEN 'Y' ELSE 'N' END AS Actual
FROM @Clients C
LEFT OUTER JOIN @Statuses S ON C.ClientID = S.ClientID
LEFT OUTER JOIN @StatusMappings SM on SM.StatusID = S.StatusID
) qry
PIVOT
(
MAX(Actual) FOR StatusDescription in ([Draft Letter],[Awaiting Response],[Complete])
) pvt
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
|
|
|
|
|
Thanks CHill60, yes I will need to use dynamic SQL. I've seen various articles using cursors, XML, etc. I just wondered which was the best approach to take.
|
|
|
|
|
Cursor is a definite "No!"  (See my article Processing Loops in SQL Server[^] )
XML is a definite possibility, but the code I've shown using PIVOT is quite compact. There is an example of generating dynamic sql for a pivot in the article.
|
|
|
|
|
Wow, looks a great article. Thanks, will take a look at this and reply once I have.
|
|
|
|
|
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!
|
|
|
|
|
|
|
|
A rubbish question I agree, but not sure why it's a spam setup.
Mea culpa, just seen it.
|
|
|
|
|
Hi..
Please help me in running below sql query by creating and calling a stored procedure.
Query:
$incidentQuery = "SELECT * FROM pki_incidents pi
LEFT JOIN pki_tickets pt ON pt.ticket_no = pi.hosp_ticket
WHERE pt.ticket_no = '" . $_REQUEST['hosp_ticket'] . "'";
|
|
|
|
|
What is your question?
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
|
|
Hello
I can't find solution for this simple query.Want to get sum of column of VISIT_FEE that have colum MONTH_ 2 and YEAR_ 1396
My main table is like this:
ID VISIT_DATE VISIT_TIME VISIT_FEE IS_PAY
1 13960124 10:00 300000 1
6 13960208 10:50 2500000 1
7 13960208 11:00 210000 1
8 13960209 10:20 300000 1
and now i use below query:
SELECT
VISIT_FEE,
EXTRACT(YEAR FROM VISIT_DATE) AS YEAR_,
EXTRACT(MONTH FROM VISIT_DATE) AS MONTH_,
EXTRACT(DAY FROM VISIT_DATE) AS DAY_
FROM
MZS_VISIT_REQUEST
WHERE
IS_PAY = 1
and result is :
VISIT_FEE YEAR_ MONTH_ DAY_
300000 1396 1 1
2500000 1396 2 2
210000 1396 2 2
300000 1396 2 2
And now i want some of colum VISIT_FEE that have YEAR_ 1396 and MONTH_ 2 and 1
somethings like below table:
FEE YEAR_ MONTH_ DAY_
300000 1396 1 1
3010000 1396 2 2
Thanks.
modified 15-May-17 23:40pm.
|
|
|
|
|
Please replace:
EXTRACT(MONTH FROM VISIT_DATE) AS DAY_
with:
EXTRACT(DAY FROM VISIT_DATE) AS DAY_
and everything should be OK.
For further details, please see: EXTRACT (datetime)
|
|
|
|
|
Thanks for replay.
but this is just my misspelling and nothings changed on result. my show result table table is correct.
I want to get sum of column VISIT_FEE
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
