Click here to Skip to main content
15,869,972 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
i am trying to write a script that can look for names in a dbf file basing on one of the columns.
for example i have a column for bar codes its name is BAR_CODE and i have another column which is for the full name its name is FULL_NAME, i want to make a script that allow me to look for the full name when i enter the bar code and at the same time this script will allow me to delete the data that are in the column PRN_STATE for the this full name.

i already tried something :
VB
Dim cn
Dim strConn
Dim rs
Dim strQuery
Dim f, strFields
Set cn = CreateObject("ADODB.Connection")


strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Users\Shadi\Desktop;" & _
    "Extended Properties=dBase IV;"
cn.Open strConn
Set rs = CreateObject("ADODB.Recordset")

strAnswer = InputBox("Please enter the barcode:", _
   "Search")

strQuery = "SELECT * FROM test.dbf"
rs.CursorLocation = 3
rs.Open strQuery, cn, 3, 3

For Each f In rs.Fields
    strFields = strFields & f.Name & vbCrLf
Next
'MsgBox strFields
With rs
    MsgBox "Full Name: " &  .Fields("FULL_NAME").Value
    MsgBox "Card_ID: " &  .Fields("CARD_ID").Value
    MsgBox "PRN_STATE: " &  .Fields("PRN_STATE").Value
   
    .Fields("PRN_STATE").Value = " "
    .Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
this script can display the data for the first row only and delete the data from the PRN_STATE of it but i can not use the bar code to choose which full name i want to display and delete its PRN_STATE data.




Shady H.
Best Regards
Thank You
Posted
Updated 19-Jun-15 2:40am
v2
Comments
CHill60 19-Jun-15 8:48am    
You have no where clause e.g. strQuery = "SELECT * FROM test.dbf WHERE BAR_CODE = '" + strAnswer + "'" - although this will be open to SQL Injection - you're better off using oledb parameters (but I don't know how to do this in vbscript) or having a stored procedure to return the data
Shady H. 19-Jun-15 8:53am    
Thank you That,now it is working
Shady H. 19-Jun-15 8:59am    
I still need one more help,
after i enter the bar code i have to press OK button or ENTER,is there any way to make it continuous? i mean after entering the bar code the data of the PRN_STATE will be deleted and the box message will remain waiting for the new bar code.
thank you
Shady H. 19-Jun-15 9:36am    
Thanks,but that worked only for 2 bar codes:
when i entered the first value it worked and the message box remained but when i entered the second bar code it gave me an error saying:

Line: 20
Char: 1
Error: Object required
Code: 800A01A8
CHill60 19-Jun-15 9:51am    
I've updated my solution to help you over this. I suspect you have left the creation of the connection outside the loop but setting it to nothing within the loop

1 solution

For the initial question ...

You need to add a where clause e.g.
VB
strQuery = "SELECT * FROM test.dbf WHERE BAR_CODE = '" + strAnswer + "'"


Note this will be open to SQL Injection - you're better off using oledb parameters (but I don't know how to do this in vbscript) or having a stored procedure to return the data.

For the follow-up question
Quote:
after i enter the bar code i have to press OK button or ENTER,is there any way to make it continuous? i mean after entering the bar code the data of the PRN_STATE will be deleted and the box message will remain waiting for the new bar code.
You can use a WHILE loop e.g.
VB
While strAnswer <> "Q"
    strAnswer = InputBox("Please enter the barcode, or Q to quit:", "Search")
    '... your existing code in here

End While


[EDIT after OP comment]
Be careful where you put the loop beginning and end e.g.
Dim cn
Dim strConn
Dim rs
Dim strQuery
Dim f, strFields
Dim strAnswer

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Users\Shadi\Desktop;" & _
    "Extended Properties=dBase IV;"

While strAnswer <> "Q"
    strAnswer = InputBox("Please enter the barcode, or Q to quit:", "Search")

    cn = CreateObject("ADODB.Connection")
    cn.Open(strConn)
    rs = CreateObject("ADODB.Recordset")

    'Remember to fix this to remove the SQL Injection vulnerability
    strQuery = "SELECT * FROM test.dbf where BAR_CODE='" & strAnswer & "'"
    rs.CursorLocation = 3
    rs.Open(strQuery, cn, 3, 3)

    With rs
        MsgBox("Full Name: " & .Fields("FULL_NAME").Value)
        MsgBox("Card_ID: " & .Fields("CARD_ID").Value)
        MsgBox("PRN_STATE: " & .Fields("PRN_STATE").Value)

        .Fields("PRN_STATE").Value = " "
        .Update()
    End With
    rs.Close()
    cn.Close()

End While

rs = Nothing
cn = Nothing
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900