Recordcount SQL Compact

Question

0.00/5 (No votes)

See more:

Hi
I have a strange promble here. I use SQL Compact Server and have a column called "StaffPass", type nvarchar lenght 16.

this is my function:

Friend Shared Function VerifyStaff(ByVal id As Integer, ByVal pass As String) As Boolean

      VerifyStaff = False

      Try
         Using ceconn As New SqlCeConnection(My.Settings.KMTDataConn)

            If ceconn.State = ConnectionState.Closed Then ceconn.Open()
            Dim trans = ceconn.BeginTransaction
            Using _
               cmd As _
                  New SqlCeCommand() _
                     With {.CommandType = CommandType.Text,
                        .CommandText =
                           String.Format(
                              "SELECT StaffID, StaffPass FROM StaffInfo  WHERE  StaffID = {0} AND StaffPass = {1}", id, pass),
                        .Connection = ceconn, .Transaction = trans}
               Dim recordCount As Long = cmd.ExecuteScalar
               If recordCount > 0 Then
                  VerifyStaff = True
                  Return VerifyStaff
               Else
                  VerifyStaff = False
                  Return VerifyStaff
               End If
            End Using
         End Using
      Catch ex As Exception
         Return VerifyStaff
      End Try

   End Function

This works fint is the password is "1111", however if the password is "K1234"
then it fails

Any Idea's

Thx

Posted 9-Jan-12 7:30am

fsudsgaard

Add a Solution

Comments

fsudsgaard 9-Jan-12 18:55pm

Thank so much guys, the moment I saw it, I knew...guess I been looking at it too long

3 solutions

Solution 2

If the password is the string, you need to catch your password into (').

VB

"SELECT StaffID, StaffPass FROM StaffInfo  WHERE  StaffID = {0} AND StaffPass = '{1}'", id, pass)"

Posted 9-Jan-12 7:53am

Maciej Los

Comments

fsudsgaard 9-Jan-12 18:57pm

Thanks for the help, it is working now

Solution 3

As already mentioned, always use parameters.

Another thing is that the code is a bit confusing. ExecuteScalar does not return the record count. It returns the first column of the first row in the result set (StaffID in this case).

Based on the code you don't seem to be interested in the actual values of StaffID and StaffPass so if that's true you could modify your query to:

SQL

SELECT COUNT(*) FROM StaffInfo WHERE StaffID = @staffid AND StaffPass = @staffpass

For more info about parameters, see: SqlCeParameter[^]

But if you want to return the values of StaffID and StaffPass, consider using SqlCeDataReader[^]

Posted 9-Jan-12 8:41am

Wendelius

Comments

fsudsgaard 9-Jan-12 18:57pm

Thanks for you help,accually already changed it..

Wendelius 10-Jan-12 0:02am

You're welcome :)

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)

Dave Kreskowiak · Accepted Answer · 2012-01-09T07:51:00

First, don't use string concatentation to build queries like this. Use parameterized queries instead. Just Google for "vb.net parameterized queries" for a ton of articles and examples.

Next, the way tou have this query string build, your SQL query will look like this when it's executed:

SELECT StaffID, StaffPass FROM StaffInfo WHERE StaffID = 0 AND StaffPass = K1234

NOtice anything wrong with that? If you want to match a string field, you have to wrap the string in quotes:

SELECT StaffID, StaffPass FROM StaffInfo WHERE StaffID = 0 AND StaffPass = 'K1234'

So, your query line would look like:

.CommandText =
    String.Format(
        "SELECT StaffID, StaffPass FROM StaffInfo  WHERE  StaffID = {0} AND StaffPass = '{1}'", id, pass),

But again, ditch this and use parameterized queries instead.

Recordcount SQL Compact

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us