Calling a query in VB

[Ashlewis]

Im currently using a string to store my query in VB.
It seems to work perfectly fine for short queries.
But as my queries become longer and longer, this method just doesnt seem to work.
Im using an access database and when i run my SQL queries in that they work fine its just when i transfer them to the vb code they seem to cause errors.

So is there a way to call saved access queries in vb without actually putting the query string in the code?

This is the code im currently using it would be nice if a solution followed this general pattern of interacting with a database:

Note: the strSportSQL string is actually all on one line, this forum displays it differently.

 'Read Sports
            Dim strSportSQL As String
            strSportSQL = "SELECT DISTINCT TblSport.sportName FROM TblSport INNER JOIN ((TblLeague INNER JOIN TblLeagueSeason ON TblLeague.leagueID = TblLeagueSeason.leagueID) INNER JOIN TblLeagueSeasonTeam ON TblLeagueSeason.leagueSeasonID = TblLeagueSeasonTeam.leagueSeasonID) ON TblSport.sportID = TblLeague.sportID GROUP BY TblSport.sportName, TblLeague.leagueID, TblLeague.size, TblLeague.leagueName HAVING (((Count(TblLeagueSeasonTeam.teamID))<[size]));"
 
            Dim DataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/Database.mdb"))
 
            Dim SportCommand As New OleDbCommand(strSportSQL, DataConn)
 
            DataConn.Open()
 

[Ashlewis]

Sorry to double post I cant seem to edit my original one.
Ive further narrowed down the problem to it causes this error, when ever i use a query with GROUP BY in it.

(in this case im using GROUP BY and HAVING)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
 
Exception Details: System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
 

[balabaster]

To run presaved queries in Access, you treat them as you would a stored procedure:

Dim oCmd As New OledbCommand("qryMySavedQuery")
oCmd.CommandType = CommandType.StoredProcedure
''If you need to add parameters, use the following syntax
'oCmd.Parameters.Add(New OledbParameter("InputParam1", "Id"))
Dim oRdr As OledbDataReader = oCmd.ExecuteReader()

OledbCommand oCmd = new OledbCommand("qryMySavedQuery");
oCmd.CommandType = CommandType.StoredProcedure;
// //If you need to add parameters, use the following syntax
//oCmd.Parameters.Add(new OledbParameter("InputParam1", "Id"));
OledbDataReader oRdr = oCmd.ExecuteReader();

That should simplify your life from all those hideous stringbuilder queries.

[Ashlewis]

Thank you.
(Im coding in VB)

Now I have another problem,
What if i want to do somthing like this

strSQL = "SELECT userName FROM tblUser WHERE userName = ' " & txtUsername.Tex t & " ' "

but obviously doing it the tidy way you showed me.

I mean how do would I use the parameter code, how does it correspond to an SQL statement.

[Ashlewis]

Please ignore that above post, i was having a moment of madness

Im now using the parameter code to run a similar query but this time the query obviously takes a parameter. This parameter is called selectedUser

This is how ive used it

Dim DataConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; data source=" & Server.MapPath("App_Data/Database.mdb"))
        Dim LeagueCommand As New OleDbCommand("QryOpenLeagues")
        oCommand.CommandType = Data.CommandType.StoredProcedure
        oCommand.Connection = DataConn
        oCommand.Parameters.Add(New OleDbParameter(ddlUser.SelectedValue, "selectedUser"))
 

I get a "Data type mismatch in criteria expression. " error for the reader i run after.

[balabaster]

In answer to your question with regards to using regular query strings:

Dim strQuery = "Select * From MyTable Where Field1 = @Param1"
Dim oCmd As New OledbCommand(strQuery, oCon)
oCmd.Parameters.Add(New OledbParameter("Param1", SelectedValue)
Dim oRdr As OledbDataReader = oCmd.ExecuteReader()

In answer to your second question - the run-time error - you have your parameter and your value the wrong way round in line 5.

You've got New OledbParameter( Value, Parameter)
it should be New OledbParameter( Parameter, Value)

[Ashlewis]

Thank you very very much!
As helpful as ever.