[RESOLVED] Unusual ActiveSynch issue..

[Pasvorto]

I have everything working. However, there is one anomoly left. Each time a scanner is unloaded (and the data loaded into my database), I have tio close the application and restart it before it will recognize the file on the next scanner.
==> This is the routine that calls the activesynch bit and loads the extracted file into the db.

Public Sub LoadScanners2()
On Error GoTo errorprocess

Dim reply1 As VbMsgBoxResult
reply1 = MsgBox("Download from scanner?", vbYesNo + vbQuestion)
If reply1 = vbYes Then
CopyFileFromPocketPC
End If

Dim KOUNTER As Long
KOUNTER = 0
Dim USERID As String
Dim dte As String
Dim TME As String
Dim TTYPE As String
Dim LOCATION As String
Dim lotjob As String
Dim quantity As String
Dim SCANNER As String
Dim myfile As String
Dim REPLY As VbMsgBoxResult
'
'===========================
Set rs = New ADODB.Recordset
'===========================
rs.Open "TMPScanLoad", CnxnTempSQL, adOpenKeyset, adLockOptimistic, adCmdTable
If Not rs.BOF And Not rs.EOF Then
REPLY = MsgBox("There are unposted scanner transactions. Do you want to delete them?", vbYesNo + vbQuestion)
If REPLY = vbYes Then
'===============================================
CnxnTempSQL.Execute ("DELETE FROM TMPSCANLOAD")
'===============================================
End If
End If
rs.Close
Set rs = Nothing
'===============
'
SCANNER = "Z:\SCANNER\SCANNERFILE"
myfile = Dir(SCANNER)
If myfile <> "" Then
Open SCANNER For Input As #1
Else
MsgBox "No input file found", vbInformation
Exit Sub
End If
Dim KOUNT As Integer
KOUNT = 0
Do While Not EOF(1)
Input #1, USERID, dte, TME, TTYPE, LOCATION, lotjob, quantity
'===========================
If Not IsNumeric(quantity) Then GoTo errorprocess
If Not IsNumeric(lotjob) Then GoTo errorprocess
If Trim(USERID) = "" Then GoTo errorprocess
If Trim(dte) = "" Then GoTo errorprocess
If Trim(TME) = "" Then GoTo errorprocess
KOUNT = KOUNT + 1
'=====================================
SQLINSERT = "INSERT INTO TMPScanLoad ([ID],[USERID],[DATE],[TIME],TRANSTYPE],[LOCATION],[LOTJOB],[QUANTITY]) VALUES("
SQLINSERT = SQLINSERT & KOUNT & ", "
SQLINSERT = SQLINSERT & "'" & USERID & "', "
SQLINSERT = SQLINSERT & "'" & Trim(dte) & "', "
SQLINSERT = SQLINSERT & "'" & Trim(TME) & "', "
SQLINSERT = SQLINSERT & "'" & TTYPE & "', "
SQLINSERT = SQLINSERT & "'" & LOCATION & "', "
SQLINSERT = SQLINSERT & CLng(lotjob) & ", "
SQLINSERT = SQLINSERT & CLng(quantity) & ")"
CnxnTempSQL.Execute SQLINSERT
'===========================
KOUNTER = KOUNTER + 1
Loop '----READ INPUT FILE
Close #1

myfile = Dir(SCANNER)
If myfile <> "" Then
Name SCANNER As SCANNER & "_" & Format(Date, "mmddyyyy") & "_" & Format(Now, "hhmm")
End If
'
MsgBox KOUNTER & " Records Loaded"
'
Exit Sub
'
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
errorprocess:
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'
'if an error has ocurred, clear the file to avoid double posting
'==============================================
CnxnTempSQL.Execute ("DELETE FROM TMPSCANLOAD")
'===============================================
Screen.MousePointer = 0
MsgBox "Scanner File has an error. Please fix it and try to reload data."
End Sub

==> This is the reoutine that extracts the file from the scanner

Public Function CopyFileFromPocketPC() As Boolean
On Error GoTo ErrHandler

Dim intRetVal As Integer
intRetVal = CeRapiInit()

If intRetVal <> INIT_SUCCESS Then
MsgBox "Failed to initialise RAPI with device with error " & _
CeGetLastError
CeRapiUninit
Exit Function
End If

Dim bytBuffer(16384) As Byte

Dim lngFileHandle As Long
Dim lngBytesRead As Long
Dim typFindFileData As CE_FIND_DATA

Dim intFreeFileID As Integer

Dim intWriteLoop As Integer

Dim FileName As String
FileName = "My Documents/ScannerFile"

'locate the file, and see if it already exists on the device
lngFileHandle = CeFindFirstFile(FileName, typFindFileData)

If lngFileHandle = INVALID_HANDLE Then
MsgBox "File - " & FileName & " - Not Found. Operation Aborted.", vbOKOnly
CopyFileFromPocketPC = False
Exit Function
End If

'we dont need this handle now that we know the file is there
CeFindClose lngFileHandle

'i know it seems odd to have to call a function called CreateFile to read a file
'but what it really refers to is create me a handle to a file of this type.
lngFileHandle = CeCreateFile(FileName, GENERIC_READ, FILE_SHARE_READ, vbNullString, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, 0)

==>> This is where it fails on the 2nd scanner if I don't close the app and restart it...I get the 'Failed to open file" error. If I restart everything runs fine.

If lngFileHandle = INVALID_HANDLE Then
MsgBox "Failed to open file " & FileName
CopyFileFromPocketPC = False
Exit Function
End If

intRetVal = CeReadFile(lngFileHandle, bytBuffer(0), 16384, lngBytesRead, 0)

'if we got a 0 return value from readfile then there is an error
'so pass it to the error handler
If intRetVal = READ_ERROR Then
GoTo ErrHandler
End If
'
intFreeFileID = FreeFile
'
Open "Z:\SCANNER\SCANNERFILE" For Binary As intFreeFileID
For intWriteLoop = 0 To lngBytesRead
Put #intFreeFileID, intWriteLoop + 1, bytBuffer(intWriteLoop)
Next intWriteLoop
MsgBox "FILE COPIED"

Close intFreeFileID

CopyFileFromPocketPC = True

Exit Function
'
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
ErrHandler:
'
MsgBox "Error " & CeGetLastError & " Occurred When Copying File " & _
FileName & " From The Device as " & FileName, vbCritical & vbOKOnly
CopyFileFromPocketPC = False

End Function

[petevick]

Hi,
when you have finished, you may need to call CeRapiUninit

Pete

[Pasvorto]

I will give that a test. It should go here, right?

Open "Z:\SCANNER\SCANNERFILE" For Binary As intFreeFileID
For intWriteLoop = 0 To lngBytesRead
Put #intFreeFileID, intWriteLoop + 1, bytBuffer(intWriteLoop)
Next intWriteLoop
MsgBox "FILE COPIED"
'
Close intFreeFileID
CeRapiUninit


CopyFileFromPocketPC = True

Exit Function

[petevick]

Hi,
yes - I think it is CeRapiInitEX, copy file, CeRapiUninit

That should get it ready for the next CeRapiInitEX

[Pasvorto]

Cool. I will test it tomorrow and let you know how it went. Thanks for all the help.

[Pasvorto]

That seemed to do the trick. I thank you once again :-)

[petevick]

Hi,
great - glad you got it working.

RAPI is a great tool for things like that

Thanks

Pete

[Pasvorto]

It's been a learning experience. My next project, expanding on this one, is to upload a file from the db on the server to some file structure on the scanner. The skid pullers could enter a lot # and find all locations of that lot in the warehouse. That would keep them from having to find a PC every time they need to get locations. I don't know if I should just upload a flat file, or try to figure out how to use the db on the scanner (Windows CE). I must give it some thought.

[petevick]

Hi,
is the data fairly static?
do the scanners have WIFI?

If the data is fairly static, then have a look at replication - you could replicate the locations from the server to a database on the device on a timely basis, and use SqlServer Compact Edition to look them up. SqlCE is very similar to SqlServer, although you have no stored procs, triggers or views.

With WIFI you could use SqlClient and look them up on the server directly.



Let me know if you need more info

Pete

[Pasvorto]

I went for a routine on the PC that extracts the data from the SQL db and creates a flat file that it uploads to the scanner. The program in the scanner reads the flat file.

[Pasvorto]

I went for a routine on the PC that extracts the data from the SQL db and creates a flat file that it uploads to the scanner. The program in the scanner reads the flat file.

[dminder]

If you want it to be more dynamic, try to use a web service. I have developed a WMS system that uses a web service and it works very well. They can update, search, transfer, etc.

Good Luck!

D

[Pasvorto]

Thanks for the response. The app is running and stable. The "necessary" functionality is in place. Now for the 'wow' stuff.