Can someone please tell me why I am getting "error while inserting data" error message
Hi mates,
I have written an app built around JSON object and ajax though with PHP.
This one is using WebMethod() with asp.net and I am running into "error while inserting data"
Though that is my custom error message, it tells me there is an issue with sanitizing data.
I am also receiving [object Object] which tells me that a certain form field is not getting stringrified.
Can someone please help?
I have written an app built around JSON object and ajax though with PHP.
This one is using WebMethod() with asp.net and I am running into "error while inserting data"
Though that is my custom error message, it tells me there is an issue with sanitizing data.
I am also receiving [object Object] which tells me that a certain form field is not getting stringrified.
Can someone please help?
<script type="text/javascript">
$(document).ready(function () {
$(document).on("click", "#btnAdd", function () { //
var rowCount = $('.data-contact-person').length + 1;
var contactdiv = '<tr class="data-contact-person">' +
'<td><input type="text" style="width:200px;" name="sourcename' + rowCount + '" class="form-control sourcename01" /></td>' +
'<td><input type="text" style="width:200px;" name="sourceaddress' + rowCount + '" class="form-control sourceaddress01" /></td>' +
'<td><input type="text" style="width:200px;" name="sourceincome' + rowCount + '" class="form-control sourceincome01" /></td>' +
'<td style="width:200px;"><button type="button" id="btnAdd" class="btn btn-xs btn-primary classAdd">Add More</button>' +
'<button type="button" id="btnDelete1" class="deleteContact btn btn btn-danger btn-xs">Remove</button></td>' +
'</tr>';
$('#maintable').append(contactdiv); // Adding these controls to Main table class
});
$(document).on("click", ".deleteContact", function () {
$(this).closest("tr").remove(); // closest used to remove the respective 'tr' in which I have my controls
});
function getAllEmpData() {
var data = [];
$('tr.data-contact-person').each(function () {
var sname = $(this).find('.sourcename01').val();
var saddress = $(this).find('.sourceaddress01').val();
var sincome = $(this).find('.sourceincome01').val();
var alldata = {
'mySource': sname,
'mySAddress': saddress,
'mySIncome': sincome
}
data.push(alldata);
});
console.log(data);
return data;
}
$("#btnSubmit").click(function () {
var data = JSON.stringify(getAllEmpData());
//console.log(data);
$.ajax({
url: 'closures.aspx/SaveData',
type: 'POST',
dataType: 'json',
contentType: 'application/json; charset=utf-8',
data: JSON.stringify({ 'empdata': data }),
success: function () {
alert("Data Added Successfully");
},
error: function () {
alert("Error while inserting data");
}
});
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<h2>Forms</h2>
<table class="table" id="maintable">
<thead>
<tr>
<th>Name</th>
<th>Address</th>
<th>Income</th>
</tr>
</thead>
<tbody>
<tr class="data-contact-person">
<td>
<input type="text" style="width:200px;" name="sourcename" class="form-control sourcename01" /></td>
<td>
<input type="text" style="width:200px;" name="sourceaddress" class="form-control sourceaddress01" /></td>
<td>
<input type="text" style="width:200px;" name="sourceincome" class="form-control sourceincome01" /></td>
<td style="width:200px;">
<button type="button" id="btnAdd" class="btn btn-xs btn-primary classAdd">Add More</button>
</td>
</tr>
</tbody>
</table>
<button type="button" id="btnSubmit" class="btn btn-primary btn-md pull-right btn-sm">Submit</button>
</div>
</form>
</body>
</html>
Developer
CERTIFIED EXPERT
If possible, you are better off debugging the code in 'closures.aspx/SaveData' method and post the exception here. Because that's where the error is coming. No point in looking at client side code. Furthermore, you will also see the values being posted.
Hi, Thanks a lot for the prompt response.
I doubt that it is coming from codebehind. I can't even get to it. If I could I would have debugged it and found the issue by now.
I could be wrong but I am well versed with this part of the code.
Please have a look.
I doubt that it is coming from codebehind. I can't even get to it. If I could I would have debugged it and found the issue by now.
I could be wrong but I am well versed with this part of the code.
Please have a look.
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Linq
Imports System.Web
Imports System.Web.Services
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports Newtonsoft.Json
Partial Public Class disclosures
Inherits System.Web.UI.Page
Public Shared Constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Protected Sub Page_Load(sender As Object, e As EventArgs)
End Sub
<WebMethod()> _
Public Shared Function SaveData(empdata As String) As String
Dim serializedData = JsonConvert.DeserializeObject(Of List(Of Employee))(empdata)
Using con = New SqlConnection(Constr)
If con.State = ConnectionState.Closed Then
con.Open()
End If
For Each data As Employee In serializedData
Using cmd = New SqlCommand("INSERT INTO SourceDetails(sourcename, sourceaddress, sourceincome, createDate) VALUES(@sname, @saddress,@sincome,@CreatedDate)")
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@sname", data.mySpouse)
cmd.Parameters.AddWithValue("@saddress", data.mySAddress)
cmd.Parameters.AddWithValue("@sincome", data.mySIncome)
cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now)
cmd.Connection = con
cmd.ExecuteNonQuery()
End Using
Next
con.Close()
End Using
Return Nothing
End Function
End Class
Public Class Employee
Public Property mySpouse() As String
Get
Return m_mySpouse
End Get
Set(value As String)
m_mySpouse = value
End Set
End Property
Private m_mySpouse As String
Public Property mySAddress() As String
Get
Return m_mySAddress
End Get
Set(value As String)
m_mySAddress = value
End Set
End Property
Private m_mySAddress As String
Public Property mySIncome() As String
Get
Return m_mySIncome
End Get
Set(value As String)
m_mySIncome = value
End Set
End Property
Private m_mySIncome As String
Public Property CreatedDate() As DateTime
Get
Return m_CreatedDate
End Get
Set(value As DateTime)
m_CreatedDate = Value
End Set
End Property
Private m_CreatedDate As DateTime
End Class
CERTIFIED EXPERT
Got it. You suspect that the request is not being posted at all!
If such is the case why not try an exhaustive form for error function as shown below:
The above code is being copied from here.
If such is the case why not try an exhaustive form for error function as shown below:
error: function (jqXHR, exception) {
var msg = '';
if (jqXHR.status === 0) {
msg = 'Not connect.\n Verify Network.';
} else if (jqXHR.status == 404) {
msg = 'Requested page not found. [404]';
} else if (jqXHR.status == 500) {
msg = 'Internal Server Error [500].';
} else if (exception === 'parsererror') {
msg = 'Requested JSON parse failed.';
} else if (exception === 'timeout') {
msg = 'Time out error.';
} else if (exception === 'abort') {
msg = 'Ajax request aborted.';
} else {
msg = 'Uncaught Error.\n' + jqXHR.responseText;
}
},
The above code is being copied from here.
Developer
CERTIFIED EXPERT
If (appropriate / possible) you could also post the value of 'data' as well, please.
You said:
Got it. You suspect that the request is not being posted at all!
Exactly my point.
I am about to remote in to add your code bit and let you know asap.
Thanks again
Got it. You suspect that the request is not being posted at all!
Exactly my point.
I am about to remote in to add your code bit and let you know asap.
Thanks again
Developer
CERTIFIED EXPERT
It goes at line 48-50 of your client-side code posted above.
But wait a minute. Do you not think there is a case of a missing , (comma) after the error function on line 50?
Have a close look. Please also refer to the link I posted to compare the typical ajax call code.
But wait a minute. Do you not think there is a case of a missing , (comma) after the error function on line 50?
Have a close look. Please also refer to the link I posted to compare the typical ajax call code.
Developer
CERTIFIED EXPERT
No. Line 50 in your client-side code.
error: function () {
alert("Error while inserting data");
},
CERTIFIED EXPERT
The comma is NOT there. I think it should be there. Also have you tried seeing client-side error log, which will typically be available under the 'console' table after you press F12 in browser window.
PHP
CERTIFIED EXPERT
This is due to data not sent exact json format.
Can you try just removing dataTyp:json line ? It works without this line, I have used url: 'closures.php', for testing you can change to your page.
Here is the reference link I got this idea from.
http://stackoverflow.com/questions/19976627/posting-json-with-jquery-ajax-to-php
Can you try just removing dataTyp:json line ? It works without this line, I have used url: 'closures.php', for testing you can change to your page.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$(document).on("click", "#btnAdd", function () { //
var rowCount = $('.data-contact-person').length + 1;
var contactdiv = '<tr class="data-contact-person">' +
'<td><input type="text" style="width:200px;" name="sourcename' + rowCount + '" class="form-control sourcename01" /></td>' +
'<td><input type="text" style="width:200px;" name="sourceaddress' + rowCount + '" class="form-control sourceaddress01" /></td>' +
'<td><input type="text" style="width:200px;" name="sourceincome' + rowCount + '" class="form-control sourceincome01" /></td>' +
'<td style="width:200px;"><button type="button" id="btnAdd" class="btn btn-xs btn-primary classAdd">Add More</button>' +
'<button type="button" id="btnDelete1" class="deleteContact btn btn btn-danger btn-xs">Remove</button></td>' +
'</tr>';
$('#maintable').append(contactdiv); // Adding these controls to Main table class
});
$(document).on("click", ".deleteContact", function () {
$(this).closest("tr").remove(); // closest used to remove the respective 'tr' in which I have my controls
});
function getAllEmpData() {
var data = [];
$('tr.data-contact-person').each(function () {
var sname = $(this).find('.sourcename01').val();
var saddress = $(this).find('.sourceaddress01').val();
var sincome = $(this).find('.sourceincome01').val();
var alldata = {
'mySource': sname,
'mySAddress': saddress,
'mySIncome': sincome
}
data.push(alldata);
});
console.log(data);
return data;
}
$("#btnSubmit").click(function () {
var data = JSON.stringify(getAllEmpData());
//console.log(data);
$.ajax({
url: 'closures.php',
type: 'POST',
contentType: 'application/json; charset=utf-8',
data: JSON.stringify({ 'empdata': data }),
success: function () {
alert("Data Added Successfully");
},
error: function () {
alert("Error while inserting data");
}
});
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div class="container">
<h2>Forms</h2>
<table class="table" id="maintable">
<thead>
<tr>
<th>Name</th>
<th>Address</th>
<th>Income</th>
</tr>
</thead>
<tbody>
<tr class="data-contact-person">
<td>
<input type="text" style="width:200px;" name="sourcename" class="form-control sourcename01" /></td>
<td>
<input type="text" style="width:200px;" name="sourceaddress" class="form-control sourceaddress01" /></td>
<td>
<input type="text" style="width:200px;" name="sourceincome" class="form-control sourceincome01" /></td>
<td style="width:200px;">
<button type="button" id="btnAdd" class="btn btn-xs btn-primary classAdd">Add More</button>
</td>
</tr>
</tbody>
</table>
<button type="button" id="btnSubmit" class="btn btn-primary btn-md pull-right btn-sm">Submit</button>
</div>
</form>
</body>
</html>
Here is the reference link I got this idea from.
http://stackoverflow.com/questions/19976627/posting-json-with-jquery-ajax-to-php
Any reason you are not using serializeArray() to get your form data
$("#btnSubmit").click(function () {
// USE jQuery serializeArray TO GATHER ALL FORM DATA
var formData = (this.form).serializeArray();
var data = JSON.stringify(formData);
//console.log(data);
$.ajax({
url: 'closures.php',
type: 'POST',
contentType: 'application/json; charset=utf-8',
data: JSON.stringify({ 'empdata': data }),
success: function () {
alert("Data Added Successfully");
},
error: function () {
alert("Error while inserting data");
}
});
});
Note
dataType:'JSON'
Is for return data only - it tells jQuery to treat the response as a JSON response which jQuery then automatically parses into a JavaScript object.
As your function is not returning any data you do not need it and it has no bearing on the sending of the data - that is what the ContentType Request Header is for.
dataType:'JSON'
Is for return data only - it tells jQuery to treat the response as a JSON response which jQuery then automatically parses into a JavaScript object.
As your function is not returning any data you do not need it and it has no bearing on the sending of the data - that is what the ContentType Request Header is for.
Thank you guys very much. YOU all have been awesome.
I will test various parts of your code as soon as I get to work this morning and report back.
I really appreciate the great responses.
I will test various parts of your code as soon as I get to work this morning and report back.
I really appreciate the great responses.
A couple of thoughts and then one question.
First, when I tried your solution Julian, I am getting the following error:
Error: Object doesn't support property or method 'serializeArray'
When I tried yours ( actually I tried yours first) but when I tried it, it said "Data added successfully"
Infact, no data was added to the database. It turns out that the reason it said data was added successfully was because SaveData webmethod was removed from the script, from both of your solutions as a matter of fact.
That leads to my question, any reason it was removed?
it is in there because of the INSERT statement.
Thanks again for your help.
First, when I tried your solution Julian, I am getting the following error:
Error: Object doesn't support property or method 'serializeArray'
When I tried yours ( actually I tried yours first) but when I tried it, it said "Data added successfully"
Infact, no data was added to the database. It turns out that the reason it said data was added successfully was because SaveData webmethod was removed from the script, from both of your solutions as a matter of fact.
That leads to my question, any reason it was removed?
it is in there because of the INSERT statement.
Thanks again for your help.
That leads to my question, any reason it was removed?No that was a typo - to do a test here I had to change that to point to one of my own scripts when I pasted it back I did not copy the url in correctly. Should be
$("#btnSubmit").click(function () {
// USE jQuery serializeArray TO GATHER ALL FORM DATA
var formData = (this.form).serializeArray();
var data = JSON.stringify(formData);
//console.log(data);
$.ajax({
url: 'closures.aspx/SaveData',
type: 'POST',
contentType: 'application/json; charset=utf-8',
data: JSON.stringify({ 'empdata': data }),
success: function () {
alert("Data Added Successfully");
},
error: function () {
alert("Error while inserting data");
}
});
});
First, when I tried your solution Julian, I am getting the following error:You need to show us the code where you tried to do this. serializeArray is a valid jQuery function and will serialize your form variables into an array that can then be stringified into JSON
Here it is as I just used it:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Forms</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
<script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$(document).on("click", "#btnAdd", function () { //
var rowCount = $('.data-contact-person').length + 1;
var contactdiv = '<tr class="data-contact-person">' +
'<td><input type="text" style="width:200px;" name="sourcename' + rowCount + '" placeholder="Name of income source..." class="form-control sourcename01" /></td>' +
'<td><input type="text" style="width:200px;" name="sourceaddress' + rowCount + '" placeholder="Address of income source..." class="form-control sourceaddress01" /></td>' +
'<td><input type="text" style="width:200px;" name="sourceincome' + rowCount + '" placeholder="Income..." class="form-control sourceincome01" /></td>' +
'<td style="width:200px;"><button type="button" id="btnAdd" class="btn btn-xs btn-primary classAdd">Add More</button>' +
'<button type="button" id="btnDelete1" class="deleteContact btn btn btn-danger btn-xs">Remove</button></td>' +
'</tr>';
$('#maintable').append(contactdiv); // Adding these controls to Main table class
});
$(document).on("click", ".deleteContact", function () {
$(this).closest("tr").remove(); // closest used to remove the respective 'tr' in which I have my controls
});
function getAllEmpData() {
var data = [];
$('tr.data-contact-person').each(function () {
var sname = $(this).find('.sourcename01').val();
var saddress = $(this).find('.sourceaddress01').val();
var sincome = $(this).find('.sourceincome01').val();
var alldata = {
'mySource': sname,
'mySAddress': saddress,
'mySIncome': sincome
}
data.push(alldata);
});
console.log(data);
return data;
}
$("#btnSubmit").click(function () {
// USE jQuery serializeArray TO GATHER ALL FORM DATA
var formData = (this.form).serializeArray();
var data = JSON.stringify(formData);
//console.log(data);
$.ajax({
url: 'closures.aspx/SaveData',
type: 'POST',
contentType: 'application/json; charset=utf-8',
data: JSON.stringify({ 'empdata': data }),
success: function () {
alert("Data Added Successfully");
},
error: function () {
alert("Error while inserting data");
}
});
});
});
</script>
<style type="text/css">
.bs-example{
margin-left: 250px;
margin-top: 30px;
}
</style>
</head>
<body>
<div class="bs-example">
<form id="form1" runat="server">
<div class="container">
<h2>Forms</h2>
<table id="maintable">
<thead>
<tr>
<th>Name</th>
<th>Address</th>
<th>Income</th>
</tr>
</thead>
<tbody>
<tr class="data-contact-person">
<td>
<input type="text" style="width:200px;" name="sourcename" class="form-control sourcename01" placeholder="Name of income source..." /></td>
<td>
<input type="text" style="width:200px;" name="sourceaddress" class="form-control sourceaddress01" placeholder="Address of income source..." /></td>
<td>
<input type="text" style="width:200px;" name="sourceincome" class="form-control sourceincome01" placeholder="Income..." /></td>
<td style="width:200px;">
<button type="button" id="btnAdd" class="btn btn-xs btn-primary classAdd">Add More</button>
</td>
</tr>
</tbody>
</table><br />
<button type="button" id="btnSubmit" class="btn btn-primary btn-md pull-center btn-sm">Submit</button>
</div>
</form>
</div>
</body>
</html>
That's your code I copied boss.
Can you please post the correct code if you don't mind, just the script not entire thing.
Thanks for all your help.
I have been stuck on this now for almost two days.
Can you please post the correct code if you don't mind, just the script not entire thing.
Thanks for all your help.
I have been stuck on this now for almost two days.
I added a script debugger:
"The parametized query \u0027(@sname nvarchar(4000), @saddress nvarchar(4000), @sincome nvarcha\u0027 expects parameter \u0027@sname\u0027, which was not supplied."
This doesn't make sense.
error: function(xhr, status, error) {
alert(xhr.responseText);
}
"The parametized query \u0027(@sname nvarchar(4000), @saddress nvarchar(4000), @sincome nvarcha\u0027 expects parameter \u0027@sname\u0027, which was not supplied."
This doesn't make sense.
Developer
CERTIFIED EXPERT
Please refer to your original post of the question and line 26 in it and please see the line below from your server side code:
Should it not be 'mySource'?
cmd.Parameters.AddWithValue("@sname", data.mySpouse)
Should it not be 'mySource'?
That's your code I copied boss.Which I got from your opening post.
I am also receiving [object Object] which tells me that a certain form field is not getting stringrified.Breaking your code down
You have the first step which is to get the data - let's use your approach for now - we can look at serializeArray down the road
var data = JSON.stringify(getAllEmpData());
In your AJAX call you have this
data: JSON.stringify({ 'empdata': data }),
Let us take a step back - what are you expecting to see on the server, I doubt it is an object that has a property pointing to more data that needs to be decoded.
If I look at your server side code I am seeing this
Dim serializedData = JsonConvert.DeserializeObject(Of List(Of Employee))(empdata)
Therefore I would make the following change to your original code
$("#btnSubmit").click(function () {
var data = JSON.stringify(getAllEmpData());
//console.log(data);
$.ajax({
url: 'closures.aspx/SaveData',
type: 'POST',
dataType: 'json',
contentType: 'application/json; charset=utf-8',
// ***** DON'T STRINGIFY THE data PROPERTY *****
data: { 'empdata': data },
success: function () {
alert("Data Added Successfully");
},
error: function () {
alert("Error while inserting data");
}
});
Man, this is so complicated.
It turns out that the code works as is as long as there is just one class.
For instance, If I use just one class called data-contact-person, it works and data is successfully inserted into the database.
If however, I add another class as shown below:
and attempt to have multiple INSERT statements using same saveData webmethod, it doesn't work.
Hence the error and only I was able to pinpoint the issue was using the debug script I posted earlier.
There is got to be a better way.
It turns out that the code works as is as long as there is just one class.
For instance, If I use just one class called data-contact-person, it works and data is successfully inserted into the database.
If however, I add another class as shown below:
$(document).ready(function () {
$(document).on("click", "#btnAdd", function () { //
var rowCount = $('.data-contact-person').length + 1;
var contactdiv = '<tr class="data-contact-person">' +
'<td><input type="text" style="width:200px;" name="sourcename' + rowCount + '" placeholder="Name of income source..." class="form-control sourcename01" /></td>' +
'<td><input type="text" style="width:200px;" name="sourceaddress' + rowCount + '" placeholder="Address of income source..." class="form-control sourceaddress01" /></td>' +
'<td><input type="text" style="width:200px;" name="sourceincome' + rowCount + '" placeholder="Income..." class="form-control sourceincome01" /></td>' +
'<td style="width:200px;"><button type="button" id="btnAdd" class="btn btn-xs btn-primary classAdd">Add More</button>' +
'<button type="button" id="btnDelete1" class="deleteContact btn btn btn-danger btn-xs">Remove</button></td>' +
'</tr>';
$('#maintable').append(contactdiv); // Adding these controls to Main table class
});
$(document).on("click", "#btnAdd2", function () { //
var rowCount = $('.data-contact-person2').length + 1;
var contactdiv = '<tr class="data-contact-person2">' +
'<td><input type="text" style="width:200px;" name="spousename' + rowCount + '" placeholder="Name of income source of spouse..." class="form-control spousename01" /></td>' +
'<td><input type="text" style="width:200px;" name="spouseaddress' + rowCount + '" placeholder="Address of income source of spouse..." class="form-control spouseaddress01" /></td>' +
'<td><input type="text" style="width:200px;" name="spouseincome' + rowCount + '" placeholder="Income..." class="form-control spouseincome01" /></td>' +
'<td><button type="button" id="btnAdd2" class="btn btn-xs btn-primary classAdd">Add More</button>' +
'<button type="button" id="btnDelete2" class="deleteContact btn btn btn-danger btn-xs">Add More</button></td>' +
'</tr>';
$('#maintable2').append(contactdiv); // Adding these controls to Main table class
});
and attempt to have multiple INSERT statements using same saveData webmethod, it doesn't work.
Hence the error and only I was able to pinpoint the issue was using the debug script I posted earlier.
There is got to be a better way.