how to merge this tow queries in dataset

Question

1.00/5 (1 vote)

See more:

C#

SqlCommand SqlComm = new SqlCommand();
SqlComm.Connection = conn;
SqlComm.CommandType = CommandType.Text;
SqlComm.CommandText = "select jobcard_creation.jobcard_no,jobcard_creation.vehicle_no,jobcard_creation.vehicle_model,jobcard_creation.date, jobcard_staff.staff_name, "
     + "  jobcard_creation.delivery_date,jobcard_creation.total_days,vehicle_type.vehicle_name,Job_type.jobtype_name from jobcard_creation "
    + " left outer join vehicle_type on jobcard_creation.department_id=vehicle_type.vehicle_id   left outer join Job_type on job_type.jobtype_id=jobcard_creation.job_type  "
    + " left join jobcard_staff on jobcard_staff.jobcard_id=jobcard_creation.jobcard_id  where "
    + " jobcard_creation.jobcard_id='" + txtJobCardId.Text + "' and jobcard_creation.type='N' and jobcard_staff.type='N' ";  

SqlCommand sqlcom1 = new SqlCommand();
sqlcom1.Connection = conn;
sqlcom1.CommandType = CommandType.Text;
sqlcom1.CommandText="select describtion from jobcard_work_description where jobcard_id='" + txtJobCardId.Text + "' ";
// Create instance of Northwind DataSetXSD

Posted 20-May-15 1:18am

sekar305

Updated 20-May-15 1:35am

Tomas Takac

v2

Add a Solution

Comments

Richard Deeming 20-May-15 7:52am

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2015-05-20T02:03:00

You just need to join on the jobcard_work_descriptiontable and include describtion in your select clause (although I suspect it should be description) E.g.

SQL

select JC.jobcard_no,JC.vehicle_no,JC.vehicle_model,JC.[date], JS.staff_name,
        JC.delivery_date,JC.total_days,VT.vehicle_name,JT.jobtype_name,
        JWD.describtion
from jobcard_creation JC
left outer join vehicle_type VT on JC.department_id=vt.vehicle_id
left outer join Job_type JT on JT.jobtype_id=JC.job_type
left join jobcard_staff JS on JS.jobcard_id=JC.jobcard_id
left join jobcard_work_description JWD on JC.jobcard_id=JWD.jobcard_id
where JC.jobcard_id=@cardid and JC.[type]='N' and JS.type='N'

Couple of things to note:
- I've surrounded column names that are reserved words with square brackets e.g. [type] and [date]
- I've used table aliases to make the select clause a bit more readable e.g. JC, JT, VT etc.
- Take note of the comment from Richard Deeming - NEVER use string concatenation to build a SQL query. See @cardid in my query above

_Asif_ · Answer 2 · 2015-05-20T02:48:00

Another approach could be like. Check this[^] for Reference!

C#

public DataSet SelectOne(int id)
{
    DataSet result = new DataSet();
    using (DbCommand command = Connection.CreateCommand())
    {
        command.CommandText = @"
select * from table1
select * from table2
        ";

        var param = ParametersBuilder.CreateByKey(command, "ID", id, null);
        command.Parameters.Add(param);

        Connection.Open();
        using (DbDataReader reader = command.ExecuteReader())
        {
            result.MainTable.Load(reader);
            reader.NextResult();
            result.SecondTable.Load(reader);
            // ...
        }
        Connection.Close();
    }
    return result;
}

how to merge this tow queries in dataset

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0