[RESOLVED] How can I do this in sql ?

[xperator]

Hi guys,

Today the costumer asked me to add something to the report section of program. (crystal reports)
I have to query from another table but the field names all the same on both.

I am a little confused on the sql query that should be used. This is currently the old one being used :

strSQL = "SELECT tbldaftar.FldNosabt, tbldaftar.FldSDate, tbldaftar.FldCodeMabda, tbldaftar.Fldno_post, tbldaftar.FldVahedName FROM tbldaftar WHERE Fldno_post = '" & frmmain.txtPPostCode.Text & "' ORDER BY FldCodeMabda"

I explain how data works and what exactly I want :

Main Table named 'tbldaftar'
----------------------------
FldNosabt
FldSDate
FldCodeMabda
FldNo_post
FldVahedName

Second Table named 'TblRonevesht' (which has to be added to sql query)
----------------------------
FldNosabt
FldCodeMabda
FldVahedName


As you can see second table has the same field name from the main one. but the data is different. they have one field in common which has the same data named 'FldNosabt'

Let me write down an example how the report output might be:

1.User enters number '12' in textbox and then clicks the report button

2.My program query the database and there is 1 record in the main table with field values : (FldNo_post) = 12 and (FldNosabt) = 500

In the second table there is 2 record which has : (FldNosabt) = 500

3.In crystal reports, the output results should be 3 records. ( 1 from the main table and 2 from second table )


Is it a simple Inner Join ? How should get data from those two table which has same field names ? Please Help me out.

[Grimfort]

If you are extracting the same number of columns from both tables, or even add fake data for those column missing from the main table, you can use a union query to combine them. Loads of other examples out there.

[xperator]

Thanks For QuickReply man.

It is a little hard to explain. Let's say the main table is about letters and the second table is where the letters has been sent to.

In the old report it prints all letters information from the main table. but now it has to check the second table and if they are any letters that has been sent, It has to be added as a record output in crystal reports.

This is why the second table has same field names but different data

[wild_bill]

You can add another query, each query will return another datatable when you use a data adapter's fill method. Also, I highly recomend using parameters in you command objects, rather than concatonating data into the query text.

Dim strSQL = _
"SELECT FldNosabt, FldSDate, FldCodeMabda, Fldno_post, FldVahedName " & Environment.NewLine & _
"FROM tbldaftar " & Environment.NewLine & _
"WHERE Fldno_post = @Fldno_post " & Environment.NewLine & _
"ORDER BY FldCodeMabda" & Environment.NewLine & _
"" & Environment.NewLine & _
"SELECT r.FldNosabt, r.FldCodeMabda, r.FldVahedName" & Environment.NewLine & _
"FROM tblRonevesht r" & Environment.NewLine & _
"INNER JOIN tblDaftar t on r.fldnosabt=t.fldnosabt" & Environment.NewLine & _
"WHERE Fldno_post = @Fldno_post " & Environment.NewLine & _
"ORDER BY r.FldCodeMabda"

'ALWAYS, ALWAYS, ALWAYS use parameterized queries vs. concatonation
myCommand.Parameters.Add("@Fldno_post", SqlDbType.VarChar).Value = frmmain.txtPPostCode.Text

[xperator]

Thanks for reply.

I have already used a dataset and datatable in my report how can i just add another one ? I don't want to make another crystal report.

I have an idea. If I make another dataset and fill it with the query you used, Is it possible to add every row in the second dataset to the main one ?

[wild_bill]

You will only need to fill one dataset, it will have two datatables in it with the data you need.

[xperator]

I am getting error :

System.Data.OleDb.OleDbException (0x80040E14): Syntax error. in query expression 'FldCodeMabda

SELECT r.FldNosabt'.

What's the problem ?

Edit : You put INNER JOIN in the second select statement. Are you sure it should be that way ?

[wild_bill]

I am only familiar with T-SQL for MS SQL Server. Which database are you using?

[xperator]

It is an Access Database (Microsoft.Jet.OLEDB.4.0)

I tried checking Capital worlds, field names, removed 'order by' but still I get the error.
Something is wrong with the second select statement. Is there anything should be put after the first statement ? Is it possible to combine them ?

Edit : Is it possible to separate them into two different string queries? then run the fill code :

Dim da As New OleDb.OleDbDataAdapter(strSQL, conn)
            da.Fill(DaftarDataSet, "tbldaftar")

EDIT 2 : I get it working now, but the output result is not correct, record results from second table are in wrong field places.

EDIT 3 : Sorry I did it wrong, I separate into two strings but Inner join doesn't work so it return all records.

[xperator]

Still no luck guys. I read somewhere MS access database doesn't support multi select statement queries!
Is that true ?

Then anyone knows how should can I achieve my goal ? I want to select from the main table and second table while keeping their relation.
For each record in the main table they might be a few related records in the second table. And I want those records on the report too.

I tried separating into two different sql queries but if I do that the second table loses it's relation because there is 1 select statement in each query.

Please Help me I am really stuck.

[wild_bill]

Are you using a strongly typed dataset, or a generic dataset to store your data?

[xperator]

Sorry I don't understand what you mean.

[wild_bill]

Then I'll assume you're using a generic dataset, here is an example

    Private Function GetData() As DataSet

        Dim daftarQuery = _
        "SELECT FldNosabt, FldSDate, FldCodeMabda, Fldno_post, FldVahedName " & Environment.NewLine & _
        "FROM tbldaftar " & Environment.NewLine & _
        "WHERE Fldno_post = ? " & Environment.NewLine & _
        "ORDER BY FldCodeMabda"
        Dim daftarTable = FillTable(daftarQuery, frmmain.txtPPostCode.Text)

        Dim roneveshtQuery = _
        "SELECT r.FldNosabt, r.FldCodeMabda, r.FldVahedName" & Environment.NewLine & _
        "FROM tblRonevesht r" & Environment.NewLine & _
        "INNER JOIN tblDaftar t on r.fldnosabt=t.fldnosabt" & Environment.NewLine & _
        "WHERE Fldno_post = ? " & Environment.NewLine & _
        "ORDER BY r.FldCodeMabda"
        Dim roneveshtTable = FillTable(roneveshtQuery, frmmain.txtPPostCode.Text)

        Dim myData As New DataSet
        myData.Tables.Add(daftarTable)
        myData.Tables.Add(roneveshtTable)

        Return myData

    End Function

    Public Function FillTable(ByVal query As String, ByVal fldnoPost As String) As DataTable


        Using con As New OleDb.OleDbConnection("connectionstring")
            con.Open()

            Using cmd As New OleDb.OleDbCommand(query, con)
                'ALWAYS, ALWAYS, ALWAYS use parameterized queries vs. concatonation
                cmd.Parameters.AddWithValue("Fldno_post", fldnoPost)

                Using da As New OleDb.OleDbDataAdapter(cmd)

                    Dim dt As New DataTable
                    da.Fill(dt)
                    Return dt

                End Using
            End Using

        End Using

    End Function

[xperator]

Ok man Thank you a thousands times. It works now

There is a little problem, the output results from the second query are placed in wrong place. How should i solve this ?
It's because I have used 7 Fields in the tbldaftar and in the crystal report design. but I have selected 3 fields in the second query.

Is there a way to select a fake data ?

[wild_bill]

The best solution would be to fix you report, but I'm not familiar with crystal. To answer you question, yes, you can select fake/static data:

        Dim roneveshtQuery = _
        "SELECT r.FldNosabt, r.FldCodeMabda, r.FldVahedName,'','','',''" & Environment.NewLine & _
        "FROM tblRonevesht r" & Environment.NewLine & _
...

[xperator]

The best solution would be to fix you report, but I'm not familiar with crystal. To answer you question, yes, you can select fake/static data:

        Dim roneveshtQuery = _
        "SELECT r.FldNosabt, r.FldCodeMabda, r.FldVahedName,'','','',''" & Environment.NewLine & _
        "FROM tblRonevesht r" & Environment.NewLine & _
...

Thanks again it did worked, I just put '' where the fields should be empty.

God Bless You man. Next time I prayed to god i have to mention you too