Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
I have a datagridview that I want to display a data from a database. But I don't want it to display all of the data. I want it to display the data for a specific ID only. Meaning if the user enters 3 ID, it will display the info for that 3 ID. Hence I want to use a rich text box as a filter so that the user can enter multiple ID for each line in the rich text box. The user can enter the ID No. within the rich text box and the data will be used as a filter to display the data for that particular ID. But I cannot make it read beyond the first line of the rich text box. If I enter just one ID in the first line, it works perfectly, but if I enter a second ID in the second line, or in the third line, then it will not display anything at all. I tried using for loop to read each line of the rich text box but it doesn't work. Any advice or solution??
here is my code :
C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;


namespace TrackCon
{
    public partial class trackInput : Form
    {
        public trackInput()
        {
            InitializeComponent();
        }

        /*private void trackInput_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'trackingBMSDATADataSet.BRDATA' table. You can move, or remove it, as needed.
            this.bRDATATableAdapter.Fill(this.trackingBMSDATADataSet.BRDATA);

        }*/

        private void trackBtn_Click(object sender, EventArgs e)
        {   DataTable dt = null;
            string connoInput = richTextBox1.Text;
            string conString = Properties.Settings.Default.BMSDATAConnectionString;
            using (SqlCeConnection con = new SqlCeConnection(@"Data Source=C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\TrackCon\TrackCon\BMSDATA.sdf;Persist Security Info = True;Password=Gdex123$"))
                {
                    con.Open();
                    SqlCeCommand com = new SqlCeCommand("SELECT conno,cmpsno,ctrx,dsysdate,cstnno,corigin FROM BRDATA WHERE conno = '" + richTextBox1.Text + "'OR cmpsno = '" + richTextBox1.Text + "'", con);
                    SqlCeDataAdapter adap = new SqlCeDataAdapter(com);
                    DataSet set = new DataSet();
                    adap.Fill(set);
                    if (set.Tables.Count > 0)
                    {
                        dt = set.Tables[0];
                    }
                    dataGridView1.DataSource = dt;
                    con.Close();
                }
            
        }
    }
}
Posted

1 solution

Since, only ID numbers are to be given by the user, I think a text box can be used to enter the ID with comma separation and then

C#
//To replace , surrounded by none or more spaces
//If the cmpsno is string type
string Ids = " cmpsno= '" + System.Text.RegularExpressions.Regex.Replace(TextBox1.Text.Trim(),@"\s*,\s*","' OR cmpsno= '") + "'";
//If the cmpsno is any of the numeric type
string Ids = " cmpsno= " + System.Text.RegularExpressions.Regex.Replace(TextBox1.Text.Trim(),@"\s*,\s*"," OR cmpsno= ");

//Replace the part of select statement
"SELECT conno,cmpsno,ctrx,dsysdate,cstnno,corigin FROM BRDATA WHERE conno = '" + richTextBox1.Text + "'OR cmpsno = '" + richTextBox1.Text + "'"
//with
"SELECT conno,cmpsno,ctrx,dsysdate,cstnno,corigin FROM BRDATA WHERE " + Ids
 
Share this answer
 
v3
Comments
Sergey Alexandrovich Kryukov 25-Mar-12 23:03pm    
I must note that using input from UI (richTextBox1.Text) in the query is a huge security hole. It can be anything, and even -- guess what -- SQL code. This is well-known exploit called "SQL injection" (see it in Wikepedia).
You should use parametrized query which is free from this problem.
--SA
nizam15 25-Mar-12 23:10pm    
thanks a lot for the tips SA. I'm quite new so that information will be very useful. I'll keep that in mind. I'll try implementing the parametrized query after I solve my little problem here:D
ProEnggSoft 26-Mar-12 0:09am    
Thank you parametrized query suggestion.
As an alternative if all the data is read in to the DataTable, binding the data to BindingSource, then binding it to the data control and for displaying particular records, the filter property of BindingSource is used. Will it be OK.
nizam15 25-Mar-12 23:06pm    
thanks ProEnggSoft for the tips but still it isn't working. It still works only if I enter a single ID. If I enter multiple ID's even with the comma separation, it won't display anything at all.
ProEnggSoft 25-Mar-12 23:59pm    
The problem may be either the data type of cmpsno is numeric type or there may be spaces around , . Please see I have updated my solution.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900