Cant find duplicate record in SQLCE

Question

0.00/5 (No votes)

See more:

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)

)

VB

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

Posted 3-Apr-13 20:46pm

Syahrul Anuar

Updated 3-Apr-13 21:34pm

Aarti Meswania

v2

Add a Solution

2 solutions

Solution 1

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[^])?

Posted 3-Apr-13 21:41pm

CPallini

Comments

Syahrul Anuar 4-Apr-13 12:36pm

Im not sure.. thats puzzle me. if i store all numbers like PalletNo = 123456 and query it SELECT * FROM SCAN WHERE PalletNo=" & txtPalletNo.text it did return a row. but if palletno= S123456 it coz runtime error if i put 'S223456' it didnt return a row. thanks.

Syahrul Anuar 6-Apr-13 23:21pm

I have solved it, my table have 2 column only, Which is PalletNo and Rackno. I add another column AddUser suddenly my query worked. anyway Thanks..

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2013-04-03T21:51:00

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?

VB

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