Help with TableAdapter - Visual Basic .NET

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

Apr 4 '07 #1

Subscribe Reply

1264

Mike McIntyre

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

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com
"SAL" <SA**@NoNo.comw rote in message
news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..

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

Apr 5 '07 #2

SAL

Hi Mike,
Here's the query:
SELECT
ReportedWeeds.R ID, Weeds.Name, ReportedWeeds.D ensity, BioAgents.BName ,
ReportedWeeds.A ssr_SN, ReportedWeeds.T ownRange, ReportedWeeds.L otSize,
ReportedWeeds.t heDate, ROW.[Desc] AS RDesc, ReportedWeeds.O wner,
ReportedWeeds.O wnStAddrs, ReportedWeeds.O wnCity, ReportedWeeds.O wnState,
ReportedWeeds.O wnZip, ReportedWeeds.O wnPhone, MethodContacted .[Desc] AS
MDesc, ReportedWeeds.S itusHsnbr + ' ' + ReportedWeeds.S itusStDir + ' ' +
ReportedWeeds.S itusStName + ' ' + ReportedWeeds.S itusSType + ' ' +
ReportedWeeds.S itusCity AS SITADDRS, ReportedWeeds.S ource,
ReportedWeeds.S ourceCity, ReportedWeeds.S ourceState,
ReportedWeeds.S ourceStDir,
ReportedWeeds.S ourceHsnbr, ReportedWeeds.S ourceStName,
ReportedWeeds.S ourceZip, ReportedWeeds.S ourcePhone,
ReportedWeeds.C omments, ReportedWeeds.F ollowUp, ReportedWeeds.F ollowUpNotes,
ReportedWeeds.A tlas

FROM ((((ReportedWee ds LEFT OUTER JOIN
ROW ON ReportedWeeds.R OWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.W ID = Weeds.WID) LEFT OUTER JOIN
MethodContacted ON ReportedWeeds.M CID = MethodContacted .MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.B ioAgent = 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
"Mike McIntyre" <mi****@getdotn etcode.comwrote in message
news:e0******** ******@TK2MSFTN GP06.phx.gbl...

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

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com
"SAL" <SA**@NoNo.comw rote in message
news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..
>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

Apr 5 '07 #3

Cor Ligthert [MVP]

Sal,

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

Cor

"SAL" <SA**@NoNo.coms chreef in bericht
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..

Hi Mike,
Here's the query:
SELECT
ReportedWeeds.R ID, Weeds.Name, ReportedWeeds.D ensity, BioAgents.BName ,
ReportedWeeds.A ssr_SN, ReportedWeeds.T ownRange, ReportedWeeds.L otSize,
ReportedWeeds.t heDate, ROW.[Desc] AS RDesc, ReportedWeeds.O wner,
ReportedWeeds.O wnStAddrs, ReportedWeeds.O wnCity, ReportedWeeds.O wnState,
ReportedWeeds.O wnZip, ReportedWeeds.O wnPhone, MethodContacted .[Desc] AS
MDesc, ReportedWeeds.S itusHsnbr + ' ' + ReportedWeeds.S itusStDir + ' ' +
ReportedWeeds.S itusStName + ' ' + ReportedWeeds.S itusSType + ' ' +
ReportedWeeds.S itusCity AS SITADDRS, ReportedWeeds.S ource,
ReportedWeeds.S ourceCity, ReportedWeeds.S ourceState,
ReportedWeeds.S ourceStDir,
ReportedWeeds.S ourceHsnbr, ReportedWeeds.S ourceStName,
ReportedWeeds.S ourceZip, ReportedWeeds.S ourcePhone,
ReportedWeeds.C omments, ReportedWeeds.F ollowUp,
ReportedWeeds.F ollowUpNotes,
ReportedWeeds.A tlas

FROM ((((ReportedWee ds LEFT OUTER JOIN
ROW ON ReportedWeeds.R OWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.W ID = Weeds.WID) LEFT OUTER JOIN
MethodContacted ON ReportedWeeds.M CID = MethodContacted .MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.B ioAgent = 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
"Mike McIntyre" <mi****@getdotn etcode.comwrote in message
news:e0******** ******@TK2MSFTN GP06.phx.gbl...
>Can you send the query with the problem so it can be inspect or
correctness?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com
"SAL" <SA**@NoNo.comw rote in message
news:%2******* *********@TK2MS FTNGP02.phx.gbl ...
>>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

Apr 6 '07 #4

RobinS

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

Robin S.
----------------------------------
"SAL" <SA**@NoNo.comw rote in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..

Hi Mike,
Here's the query:
SELECT
ReportedWeeds.R ID, Weeds.Name, ReportedWeeds.D ensity, BioAgents.BName ,
ReportedWeeds.A ssr_SN, ReportedWeeds.T ownRange, ReportedWeeds.L otSize,
ReportedWeeds.t heDate, ROW.[Desc] AS RDesc, ReportedWeeds.O wner,
ReportedWeeds.O wnStAddrs, ReportedWeeds.O wnCity, ReportedWeeds.O wnState,
ReportedWeeds.O wnZip, ReportedWeeds.O wnPhone, MethodContacted .[Desc] AS
MDesc, ReportedWeeds.S itusHsnbr + ' ' + ReportedWeeds.S itusStDir + ' ' +
ReportedWeeds.S itusStName + ' ' + ReportedWeeds.S itusSType + ' ' +
ReportedWeeds.S itusCity AS SITADDRS, ReportedWeeds.S ource,
ReportedWeeds.S ourceCity, ReportedWeeds.S ourceState,
ReportedWeeds.S ourceStDir,
ReportedWeeds.S ourceHsnbr, ReportedWeeds.S ourceStName,
ReportedWeeds.S ourceZip, ReportedWeeds.S ourcePhone,
ReportedWeeds.C omments, ReportedWeeds.F ollowUp,
ReportedWeeds.F ollowUpNotes,
ReportedWeeds.A tlas

FROM ((((ReportedWee ds LEFT OUTER JOIN
ROW ON ReportedWeeds.R OWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.W ID = Weeds.WID) LEFT OUTER JOIN
MethodContacted ON ReportedWeeds.M CID = MethodContacted .MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.B ioAgent = 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
"Mike McIntyre" <mi****@getdotn etcode.comwrote in message
news:e0******** ******@TK2MSFTN GP06.phx.gbl...
>Can you send the query with the problem so it can be inspect or
correctness?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com
"SAL" <SA**@NoNo.comw rote in message
news:%2******* *********@TK2MS FTNGP02.phx.gbl ...
>>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

Apr 6 '07 #5

SAL

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

S
"Cor Ligthert [MVP]" <no************ @planet.nlwrote in message
news:OS******** ******@TK2MSFTN GP04.phx.gbl...

Sal,

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

Cor

"SAL" <SA**@NoNo.coms chreef in bericht
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..
>Hi Mike,
Here's the query:
SELECT
ReportedWeeds.R ID, Weeds.Name, ReportedWeeds.D ensity, BioAgents.BName ,
ReportedWeeds. Assr_SN, ReportedWeeds.T ownRange, ReportedWeeds.L otSize,
ReportedWeeds. theDate, ROW.[Desc] AS RDesc, ReportedWeeds.O wner,
ReportedWeeds. OwnStAddrs, ReportedWeeds.O wnCity, ReportedWeeds.O wnState,
ReportedWeeds. OwnZip, ReportedWeeds.O wnPhone, MethodContacted .[Desc] AS
MDesc, ReportedWeeds.S itusHsnbr + ' ' + ReportedWeeds.S itusStDir + ' ' +
ReportedWeeds. SitusStName + ' ' + ReportedWeeds.S itusSType + ' ' +
ReportedWeeds. SitusCity AS SITADDRS, ReportedWeeds.S ource,
ReportedWeeds. SourceCity, ReportedWeeds.S ourceState,
ReportedWeeds. SourceStDir,
ReportedWeeds. SourceHsnbr, ReportedWeeds.S ourceStName,
ReportedWeeds. SourceZip, ReportedWeeds.S ourcePhone,
ReportedWeeds. Comments, ReportedWeeds.F ollowUp,
ReportedWeeds. FollowUpNotes,
ReportedWeeds. Atlas

FROM ((((ReportedWee ds LEFT OUTER JOIN
ROW ON ReportedWeeds.R OWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.W ID = Weeds.WID) LEFT OUTER JOIN
MethodContacte d ON ReportedWeeds.M CID = MethodContacted .MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.B ioAgent = 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
concatenatio n 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" <mi****@getdotn etcode.comwrote in message
news:e0******* *******@TK2MSFT NGP06.phx.gbl.. .
>>Can you send the query with the problem so it can be inspect or
correctness ?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com
"SAL" <SA**@NoNo.comw rote in message
news:%2****** **********@TK2M SFTNGP02.phx.gb l...
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

Apr 6 '07 #6

SAL

Robin,
yes, read down further... :)

S
"RobinS" <Ro****@NoSpam. yah.nonewrote in message
news:7s******** *************** *******@comcast .com...

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

Robin S.
----------------------------------
"SAL" <SA**@NoNo.comw rote in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..
>Hi Mike,
Here's the query:
SELECT
ReportedWeeds.R ID, Weeds.Name, ReportedWeeds.D ensity, BioAgents.BName ,
ReportedWeeds. Assr_SN, ReportedWeeds.T ownRange, ReportedWeeds.L otSize,
ReportedWeeds. theDate, ROW.[Desc] AS RDesc, ReportedWeeds.O wner,
ReportedWeeds. OwnStAddrs, ReportedWeeds.O wnCity, ReportedWeeds.O wnState,
ReportedWeeds. OwnZip, ReportedWeeds.O wnPhone, MethodContacted .[Desc] AS
MDesc, ReportedWeeds.S itusHsnbr + ' ' + ReportedWeeds.S itusStDir + ' ' +
ReportedWeeds. SitusStName + ' ' + ReportedWeeds.S itusSType + ' ' +
ReportedWeeds. SitusCity AS SITADDRS, ReportedWeeds.S ource,
ReportedWeeds. SourceCity, ReportedWeeds.S ourceState,
ReportedWeeds. SourceStDir,
ReportedWeeds. SourceHsnbr, ReportedWeeds.S ourceStName,
ReportedWeeds. SourceZip, ReportedWeeds.S ourcePhone,
ReportedWeeds. Comments, ReportedWeeds.F ollowUp,
ReportedWeeds. FollowUpNotes,
ReportedWeeds. Atlas

FROM ((((ReportedWee ds LEFT OUTER JOIN
ROW ON ReportedWeeds.R OWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.W ID = Weeds.WID) LEFT OUTER JOIN
MethodContacte d ON ReportedWeeds.M CID = MethodContacted .MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.B ioAgent = 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
concatenatio n 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" <mi****@getdotn etcode.comwrote in message
news:e0******* *******@TK2MSFT NGP06.phx.gbl.. .
>>Can you send the query with the problem so it can be inspect or
correctness ?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com
"SAL" <SA**@NoNo.comw rote in message
news:%2****** **********@TK2M SFTNGP02.phx.gb l...
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

Apr 6 '07 #7

RobinS

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" <SA**@NoNo.comw rote in message
news:Oz******** ******@TK2MSFTN GP02.phx.gbl...

Robin,
yes, read down further... :)

S
"RobinS" <Ro****@NoSpam. yah.nonewrote in message
news:7s******** *************** *******@comcast .com...
>Have you tried using the ampersand and the single quote? I don't think
Access supports + for concatenation.

Robin S.
----------------------------------
"SAL" <SA**@NoNo.comw rote in message
news:%2******* *********@TK2MS FTNGP03.phx.gbl ...
>>Hi Mike,
Here's the query:
SELECT
ReportedWeeds.R ID, Weeds.Name, ReportedWeeds.D ensity,
BioAgents.BNa me,
ReportedWeeds .Assr_SN, ReportedWeeds.T ownRange, ReportedWeeds.L otSize,
ReportedWeeds .theDate, ROW.[Desc] AS RDesc, ReportedWeeds.O wner,
ReportedWeeds .OwnStAddrs, ReportedWeeds.O wnCity,
ReportedWeeds .OwnState,
ReportedWeeds .OwnZip, ReportedWeeds.O wnPhone, MethodContacted .[Desc] AS
MDesc, ReportedWeeds.S itusHsnbr + ' ' + ReportedWeeds.S itusStDir + ' '
+
ReportedWeeds .SitusStName + ' ' + ReportedWeeds.S itusSType + ' ' +
ReportedWeeds .SitusCity AS SITADDRS, ReportedWeeds.S ource,
ReportedWeeds .SourceCity, ReportedWeeds.S ourceState,
ReportedWeeds .SourceStDir,
ReportedWeeds .SourceHsnbr, ReportedWeeds.S ourceStName,
ReportedWeeds .SourceZip, ReportedWeeds.S ourcePhone,
ReportedWeeds .Comments, ReportedWeeds.F ollowUp,
ReportedWeeds .FollowUpNotes,
ReportedWeeds .Atlas

FROM ((((ReportedWee ds LEFT OUTER JOIN
ROW ON ReportedWeeds.R OWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.W ID = Weeds.WID) LEFT OUTER JOIN
MethodContact ed ON ReportedWeeds.M CID = MethodContacted .MCID) LEFT
OUTER
JOIN
BioAgents ON ReportedWeeds.B ioAgent = BioAgents.BAID)

WHERE (ReportedWeeds. Source LIKE '%' + ? + '%') AND
(ReportedWeed s.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
"Mike McIntyre" <mi****@getdotn etcode.comwrote in message
news:e0****** ********@TK2MSF TNGP06.phx.gbl. ..
Can you send the query with the problem so it can be inspect or
correctnes s?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com
"SAL" <SA**@NoNo.comw rote in message
news:%2***** ***********@TK2 MSFTNGP02.phx.g bl...
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
>

Apr 7 '07 #8

Cor Ligthert [MVP]

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" <SA**@NoNo.coms chreef in bericht
news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..

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

S
"Cor Ligthert [MVP]" <no************ @planet.nlwrote in message
news:OS******** ******@TK2MSFTN GP04.phx.gbl...
>Sal,

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

Cor

"SAL" <SA**@NoNo.coms chreef in bericht
news:%2******* *********@TK2MS FTNGP03.phx.gbl ...
>>Hi Mike,
Here's the query:
SELECT
ReportedWeeds.R ID, Weeds.Name, ReportedWeeds.D ensity,
BioAgents.BNa me,
ReportedWeeds .Assr_SN, ReportedWeeds.T ownRange, ReportedWeeds.L otSize,
ReportedWeeds .theDate, ROW.[Desc] AS RDesc, ReportedWeeds.O wner,
ReportedWeeds .OwnStAddrs, ReportedWeeds.O wnCity, ReportedWeeds.O wnState,
ReportedWeeds .OwnZip, ReportedWeeds.O wnPhone, MethodContacted .[Desc] AS
MDesc, ReportedWeeds.S itusHsnbr + ' ' + ReportedWeeds.S itusStDir + ' ' +
ReportedWeeds .SitusStName + ' ' + ReportedWeeds.S itusSType + ' ' +
ReportedWeeds .SitusCity AS SITADDRS, ReportedWeeds.S ource,
ReportedWeeds .SourceCity, ReportedWeeds.S ourceState,
ReportedWeeds .SourceStDir,
ReportedWeeds .SourceHsnbr, ReportedWeeds.S ourceStName,
ReportedWeeds .SourceZip, ReportedWeeds.S ourcePhone,
ReportedWeeds .Comments, ReportedWeeds.F ollowUp,
ReportedWeeds .FollowUpNotes,
ReportedWeeds .Atlas

FROM ((((ReportedWee ds LEFT OUTER JOIN
ROW ON ReportedWeeds.R OWID = ROW.ROWID) LEFT OUTER JOIN
Weeds ON ReportedWeeds.W ID = Weeds.WID) LEFT OUTER JOIN
MethodContact ed ON ReportedWeeds.M CID = MethodContacted .MCID) LEFT OUTER
JOIN
BioAgents ON ReportedWeeds.B ioAgent = BioAgents.BAID)

WHERE (ReportedWeeds. Source LIKE '%' + ? + '%') AND
(ReportedWeed s.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
"Mike McIntyre" <mi****@getdotn etcode.comwrote in message
news:e0****** ********@TK2MSF TNGP06.phx.gbl. ..
Can you send the query with the problem so it can be inspect or
correctnes s?

--
Mike

Mike McIntyre [MVP]
http://www.getdotnetcode.com
"SAL" <SA**@NoNo.comw rote in message
news:%2***** ***********@TK2 MSFTNGP02.phx.g bl...
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
>

Apr 7 '07 #9

Similar topics

7546

TableAdapter Performance

by: Programatix | last post by:

Hi, Have anyone ever benchmark the TableAdapter performance compared to DataTable's Load method? I found out that the DataTable's Load method is about 10x faster than TableAdapter's Fill method. I used the same SELECT query and DataSet (predefined), for both test. For the DataTable's Load method, we first need to create a DataReader.

Visual Basic .NET

7208

ADO.NET 2.0 TableAdapter Configuration Wizard

by: Mike | last post by:

Dear Group, When I add a DataTable to a Typed Dataset, and a TableAdapter to the DataTable, I am able to create methods to send updates directly to the database (GenerateDBDirectMethods), however when my TableAdapter Fill SQL contains a WHERE clause which looks up a field from another joined table, the Fill works perfectly, but I cannot Insert, Update or Delete using the TableAdapter. Does anyone know why this is?

Visual Basic .NET

4549

Data Access Layer using TableAdapter & Performance / Packaging

by: Demetri | last post by:

I have a question / concern regarding the new suggested way of creating a data access layer in an n-tier application. Typically, a web application specifically, using the SOA (Service Oriented Architecture) approach. At various sites such as 15 Seconds (http://www.15seconds.com/issue/050721.htm) they advocate using the TableAdapter wizard to generate the data access layer. Describing the TableAdapter wizard, here is a quote from the...

.NET Framework

1749

TableAdapter Problem

by: GaryDean | last post by:

I'm comparing use of DataView -ObjectDataSource -TableAdapter to DataView -SqlDataSource using the Customers table on the NorthWind database. The SQL given to both wizards is: SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers But the two wizards generate different UPDATE statements (Pasted below) and

ASP.NET

3991

Help! Making wildcard TableAdapter query work

by: googlegroups.dsbl | last post by:

I'm really confused here, and am wondering if someone knows what could be the issue with my TableAdapter query. A few months ago, I created a really neat program that has th ability to search by a client account number. In the TableAdapter, I created a new query called "FillbyAccountNumber". When I created this, I set up the SELECT statement so that it selected all the fields I wanted and then used the following WHERE clause: WHERE...

C# / C Sharp

18864

Select Value From Specific Table Column in TableAdapter

by: =?Utf-8?B?UmljaCBIdXRjaGlucw==?= | last post by:

I'm not really sure how to ask this question because I'm still getting my feet wet with data access and VB.NET, but here goes: To start off with, I'm using VB 2005 Express to connect to an Access database. I have a dataset in which a parameterized query returns the correct result set in the forms of a tableadapter and a bindingnavigator. In other words, I can perform my query and see that the tableadapter and bindingnavigator contain...

Visual Basic .NET

1714

TableAdapter, Insert, and Datatype

by: Yin99 | last post by:

I'm using TableAdapter with .NET 2.0 and SQL 2005. In SQL Database, a column's type is "float". Default TableAdapter methods update, insert, get, etc. maps this automatically to c# type "double" Question- Why is it when I create a new SQL Query, Insert Method, all of a sudden the tableadapter is calling the variable "float" when the default Insert method to tableadapter it is "double" ? What is

ASP.NET

1977

Partial Class help with designer TableAdapters

by: SAL | last post by:

Hello, I would like to be able to set the WHERE clause of a select statement on the fly. I have a DataAccess layer designed using the DataSet designer and a BusinessLogic layer using classes. The business logic layer is decorated using the <System.ComponentModel.DataObject()> attributes and they are used to bind to controls and Object datasets on web forms. Is there a resonable way to set the WHERE clause on the fly with a partial class...

ASP.NET

1235

DataTable and TableAdapter

by: Tony Johansson | last post by:

Hello! Is it always that a TableAdapter and a DataTable is a pair. When I use the Dataset Designer and create a sql clause a TableAdapter and a DataTable is created which act like a pair. I mean when you have a TableAdapter you must always have a corresponding DataTable for this TableAdapter. Is it right what I have written here?

C# / C Sharp

8326

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

8743

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8622

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7355

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

4173

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4333

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2745

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

1973

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1736

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General