database search

[virusisfound]

I am working in C# windows application and SQL I have one problem i.e I want to know that can we search data directly from database.

for table we write a queary = Select * from tablename.
like that is there is any queary to directly search from entire database tables.

[Mitja Bonca]

You mean not to get values into dataTable?
Something like using DaraReader?

private void GetDataDirectly(int id)
        {
            using (SqlConnection sqlConn = new SqlConnection("connectionString"))
            {
                string query = @"SELECT a, b ,c FROM MyTable WHERE IdTable = @id";
                SqlCommand cmd = new SqlCommand(query, sqlConn);
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        lable1.Text = (string)reader[0];  //a string
                        textBox1.Text = (string)reader[1];  //a string 
                        textBox2.Text = Convert.ToInt32(reader[2]); //some number
                    }
                }
            }
        }

If this is not it, please provide us some more useful description of your issue.
Mitja

[lianpiau]

I am working in C# windows application and SQL I have one problem i.e I want to know that can we search data directly from database.

for table we write a queary = Select * from tablename.
like that is there is any queary to directly search from entire database tables.

I don't think can search all table. unless you use JOIN command to join all table.

[virusisfound]

I know that i have to use join but in join tere is one problem i.e when i join 2 tables. at that time it will display all data present in column but i want to display unique data.

example:

table1

ass
crd
deb

table 2
ass
drc
deb

now the data preaset in database like if i have three entries in table1 with the name of cash account now i want to display the total of three entries in report.

its complecated but i want to do it like this way.

I hope you understand my problem.

[lianpiau]

I know that i have to use join but in join tere is one problem i.e when i join 2 tables. at that time it will display all data present in column but i want to display unique data.

example:

table1

ass
crd
deb

table 2
ass
drc
deb

now the data preaset in database like if i have three entries in table1 with the name of cash account now i want to display the total of three entries in report.

its complecated but i want to do it like this way.

I hope you understand my problem.

Do u using Crytsal Report.

[virusisfound]

yes i am using crystal report

[lianpiau]

research formula in Crystal report

[lianpiau]

yes i am using crystal report

In the crystal report, U can link the table together. Just like in access database, table can together(drag n drop). U can use built in function to sum the total. It symbol like opposite E. Or u also can use formula to sum. after that, u can drag n drop the formula in report to display sum. U can research formula of crystal report.

[virusisfound]

i want to give a condition to it

like if three rows having same name but diffrent values then it make sum of these three rows.

[virusisfound]

I mean can we compare data present in database first and then make sum of that particular data.

[xanawa]

Select *
FROM tablename
WHERE name = 'Anna'

name is the column name and Anna is the value u would like to search!!

The code below is used on a Search button click which gets the name from a textbox

public SqlConnection conn = new SqlConnection(@"Data Source=ANNAMARIE-PC;Database = DBNorthwind;Integrated Security=True;");

try
            {
                string searchName = txtCompanyName.Text.ToString();

                conn.Open();

                SqlCommand comm = new SqlCommand("SELECT * FROM tblCustomer WHERE CompanyName = '" + searchName + "'", conn);


                SqlDataReader r = comm.ExecuteReader();

                while (r.Read())
                {
                    txtCustomerID.Text = (r["CustomerID"].ToString().TrimEnd());
                    txtCompanyName.Text = (r["CompanyName"].ToString().TrimEnd());
                    txtContactName.Text = (r["ContactName"].ToString().TrimEnd());
                    txtContactTitle.Text = (r["ContactTitle"].ToString().TrimEnd());
                    txtAddress.Text = (r["Address"].ToString().TrimEnd());
                    txtCity.Text = (r["City"].ToString().TrimEnd());
                    txtPostalCode.Text = (r["PostalCode"].ToString().TrimEnd());
                    txtCountry.Text = (r["Country"].ToString().TrimEnd());
                    txtPhone.Text = (r["Phone"].ToString().TrimEnd());
                    txtFax.Text = (r["Fax"].ToString().TrimEnd());
                    txtRegion.Text = (r["Region"].ToString().TrimEnd());
                }

                r.Close();
            }

            catch (Exception ex)
            {
                throw new Exception(ex.ToString(), ex);
            }
            finally
            {
                conn.Close();
            }

Hope this helps you

[virusisfound]

You don't understand what i want. just tell me can we directly search from database like we search from table.

i have cash as a data save in a multiple tables and i want to search this from entire database. this cash value present in entire database it should search from there and display it in report.

[xanawa]

do u mean like a relationship?

[virusisfound]

I don't know how to give relationship in SQL database and how to display it in crystal report. please guide me for that.

[xanawa]

1. Open your SQL Server
2. Go on the left pane of the screen where it says: "Object Explorer"
3. Expand the Connection and the Databases
4. Expand your database eg. "DBCompany"
5. Right Click on "Database Diagram" and click "New Database Diagram"
6. A dialog appears with all the tables in teh database add them all and click close.
7. Drag your foreign key and drop it on teh primary key of the other table.
8. Another dialogue box will appear Where you have to choose the table of where the primary key is and the table of where the foreign key is.. click ok

Do steps from 7 to 8 to make other relationships

[virusisfound]

But that column does not have primary key in the table.

[xanawa]

Do not declare it as a primary key.. but when u have to amke the relationship use it as a primary key!!

[virusisfound]

Table has already one primary key which is must then how can i declare it primary key.
a table has only one primary key

[xanawa]

than u have to use the primary key that u declared and put it as a foreign key on the other table.. Do you have any basics of ERD Diagram?

[virusisfound]

Yes I know about the ERD.