[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Parameterized Query which was not supplied.

Posted on 2008-11-10
4
Medium Priority
?
2,488 Views
Last Modified: 2012-05-05
Hi Experts,
I have a gridview that is bound to an objectdatasource. The gridview has 3 columns: ID, Title and SubTitle.

When I edit a row, I will get the following error if I leave the SubTitle textbox blank.
Parameterized Query '(@Title nvarchar(9),@SubTitle nvarchar(4000),@ID nvarchar(3))UPD' expects parameter @SubTitle, which was not supplied.

I will get a similar error if I leave the Title textbox blank.

How do I fix it?
Thanks


Imports System.Data.SqlClient
Imports System.Configuration
 
Public Class Books
    Public Shared Sub UpdateBooks(ByVal Title As String, ByVal SubTitle As String, ByVal ID As String)
        Dim connection As SqlConnection = StartConnection()
        Dim sSqlQuery As String = ""
        sSqlQuery = "UPDATE dxBooks SET Title = @Title, SubTitle = @SubTitle WHERE (ID = @ID)"
        Dim Cmd As New SqlCommand(sSqlQuery, connection)
        Cmd.Parameters.AddWithValue("@Title", Title)
        Cmd.Parameters.AddWithValue("@SubTitle", SubTitle)
        Cmd.Parameters.AddWithValue("@ID", ID)
        Cmd.ExecuteNonQuery()
        connection.Close()
    End Sub
End Class

Open in new window

0
Comment
Question by:noobe1
  • 2
4 Comments
 
LVL 22

Expert Comment

by:prairiedog
ID: 22925150
How do you call UpdateBooks in your code?
0
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 22926654
You don't include the @ with the parameter names in the methods for System.Data.SqlClient.SqlParameters objects.
Imports System.Data.SqlClient
Imports System.Configuration
 
Public Class Books
    Public Shared Sub UpdateBooks(ByVal Title As String, ByVal SubTitle As String, ByVal ID As String)
        Dim connection As SqlConnection = StartConnection()
        Dim sSqlQuery As String = ""
        sSqlQuery = "UPDATE dxBooks SET Title = @Title, SubTitle = @SubTitle WHERE (ID = @ID)"
        Dim Cmd As New SqlCommand(sSqlQuery, connection)
        Cmd.Parameters.AddWithValue("Title", Title)
        Cmd.Parameters.AddWithValue("SubTitle", SubTitle)
        Cmd.Parameters.AddWithValue("ID", ID)
        Cmd.ExecuteNonQuery()
        connection.Close()
    End Sub
End Class

Open in new window

0
 

Author Comment

by:noobe1
ID: 22927163
prairiedog,
I specified UpdateBooks in the objectdatasource's UpdateMethod.

GreymanMSC,
I'm still getting the same errors.

I think the problem lies in the values that is passed to UpdateBooks. If I enter "ABC" for Title and "EFG" for SubTitle in the gridview, the code runs fine. If I enter "ABC" for Title and "" for SubTitle, I get the error.

Maybe the gridview does not pass any value for SubTitle when it is an empty string. Just a guess.

Thanks
0
 

Accepted Solution

by:
noobe1 earned 0 total points
ID: 22927277
I solved the problem. All that is needed is to test if the value passes is an empty string. If yes, set the parameter's value to DBNull.Value

        If (SubTitle = String.Empty) Then
            Cmd.Parameters.AddWithValue("@SubTitle", DBNull.Value)
        Else
            Cmd.Parameters.AddWithValue("@SubTitle", SubTitle)
        End If


Imports System.Data.SqlClient
Imports System.Configuration
 
Public Class Books
    Public Shared Sub UpdateBooks(ByVal Title As String, ByVal SubTitle As String, ByVal ID As String)
        Dim connection As SqlConnection = StartConnection()
        Dim sSqlQuery As String = ""
        sSqlQuery = "UPDATE dxBooks SET Title = @Title, SubTitle = @SubTitle WHERE (ID = @ID)"
        Dim Cmd As New SqlCommand(sSqlQuery, connection)
        Cmd.Parameters.AddWithValue("@Title", Title)
                If (SubTitle = String.Empty) Then
            Cmd.Parameters.AddWithValue("@SubTitle", DBNull.Value)
        Else
            Cmd.Parameters.AddWithValue("@SubTitle", SubTitle)
        End If
        Cmd.Parameters.AddWithValue("@ID", ID)
        Cmd.ExecuteNonQuery()
        connection.Close()
    End Sub
End Class

Open in new window

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month20 days, 3 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question