Click here to Skip to main content
15,902,114 members
Please Sign up or sign in to vote.
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
Updated 3-Apr-13 21:34pm
v2

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
 
Share this answer
 
Comments
Syahrul Anuar 4-Apr-13 12:31pm    
Thanks.. thats good advice.. i will learn on the way
fjdiewornncalwe 4-Apr-13 14:10pm    
+5. Great advice.
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[^])?
 
Share this answer
 
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..

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