CollegeSeniorProjectNeedsHelp

I am trying to add a query to my TableAdapter Fill. I am wanting to filter the results based on 3 items that are stored in passed variables. From the documentation that I have, the SQL Select statement should be...

SELECT item1, item2

FROM table

WHERE (filter1=@first and filter2=@second and filter3=@third)

however, whenever I try to do this (or even try to do just a single passing of variable in), I get "Error in WHERE clause near '@'. Unable to parse query text."

How should I be setting up this process Do I not build it onto the datafill What is the best way to do this If I force the value of the filter1 (filter1=1), that part works.

Thanks.



Re: Visual Basic General SQL Query

Deepraj_Majumdar_35eaf1

You can try in that way:

SELECT item1, item2

FROM table

WHERE (filter1=" & ' first ' & " and filter2= " & ' second ' & " and filter3= " & ' third ' & ")






Re: Visual Basic General SQL Query

CollegeSeniorProjectNeedsHelp

Are you sayint to use the & symbol instead of the @ symbol Thanks.





Re: Visual Basic General SQL Query

dyspgmr

I have encountered this several times myself and the way it went away was by chaning the parenthesis:

WHERE (filter1=@first) and (filter2=@second) and (filter3=@third)

And if the error still continues, make sure that ALL the parameters are declared and assigned before the fill command is executed.

Ben





Re: Visual Basic General SQL Query

CollegeSeniorProjectNeedsHelp

Ben, I am getting the errors in the Dataset.xsd screen as I am building the filters. Is this the wrong place to set this up

I tried your formatting but with even WHERE (filter1=@first) and "first" being a public variable, I still get the same error I have been getting.

Thanks for the help.





Re: Visual Basic General SQL Query

cybertaz69

Are you using first as the var name If so that is a SQL key word........ Use ben example with different var name.....






Re: Visual Basic General SQL Query

CollegeSeniorProjectNeedsHelp

I am not using first. I have used location, locationID and passedLocation which is a public variable. I get the same error message with each of the names.

Thanks.





Re: Visual Basic General SQL Query

cybertaz69

off the wall but what type of data source are you connecting too SQL, MYSQL, Access, other

If your connecting too SQL can you post the SQL query your trying and not the example






Re: Visual Basic General SQL Query

CollegeSeniorProjectNeedsHelp

I am connecting to MySQL though should that make any difference I thought that SQL language was standardized.

The actual code currently is:

SELECT team1, team2

FROM scheduletable

WHERE (locationID=@passedLocation)

the @xxxxx has been different items along the way, but doesn't seem to matter what is there. And to clarify is this the query for the table in the DataSet.

Thanks.





Re: Visual Basic General SQL Query

cybertaz69

You would think but I found queries designed for SQL server work and won't work in access. Data formats and varable references are the two bigest issues.

Back to your problem..

Is locationID a column in scheduletable (Sorry to ask this but the statment is formated correctly) never mind see link bellow.

Did find this link......

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=653253&SiteID=1

Is does have some references that may help.

With what i've read VB2005 express can't connect to mysql and vb2005 full requires ADO.NET driver....






Re: Visual Basic General SQL Query

CollegeSeniorProjectNeedsHelp

I am running the full VB from Visual Studio 2005 and have the ADO.NET driver and the Visual Studio Plugin from MySQL (latest "working" versions or recommended versions per MySQL forums).

Any other suggestions

Thanks





Re: Visual Basic General SQL Query

cybertaz69

sorry, I don't use mySQL as a datasource so I can't really help you on that topic. Some thing I would try if I was in your place is don't uses the designer. Try coding the connection and SQL statment by hand...... and read in the results to the typeed dataset. This basiclly means coding all the select, updates, inserts, and delete statments.

good luck




Re: Visual Basic General SQL Query

Graham

Hi,

I've had no luck writing queries that way either. However, the following sort of thing works for me:

SELECT Foo FROM Bar WHERE Noseharp = ;

The question mark is a placeholder for a value supplied by a Parameter object.

I have used this in both ADO-Classic and ADO.NET clients, querying both JET and MS-SQL databases.

HTH.

-- graham





Re: Visual Basic General SQL Query

CollegeSeniorProjectNeedsHelp

Graham,

Have you been able to do selects for multiple columns with multiple where items

Thanks.





Re: Visual Basic General SQL Query

Graham

Absolutely.

Code Snippet

SELECT Abc, Def

FROM Fgh

WHERE (Jkl = ) AND (Mno = );

That query, obviously, requires two Parameter objects. Be sure to add them to your Command's Parameters collection in the correct order. Yes, the Parameter objects have a Name member, but it is ignored -- only order matters.

This sort of thing also works for INSERT, UPDATE, and EXECUTE queries.

Code Snippet

UPDATE Fgh

SET Abc =

WHERE Mno = ;

-- graham