记录计数SQL Compact [英] Recordcount SQL Compact

问题描述

嗨
我在这里有一个奇怪的问题.我使用SQL Compact Server，并有一个名为"StaffPass"的列，键入nvarchar lenght 16.
这是我的功能:

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

这可以正常工作，因为密码是"1111"，但是如果密码是"K1234"
那就失败了

任何想法

Thx

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

Any Idea''s

Thx

推荐答案

首先，不要使用字符串连接来构建这样的查询.请改用参数化查询.只是Google提供了有关"vb.net参数化查询"的大量文章和示例.

接下来，使用此查询字符串构建方式，您的SQL查询在执行时将如下所示:

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.

如果密码是字符串，则需要将密码捕获到('')中.

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

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

如上所述，请始终使用参数.

另一件事是代码有点混乱. ExecuteScalar不返回记录计数.它返回结果集中第一行的第一列(在这种情况下为StaffID).

根据代码，您似乎对StaffID和StaffPass的实际值不感兴趣，因此，如果为true，则可以将查询修改为:

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:

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

有关参数的更多信息，请参见: SqlCeParameter [ ^ ]

但是，如果要返回StaffID和StaffPass的值，请考虑使用 ^ ]

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

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

这篇关于记录计数SQL Compact的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！