Expection in Select Query

Question

0.00/5 (No votes)

See more:

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

Here is My Code

VB

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

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

VB

'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...

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

Posted 23-Nov-11 22:55pm

Ashok19r91d

Updated 23-Nov-11 22:59pm

v2

Add a Solution

3 solutions

Solution 2

VB

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

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

VB

Rs = Cmd.ExecuteReader()

Do the Brackets.....

Posted 24-Nov-11 1:06am

NikulDarji

Updated 24-Nov-11 3:17am

v2

Comments

Ashok19r91d 24-Nov-11 9:57am

It too not Work...
Same exception Occurs now...

The Default Value of Third Parameter (Where Condition) is "" so It too failed Sorry...

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)

Not Active · Accepted Answer · 2011-11-24T03:55:00

Solution 3

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.

Posted 24-Nov-11 3:55am

Not Active

Comments

Ashok19r91d 24-Nov-11 10:03am

But for Two Tire Programming it's too helpful to me(Same Application can be used with both MS Access and SQL Server by Changing Connection String)

In Addition to that I Don't know How to Create Stored Procedure in SQL Server., Where I Get Reference about that?
Please Help me...

[no name] 24-Nov-11 10:52am

"Where I Get Reference about that?" Seriously? There is this thing, maybe you've heard of called the Internet.

"Two Tire Programming" - Translation: Too Lazy to do it correctly.

Maciej Los 24-Nov-11 14:46pm

I agree with you.

*Karthik Harve* · Accepted Answer · 2011-11-23T23:07:00

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

SQL

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

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