选择查询中的期望 [英] Expection in Select Query

问题描述

Hello程序员，
在Vb.Net 2010中将Select Query与Where语句一起使用时出现问题.

这是我的代码

Hello Programmers,
I''m Having Trouble in Using Select Query With Where Statement in Vb.Net 2010.

Here is My Code

Imports System.Data.OleDb

Module MyMod1
    Public Cn As New OleDbConnection
    Public Cmd As New OleDbCommand
    Public Rs As OleDbDataReader

    Function GetNextCode(ByVal TabelName As String, ByVal ColumnName As String, Optional ByVal WhereCondition As String = "", Optional ByVal UseDeletedCode As Boolean =False ) As Integer
        Try
            If UseDeletedCode = True Then
                If WhereCondition = "" Then
                    Qry = "SELECT " & ColumnName & " FROM " & TabelName & " WHERE (isDeleted = True) ORDER BY " & ColumnName 
                Else
                    Qry = "Select " & ColumnName & " From " & TabelName & " Where (isDeleted = True) And " & WhereCondition & " Order By " & ColumnName
                End If
                Cmd = Cn.CreateCommand
                Cmd.CommandText = Qry
                Rs = Cmd.ExecuteReader ' Error Rises When Executing This Statement
                If Rs.Read Then
                    GetNextCode = Rs.GetValue(0).ToString
                    Rs.Close()
                    Exit Function
                End If
                Rs.Close()
            End If
        Catch ex As Exception
            GetNextCode = 0
        End Try
    End Function
End Module

带有消息的期望值
没有为一个或多个必需参数给出值.

深入了解我的代码和数据结构
isDeleted是一个布尔列，可在我的数据库的所有表中使用
我将动态提供ColumnName，TableName值
样品通话

Expection Thrown with Message
No value given for one or more required parameters.

In depth of My Code and Data Structure
isDeleted is a Boolean Column Available in All Tables of My Database
I''ll Supply ColumnName, TableName Values Dynamically
Sample Call

'Statement 1
Text1.Text = GetNextCode("LoanID","LoanMain",,True) ' Statement Reports Error in Above Function.

'Statement 2
Text1.Text = GetNextCode("LoanID","LoanMain") ' Statement Executes Successfully Without Errors.

' I Need to Use First Statement only...

现在，请AnyBody帮助我知道为什么会发生这种期望以及如何克服

Now Please AnyBody Help me to Know why this Expection occurs and how to Overcome

推荐答案

您不应该使用字符串串联来构成sql查询.关于SQL注入攻击的文章太多了，我很难相信人们为什么仍然使用它.使用参数化查询或存储过程.

You should not be using string concatenation to form a sql query. There has been so much written about SQL injection attacks I find it difficult to believe why people still use this. Use a parameterized query or stored procedure.

SQL无法将字符串值"TRUE"转换为布尔值，因此将1表示为true，将0表示为false.在这样的代码更改中，

SQL cannot cast string value "TRUE" to boolean, So use 1 for true and 0 for false. In your code change like this,,

If WhereCondition = "" Then
                   Qry = "SELECT " & ColumnName & " FROM " & TabelName & " WHERE (isDeleted = 1) ORDER BY " & ColumnName
               Else
                   Qry = "Select " & ColumnName & " From " & TabelName & " Where (isDeleted = 1) And " & WhereCondition & " Order By " & ColumnName
               End If

如果该列的类型为bit/boolean，则将1和0分别视为true和false.

if the column is of type bit/boolean then 1 and 0 will treated as true and false respectively.

Text1.Text = GetNextCode("LoanID","LoanMain","",True) 

嘿试试这个..... :)

Hey Try this.......:)

Rs = Cmd.ExecuteReader() 

做括号.....

Do the Brackets.....

这篇关于选择查询中的期望的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！