select two databases

I have the following Select statement which uses two different databases, one is in my app_data folder, the other is somewhere else, the path is in the string I call dbpath.

It works when I run it on my computer, it works when I put it on the internet, it does not work when I put it on the server where it has to run. (Error in From clause) What could the problem be?

Dim sqlUnion As String = "SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM " & cs2 & " INNER JOIN " & cs3 & " ON tblVertrag.ID=tblVertragGattung.VertragID WHERE GattungID=? ORDER BY Kreditor_ID UNION SELECT Kredi_ID,Kredi FROM Projek WHERE Lager_ID=?"

Dim cs2 As String = "~/App_Data/Visieren.mdb..tblVertrag"
Dim cs3 As String = "~/App_Data/Visieren.mdb..tblVertragGattung"
cs2 = HttpContext.Current.Server.MapPath(cs2)
cs3 = HttpContext.Current.Server.MapPath(cs3)

Using cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath)
Using dbcomm As New OleDbCommand(sqlUnion, cn)
dbcomm.Parameters.AddWithValue("Lager_ID", GattungID)
dbcomm.Parameters.AddWithValue("GattungID", GattungID)
cn.Open()
dbread = dbcomm.ExecuteReader

dt2.Load(dbread)
dbread.Close()
cn.Close()

End Using

Re: select two databases

Try this:

Dim sqlUnion As String = String.Format("SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag IN '{0}' INNER JOIN tblVertragGattung IN '{0}' ON tblVertrag.ID=tblVertragGattung.VertragID WHERE GattungID=? ORDER BY Kreditor_ID UNION SELECT Kredi_ID,Kredi FROM Projek WHERE Lager_ID=?", Server.MapPath("~/App_Data/Visieren.mdb"))

Re: select two databases

hans_v

Try this:

Dim sqlUnion As String = String.Format("SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag IN '{0}' INNER JOIN tblVertragGattung IN '{0}' ON tblVertrag.ID=tblVertragGattung.VertragID WHERE GattungID=? ORDER BY Kreditor_ID UNION SELECT Kredi_ID,Kredi FROM Projek WHERE Lager_ID=?", Server.MapPath("~/App_Data/Visieren.mdb"))

This gives me Error even when running on my computer - Syntax Error in From Clause

Re: select two databases

First we need to identify where the problem occurs. Can you try to execute the following SQL statements?:

Dim sqlUnion As String = String.Format("SELECT * FROM tblVertrag IN '{0}'", Server.MapPath("~/App_Data/Visieren.mdb"))

Dim sqlUnion As String = String.Format("SELECT * FROM tblVertragGattung IN '{0}'", Server.MapPath("~/App_Data/Visieren.mdb"))

Dim sqlUnion As String = "SELECT * FROM Projek"

Re: select two databases

all of those 3 statements work

Re: select two databases

Dim sqlUnion As String = String.Format("SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag INNER JOIN tblVertragGattung ON tblVertrag.ID=tblVertragGattung.VertragID IN '{0}' WHERE GattungID=? UNION SELECT Kredi_ID,Kredi FROM Projek WHERE Lager_ID=? ORDER BY Kredi_ID ", Server.MapPath("~/App_Data/Visieren.mdb"))

Re: select two databases

Thank you, this works. The only thing is, it doesn't select only distinct values - doesn't Union by default select only distinct values?

Re: select two databases

pulsmartin
The only thing is, it doesn't select only distinct values

Dim sqlUnion As String = String.Format("SELECT DISTINCT Kredi_ID FROM (SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag INNER JOIN tblVertragGattung ON tblVertrag.ID=tblVertragGattung.VertragID IN '{0}' WHERE GattungID=? UNION SELECT Kredi_ID,Kredi FROM Projek WHERE Lager_ID=?) ORDER BY Kredi_ID ", Server.MapPath("~/App_Data/Visieren.mdb"))

Re: select two databases

What I meant is that if the same Kredi_ID exists in both tables, I want it to show only once. Could it be that it shows it to me twice because even though Kredi_ID is the same, Kredi is not?

Re: select two databases

pulsmartin
What I meant is that if the same Kredi_ID exists in both tables, I want it to show only once.

I understand, and I think my last solution will do just that....

Re: select two databases

It still returns two rows if it exists in both tables(I added the bold Kredi to your select statement)

Dim sqlUnion As String = String.Format("SELECT DISTINCT Kredi_ID,Kredi FROM (SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag INNER JOIN tblVertragGattung ON tblVertrag.ID=tblVertragGattung.VertragID IN '{0}' WHERE GattungID=? UNION SELECT Kredi_ID,Kredi FROM Projek WHERE Lager_ID=?) ORDER BY Kredi_ID ", Server.MapPath("~/App_Data/Visieren.mdb"))

Re: select two databases

You mean your statement returns 2 records with the same kredi_ID AND Kredi ????

Re: select two databases

pulsmartin

What I meant is that if the same Kredi_ID exists in both tables, I want it to show only once. Could it be that it shows it to me twice because even though Kredi_ID is the same, Kredi is not?

If both columns are the same then it's not shown twice. But if kredi_ID is same and Kredi different then it's shown twice. How do I go about that?

Re: select two databases

OK here is what I found searching on the internet ( now that I know where the problem lies) , seems to be working. Is this the correct way of doing it?

Dim sqlUnion As String = String.Format("SELECT Kredi_ID, Max(Kredi) as Kredi FROM (SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag INNER JOIN tblVertragGattung ON tblVertrag.ID=tblVertragGattung.VertragID IN '{0}' WHERE GattungID=? UNION SELECT Kredi_ID, Kredi FROM Projek WHERE Lager_ID=?) GROUP BY Kredi_ID ORDER BY 2", HttpContext.Current.Server.MapPath("~/App_Data/Visieren.mdb"))

Re: select two databases

pulsmartin
If both columns are the same then it's not shown twice.

That is exactly what DISTINCT is all about. It does show only unique rows.

pulsmartin
But if kredi_ID is same and Kredi different then it's shown twice. How do I go about that?

Then you need to decide which of the 2 (or more) values of Kredi you want to show?

Re: select two databases

pulsmartin

OK here is what I found searching on the internet ( now that I know where the problem lies) , seems to be working. Is this the correct way of doing it?

Dim sqlUnion As String = String.Format("SELECT Kredi_ID, Max(Kredi) as Kredi FROM (SELECT Kreditor_ID as Kredi_ID,Kreditor as Kredi FROM tblVertrag INNER JOIN tblVertragGattung ON tblVertrag.ID=tblVertragGattung.VertragID IN '{0}' WHERE GattungID=? UNION SELECT Kredi_ID, Kredi FROM Projek WHERE Lager_ID=?) GROUP BY Kredi_ID ORDER BY 2", HttpContext.Current.Server.MapPath("~/App_Data/Visieren.mdb"))

Yep, that looks good.... This will only show each Kredi_ID only once, and if there are multiple values of Kredi, it will show the maximum value....

Re: select two databases

Thanks for sticking with me until the end