OleDbReader and select query problem

  • Thread starter Thread starter rob merritt
  • Start date Start date
R

rob merritt

I have a function that on page load iterate through a
OleDbDataReader this works with a simple query like

SELECT * FROM TeachingExp WHERE (TeachingExp.UserId_fk='" +
Session["UserName"] + "')"

but with a more complicated query

SELECT * FROM TeachingExp, TeacherPrefs, SubExp, References,
personalData, EduHistory, EduEquity, CriminalRecord, Comments,
Certification
WHERE (TeachingExp.UserId_fk='"+Session["UserName"]+"' and
TeacherPrefs.UserId_fk='"+Session["UserName"]+"' and
SubExp.UserId_fk='"+Session["UserName"]+"'
and References.UserId_fk='"+Session["UserName"]+"' and
personalData.UserId_fk='"+Session["UserName"]+"' and
EduHistory.UserId_fk='"+Session["UserName"]+"'
and EduEquity.UserId_fk='"+Session["UserName"]+"' and
CriminalRecord.UserId_fk='"+Session["UserName"]+"' and
Comments.UserId_fk='"+Session["UserName"]+"'
and Certification.UserId_fk='"+Session["UserName"]+"')";

void Fill_Session_Vars()
{
string connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\WebSites\\appForm\\data\\ASPNetDB.mdb";
OleDbConnection cn = new OleDbConnection(connectString);
cn.Open();
string selectString = "SELECT * FROM TeachingExp, TeacherPrefs,
SubExp, References, personalData, EduHistory, EduEquity,
CriminalRecord, Comments, Certification
WHERE (TeachingExp.UserId_fk='"+Session["UserName"]+"' and
TeacherPrefs.UserId_fk='"+Session["UserName"]+"' and
SubExp.UserId_fk='"+Session["UserName"]+"'
and References.UserId_fk='"+Session["UserName"]+"' and
personalData.UserId_fk='"+Session["UserName"]+"' and
EduHistory.UserId_fk='"+Session["UserName"]+"'
and EduEquity.UserId_fk='"+Session["UserName"]+"' and
CriminalRecord.UserId_fk='"+Session["UserName"]+"' and
Comments.UserId_fk='"+Session["UserName"]+"'
and Certification.UserId_fk='"+Session["UserName"]+"')";

//this works string selectString = "SELECT * FROM TeachingExp
WHERE (TeachingExp.UserId_fk='" + Session["UserName"] + "')";
try
{
OleDbCommand cmd = new OleDbCommand(selectString, cn);
OleDbDataReader reader = cmd.ExecuteReader();
int i = 0;
while (reader.Read())
{
Response.Write(reader.ToString() + "<br>");
i++;
}
reader.Close();
cn.Close();
}
catch (Exception ex)
{
Response.Write("<br>"+selectString);
Response.Write("<br>"+ex);
}
}

HERE IS THE ERROR:

SELECT * FROM TeachingExp, TeacherPrefs, SubExp, References,
personalData, EduHistory, EduEquity, CriminalRecord, Comments,
Certification WHERE (TeachingExp.UserId_fk='merrittr' and
TeacherPrefs.UserId_fk='merrittr' and SubExp.UserId_fk='merrittr' and
References.UserId_fk='merrittr' and personalData.UserId_fk='merrittr'
and EduHistory.UserId_fk='merrittr' and EduEquity.UserId_fk='merrittr'
and CriminalRecord.UserId_fk='merrittr' and
Comments.UserId_fk='merrittr' and Certification.UserId_fk='merrittr')

Syntax error in FROM clause.
 
Rob,

Use for that OleDbParameters (be aware that this is one of the few parts
where there is a difference in the use of the system.data classes with
sqlclient).

I changed this in this message from a VBNet piece to C# so watch typos
///using System.Data.OleDb;
OleDbCommand cmd = new OleDbCommand("Select MyField from MyTable Where MyId
=?", conn);
cmd.Parameters.Add _
(New OleDb.OleDbParameter("", OleDbType.VarChar))
cmd.Parameters[0].Value = MyId;

Be aware that you with OleDb have to add the parameters as used in the SQL
string.

I hope this helps,

Cor
 
Ok I will give that a try. however my query seems to get built successfully
see below. but for some reason I get a "Syntax error in FROM clause." message



SELECT * FROM TeachingExp WHERE (TeachingExp.UserId_fk='" +
Session["UserName"] + "')"

but with a more complicated query

SELECT * FROM TeachingExp, TeacherPrefs, SubExp, References,
personalData, EduHistory, EduEquity, CriminalRecord, Comments,
Certification
WHERE (TeachingExp.UserId_fk='"+Session["UserName"]+"' and
TeacherPrefs.UserId_fk='"+Session["UserName"]+"' and
SubExp.UserId_fk='"+Session["UserName"]+"'
and References.UserId_fk='"+Session["UserName"]+"' and
personalData.UserId_fk='"+Session["UserName"]+"' and
EduHistory.UserId_fk='"+Session["UserName"]+"'
and EduEquity.UserId_fk='"+Session["UserName"]+"' and
CriminalRecord.UserId_fk='"+Session["UserName"]+"' and
Comments.UserId_fk='"+Session["UserName"]+"'
and Certification.UserId_fk='"+Session["UserName"]+"')";



SELECT * FROM TeachingExp, TeacherPrefs, SubExp, References,
personalData, EduHistory, EduEquity, CriminalRecord, Comments,
Certification WHERE (TeachingExp.UserId_fk='merrittr' and
TeacherPrefs.UserId_fk='merrittr' and SubExp.UserId_fk='merrittr' and
References.UserId_fk='merrittr' and personalData.UserId_fk='merrittr'
and EduHistory.UserId_fk='merrittr' and EduEquity.UserId_fk='merrittr'
and CriminalRecord.UserId_fk='merrittr' and
Comments.UserId_fk='merrittr' and Certification.UserId_fk='merrittr')

Syntax error in FROM clause.
 
Aaaaaaag the table Reference was causing the problem renamed it to
TeacherReference and it works now
 
Back
Top