hi Wayne,

I try the code but I encounter a Record not found error so I change some buffers and
add a column in database which is numbers with Data Type number;
SQLStatement db "select * from AngBiblia ",0
WhereStatement db "where ((books = ? and verse = ? ) or ID = ?) ",0


to


invoke SQLBindParameter,hStmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,25,0,addr SearchName,25,addr StrLen
invoke SQLBindParameter,hStmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,25,0,addr SearchVerse,25,addr StrLen1
;invoke SQLBindParameter,hStmt,3,SQL_PARAM_INPUT,SQL_C_ULONG,SQL_INTEGER,0,0,addr TheID,25,addr StrLen2


also if I uncomment the above SQLBindParameter for TheID the program of the SQL Statement cannot found a record.

i think the problem is in the TargetType..
Posted on 2003-11-24 23:58:59 by coredump
hi minor28,

regarding in statement "select * from AngBiblia WHERE text like '*?*' "
is it ok if I create another QueryStrings proc like in order to bind the
statement?



regards,
coredump
Posted on 2003-11-25 01:26:44 by coredump
My English is poor so I do not quite understand your question. I suppose you want to replace the question marks with seach parameters. Then you have to divide the string as follows.
.data

SQLStatement db "select * from AngBiblia ",0
WhereStatement1 db "where books = ",0
WhereStatement2 db " and verse = ",0

.code
invoke RtlZeroMemory,addr Conn,256
invoke lstrcpy,addr Conn,addr SQLStatement
invoke GetWindowText,hCombobox01,ADDR SearchName,25
invoke lstrcat,addr Conn,addr WhereStatement1
invoke lstrcat,addr Conn,addr SearchName
invoke lstrcat,addr Conn,addr WhereStatement2
invoke GetWindowText,hEdit02,ADDR SearchVerse,25
invoke lstrcat,addr Conn,addr SearchVerse


Regards
Posted on 2003-11-25 03:53:43 by minor28
I try the code but I encounter a Record not found error so I change some buffers and add a column in database which is numbers with Data Type number.


Are you sure you were using the database included in the zip file, it already had that field?

Anyway, here is an example of what you need to do for searching. It will only search for 1 word but will give you an idea how it's done. minor28's way will work also if you use SQLExecDirect.

I was lazy and jumbled everything into one SQL statement, you may want to create seperate SQL statements for "Read" "Prev/Next" and "Search" functions. It would just be cleaner.

Also, in the Control Panel for ODBC you can turn tracing on, it helps a lot to see what is being sent to the ODBC driver.
Posted on 2003-11-25 08:16:56 by Wayne
I took a look at the MainWindow code. Added a MainMenu. Added a couple of 'Exodus' and '1:4' the mdn database.

Parameters are legal only in certain places in SQL statements. For example, they are not allowed in the select list (quote SDK). Therefore you have to use SQLExecDirect.

If you want to browse the recordset you can't close the cursor.
Posted on 2003-11-25 10:37:40 by minor28
Forgott to attach the code
Posted on 2003-11-25 10:39:14 by minor28
For scrolling: The cursor type must be changed from default "forward only" to "static".
ODBCConnect proc hWnd:DWORD

invoke SQLAllocHandle,SQL_HANDLE_ENV,SQL_NULL_HANDLE,addr hEnv
.IF ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke SQLSetEnvAttr,hEnv,SQL_ATTR_ODBC_VERSION,\
SQL_OV_ODBC3,0
.IF ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke SQLAllocHandle,SQL_HANDLE_DBC,hEnv,addr hConn
.IF ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke lstrcpy,addr ConnectString,addr strConnect
invoke lstrcat,addr ConnectString,addr ProgPath
invoke lstrcat,addr ConnectString,addr DBName
invoke SQLDriverConnect,hConn,hWnd,addr ConnectString,sizeof ConnectString,\
addr Conn,sizeof Conn,addr StrLen,SQL_DRIVER_COMPLETE
.IF ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke SwitchMenuState,TRUE
invoke SetWindowText,hWnd,addr DBName
invoke SQLAllocHandle,SQL_HANDLE_STMT,hConn,addr hStmt
.if ax==SQL_SUCCESS || SQL_SUCCESS_WITH_INFO
invoke SQLSetStmtAttr,hStmt,SQL_ATTR_CURSOR_TYPE,\
SQL_CURSOR_STATIC,0
invoke SwitchMenuState,TRUE
.else
invoke MessageBox,hWnd,addr AllocStmtFail,addr AppName,MB_OK+MB_ICONERROR
.endif
.ELSEIF
invoke SQLFreeHandle,SQL_HANDLE_DBC,hConn
invoke SQLFreeHandle,SQL_HANDLE_ENV,hEnv
invoke MessageBox,hWnd,addr ConnFail,addr AppName,MB_OK+MB_ICONERROR
.endif
.ELSE
invoke SQLFreeHandle,SQL_HANDLE_ENV,hEnv
invoke MessageBox,hWnd,addr AllocConnFail,addr AppName,MB_OK+MB_ICONERROR
.endif
.ELSE
invoke SQLFreeHandle,SQL_HANDLE_ENV,hEnv
invoke MessageBox,hWnd,addr SetAttrFail,addr AppName,MB_OK+MB_ICONERROR
.endif
.ELSE
invoke MessageBox,hWnd,addr AllocEnvFail,addr AppName,MB_OK+MB_ICONERROR
.endif
ret
ODBCConnect endp

Read a new statement:
.IF ax==Button01ID ;Read

invoke RtlZeroMemory,addr Conn,256
invoke lstrcpy,addr Conn,addr SQLStatement ;"select * from AngBiblia"
invoke GetWindowText,hCombobox01,ADDR SearchName,25
.if eax!=0
invoke GetWindowText,hEdit02,ADDR SearchVerse,25
.if eax!=0
invoke lstrcat,addr Conn,addr WhereStatement1 ;" where books = '"
invoke lstrcat,addr Conn,addr SearchName
invoke lstrcat,addr Conn,addr AndStatement ;" and verse = '"
invoke lstrcat,addr Conn,addr SearchVerse
invoke lstrcat,addr Conn,addr EndStatement ;"'"
.else
invoke lstrcat,addr Conn,addr WhereStatement1 ;" where books = '"
invoke lstrcat,addr Conn,addr SearchName
invoke lstrcat,addr Conn,addr EndStatement ;"'"
.endif
.else
invoke GetWindowText,hEdit02,ADDR SearchVerse,25
.if eax!=0
invoke lstrcat,addr Conn,addr WhereStatement2 ;" where verse = '"
invoke lstrcat,addr Conn,addr SearchVerse
invoke lstrcat,addr Conn,addr EndStatement ;"'"
.endif
.endif
invoke lstrlen,addr Conn
.IF ax==0
invoke MessageBox,hWnd,addr NoData,addr AppName,MB_OK+MB_ICONERROR
invoke SetFocus,eax
.ELSE
invoke SetWindowText,hEdit01,NULL
invoke lstrlen,addr Conn
invoke SQLExecDirect,hStmt,addr Conn,eax
invoke SQLBindCol, hStmt,1,SQL_C_CHAR, addr TheBook, sizeof TheBook,addr TextLength
invoke SQLBindCol, hStmt,2,SQL_C_CHAR, addr TheVerse, sizeof TheVerse,addr TextLength
invoke SQLBindCol, hStmt,3,SQL_C_CHAR, addr TheText, sizeof TheText,addr TextLength
invoke SQLBindCol, hStmt,4,SQL_C_SLONG, addr TheID, 0,addr TextLength
invoke SQLFetch, hStmt
.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke SetWindowText,hEdit01, addr TheText
.else
invoke SetWindowText,hEdit01, addr TheError
.endif
.endif


Scrolling:
.ELSEIF ax==Button03ID ;Previous	

invoke SQLFetchScroll,hStmt,SQL_FETCH_PRIOR,0
.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke SetWindowText,hEdit01, addr TheText
invoke SetWindowText,hEdit02, addr TheVerse
.else
invoke SetWindowText,hEdit01, addr TheError
.endif
.ELSEIF ax==Button04ID ;Next
invoke SQLFetchScroll,hStmt,SQL_FETCH_NEXT,0
.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke SetWindowText,hEdit01, addr TheText
invoke SetWindowText,hEdit02, addr TheVerse
.else
invoke SetWindowText,hEdit01, addr TheError
.endif


Regards
Posted on 2003-11-26 04:54:33 by minor28
hi minor28,

Thank you!you give me an idea but i encounter a problem in READ button after I
click the button the query is ok but if i change the value in Edit02 which is the verse the code always jump to TheError message.



regards,
coredump
Posted on 2003-11-26 21:23:04 by coredump
Add this.
.IF ax==Button01ID ;Read

invoke SQLFreeStmt,hStmt,SQL_CLOSE

Regards
Posted on 2003-11-27 01:29:00 by minor28
thank you.the Next and Previous button also have a error message
Record not found






regards,
coredump
Posted on 2003-11-27 03:01:40 by coredump
The "SQLFreeStmt" closes the statement and the cursor before a new statement is executed which result in a recordset depending on where parameters. For example "Select * from AngBiblia where verse = '1:18'" give you a recordset of one row. If you push the next button (or the previous button) there are no more rows to goto. Therefore the error message "Record not found". It is actually not an error it only tells you either the end or start of the recordset. I you don't want the error message you have to change your code to detect the start and the end of the recordset.
Posted on 2003-11-27 04:17:13 by minor28
hi minor28,

Thank you.. i will try to find the problem...








regards,
coredump
Posted on 2003-11-28 02:57:42 by coredump
coredump, what's your problem?
.ELSEIF ax==Button03ID ;Previous	

invoke SQLFetchScroll,hStmt,SQL_FETCH_PRIOR,0
.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke SetWindowText,hEdit01, addr TheText
invoke SetWindowText,hEdit02, addr TheVerse
.elseif ax==SQL_NO_DATA
invoke SetWindowText,hEdit01, addr BOF ;.data BOF db "Beginning of file",0
.else ;e.i. SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE
invoke SetWindowText,hEdit01, addr TheError
.endif
.ELSEIF ax==Button04ID ;Next
invoke SQLFetchScroll,hStmt,SQL_FETCH_NEXT,0
.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke SetWindowText,hEdit01, addr TheText
invoke SetWindowText,hEdit02, addr TheVerse
.elseif ax==SQL_NO_DATA
invoke SetWindowText,hEdit01, addr EOF ;.data EOF db "End of file",0
.else ;e.i. SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE
invoke SetWindowText,hEdit01, addr TheError
.endif
Posted on 2003-11-28 15:08:20 by minor28
hi minor28,

Thanks.

[size=9].ELSEIF ax==Button04ID	;Next

invoke SQLFetchScroll,hStmt,SQL_FETCH_NEXT,0
.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke SetWindowText,hEdit01, addr TheText
invoke SetWindowText,hEdit02, addr TheVerse
.elseif ax==SQL_NO_DATA
invoke SetWindowText,hEdit01, addr EOF ;.data EOF db "End of file",0
.else ;e.i. SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE
invoke SetWindowText,hEdit01, addr TheError
.endif[/SIZE]


if the Statement or the query is SQL_SUCCESS or SQL_SUCCESS_WITH_INFO
it will display the query result to hEdit01 and hEdit02 and if the query is
SQL_NO_DATA it will display the EOF of BOF but if the query has a problem or error
it will display in hEdit01 the value of TheError. My problem is there is a problem
in query because it always jump to the TheError. so it means to say that the ax is not equal to SQL_SUCCESS,SQL_SUCCESS_WITH_INFO AND SQL_NO_DATA.


Thanks in advanced



regards,
coredump
Posted on 2003-11-28 18:59:10 by coredump
Is this what is happen?

1) your connection is SQL_SUCCESS
2) your Read is SQL_SUCCESS and the first row of the recordset is shown in editbox.
3) pushing next or previous button is SQL_ERROR.

If this is the situation the only thing I can think of is that you close the cursor in the read process. The cursor must be open as long as you are working with the recordset.

If your connection is OK but the Read is not OK then your SQL statement is wrong.
Posted on 2003-11-29 09:58:38 by minor28
hi minor28,

Good day!

I change the SQL statement to "select * from table" in order to have all
records in the database.The program will display the first record in the database.
The Next button is now running but after pressing the Next button the previous
button cannot go back to the previous records.always jump to TheError message.



[size=9]
.ELSEIF ax==Button03ID ;Previous
invoke SQLFetchScroll,hStmt,SQL_FETCH_PRIOR,0
.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke SetWindowText,hEdit01, addr TheText
invoke SetWindowText,hEdit02, addr TheVerse
.elseif ax==SQL_NO_DATA
invoke SetWindowText,hEdit01, addr BOF
.else ;e.i. SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE
invoke SetWindowText,hEdit01, addr TheError
.endif
.ELSEIF ax==Button04ID ;Next
invoke SQLFetchScroll,hStmt,SQL_FETCH_NEXT,0
.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
invoke SetWindowText,hEdit01, addr TheText
invoke SetWindowText,hEdit02, addr TheVerse
.elseif ax==SQL_NO_DATA
invoke SetWindowText,hEdit01, addr EOF
.else ;e.i. SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE
invoke SetWindowText,hEdit01, addr TheError
.endif
[/size]


btw, Thank you so much..

regards,
coredump
Posted on 2003-11-30 20:27:02 by coredump
Hello Wayne minor28,

you got it!!! Thank you,,... i review all post reply today.. and i read your
reply..thank you...



Regards,
coredump
Posted on 2003-11-30 22:53:29 by coredump
hi all,

i create a FillDataList proc which will call if the search button will click
the statement is "select * from table where text like '%word%' ". all result
will fill in the listview with the columns books and verse.the program can add the books
but the problem is the verse i cannot see the verse in listview only the last result
of the query in verse can show in the listview.

what do you think the problem?





FillDataList proc
LOCAL row:DWORD
LOCAL lvi:LV_ITEM

invoke SQLBindCol, hStmt,1,SQL_C_CHAR, addr TheBook, sizeof TheBook,addr TextLength
invoke SQLBindCol, hStmt,2,SQL_C_CHAR, addr TheVerse, sizeof TheVerse,addr TextLength
invoke SQLBindCol, hStmt,3,SQL_C_CHAR, addr TheText, sizeof TheText,addr TextLength
invoke SQLBindCol, hStmt,4,SQL_C_SLONG, addr TheID, 0,addr TextLength
mov row,0
.while TRUE
mov byte ptr ds:[TheBook],0
mov byte ptr ds:[TheVerse],0
mov byte ptr ds:[TheText],0
mov byte ptr ds:[TheID],0
invoke SQLFetch, hStmt
.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
mov lvi.imask,LVIF_TEXT+LVIF_PARAM
push row
pop lvi.iItem
mov lvi.iSubItem,0
mov lvi.pszText, offset TheBook
push row
pop lvi.lParam
invoke SendMessage,hList, LVM_INSERTITEM,0, addr lvi
mov lvi.imask,LVIF_TEXT
inc lvi.iSubItem
mov lvi.pszText,offset TheVerse
invoke SendMessage,hList,LVM_SETITEM, 0,addr lvi
inc row
.else
.break
.endif
.endw
ret

FillDataList endp


Posted on 2003-12-01 03:47:28 by coredump
This should work
invoke SQLFetchScroll,hStmt,SQL_FETCH_NEXT,0

.if ax==SQL_SUCCESS || ax==SQL_SUCCESS_WITH_INFO
mov lvi.imask,LVIF_TEXT
push row
pop lvi.iItem
mov lvi.iSubItem,0
mov lvi.pszText, offset TheBook
invoke SendMessage,hList, LVM_INSERTITEM,0, addr lvi
inc lvi.iSubItem
mov lvi.pszText,offset TheVerse
invoke SendMessage,hList,LVM_SETITEM, 0,addr lvi
.else


Best regards

Edit: Don't forget the first item
Posted on 2003-12-01 05:36:53 by minor28
hi minor28,

Thank you so much. it works!!!

this forum is great!!!


Thank you to all ASM coders..




regards,
coredump
Posted on 2003-12-01 05:52:31 by coredump