Board index » Visual Studio » Help with TableAdapter

Visual Studio212
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


-

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



--

Mike



Mike McIntyre [MVP]

www.getdotnetcode.com">www.getdotnetcode.com





"SAL" <SAL_@NoNo.com>wrote in message



-

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

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" <mikemc@getdotnetcode.com>wrote in message



-

Sal,



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



Cor



"SAL" <SAL_@NoNo.com>schreef in bericht



-

Have you tried using the ampersand and the single quote? I don't think

Access supports + for concatenation.



Robin S.

----------------------------------

"SAL" <SAL_@NoNo.com>wrote in message



-

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



S

"Cor Ligthert [MVP]" <notmyfirstname@planet.nl>wrote in message



-

Robin,

yes, read down further... :)



S

"RobinS" <RobinS@NoSpam.yah.none>wrote in message



-

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" <SAL_@NoNo.com>wrote in message



-

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" <SAL_@NoNo.com>schreef in bericht



-