VB5 and MySql Query Problem 1

[ToeShot]

First I am using VB5 Enterprise Edition. and I am trying to run this query in this code:

Private Sub cmdUpdate_Click()
strsql = "DROP TABLE temp;" _
& "CREATE TABLE temp" _
& "SELECT COUNT(*) AS Calls, SUM(minutes) AS Minutes FROM" _
& "details WHERE time BETWEEN '7:01' AND '15:00';" _
& "INSERT INTO temp" _
& "SELECT COUNT(*), SUM(minutes) FROM details WHERE time" _
& "NOT BETWEEN '7:00' AND '15:00'; _
& "INSERT INTO temp" _
& "SELECT COUNT(*), SUM(minutes) FROM details;" _
& "SELECT * FROM temp;"

datPrimaryRS.RecordSource = strsql
grdDataGrid.Refresh
datPrimaryRS.Refresh
End Sub

I am running this piece of code against MySql 3.39.41 and according to there HTML manual This the way I need to run this query since they don't support union queries yet.

this works in the MySql enviorment. But not when I run the Code in VB. Does anyone have any ideas or knows why.

 

[harmmeijer]

Could you try this:

Dim cmdMakeTemp As ADODB.Command
Dim someConnection As Connection
someConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb;Persist Security Info=False"
' I do not know the connection string of a mySQL connection
cmdMakeTemp.CommandText = "DROP TABLE temp;" _
& "CREATE TABLE temp" _
& "SELECT COUNT(*) AS Calls, SUM(minutes) AS Minutes FROM" _
& "details WHERE time BETWEEN '7:01' AND '15:00';" _
& "INSERT INTO temp" _
& "SELECT COUNT(*), SUM(minutes) FROM details WHERE time" _
& "NOT BETWEEN '7:00' AND '15:00';" _
& "INSERT INTO temp" _
& "SELECT COUNT(*), SUM(minutes) FROM details;"
someConnection.Open
cmdMakeTemp.ActiveConnection = someConnection
cmdMakeTemp.Execute
Set someConnection = Nothing
Set cmdMakeTemp = Nothing
datPrimaryRS.RecordSource = "SELECT * FROM temp;"

 

[chiph]

You missed the semi-colon when creating your temp table.

Chip H.

 

[ToeShot]

I got it Thanks harmmeijer for your Idea it set me on the right path. Since I am not using ADO and am using DAO I had to create a querydef. Here is what I came up with that worked.

Dim qdfTempTable As QueryDef

Set qdfTempTable = datPrimaryRS.Database.CreateQueryDef

qdfTempTable.SQL = "DROP TABLE temp;" _
& "CREATE TABLE temp" _
& "SELECT COUNT(*) AS Calls, SUM(minutes) AS Minutes FROM" _
& "details WHERE time BETWEEN '7:01' AND '15:00';" _
& "INSERT INTO temp" _
& "SELECT COUNT(*), SUM(minutes) FROM details WHERE time" _
& "NOT BETWEEN '7:00' AND '15:00';" _
& "INSERT INTO temp" _
& "SELECT COUNT(*), SUM(minutes) FROM details;"

datPrimaryRS.RecordSource = "SELECT * FROM temp;"
grdDataGrid.Refresh
datPrimaryRS.Refresh

Chiph I can't add that semi colon since I need The Select Into statements to create my tables.

 

[chiph]

OK, I forgot you can populate the table at the same time you're creating it. I've always done it as separate steps.

Glad you got it working. I saw a story on Slashdot today (

http://slashdot.org)

about the new features in MySQL 4.0.

Chip H.