如何允许我访问dbf文件并更改其包含的脚本 [英] How do I a script that allow me to access for dbf file and change its contains

问题描述

i我正在尝试编写一个脚本，可以根据其中一个列查找dbf文件中的名称。

例如我有一个列条形码的名称是BAR_CODE，我有另一列是全名，其名称是FULL_NAME，我想创建一个脚本，允许我在输入条形码时查找全名，同时这个脚本将允许我删除PRN_STATE列中此全名的数据。



i已尝试过：

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 :

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

此脚本可以显示仅第一行的数据并从PRN_STATE删除数据，但我无法使用条形码选择我想要显示的全名并删除其PRN_STATE数据。









Shady H.

最好的问候

谢谢

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

推荐答案

对于最初的问题...



你需要添加一个where子句，例如

For the initial question ...

You need to add a where clause e.g.

strQuery = "SELECT * FROM test.dbf WHERE BAR_CODE = '" + strAnswer + "'"

注意这将对SQL注入开放 - 你最好使用oledb参数（但我不知道如何在vbscript中执行此操作）或使用存储过程来返回数据。 />


对于后续问题

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

引用：

我输入条形码后我有按OK按钮或ENTER，有没有办法让它连续？我的意思是在输入条形码后，PRN_STATE的数据将被删除，并且盒子消息将继续等待新的条形码。

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.

您可以使用WHILE循环，例如

You can use a WHILE loop e.g.

While strAnswer <> "Q"
    strAnswer = InputBox("Please enter the barcode, or Q to quit:", "Search")
    '... your existing code in here

End While

[OP评论后编辑]

小心你把循环开始和结束的地方例如

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

这篇关于如何允许我访问dbf文件并更改其包含的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！