locked
insert null value with object data source RRS feed

  • Question

  • User1089256414 posted

    Hi,

    I use objectdatasource for ForwView with insert method.   after enter companyname, internet value, but not Comment value, then hit "create vendor" button and get error as shown below (note sometimes comment field value is null becuase some user do not provide), so how to handle null value for insert method if user does not provide comment value?

    Error message: "Procedure or Function 'VendorsInsert' expects parameter '@comment', which was not supplied"

     

    Here is part code:

    -------------------------------------------------------------

     <asp:FormView ID="FormView1" runat="server" DataSourceID="odsAddVendors" DefaultMode="Insert"
                        Width="486px">
                        <InsertItemTemplate>
                            <table>
                              
                                <tr>
                                    <td>
                                        CompanyName:
                                    </td>
                                    <td>
                                        <asp:TextBox ID="txtCompanyName" runat="server" Text='<%# Bind("CompanyName") %>' Width="400px"></asp:TextBox>
                                    </td>
                                </tr>
                                <tr>
                                    <td>
                                        Internet:
                                    </td>
                                    <td>
                                        <asp:TextBox ID="txtInternet" runat="server" Text='<%# Bind("Internet") %>' Width="400px"></asp:TextBox>
                                    </td>
                                    <td>
                                </tr>
                                <tr>
                                    <td>
                                        Comment:
                                    </td>
                                    <td>
                                        <asp:TextBox ID="txtComment" runat="server" Text='<%# Bind("Comment") %>' Width="400px"></asp:TextBox>
                                    </td>
                                </tr>
                          
                              
                               
                            </table>
                            <asp:Button ID="btnNewTag" runat="server" Text="Create Vendor" CommandName="Insert" />
                        </InsertItemTemplate>
    </asp:FormView>

    <asp:ObjectDataSource ID="odsAddVendors" runat="server" InsertMethod="Insert"
                        OldValuesParameterFormatString="{0}"
                        TypeName="MaintainVendors" >
                      
                      
                        <InsertParameters>                       
                            <asp:Parameter Name="CompanyName" Type="string" />
                            <asp:Parameter Name="Internet" Type="String" />
                            <asp:Parameter Name="Comment" Type="String" />
          </InsertParameters>
                      
                    </asp:ObjectDataSource>

    <DataObjectMethod(DataObjectMethodType.Insert, True)> _
        Public Function Insert(ByVal CompanyName As String, ByVal Internet As String, ByVal comment As String) As Integer

            Using conn()

                Dim cmd As SqlCommand = New SqlCommand("dbo.VendorsInsert", conn)
                cmd.CommandType = CommandType.StoredProcedure


                cmd.Parameters.AddWithValue("@CompanyName", CompanyName).DbType = DbType.String
                cmd.Parameters.AddWithValue("@Internet", Internet).DbType = DbType.String
                cmd.Parameters.AddWithValue("@Comment", Comment).DbType = DbType.String

              
                conn.Open()
                Dim rowsAffected As Integer = cmd.ExecuteNonQuery
                Return rowsAffected

            End Using

        End Function

    ------------------------------------------------

    Thanks in advance,

     

    Martin

     

     

     

    Tuesday, December 7, 2010 3:09 PM

Answers

  • User-408628329 posted

    Hi Martin,

    I am sorry, didnt read your function correctly and assumed the class was part of the aspx page. I am assuming that you have this class in a separate file not related to your aspx page, that is why you are not able to see the txtComments textbox.

    Since you are passing in the "comment" as one of the parameters,  use the parameter variable to check for null or empty string.

    if(comment == null || comment =="")
               cmd.Parameters.AddWithValue("@Comment", "").DbType = DbType.String
    else
                cmd.Parameters.AddWithValue("@Comment", comment).DbType = DbType.String
    end if



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 7, 2010 6:31 PM
  • User-408628329 posted

    Just thought of something. I ran into a problem like that with the update using the stored procedure.

    I had a person table with lets say fields: id, fname, lname, ssn, dob. In my case when someone updates personal info, i did not want to lets say the ssn field to change, so i did not include this field in my list of parameters for the stored procedure. And my update was done through GridView and SqlDataSource, which included the stored proc for the update. By default, when the update was execute, all the parameters that showed up in GridView got sent to the stored proc, and since ssn was not part of my stored proc parameter, it throwed a similar error like yours.

    So another thing for you to check is to see if the parameters in your stored proc match all the params in your formview. But as far as I read your code they do.


    Just a thought  :)


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 7, 2010 6:47 PM

All replies

  • User-408628329 posted

    You can check to see if there is anything in the txtComment textbox, if there is not text add an empty string as the parameter or try to use DBNull

    // Sorry using C#, but should be very similar
    if(this.txtComment.Text == null || this.txtComment.Text == "")
       cmd.Parameters.Add("@Comment", "");
    else
       cmd.Parameters.Add("@Comment", Comment);
    Also is the your DB table configured to accept NULL values for the comment field? If its set to NOT ALLOW NULLS, you might want to change that.

    But either way, if you were to return the txtComment.Text on the empty textbox, it should give the empty string, so I dont see the problem with null values.

    Let me know if this works
    Tuesday, December 7, 2010 5:07 PM
  • User1089256414 posted

    I tried to check if txtComment null (see below bold lines), but cannot find txtComment control?  I use below below DataObject

     

    -------------------------------------
    <DataObject(True)> _
    Public Class MaintainVendors

    <DataObjectMethod(DataObjectMethodType.Insert, True)> _
        Public Function Insert(ByVal CompanyName As String, ByVal Internet As String, ByVal comment As String) As Integer

            Using conn()

                Dim cmd As SqlCommand = New SqlCommand("dbo.VendorsInsert", conn)
                cmd.CommandType = CommandType.StoredProcedure


                cmd.Parameters.AddWithValue("@CompanyName", CompanyName).DbType = DbType.String
                cmd.Parameters.AddWithValue("@Internet", Internet).DbType = DbType.String

     

     ???    if txtComment.text.lenth =0 then

               cmd.Parameters.AddWithValue("@Comment", "").DbType = DbType.String

             else
                cmd.Parameters.AddWithValue("@Comment", Comment).DbType = DbType.String

            end if

              
                conn.Open()
                Dim rowsAffected As Integer = cmd.ExecuteNonQuery
                Return rowsAffected

            End Using

        End Function
    end class

    Tuesday, December 7, 2010 6:14 PM
  • User-408628329 posted

    Hi Martin,

    I am sorry, didnt read your function correctly and assumed the class was part of the aspx page. I am assuming that you have this class in a separate file not related to your aspx page, that is why you are not able to see the txtComments textbox.

    Since you are passing in the "comment" as one of the parameters,  use the parameter variable to check for null or empty string.

    if(comment == null || comment =="")
               cmd.Parameters.AddWithValue("@Comment", "").DbType = DbType.String
    else
                cmd.Parameters.AddWithValue("@Comment", comment).DbType = DbType.String
    end if



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 7, 2010 6:31 PM
  • User-408628329 posted

    Just thought of something. I ran into a problem like that with the update using the stored procedure.

    I had a person table with lets say fields: id, fname, lname, ssn, dob. In my case when someone updates personal info, i did not want to lets say the ssn field to change, so i did not include this field in my list of parameters for the stored procedure. And my update was done through GridView and SqlDataSource, which included the stored proc for the update. By default, when the update was execute, all the parameters that showed up in GridView got sent to the stored proc, and since ssn was not part of my stored proc parameter, it throwed a similar error like yours.

    So another thing for you to check is to see if the parameters in your stored proc match all the params in your formview. But as far as I read your code they do.


    Just a thought  :)


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 7, 2010 6:47 PM
  • User1089256414 posted

    Thank you very much. That works

     

    Martin 

    Wednesday, December 8, 2010 11:15 AM
  • User1089256414 posted

    if girdview has dropdownlist (ddl) TemplateField, how to attach sqldatasource to ddl? for example the sql table has AL value for state field, the gridview ddl lists all states in gridview, how Al is selected in ddl when bind to ddl?

    secondly, when user selects different state like GA, how update stored precedure works? that means how to find ddl selected value in update procedure as shown below:

    -------------------------------------------------------------
    <DataObjectMethod(DataObjectMethodType.update, True)> _
        Public Function Insert(ByVal State As String) As Integer

            Using conn()

                Dim cmd As SqlCommand = New SqlCommand("dbo.Vendorsupdate", conn)
                cmd.CommandType = CommandType.StoredProcedure

         'get state value from ddl, how?

                cmd.Parameters.AddWithValue("@state", state).DbType = DbType.String
               

              
                conn.Open()
                Dim rowsAffected As Integer = cmd.ExecuteNonQuery
                Return rowsAffected

            End Using

        End Function
    --------------------------------------------------------------
     

    Thanks,

    Martin

    Thursday, December 9, 2010 9:42 AM