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
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"))
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
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"))
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"))
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?
pulsmartin
Member
108 Points
309 Posts
select two databases
Feb 20, 2012 09:10 AM|LINK
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
End Using
hans_v
All-Star
35998 Points
6551 Posts
Re: select two databases
Feb 20, 2012 09:57 AM|LINK
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"))
pulsmartin
Member
108 Points
309 Posts
Re: select two databases
Feb 20, 2012 10:24 AM|LINK
This gives me Error even when running on my computer - Syntax Error in From Clause
hans_v
All-Star
35998 Points
6551 Posts
Re: select two databases
Feb 20, 2012 10:43 AM|LINK
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"
pulsmartin
Member
108 Points
309 Posts
Re: select two databases
Feb 20, 2012 12:05 PM|LINK
all of those 3 statements work
hans_v
All-Star
35998 Points
6551 Posts
Re: select two databases
Feb 20, 2012 06:39 PM|LINK
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"))
pulsmartin
Member
108 Points
309 Posts
Re: select two databases
Feb 21, 2012 08:16 AM|LINK
Thank you, this works. The only thing is, it doesn't select only distinct values - doesn't Union by default select only distinct values?
hans_v
All-Star
35998 Points
6551 Posts
Re: select two databases
Feb 21, 2012 11:08 AM|LINK
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"))
pulsmartin
Member
108 Points
309 Posts
Re: select two databases
Feb 22, 2012 07:06 AM|LINK
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?
hans_v
All-Star
35998 Points
6551 Posts
Re: select two databases
Feb 22, 2012 08:22 AM|LINK
I understand, and I think my last solution will do just that....