无法在SQLCE中找到重复记录 [英] Cant find duplicate record in SQLCE

问题描述

我的名字是Anuar，我正在使用VB.net在移动扫描仪上开发扫描应用程序。

我在VB.net上很安静。我的程序将扫描PalletNo和RackNo并将其存储在数据库（SQLCE）上。对于每个重复的PalletNo，系统将通过消息警告用户。我已成功保存记录，但我无法查询特定记录进行检查..请参阅我的编码。请帮忙？



我的表是扫描（

Hi,
My name is Anuar, I am developing scanning application on mobile scanner using VB.net.
i''m quiet new with VB.net. My program will scan PalletNo and RackNo and store it on database (SQLCE). For every duplicate PalletNo, system will warn user with message. i have successfully save the record but i cant query specific record for checking.. refer to my coding. please help?

My Table is Scan (

PalletNo(nChar 10), RackNo(nChar 5)

）

)

Private Sub txtPallet_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtPallet.KeyPress

        If e.KeyChar = Chr(13) Then
            Try
                'Check connection to network
                If Not Connected_To_Network() Then
                    MessageBox.Show("You are not connected to the network!", "No Connection")
                    Exit Sub
                End If
                'Create Connection to database Local
                cn = New SqlCeConnection("Data source = \Program files\SCANNER\appDatabase.sdf;persist security info= false")
                cn.Open()

                'check data exist or not
                cmd = New SqlCeCommand("Select * From Scan Where PalletNo ='" & txtPallet.Text & "'", cn)
                Rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)
                Row = Rs.HasRows
                
                'Proceed
                If Row = False Then 'No data
                    'Proceed with saving the data..
                    Else
                        MsgBox("Invalid Pallet No.", MsgBoxStyle.Critical)
                        txtPallet.Text = ""
                        txtPallet.Focus()
                    End If
                End If
                
                cmd2.Dispose()
                DS.Dispose()

            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical)
            End Try
            
        End If
    End Sub 

谢谢

Thank You

推荐答案

好悲伤！

那里有很多糟糕和危险的做法！

1）不要在应用程序中保存你的数据库r - 您经常（并且越来越多）需要管理员权限才能写入此类文件夹。此外，这些文件夹很少被包含在常规备份计划中。将其存放在用于此类数据的地方：我应该在哪里存储数据？ [ ^ ]

2）不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。

3）返回不需要的数据库条目是不好的做法 - 浪费内存和带宽，并且可能会浪费时间，具体取决于数据内容。始终指定您的字段！



要添加到此，为什么要返回任何行？所有你想知道的是这条记录存在吗？那么为什么不返回现有行的数量呢？

Good grief!
A good selection of poor and dangerous practices there!
1) Don''t save your database in the application folder - you will often (and increasingly) need admin permissions to write to such folders. in addition, such folders are a lot less likely yo be included on a regular backup schedule. Store it in a place intended for such data: Where should I store my data?[^]
2) Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
3) It is bad practice to return database entries you don''t need - it wastes memory and bandwidth, and can waste time depending on the data content. Specify your fields at all times!

And to add to this, why are you returning any rows at all? All you want to know is "does this record exist?" so why not return the count of existing rows instead?

cmd = new SqlCeCommand("SELECT COUNT(PalletNo) FROM Scan WHERE PalletNo = @PN", cn)
cmd.Parameters.AddWithValue("@PN", txtPallet.Text)
If (cmd.ExecuteScalar = 0) Then
   ' Save your data
Else
   ' Report a problem
End If

那么，您的查询应该有效，如果用户输入的 PalletNo 与ta中存储的完全相同ble（注意它们是字符串）。

你的问题究竟是什么？

您是否直接在SQLCE中尝试查询（例如使用服务器压缩工具箱 [ ^ ]）？

Well, your query should work, provided the PalletNo the user enters is exactly the same stored in the table (note they are strings).
What''s exactly your problem?
Did you try your query directly in a SQLCE (using for instance QL Server Compact Toolbox[^])?

这篇关于无法在SQLCE中找到重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！