SQLite3Step(myStmtHandle) wont go to next row

Dec 28, 2014 at 11:47 PM
this is my first attempt at sql. i've shortened the query so it would fit here. i'm trying to copy the data from a sqlite database to a mysql database. everything here works excep for the sqlite3step at the bottom. it won't go to the next row.

any suggestions would be most appreciated.

RetVal = SQLite3PrepareV2(myDbHandleV2, var_str, myStmtHandle)
RetVal = SQLite3Step(myStmtHandle)
colCount = SQLite3ColumnCount(myStmtHandle)
Debug.Print (RetVal)

Do While Not SQLITE_DONE
Debug.Print (RetVal)
For i = 0 To colCount - 1

colName = SQLite3ColumnName(myStmtHandle, i)
colType = SQLite3ColumnType(myStmtHandle, i)
colTypeName = TypeName(colType)
colValue = ColumnValue(myStmtHandle, i, colType)
If colValue <> Empty Then
Select Case colName
  Case "AD_ADDR1"
    value1 = colValue
  Case "AD_ADDR2"
    value2 = colValue
  Case "EX_DATSRC1"
    value3 = colValue
  Case "SC_SOURCE2"
    value4 = colValue
End Select
End If
Next
value41 = userid
value42 = value43 + " " + value1

qry = "INSERT INTO Comps(AD_ADDR1, AD_ADDR2, EX_DATSRC1, SC_SOURCE2, EX_DOM," & _
"PR_SALEPR1, FN_CONC1, FN_CONC2, DT_SALTIM1, ` "VALUES (""" & value1 & """,""" & value2 & """,""" & value3 &
Set oRs = oConn.Execute(qry)
RetVal = SQLite3Step(myStmtHandle)
Debug.Print (RetVal)
Loop
Dec 29, 2014 at 9:41 AM
What is the value returned into RetVal?

The documentation for Step is here: https://www.sqlite.org/c3ref/step.html

-Govert
Dec 29, 2014 at 1:44 PM

The first time retval= 100 the 2nd time retval =101.

I have over 200 rows in this database and can access it fine through sqlite studio.

Dec 29, 2014 at 1:57 PM
OK, so those return values mean:

(100) SQLITE_ROW - The SQLITE_ROW result code returned by sqlite3_step() indicates that another row of output is available.

(101) SQLITE_DONE - The SQLITE_DONE result code indicates that an operation has completed. The SQLITE_DONE result code is most commonly seen as a return value from sqlite3_step() indicating that the SQL statement has run to completion.

You should check the loop - do you mean to check RetVal in the Do While.... condition?

Anyway - this does not sound like an issue with SQLite or the SQLite wrapper. Maybe the SQL statement is not right, or something.

-Govert
Dec 29, 2014 at 1:57 PM
If you're still stuck you can send me the files, and I can have a look.

-Govert
Dec 29, 2014 at 2:28 PM

Here is everything I’m using

I looked up the error messages you mentioned prior to posting this. I’m stumped, I’m using sqlite for excel the newest version, excel newest version with updates on windows 8.1.

This is my first experience with sql,

Thanks in advance for your help.

Sub connect_national_DB()

On Error GoTo DBError

Dim oConn, oRs

Dim qry, connectstr

Dim db_name, db_username, db_userpassword

Dim db_server

Dim userid, var_str As String

Dim i, count, colCount, colType As Long

Dim colName, temp_str, colTypeName As String

Dim colValue As Variant

Dim value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11, value12, value13, value15, value16, value17, value18 As String

Dim value19, value20, value21, value22, value23, value24, value25, value26, value27, value28, value29, value30, value31, value32, value33, value34, value35 As String

Dim value36, value37, value38, value39, value40, value41, value42, value43 As String

db_server = "23.229.143.68"

db_name = "nationalcomp"

db_username = "pandale"

db_userpassword = "Uvalde1960!?"

connectstr = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=23.229.143.68; PORT=3306;" & "DATABASE=nationalcomp; USER=pandale; PASSWORD=Uvalde1960!?; OPTION=3;"

Set oRs = CreateObject("ADODB.Recordset")

Set oConn = CreateObject("ADODB.Connection")

userid = Sheets("register").Cells(22, 2)

dt = Nothing

DBError:

Debug.Print (Error)

oConn.Open connectstr

qry = "truncate table Comps"

Set oRs = oConn.Execute(qry)

qry = "select count(*) as the_count from Comps where userid = """ & userid & """"

Set oRs = oConn.Execute(qry)

Set exists = oRs.Fields("the_count")

If exists = 0 Then

open_comp_db

var_str = "SELECT * FROM TOTALFormSource LIMIT 1"

' Create the sql statement - getting a StmtHandle back

RetVal = SQLite3PrepareV2(myDbHandleV2, var_str, myStmtHandle)

RetVal = SQLite3Step(myStmtHandle)

colCount = SQLite3ColumnCount(myStmtHandle)

Debug.Print (RetVal)

Do While Not SQLITE_DONE

Debug.Print (RetVal)

For i = 0 To colCount - 1

colName = SQLite3ColumnName(myStmtHandle, i)

colType = SQLite3ColumnType(myStmtHandle, i)

colTypeName = TypeName(colType)

colValue = ColumnValue(myStmtHandle, i, colType)

If colValue <> Empty Then

Select Case colName

Case "AD_ADDR1"

value1 = colValue

Case "AD_ADDR2"

value2 = colValue

Case "EX_DATSRC1"

value3 = colValue

Case "SC_SOURCE2"

value4 = colValue

Case "EX_DOM"

value5 = colValue

Case "PR_SALEPR1"

value6 = colValue

Case "FN_CONC1"

value7 = colValue

Case "FN_CONC2"

value8 = colValue

Case "DT_SALTIM1"

value9 = colValue

Case "LC_LOCAT1"

value10 = colValue

Case "RG_RIGHTS"

value11 = colValue

Case "ST_SITSIZE"

value12 = colValue

Case "ST_SITEVW"

value13 = colValue

Case "DA_DESAPL1"

value14 = colValue

Case "DA_CONSTQL"

value15 = Right(colValue, 1)

Case "AG_AGYRBLT"

value16 = colValue

Case "AG_COND1"

value17 = Right(colValue, 1)

Case "RM_TOTAL"

value18 = colValue

Case "RM_BED"

value19 = colValue

Case "RM_BATH"

value20 = colValue

Case "SF_GLA"

value21 = colValue

Case "EX_BSMSF"

value22 = colValue

Case "EX_BSMFIN"

value23 = colValue

Case "EX_BSMRECRM"

value24 = colValue

Case "EX_BSMBED"

value25 = colValue

Case "EX_BSMFBATH"

value26 = colValue

Case "EE_EFFIC1"

value27 = colValue

Case "FU_FUNCTUT"

value28 = colValue

Case "HC_HTCOOL"

value29 = colValue

Case "CR_GARPRK1"

value30 = colValue

Case "PF_PORPAT1"

value31 = colValue

Case "BL_BLANK1"

value32 = colValue

Case "BL_BLANK2"

value33 = colValue

Case "BL_BLANK3"

value34 = colValue

Case "HS_PRIOR1"

value35 = colValue

Case "HS_PRIOR2"

value36 = colValue

Case "HS_PRIOR3"

value37 = colValue

Case "RECEFFECTDATE"

value38 = colValue

Case "LATITUDE"

value39 = colValue

Case "LONGITUDE"

value40 = colValue

Case "DB_ZIPCODE"

value43 = colValue

End Select

End If

Next

value41 = userid

value42 = value43 + " " + value1

qry = "INSERT INTO `Comps`(`AD_ADDR1`, `AD_ADDR2`, `EX_DATSRC1`, `SC_SOURCE2`, `EX_DOM`," & _

"`PR_SALEPR1`, `FN_CONC1`, `FN_CONC2`, `DT_SALTIM1`, `LC_LOCAT1`, `RG_RIGHTS`, `ST_SITSIZE`, `ST_SITEVW`, `DA_DESAPL1`," & _

"`DA_CONSTQL`, `AG_AGYRBLT`, `AG_COND1`, `RM_TOTAL`, `RM_BED`, `RM_BATH`, `SF_GLA`, `EX_BSMSF`, `EX_BSMFIN`, `EX_BSMRECRM`," & _

"`EX_BSMBED`, `EX_BSMFBATH`, `EE_EFFIC1`, `FU_FUNCTUT`, `HC_HTCOOL`, `CR_GARPRK1`, `PF_PORPAT1`, `BL_BLANK1`, `BL_BLANK2`," & _

"`BL_BLANK3`, `HS_PRIOR1`, `HS_PRIOR2`, `HS_PRIOR3`, `RECEFFECTDATE`, `LATTITUDE`, `LONGITUDE`, `USERID`,`ZIP_ADDRE_KEY`, `DB_ZIPCODE`)" & _

"VALUES (""" & value1 & """,""" & value2 & """,""" & value3 & """ ,""" & value4 & """ ,""" & value5 & """,""" & value6 & """,""" & value7 & """,""" & value8 & """,""" & value9 & """,""" & value10 & """," & _

"""" & value11 & """,""" & value12 & """,""" & value13 & """,""" & value14 & """,""" & value15 & """,""" & value16 & """,""" & value17 & """,""" & value18 & """,""" & value19 & """,""" & value20 & """," & _

"""" & value21 & """,""" & value22 & """,""" & value23 & """,""" & value24 & """,""" & value25 & """,""" & value26 & """,""" & value27 & """,""" & value28 & """,""" & value29 & """,""" & value30 & """," & _

"""" & value31 & """,""" & value32 & """,""" & value33 & """,""" & value34 & """,""" & value35 & """,""" & value36 & """,""" & value37 & """,""" & value38 & """,""" & value39 & """,""" & value40 & """," & _

"""" & value41 & """,""" & value42 & """,""" & value43 & """)"

Set oRs = oConn.Execute(qry)

RetVal = SQLite3Step(myStmtHandle)

Debug.Print (RetVal)

Loop

End If

Set oRs = Nothing

Set oConn = Nothing

End Sub

Public Sub open_comp_db()

Dim RetVal As Long

Dim comp_db_File As String

Dim InitReturn As Long

InitReturn = SQLite3Initialize ' Default path is ThisWorkbook.Path but can specify other path where the .dlls reside.

If InitReturn <> SQLITE_INIT_OK Then

Debug.Print "Error Initializing SQLite. Error: " & Err.LastDllError

Exit Sub

End If

comp_db_File = "C:\users\" & Environ("username") & "\documents\a la mode\database\spectrum.s3db"

' Open the database in read only

RetVal = SQLite3OpenV2(comp_db_File, myDbHandleV2, SQLITE_OPEN_READONLY, "")

End Sub

Dec 29, 2014 at 2:33 PM
Can you mail me your project and a sample database to govert@icon.co.za? I don't have MySql, but I'll take those parts out for now...

-Govert