locked
Unable to Insert data using Jquery RRS feed

  • Question

  • User-582711651 posted

    Hi, 

    Anyone, please help me, 

    tried several ways, but I am unable to insert data by using jquery with SQL StoredProcedure in VB code, I think some mistakes are there in my code, and SQL/Storeprocudure DB all things are good.

    My jQuery:
    
     <script type="text/javascript" src="http://cdn.jsdelivr.net/json2/0.1/json2.js"></script>
     <script type="text/javascript">
        $(function () {
            $("[id*=btnsubmit]").bind("click", function () {
                    var f_db = {};
                    f_db.recid  = $('#lbl_trckingid').val();                
                    f_db.recvdt = $('#txt_PostCouriReceivedOn').val();
                    f_db.recvby = $('#txt_DocReceivedByName').val();
                    f_db.recvph = $('#txt_ReceivedPersonMobPH').val();
                    f_db.remarks = $('#txt_GeneralRemarks').val();
                $.ajax({
                    type: "POST",
                    url: "ICT_TxnHOApproval.aspx/Doc_ackwd",
                    data: '{f_db: ' + JSON.stringify(f_db) + '}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        alert("The Document Acknowledgement info has been updated successfully.");
                        window.location.reload();
                    }
                });
                return false;
            });
        });
    </script>
    
    my Aspx page
    
    <div class="box-body">
    <div class="col-md-6">
    <div class="form-group">
    <label>MemberCode</label>
    <asp:Label ID="lbl_CustomerCode" runat="server" Text="" CssClass="form-control"></asp:Label>
    <asp:Label ID="lbl_trckingid" runat="server" Text="5512" ForeColor="Silver"></asp:Label> </div> </div> <div class="col-md-6"> <div class="form-group"> <label>MemberName</label> <asp:Label ID="lbl_CustomerName" runat="server" Text="" CssClass="form-control"></asp:Label> </div> </div> <div class="col-md-6"> <div class="form-group"> <label>01.Document Received on*</label> <div class="input-group date"> <div class="input-group-addon"> <i class="fa fa-calendar"></i> </div> <asp:TextBox ID="txt_PostCouriReceivedOn" runat="server" CssClass="form-control" placeholder="Future Date NotAllow" Width="200px" autocomplete="off"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Documents received on?" ForeColor="red" ControlToValidate="txt_PostCouriReceivedOn" ValidationGroup="group2"></asp:RequiredFieldValidator> </div> </div> </div> <div class="col-md-6"> <div class="form-group"> <label>02.Recvd.By*</label> <asp:TextBox ID="txt_DocReceivedByName" runat="server" CssClass="form-control" TextMode="SingleLine" placeholder="100 Chars only" MaxLength="100" Style="text-transform: uppercase" onkeydown="return((event.keyCode >= 65 && event.keyCode <= 120) || (event.keyCode >= 48 && event.keyCode <= 57) || (event.keyCode==8)|| (event.keyCode==9) || (event.keyCode==55) || (event.keyCode==16) || (event.keyCode==110) || (event.keyCode==190) || (event.keyCode==188) || (event.keyCode==35) || (event.keyCode==36) || (event.keyCode==37) || (event.keyCode==38) || (event.keyCode==39) || (event.keyCode==40) || (event.keyCode==46) || (event.keyCode==32));"></asp:TextBox> <asp:RegularExpressionValidator ID="RegEx_DispPerson" runat="server" ErrorMessage="Alphabets only" ForeColor="red" ControlToValidate="txt_DocReceivedByName" ValidationGroup="group2" ValidationExpression="^[A-Za-z ]+$"></asp:RegularExpressionValidator> <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Documents received person name? " ForeColor="red" ControlToValidate="txt_DocReceivedByName" ValidationGroup="group2"></asp:RequiredFieldValidator> </div> </div> <div class="col-md-6"> <div class="form-group"> <label>03.ReceiverMob.PH#*</label> <asp:TextBox ID="txt_ReceivedPersonMobPH" runat="server" CssClass="form-control" placeholder="10 Digit/Start with 9,7,6,8" MaxLength="10" onkeypress="return isNumberKey(event)"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ErrorMessage="MobPh#?" ForeColor="red" ControlToValidate="txt_ReceivedPersonMobPH" ValidationGroup="group2"></asp:RequiredFieldValidator> <asp:RegularExpressionValidator ID="RegEx_MobPh" runat="server" ErrorMessage="10 Nos / Starts 9/8/7/6" ForeColor="red" ControlToValidate="txt_ReceivedPersonMobPH" ValidationGroup="group2" ValidationExpression="^[6789]\d{9}$"></asp:RegularExpressionValidator> </div> </div> <div class="col-md-6"> <div class="form-group"> <label>04.Remarks</label> <asp:TextBox ID="txt_GeneralRemarks" runat="server" CssClass="form-control" TextMode="SingleLine" placeholder="100 Chars only" MaxLength="100" Style="text-transform: uppercase" onkeydown="return((event.keyCode >= 65 && event.keyCode <= 120) || (event.keyCode >= 48 && event.keyCode <= 57) || (event.keyCode==8)|| (event.keyCode==9) || (event.keyCode==55) || (event.keyCode==16) || (event.keyCode==110) || (event.keyCode==190) || (event.keyCode==188) || (event.keyCode==35) || (event.keyCode==36) || (event.keyCode==37) || (event.keyCode==38) || (event.keyCode==39) || (event.keyCode==40) || (event.keyCode==46) || (event.keyCode==32));"></asp:TextBox> </div> </div> <div class="col-md-12"> <center> <div class="row"> <asp:Button ID="btnsubmit" runat="server" Text="Save" class="btn btn-primary" Visible="true" ValidationGroup="group2" ></asp:Button> &nbsp; <asp:Button ID="btn_Reset" runat="server" Text="Reset" class="btn btn-danger" Visible="true" ></asp:Button> <button class="btn btn-danger" data-dismiss="modal" type="button">Close</button> </div> </center> </div> </div>
    VB code
    
    Public Class f_db
            Public Property Rid() As Integer
                Get
                    Return _Rid
                End Get
                Set(value As Integer)
                    _Rid = value
                End Set
            End Property
            Private _Rid As Integer
    
            Public Property recvdt() As String
                Get
                    Return _recvdt
                End Get
                Set(value As String)
                    _recvdt = value
                End Set
            End Property
            Private _recvdt As String
            Public Property recvby() As String
                Get
                    Return _recvby
                End Get
                Set(value As String)
                    _recvby = value
                End Set
            End Property
            Private _recvby As String
            Public Property recvph() As String
                Get
                    Return _recvph
                End Get
                Set(value As String)
                    _recvph = value
                End Set
            End Property
            Private _recvph As String
            Public Property remarks() As String
                Get
                    Return _remarks
                End Get
                Set(value As String)
                    _remarks = value
                End Set
            End Property
            Private _remarks As String
        End Class
    
    <WebMethod()>
        <ScriptMethod()>
        Public Shared Sub Doc_ackwd(f_db As f_db)
            Dim DBConnectionString As String = ConfigurationManager.ConnectionStrings("AppCS").ConnectionString
            Dim selectSQL As String
            Dim sCon As New SqlConnection(DBConnectionString)
            Dim sCmd As New SqlCommand(selectSQL, sCon)
            Dim param As SqlParameter
            Dim dtadp As New SqlDataAdapter(sCmd)
            Dim dtset As New DataSet
            Try
                sCon.Open()
                sCmd.CommandType = CommandType.StoredProcedure
                sCmd.CommandText = "ICTUSP_Document_info_HO_Txn"
                sCmd.Parameters.Clear()
                sCmd.Parameters.Add(New SqlParameter("@i_SP_AttributeId", SqlDbType.Int)).Value = 1
                sCmd.Parameters.Add(New SqlParameter("@i_LoginUserECode", SqlDbType.VarChar)).Value = "99" 
                sCmd.Parameters.Add(New SqlParameter("@i_ParentRecordID", SqlDbType.Int)).Value = f_db.Rid
                sCmd.Parameters.Add(New SqlParameter("@i_PostCourierDate", SqlDbType.Date)).Value = CDate(f_db.recvdt)
                sCmd.Parameters.Add(New SqlParameter("@i_PostCourierReceiverName", SqlDbType.VarChar)).Value = f_db.recvby
                sCmd.Parameters.Add(New SqlParameter("@i_PostCourierReceiverMobNo", SqlDbType.VarChar)).Value = f_db.recvph
                sCmd.Parameters.Add(New SqlParameter("@i_Remarks", SqlDbType.VarChar)).Value = f_db.remarks
                sCmd.Parameters.Add(New SqlParameter("@i_RecordUpdtUser_IPMACAddr", SqlDbType.VarChar)).Value = "1001"
                param = New SqlParameter("@o_ErrorStatus", SqlDbType.Int) With {
                    .Direction = ParameterDirection.Output
                }
                sCmd.Parameters.Add(param)
                dtadp.Fill(dtset)
                If sCmd.Parameters("@o_ErrorStatus").Value = 1 Then
                    'lblmessage.Text = "Info: Your response updated successfully"
                    ' ScriptManager.RegisterClientScriptBlock(Me, Me.GetType(), "alertMessage", "alert('Msg: Your response updated successfully')", True)
    
                End If
            Finally
                sCon.Close()
            End Try
        End Sub

    Tuesday, October 6, 2020 8:19 PM

Answers

  • User475983607 posted

    There are quite a few issues with the code.  IMHO, the biggest design issue is there little reason to use AJAX since you refresh the page.   The main purpose for using AJAX is do a partial page refresh.  Your design is actually doing one more request than needed.  I recommend removing the AJAX,  moving the save logic to a button click handlers, and do a standard post back. 

    If you really want to use AJAX then below are the fixes that have to be made to the code.

        Public Class f_db
            Public Property recid() As Integer
                Get
                    Return _Rid
                End Get
                Set(value As Integer)
                    _Rid = value
                End Set
            End Property
            Private _Rid As Integer
    
            Public Property recvdt() As String
                Get
                    Return _recvdt
                End Get
                Set(value As String)
                    _recvdt = value
                End Set
            End Property
            Private _recvdt As String
            Public Property recvby() As String
                Get
                    Return _recvby
                End Get
                Set(value As String)
                    _recvby = value
                End Set
            End Property
            Private _recvby As String
            Public Property recvph() As String
                Get
                    Return _recvph
                End Get
                Set(value As String)
                    _recvph = value
                End Set
            End Property
            Private _recvph As String
            Public Property remarks() As String
                Get
                    Return _remarks
                End Get
                Set(value As String)
                    _remarks = value
                End Set
            End Property
            Private _remarks As String
        End Class
             $(function () {
                 $("[id*=btnsubmit]").bind("click", function (e) {
                     e.preventDefault();
                     var f_db = {};
                     f_db.recid = $('#<%=lbl_trckingid.ClientID%>').text();
                     f_db.recvdt = $('#<%=txt_PostCouriReceivedOn.ClientID%>').val();
                     f_db.recvby = $('#<%=txt_DocReceivedByName.ClientID%>').val();
                     f_db.recvph = $('#<%=txt_ReceivedPersonMobPH.ClientID%>').val();
                     f_db.remarks = $('#<%=txt_GeneralRemarks.ClientID%>').val();
                     $.ajax({
                         type: "POST",
                         url: "ICT_TxnHOApproval.aspx/Doc_ackwd",
                         data: '{f_db: ' + JSON.stringify(f_db) + '}',
                         contentType: "application/json; charset=utf-8",
                         dataType: "json",
                         success: function (response) {
                             console.log("The Document Acknowledgement info has been updated successfully.");
                             console.log(response);
                             //window.location.reload();
                         }
                     });
                     return false;
                 });
             });

    I can't test the stored procedure.  The best I can do is verify the data is submitted to the web method.  Web methods should be functions not subs.

        <WebMethod()>
        <ScriptMethod()>
        Public Shared Function Doc_ackwd(f_db As f_db) As f_db
            Return f_db
        End Function

    Lastly, start using the browsers dev tools to debug your JavaScript.  Many of the error are clear if you use the standard tool chain.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 6, 2020 9:04 PM
  • User-1330468790 posted

    Hi ayyappan.CNN,

     

    One feasible way is to add a field to the class f_db, like ErrorStatus and assign this field with different values based on the value of the variable "@o_ErrorStatus". Then you could check this field in the response of the ajax call.

    For example,

    $.ajax({
                    type: "POST",
                    url: "ICT_TxnHO_DocAcknowledgement.aspx/Doc_ackwd",
                    data: '{f_db: ' + JSON.stringify(f_db) + '}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        // if condition to check the vallue of response.errorStatus
                        alert("The document acknowledgement information has been updated successfully!");
                        $("#Popu_HOApproval").modal('hide');
                    },
                    error: function (data, ex) {
                        alert("Sorry there is err. " + data + " Ex: " + ex);
                        $(".modal-backdrop").remove();
                        $("#Popu_HOApproval").modal('show');
                    }
                    
                });

     

    Hope helps.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 12, 2020 9:22 AM

All replies

  • User475983607 posted

    There are quite a few issues with the code.  IMHO, the biggest design issue is there little reason to use AJAX since you refresh the page.   The main purpose for using AJAX is do a partial page refresh.  Your design is actually doing one more request than needed.  I recommend removing the AJAX,  moving the save logic to a button click handlers, and do a standard post back. 

    If you really want to use AJAX then below are the fixes that have to be made to the code.

        Public Class f_db
            Public Property recid() As Integer
                Get
                    Return _Rid
                End Get
                Set(value As Integer)
                    _Rid = value
                End Set
            End Property
            Private _Rid As Integer
    
            Public Property recvdt() As String
                Get
                    Return _recvdt
                End Get
                Set(value As String)
                    _recvdt = value
                End Set
            End Property
            Private _recvdt As String
            Public Property recvby() As String
                Get
                    Return _recvby
                End Get
                Set(value As String)
                    _recvby = value
                End Set
            End Property
            Private _recvby As String
            Public Property recvph() As String
                Get
                    Return _recvph
                End Get
                Set(value As String)
                    _recvph = value
                End Set
            End Property
            Private _recvph As String
            Public Property remarks() As String
                Get
                    Return _remarks
                End Get
                Set(value As String)
                    _remarks = value
                End Set
            End Property
            Private _remarks As String
        End Class
             $(function () {
                 $("[id*=btnsubmit]").bind("click", function (e) {
                     e.preventDefault();
                     var f_db = {};
                     f_db.recid = $('#<%=lbl_trckingid.ClientID%>').text();
                     f_db.recvdt = $('#<%=txt_PostCouriReceivedOn.ClientID%>').val();
                     f_db.recvby = $('#<%=txt_DocReceivedByName.ClientID%>').val();
                     f_db.recvph = $('#<%=txt_ReceivedPersonMobPH.ClientID%>').val();
                     f_db.remarks = $('#<%=txt_GeneralRemarks.ClientID%>').val();
                     $.ajax({
                         type: "POST",
                         url: "ICT_TxnHOApproval.aspx/Doc_ackwd",
                         data: '{f_db: ' + JSON.stringify(f_db) + '}',
                         contentType: "application/json; charset=utf-8",
                         dataType: "json",
                         success: function (response) {
                             console.log("The Document Acknowledgement info has been updated successfully.");
                             console.log(response);
                             //window.location.reload();
                         }
                     });
                     return false;
                 });
             });

    I can't test the stored procedure.  The best I can do is verify the data is submitted to the web method.  Web methods should be functions not subs.

        <WebMethod()>
        <ScriptMethod()>
        Public Shared Function Doc_ackwd(f_db As f_db) As f_db
            Return f_db
        End Function

    Lastly, start using the browsers dev tools to debug your JavaScript.  Many of the error are clear if you use the standard tool chain.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 6, 2020 9:04 PM
  • User-582711651 posted

    Hi mgebhard, 

    Good, based on your guidance, I've modified, my code, data inserted well, one more doubt, please help

    Based on the return value of SP I want to display the error message, if the return value is 1 the data inserted message if 2 then err. message

    refer my code 

    Aspx Page
    
     <asp:Button ID="btnsubmit" runat="server" Text="Save"  class="btn btn-primary" Visible="true" ValidationGroup="group2" OnClientClick="db_post();" ></asp:Button>
    J Qry 
    <script type="text/javascript" src="http://cdn.jsdelivr.net/json2/0.1/json2.js"></script> <script type="text/javascript"> // $(function () { - due to post back issue I have disabled this line // $("[id*=btnsubmit]").bind("click", function () { - due to post back issue I have disabled this line function db_post() { var f_db = {}; var recidx = $('#<%=lbl_rowid.ClientID%>').text(); f_db.recid = $.trim(recidx); var trkidx = $('#<%=lbl_trckingid.ClientID%>').text(); f_db.trkid = $.trim(trkidx); f_db.recvdt = $('#<%=txt_PostCouriReceivedOn.ClientID%>').val(); f_db.recvby = $('#<%=txt_DocReceivedByName.ClientID%>').val().toUpperCase(); f_db.recvph = $('#<%=txt_ReceivedPersonMobPH.ClientID%>').val(); f_db.remarks = $('#<%=txt_GeneralRemarks.ClientID%>').val().toUpperCase(); f_db.usrid = '<%= Session("ICT_Em_Code") %>'; f_db.ipadr = '<%= Session("ICT_IPADDR") %>'; $.ajax({ type: "POST", url: "ICT_TxnHO_DocAcknowledgement.aspx/Doc_ackwd", data: '{f_db: ' + JSON.stringify(f_db) + '}', contentType: "application/json; charset=utf-8", dataType: "json", success: function (response) { alert("The document acknowledgement information has been updated successfully!"); $("#Popu_HOApproval").modal('hide'); }, error: function (data, ex) { alert("Sorry there is err. " + data + " Ex: " + ex); $(".modal-backdrop").remove(); $("#Popu_HOApproval").modal('show'); } }); return false; } </script>

    Code Behind

      Public Class f_db
            Public Property recid() As String
                Get
                    Return _recid
                End Get
                Set(value As String)
                    _recid = value
                End Set
            End Property
            Private _recid As String
            Public Property trkid() As String
                Get
                    Return _trkid
                End Get
                Set(value As String)
                    _trkid = value
                End Set
            End Property
            Private _trkid As String
            Public Property recvdt() As String
                Get
                    Return _recvdt
                End Get
                Set(value As String)
                    _recvdt = value
                End Set
            End Property
            Private _recvdt As String
            Public Property recvby() As String
                Get
                    Return _recvby
                End Get
                Set(value As String)
                    _recvby = value
                End Set
            End Property
            Private _recvby As String
            Public Property recvph() As String
                Get
                    Return _recvph
                End Get
                Set(value As String)
                    _recvph = value
                End Set
            End Property
            Private _recvph As String
            Public Property remarks() As String
                Get
                    Return _remarks
                End Get
                Set(value As String)
                    _remarks = value
                End Set
            End Property
            Private _remarks As String
            Public Property usrid() As String
                Get
                    Return _usrid
                End Get
                Set(value As String)
                    _usrid = value
                End Set
            End Property
            Private _usrid As String
            Public Property ipadr() As String
                Get
                    Return _ipadr
                End Get
                Set(value As String)
                    _ipadr = value
                End Set
            End Property
            Private _ipadr As String
        End Class

    My Data Passing to SP 

        <WebMethod()>
        <ScriptMethod()>
        Public Shared Function Doc_ackwd(f_db As f_db) As f_db
            Dim DCStr As String = ConfigurationManager.ConnectionStrings("ICT_ConStr").ConnectionString
            Dim selectSQL As String
            Dim sCon As New SqlConnection(DCStr)
            Dim sCmd As New SqlCommand(selectSQL, sCon)
            Dim param As SqlParameter
            Dim dtadp As New SqlDataAdapter(sCmd)
            Dim dtset As New DataSet
            Try
                sCon.Open()
                sCmd.CommandType = CommandType.StoredProcedure
                sCmd.CommandText = "ICTUSP_DocumentAckwdByHO_Txn"
                sCmd.Parameters.Clear()
                sCmd.Parameters.Add(New SqlParameter("@i_SP_AttributeId", SqlDbType.Int)).Value = 1
                sCmd.Parameters.Add(New SqlParameter("@i_LoginUserECode", SqlDbType.VarChar)).Value = f_db.usrid
                sCmd.Parameters.Add(New SqlParameter("@i_ParentRecordID", SqlDbType.Int)).Value = f_db.recid
                sCmd.Parameters.Add(New SqlParameter("@i_TrackingId", SqlDbType.VarChar)).Value = f_db.trkid
                sCmd.Parameters.Add(New SqlParameter("@i_PostCourierDate", SqlDbType.Date)).Value = CDate(f_db.recvdt)
                sCmd.Parameters.Add(New SqlParameter("@i_PostCourierReceiverName", SqlDbType.VarChar)).Value = f_db.recvby
                sCmd.Parameters.Add(New SqlParameter("@i_PostCourierReceiverMobNo", SqlDbType.VarChar)).Value = f_db.recvph
                sCmd.Parameters.Add(New SqlParameter("@i_Remarks", SqlDbType.VarChar)).Value = f_db.remarks
                sCmd.Parameters.Add(New SqlParameter("@i_RecordUpdtUser_IPMACAddr", SqlDbType.VarChar)).Value = f_db.ipadr
                param = New SqlParameter("@o_ErrorStatus", SqlDbType.Int) With {
                    .Direction = ParameterDirection.Output
                }
                sCmd.Parameters.Add(param)
                dtadp.Fill(dtset)
                If sCmd.Parameters("@o_ErrorStatus").Value = 1 Then 
                 ' Here When @o_ErrorStatus = 1 then I want to pass Data Saved message 
    Else If sCmd.Parameters("@o_ErrorStatus").Value = 2 Then
     ' When @o_ErrorStatus = 2 then I want to pass Err message End If Catch ex As Exception Finally sCon.Close() End Try Return f_db End Function

    Wednesday, October 7, 2020 4:48 PM
  • User-1330468790 posted

    Hi ayyappan.CNN,

     

    One feasible way is to add a field to the class f_db, like ErrorStatus and assign this field with different values based on the value of the variable "@o_ErrorStatus". Then you could check this field in the response of the ajax call.

    For example,

    $.ajax({
                    type: "POST",
                    url: "ICT_TxnHO_DocAcknowledgement.aspx/Doc_ackwd",
                    data: '{f_db: ' + JSON.stringify(f_db) + '}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (response) {
                        // if condition to check the vallue of response.errorStatus
                        alert("The document acknowledgement information has been updated successfully!");
                        $("#Popu_HOApproval").modal('hide');
                    },
                    error: function (data, ex) {
                        alert("Sorry there is err. " + data + " Ex: " + ex);
                        $(".modal-backdrop").remove();
                        $("#Popu_HOApproval").modal('show');
                    }
                    
                });

     

    Hope helps.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 12, 2020 9:22 AM