Help with TableAdapter

  • Thread starter Thread starter SAL
  • Start date Start date
S

SAL

Hello,
Using the designer in my project, I created a DataTable for one of the
tables in an Access database. I created a TableAdapter for that DataTable by
adding queries. For the most part, the queries are executing correctly but
this one does not return any rows. If I past the sql into the Access
database from the TableAdapter, it returns rows. Does anyone know how to
debug what the command is sending to the Access database for any particular
query?
This query uses parameters and I'm afraid it's not sending the where clause
correctly..

Any help with this would be much appreciated

S
 
Hi Mike,
Here's the query:
SELECT
ReportedWeeds.RID, Weeds.Name, ReportedWeeds.Density, BioAgents.BName,
ReportedWeeds.Assr_SN, ReportedWeeds.TownRange, ReportedWeeds.LotSize,
ReportedWeeds.theDate, ROW.[Desc] AS RDesc, ReportedWeeds.Owner,
ReportedWeeds.OwnStAddrs, ReportedWeeds.OwnCity, ReportedWeeds.OwnState,
ReportedWeeds.OwnZip, ReportedWeeds.OwnPhone, MethodContacted.[Desc] AS
MDesc, ReportedWeeds.SitusHsnbr + ' ' + ReportedWeeds.SitusStDir + ' ' +
ReportedWeeds.SitusStName + ' ' + ReportedWeeds.SitusSType + ' ' +
ReportedWeeds.SitusCity AS SITADDRS, ReportedWeeds.Source,
ReportedWeeds.SourceCity, ReportedWeeds.SourceState,
ReportedWeeds.SourceStDir,
ReportedWeeds.SourceHsnbr, ReportedWeeds.SourceStName,
ReportedWeeds.SourceZip, ReportedWeeds.SourcePhone,
ReportedWeeds.Comments, ReportedWeeds.FollowUp, ReportedWeeds.FollowUpNotes,
ReportedWeeds.Atlas

FROM ((((ReportedWeeds LEFT OUTER JOIN
ROW ON ReportedWeeds.ROWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.WID = Weeds.WID) LEFT OUTER JOIN
MethodContacted ON ReportedWeeds.MCID = MethodContacted.MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.BioAgent = BioAgents.BAID)

WHERE (ReportedWeeds.Source LIKE '%' + ? + '%') AND (ReportedWeeds.theDate
= ?) AND (ReportedWeeds.theDate <= ?)
Click to expand...

What I found was that the concatenated field was causing the problem. I'm
thinking that Access thinks the + character is for adding numbers (i.e.,
double) and that's what's producing the error. However, if I change the
concatenation to characters Access likes to use (i.e., the ampersand and
the double quote), then the designer doesn't like that. So, it's like a
catch 22 here. What I'm doing in the interim is just including the fields
and concatenating the values for those fields and passing it to my reports.
Ugly but it's working for now. I would love to have it work like it's
supposed to..

Thanks for looking

S
 
Sal,

Do you have option strict on in top of your program?

Cor

SAL said:
Hi Mike,
Here's the query:
SELECT
ReportedWeeds.RID, Weeds.Name, ReportedWeeds.Density, BioAgents.BName,
ReportedWeeds.Assr_SN, ReportedWeeds.TownRange, ReportedWeeds.LotSize,
ReportedWeeds.theDate, ROW.[Desc] AS RDesc, ReportedWeeds.Owner,
ReportedWeeds.OwnStAddrs, ReportedWeeds.OwnCity, ReportedWeeds.OwnState,
ReportedWeeds.OwnZip, ReportedWeeds.OwnPhone, MethodContacted.[Desc] AS
MDesc, ReportedWeeds.SitusHsnbr + ' ' + ReportedWeeds.SitusStDir + ' ' +
ReportedWeeds.SitusStName + ' ' + ReportedWeeds.SitusSType + ' ' +
ReportedWeeds.SitusCity AS SITADDRS, ReportedWeeds.Source,
ReportedWeeds.SourceCity, ReportedWeeds.SourceState,
ReportedWeeds.SourceStDir,
ReportedWeeds.SourceHsnbr, ReportedWeeds.SourceStName,
ReportedWeeds.SourceZip, ReportedWeeds.SourcePhone,
ReportedWeeds.Comments, ReportedWeeds.FollowUp,
ReportedWeeds.FollowUpNotes,
ReportedWeeds.Atlas

FROM ((((ReportedWeeds LEFT OUTER JOIN
ROW ON ReportedWeeds.ROWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.WID = Weeds.WID) LEFT OUTER JOIN
MethodContacted ON ReportedWeeds.MCID = MethodContacted.MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.BioAgent = BioAgents.BAID)

WHERE (ReportedWeeds.Source LIKE '%' + ? + '%') AND (ReportedWeeds.theDate
= ?) AND (ReportedWeeds.theDate <= ?)
Click to expand...

What I found was that the concatenated field was causing the problem. I'm
thinking that Access thinks the + character is for adding numbers (i.e.,
double) and that's what's producing the error. However, if I change the
concatenation to characters Access likes to use (i.e., the ampersand and
the double quote), then the designer doesn't like that. So, it's like a
catch 22 here. What I'm doing in the interim is just including the fields
and concatenating the values for those fields and passing it to my
reports. Ugly but it's working for now. I would love to have it work like
it's supposed to..

Thanks for looking

S


Mike McIntyre said:
Can you send the query with the problem so it can be inspect or
correctness?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com
Click to expand...
Click to expand...
 
Have you tried using the ampersand and the single quote? I don't think
Access supports + for concatenation.

Robin S.
----------------------------------
SAL said:
Hi Mike,
Here's the query:
SELECT
ReportedWeeds.RID, Weeds.Name, ReportedWeeds.Density, BioAgents.BName,
ReportedWeeds.Assr_SN, ReportedWeeds.TownRange, ReportedWeeds.LotSize,
ReportedWeeds.theDate, ROW.[Desc] AS RDesc, ReportedWeeds.Owner,
ReportedWeeds.OwnStAddrs, ReportedWeeds.OwnCity, ReportedWeeds.OwnState,
ReportedWeeds.OwnZip, ReportedWeeds.OwnPhone, MethodContacted.[Desc] AS
MDesc, ReportedWeeds.SitusHsnbr + ' ' + ReportedWeeds.SitusStDir + ' ' +
ReportedWeeds.SitusStName + ' ' + ReportedWeeds.SitusSType + ' ' +
ReportedWeeds.SitusCity AS SITADDRS, ReportedWeeds.Source,
ReportedWeeds.SourceCity, ReportedWeeds.SourceState,
ReportedWeeds.SourceStDir,
ReportedWeeds.SourceHsnbr, ReportedWeeds.SourceStName,
ReportedWeeds.SourceZip, ReportedWeeds.SourcePhone,
ReportedWeeds.Comments, ReportedWeeds.FollowUp,
ReportedWeeds.FollowUpNotes,
ReportedWeeds.Atlas

FROM ((((ReportedWeeds LEFT OUTER JOIN
ROW ON ReportedWeeds.ROWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.WID = Weeds.WID) LEFT OUTER JOIN
MethodContacted ON ReportedWeeds.MCID = MethodContacted.MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.BioAgent = BioAgents.BAID)

WHERE (ReportedWeeds.Source LIKE '%' + ? + '%') AND
(ReportedWeeds.theDate
= ?) AND (ReportedWeeds.theDate <= ?)
Click to expand...

What I found was that the concatenated field was causing the problem. I'm
thinking that Access thinks the + character is for adding numbers (i.e.,
double) and that's what's producing the error. However, if I change the
concatenation to characters Access likes to use (i.e., the ampersand and
the double quote), then the designer doesn't like that. So, it's like a
catch 22 here. What I'm doing in the interim is just including the fields
and concatenating the values for those fields and passing it to my
reports. Ugly but it's working for now. I would love to have it work like
it's supposed to..

Thanks for looking

S


Mike McIntyre said:
Can you send the query with the problem so it can be inspect or
correctness?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com
Click to expand...
Click to expand...
 
Robin,
yes, read down further... :)

S
RobinS said:
Have you tried using the ampersand and the single quote? I don't think
Access supports + for concatenation.

Robin S.
----------------------------------
SAL said:
Hi Mike,
Here's the query:
SELECT
ReportedWeeds.RID, Weeds.Name, ReportedWeeds.Density, BioAgents.BName,
ReportedWeeds.Assr_SN, ReportedWeeds.TownRange, ReportedWeeds.LotSize,
ReportedWeeds.theDate, ROW.[Desc] AS RDesc, ReportedWeeds.Owner,
ReportedWeeds.OwnStAddrs, ReportedWeeds.OwnCity, ReportedWeeds.OwnState,
ReportedWeeds.OwnZip, ReportedWeeds.OwnPhone, MethodContacted.[Desc] AS
MDesc, ReportedWeeds.SitusHsnbr + ' ' + ReportedWeeds.SitusStDir + ' ' +
ReportedWeeds.SitusStName + ' ' + ReportedWeeds.SitusSType + ' ' +
ReportedWeeds.SitusCity AS SITADDRS, ReportedWeeds.Source,
ReportedWeeds.SourceCity, ReportedWeeds.SourceState,
ReportedWeeds.SourceStDir,
ReportedWeeds.SourceHsnbr, ReportedWeeds.SourceStName,
ReportedWeeds.SourceZip, ReportedWeeds.SourcePhone,
ReportedWeeds.Comments, ReportedWeeds.FollowUp,
ReportedWeeds.FollowUpNotes,
ReportedWeeds.Atlas

FROM ((((ReportedWeeds LEFT OUTER JOIN
ROW ON ReportedWeeds.ROWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.WID = Weeds.WID) LEFT OUTER JOIN
MethodContacted ON ReportedWeeds.MCID = MethodContacted.MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.BioAgent = BioAgents.BAID)

WHERE (ReportedWeeds.Source LIKE '%' + ? + '%') AND
(ReportedWeeds.theDate
= ?) AND (ReportedWeeds.theDate <= ?)
Click to expand...

What I found was that the concatenated field was causing the problem. I'm
thinking that Access thinks the + character is for adding numbers (i.e.,
double) and that's what's producing the error. However, if I change the
concatenation to characters Access likes to use (i.e., the ampersand and
the double quote), then the designer doesn't like that. So, it's like a
catch 22 here. What I'm doing in the interim is just including the fields
and concatenating the values for those fields and passing it to my
reports. Ugly but it's working for now. I would love to have it work like
it's supposed to..

Thanks for looking

S


Mike McIntyre said:
Can you send the query with the problem so it can be inspect or
correctness?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com


Hello,
Using the designer in my project, I created a DataTable for one of the
tables in an Access database. I created a TableAdapter for that
DataTable by adding queries. For the most part, the queries are
executing correctly but this one does not return any rows. If I past
the sql into the Access database from the TableAdapter, it returns
rows. Does anyone know how to debug what the command is sending to the
Access database for any particular query?
This query uses parameters and I'm afraid it's not sending the where
clause correctly..

Any help with this would be much appreciated

S
Click to expand...
Click to expand...
Click to expand...
 
No I don't but I'm confused as to how that would that help?

S
Cor Ligthert said:
Sal,

Do you have option strict on in top of your program?

Cor

SAL said:
Hi Mike,
Here's the query:
SELECT
ReportedWeeds.RID, Weeds.Name, ReportedWeeds.Density, BioAgents.BName,
ReportedWeeds.Assr_SN, ReportedWeeds.TownRange, ReportedWeeds.LotSize,
ReportedWeeds.theDate, ROW.[Desc] AS RDesc, ReportedWeeds.Owner,
ReportedWeeds.OwnStAddrs, ReportedWeeds.OwnCity, ReportedWeeds.OwnState,
ReportedWeeds.OwnZip, ReportedWeeds.OwnPhone, MethodContacted.[Desc] AS
MDesc, ReportedWeeds.SitusHsnbr + ' ' + ReportedWeeds.SitusStDir + ' ' +
ReportedWeeds.SitusStName + ' ' + ReportedWeeds.SitusSType + ' ' +
ReportedWeeds.SitusCity AS SITADDRS, ReportedWeeds.Source,
ReportedWeeds.SourceCity, ReportedWeeds.SourceState,
ReportedWeeds.SourceStDir,
ReportedWeeds.SourceHsnbr, ReportedWeeds.SourceStName,
ReportedWeeds.SourceZip, ReportedWeeds.SourcePhone,
ReportedWeeds.Comments, ReportedWeeds.FollowUp,
ReportedWeeds.FollowUpNotes,
ReportedWeeds.Atlas

FROM ((((ReportedWeeds LEFT OUTER JOIN
ROW ON ReportedWeeds.ROWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.WID = Weeds.WID) LEFT OUTER JOIN
MethodContacted ON ReportedWeeds.MCID = MethodContacted.MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.BioAgent = BioAgents.BAID)

WHERE (ReportedWeeds.Source LIKE '%' + ? + '%') AND
(ReportedWeeds.theDate
= ?) AND (ReportedWeeds.theDate <= ?)
Click to expand...

What I found was that the concatenated field was causing the problem. I'm
thinking that Access thinks the + character is for adding numbers (i.e.,
double) and that's what's producing the error. However, if I change the
concatenation to characters Access likes to use (i.e., the ampersand and
the double quote), then the designer doesn't like that. So, it's like a
catch 22 here. What I'm doing in the interim is just including the fields
and concatenating the values for those fields and passing it to my
reports. Ugly but it's working for now. I would love to have it work like
it's supposed to..

Thanks for looking

S


Mike McIntyre said:
Can you send the query with the problem so it can be inspect or
correctness?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com


Hello,
Using the designer in my project, I created a DataTable for one of the
tables in an Access database. I created a TableAdapter for that
DataTable by adding queries. For the most part, the queries are
executing correctly but this one does not return any rows. If I past
the sql into the Access database from the TableAdapter, it returns
rows. Does anyone know how to debug what the command is sending to the
Access database for any particular query?
This query uses parameters and I'm afraid it's not sending the where
clause correctly..

Any help with this would be much appreciated

S
Click to expand...
Click to expand...
Click to expand...
 
Well, you said you tried the ampersand and the double quote. I took that to
mean you changed + to & and ' to ", but didn't try & and '. (Did you get
that? ;-)

Also, I assume you are connecting to Access with the OLEDB stuff, and not
SQLServer?

Robin S.
----------------------
SAL said:
Robin,
yes, read down further... :)

S
RobinS said:
Have you tried using the ampersand and the single quote? I don't think
Access supports + for concatenation.

Robin S.
----------------------------------
SAL said:
Hi Mike,
Here's the query:
SELECT
ReportedWeeds.RID, Weeds.Name, ReportedWeeds.Density,
BioAgents.BName,
ReportedWeeds.Assr_SN, ReportedWeeds.TownRange, ReportedWeeds.LotSize,
ReportedWeeds.theDate, ROW.[Desc] AS RDesc, ReportedWeeds.Owner,
ReportedWeeds.OwnStAddrs, ReportedWeeds.OwnCity,
ReportedWeeds.OwnState,
ReportedWeeds.OwnZip, ReportedWeeds.OwnPhone, MethodContacted.[Desc] AS
MDesc, ReportedWeeds.SitusHsnbr + ' ' + ReportedWeeds.SitusStDir + ' '
+
ReportedWeeds.SitusStName + ' ' + ReportedWeeds.SitusSType + ' ' +
ReportedWeeds.SitusCity AS SITADDRS, ReportedWeeds.Source,
ReportedWeeds.SourceCity, ReportedWeeds.SourceState,
ReportedWeeds.SourceStDir,
ReportedWeeds.SourceHsnbr, ReportedWeeds.SourceStName,
ReportedWeeds.SourceZip, ReportedWeeds.SourcePhone,
ReportedWeeds.Comments, ReportedWeeds.FollowUp,
ReportedWeeds.FollowUpNotes,
ReportedWeeds.Atlas

FROM ((((ReportedWeeds LEFT OUTER JOIN
ROW ON ReportedWeeds.ROWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.WID = Weeds.WID) LEFT OUTER JOIN
MethodContacted ON ReportedWeeds.MCID = MethodContacted.MCID) LEFT
OUTER
JOIN
BioAgents ON ReportedWeeds.BioAgent = BioAgents.BAID)

WHERE (ReportedWeeds.Source LIKE '%' + ? + '%') AND
(ReportedWeeds.theDate
= ?) AND (ReportedWeeds.theDate <= ?)

What I found was that the concatenated field was causing the problem.
I'm thinking that Access thinks the + character is for adding numbers
(i.e., double) and that's what's producing the error. However, if I
change the concatenation to characters Access likes to use (i.e., the
ampersand and the double quote), then the designer doesn't like that.
So, it's like a catch 22 here. What I'm doing in the interim is just
including the fields and concatenating the values for those fields and
passing it to my reports. Ugly but it's working for now. I would love
to have it work like it's supposed to..

Thanks for looking

S


Can you send the query with the problem so it can be inspect or
correctness?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com


Hello,
Using the designer in my project, I created a DataTable for one of
the tables in an Access database. I created a TableAdapter for that
DataTable by adding queries. For the most part, the queries are
executing correctly but this one does not return any rows. If I past
the sql into the Access database from the TableAdapter, it returns
rows. Does anyone know how to debug what the command is sending to
the Access database for any particular query?
This query uses parameters and I'm afraid it's not sending the where
clause correctly..

Any help with this would be much appreciated

S
Click to expand...
Click to expand...
Click to expand...
 
That would show you that using the + is not the best way.

With option strict of the result of this

"1234" + 1 will be 1235
with option strict on it will give an error.

Cor

SAL said:
No I don't but I'm confused as to how that would that help?

S
Cor Ligthert said:
Sal,

Do you have option strict on in top of your program?

Cor

SAL said:
Hi Mike,
Here's the query:
SELECT
ReportedWeeds.RID, Weeds.Name, ReportedWeeds.Density,
BioAgents.BName,
ReportedWeeds.Assr_SN, ReportedWeeds.TownRange, ReportedWeeds.LotSize,
ReportedWeeds.theDate, ROW.[Desc] AS RDesc, ReportedWeeds.Owner,
ReportedWeeds.OwnStAddrs, ReportedWeeds.OwnCity, ReportedWeeds.OwnState,
ReportedWeeds.OwnZip, ReportedWeeds.OwnPhone, MethodContacted.[Desc] AS
MDesc, ReportedWeeds.SitusHsnbr + ' ' + ReportedWeeds.SitusStDir + ' ' +
ReportedWeeds.SitusStName + ' ' + ReportedWeeds.SitusSType + ' ' +
ReportedWeeds.SitusCity AS SITADDRS, ReportedWeeds.Source,
ReportedWeeds.SourceCity, ReportedWeeds.SourceState,
ReportedWeeds.SourceStDir,
ReportedWeeds.SourceHsnbr, ReportedWeeds.SourceStName,
ReportedWeeds.SourceZip, ReportedWeeds.SourcePhone,
ReportedWeeds.Comments, ReportedWeeds.FollowUp,
ReportedWeeds.FollowUpNotes,
ReportedWeeds.Atlas

FROM ((((ReportedWeeds LEFT OUTER JOIN
ROW ON ReportedWeeds.ROWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.WID = Weeds.WID) LEFT OUTER JOIN
MethodContacted ON ReportedWeeds.MCID = MethodContacted.MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.BioAgent = BioAgents.BAID)

WHERE (ReportedWeeds.Source LIKE '%' + ? + '%') AND
(ReportedWeeds.theDate
= ?) AND (ReportedWeeds.theDate <= ?)

What I found was that the concatenated field was causing the problem.
I'm thinking that Access thinks the + character is for adding numbers
(i.e., double) and that's what's producing the error. However, if I
change the concatenation to characters Access likes to use (i.e., the
ampersand and the double quote), then the designer doesn't like that.
So, it's like a catch 22 here. What I'm doing in the interim is just
including the fields and concatenating the values for those fields and
passing it to my reports. Ugly but it's working for now. I would love to
have it work like it's supposed to..

Thanks for looking

S


Can you send the query with the problem so it can be inspect or
correctness?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com


Hello,
Using the designer in my project, I created a DataTable for one of the
tables in an Access database. I created a TableAdapter for that
DataTable by adding queries. For the most part, the queries are
executing correctly but this one does not return any rows. If I past
the sql into the Access database from the TableAdapter, it returns
rows. Does anyone know how to debug what the command is sending to the
Access database for any particular query?
This query uses parameters and I'm afraid it's not sending the where
clause correctly..

Any help with this would be much appreciated

S
Click to expand...
Click to expand...
Click to expand...
 
Back
Top