英文:
Error 500 Caused by: java.sql.SQLException: No value specified for parameter 34
问题
以下是您提供的内容的翻译：
public User create(User users) {
Connection c = null;
PreparedStatement ps = null;
try {
c = DbConnector.getConnection();
ps = c.prepareStatement("INSERT INTO tblmembers "
+ "(memberId, memberName, memberSurname, memberAddress, memberTown, memberCounty, memberPostCode, memberCountry, memberDocumentType, memberIdNumber, memberExpiryDate, memberIssuedBy, memberIssuePlace, memberDayOfBirth, memberMonthOfBirth, memberYearOfBirth, memberJobTitle, memberNationality, memberPhone1, memberPhone2, memberStatus, memberMaritalStatus, memberGender, memberSocialMedia, memberEmail, memberPassword, memberSecurityQuestion, memberSecurityQuestionAnswer, memberTransactionCount, memberLoginCount, memberPreferredPaymentType, memberSecurityId, memberNickname, agentId) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
new String[]{"memberId"}
);
ps.setString(1, users.getMemberName());
// ... (省略了其他字段的设置，以此类推)
ps.setInt(33, users.getAgentId());
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
rs.next();
int memberId = rs.getInt(1);
users.setMemberId(memberId);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
DbConnector.close(c);
}
return users;
}
var rootURL = "http://localhost:8080/{database_name}/list/users";
function addUser() {
// ... (省略了获取表单字段的代码)
if (memberId === "") {
console.log('Missing Member ID...');
} else {
console.log('Adding New User...');
$.ajax({
type: "POST",
url: rootURL,
data: JSON.stringify({
"memberName": memberName,
// ... (省略了其他字段的设置，以此类推)
"agentId": agentId
}),
dataType: "json",
contentType: "application/json;charset=utf-8",
success: function(data, textStatus, jqXHR) {
alert('User created successfully');
$('#memberId').val(data.id);
},
error: function(jqXHR, textStatus, errorThrown) {
console.log('Registration error...');
}
});
}
}
英文:
I am creating a small website as a hobby, I am having issues in the registration form where the fields don't seem to be adding up to my SQL database. I can pull all users no problem.
I believe the problem is with the fields not matching as it give me a message saying that there is no value specified for parameter 34
Please find my code below for:
- UserDAO (java)
- javascript
- SQL
public User create(User users) {
Connection c = null;
PreparedStatement ps = null;
try {
c = DbConnector.getConnection();
ps = c.prepareStatement("INSERT INTO tblmembers "
+ "memberId,"//1
+ "memberName,"//2
+ "memberSurname,"//3
+ "memberAddress,"//4
+ "memberTown,"//5
+ "memberCounty,"//6
+ "memberPostCode,"//7
+ "memberCountry,"//8
+ "memberDocumentType,"//9
+ "memberIdNumber,"//10
+ "memberExpiryDate,"//11
+ "memberIssuedBy,"//12
+ "memberIssuePlace,"//13
+ "memberDayOfBirth,"//14
+ "memberMonthOfBirth,"//15
+ "memberYearOfBirth,"//16
+ "memberJobTitle,"//17
+ "memberNationality,"//18
+ "memberPhone1,"//19
+ "memberPhone2,"//20
+ "memberStatus,"//21
+ "memberMaritalStatus,"//22
+ "memberGender,"//23
+ "memberSocialMedia,"//24
+ "memberEmail,"//25
+ "memberPassword,"//26
+ "memberSecurityQuestion,"//27
+ "memberSecurityQuestionAnswer,"//28
+ "memberTransactionCount,"//29
+ "memberLoginCount,"//30
+ "memberPreferredPaymentType,"//31
+ "memberSecurityId,"//32
+ "memberNickname,"//33
+ "agentId,)"//34
+ "VALUES (?,?,?,?,?,?,?,?,?,?,"
+"?,?,?,?,?,?,?,?,?,?,"
+"?,?,?,?,?,?,?,?,?,?,"
+"?,?,?,?)",
new String[] { "memberId" }
);
ps.setString(1, users.getMemberName());
ps.setString(2, users.getMemberSurname());
ps.setString(3, users.getMemberAddress());
ps.setString(4, users.getMemberTown());
ps.setString(5, users.getMemberCounty());
ps.setString(6, users.getMemberPostCode());
ps.setString(7, users.getMemberCountry());
ps.setString(8, users.getMemberDocumentType());
ps.setString(9, users.getMemberIdNumber());
ps.setString(10, users.getMemberExpiryDate());
ps.setString(11, users.getMemberIssuedBy());
ps.setString(12, users.getMemberIssuePlace());
ps.setInt(13, users.getMemberDayOfBirth());
ps.setInt(14, users.getMemberMonthOfBirth());
ps.setInt(15, users.getMemberYearOfBirth());
ps.setString(16, users.getMemberJobTitle());
ps.setString(17, users.getMemberNationality());
ps.setInt(18, users.getMemberPhone1());
ps.setInt(19, users.getMemberPhone2());
ps.setString(20, users.getMemberStatus());
ps.setString(21, users.getMemberMaritalStatus());
ps.setString(22, users.getMemberGender());
ps.setString(23, users.getMemberSocialMedia());
ps.setString(24, users.getMemberEmail());
ps.setString(25, users.getMemberPassword());
ps.setString(26, users.getMemberSecurityQuestion());
ps.setString(27, users.getMemberSecurityQuestionAnswer());
ps.setInt(28, users.getMemberTransactionCount());
ps.setInt(29, users.getMemberLoginCount());
ps.setString(30, users.getMemberPreferredPaymentType());
ps.setString(31, users.getMemberSecurityId());
ps.setString(32, users.getMemberNickname());
ps.setInt(33, users.getAgentId());
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
rs.next();
int memberId = rs.getInt(1);
users.setMemberId(memberId);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
} finally {
DbConnector.close(c);
}
return users;
}
//Javascript
var rootURL = "http://localhost:8080/{database_name}/list/users";
function addUser() {
var memberId = $('#memberId').val();
var memberName = $('#memberName').val();
var memberSurname = $('#memberSurname').val();
var memberAddress = $('#memberAddress').val();
var memberTown = $('#memberTown').val();
var memberCounty = $('#memberCounty').val();
var memberPostCode = $('#memberPostCode').val();
var memberCountry = $('#memberCountry').val();
var memberDocumentType = $('#memberDocumentType').val();
var memberIdNumber = $('#memberIdNumber').val();
var memberExpiryDate = $('#memberExpiryDate').val();
var memberIssuedBy = $('#memberIssuedBy').val();
var memberIssuePlace = $('#memberIssuePlace').val();
var memberDayOfBirth = $('#memberDayOfBirth').val();
var memberMonthOfBirth = $('#memberMonthOfBirth').val();
var memberYearOfBirth = $('#memberYearOfBirth').val();
var memberJobTitle = $('#memberJobTitle').val();
var memberNationality = $('#memberNationality').val();
var memberPhone1 = $('#memberPhone1').val();
var memberPhone2 = $('#memberPhone2').val();
var memberStatus = $('#memberStatus').val();
var memberMaritalStatus = $('#memberMaritalStatus').val();
var memberGender = $('#memberGender').val();
var memberSocialMedia = $('#memberSocialMedia').val();
var memberEmail = $('#memberEmail').val();
var memberPassword = $('#memberPassword').val();
var memberSecurityQuestion = $('#memberSecurityQuestion').val();
var memberSecurityQuestionAnswer = $('#memberSecurityQuestionAnswer').val();
var memberTransactionCount = $('#memberTransactionCount').val();
var memberLoginCount = $('#memberLoginCount').val();
var memberPreferredPaymentType = $('#memberPreferredPaymentType').val();
var memberSecurityId = $('#memberSecurityId').val();
var memberNickname = $('#memberNickname').val();
var agentId = $('#agentId').val();
} else {
console.log('Adding New User... ');
$.ajax({
type : "POST",
url : rootURL,
data : JSON.stringify({
"memberName" : memberName,
"memberSurname" : memberSurname,
"memberAddress": memberAddress,
"memberTown": memberTown,
"memberCounty": memberCounty,
"memberPostCode" : memberPostCode,
"memberCountry": memberCountry,
"memberDocumentType": memberDocumentType,
"memberIdNumber": memberIdNumber,
"memberExpiryDate": memberExpiryDate,
"memberIssuedBy": memberIssuedBy,
"memberIssuePlace": memberIssuePlace,
"memberDayOfBirth" : memberDayOfBirth,
"memberMonthOfBirth" : memberMonthOfBirth,
"memberYearOfBirth" : memberYearOfBirth,
"memberJobTitle": memberJobTitle,
"memberNationality": memberNationality,
"memberPhone1" : memberPhone1,
"memberPhone2" : memberPhone2,
"memberStatus": memberStatus,
"memberMaritalStatus" : memberMaritalStatus,
"memberGender": memberGender,
"memberSocialMedia" : memberSocialMedia,
"memberEmail" : memberEmail,
"memberPassword": memberPassword,
"memberSecurityQuestion": memberSecurityQuestion,
"memberSecurityQuestionAnswer" : memberSecurityQuestionAnswer,
"memberTransactionCount" : memberTransactionCount,
"memberLoginCount": memberLoginCount,
"memberPreferredPaymentType" : memberPreferredPaymentType,
"memberSecurityId": memberSecurityId,
"memberNickname" : memberNickname,
"agentId" : agentId
}),
dataType : "json",
contentType : "application/json;charset=utf-8",
success : function(data, textStatus, jqXHR) {
alert('User created successfully');
$('#memberId').val(data.id);
},
error : function(jqXHR, textStatus, errorThrown) {
console.log('Registration error...');
}
});
}
}
答案1
得分: 0
问题出在你很可能在准备语句中作为第一个参数添加了一个 id，+ "memberId,"//1，但是你没有紧接着在代码区域内使用 ps.setString 或 ps.setInt 来绑定该参数的值。
情境1：自动生成的 ID
移除 + "memberId,"//1。
情境2：手动设置 ID（通常使用 UUID/GUID 或类似方法）
你应该保留 + "memberId,"//1，但要确保在适当索引的位置添加 ps.setString(1, users.getMemberId()) 或 ps.setInt(1, users.getMemberId())（*假设 getMemberId() 存在），具体取决于 ID 的类型。在其下方绑定的参数需要将它们的索引递增1。
这样就可以解决标题中的错误。然而，我和其他人在评论中提到代码中可能还有其他问题，一旦解决了这个特定错误，可能会出现不同的错误。
英文:
The problem appears that you likely added an id as the first parameter in your prepare statement + "memberId,"//1 but you did not follow up by binding the value of this parameter below in the region with ps.setString an ps.setInt.
Scenario 1: Auto Generated Ids
Remove + "memberId,"//1.
Scenario 2: Manually set ids (often with UUID/GUID and similar)
You will want to keep + "memberId,"//1 but ensure that you add the appropriately indexed ps.setString(1, users.getMemberId()) or ps.setInt(1, users.getMemberId()) (assuming that getMemberId() exists) depending on the type of the id. The parameters being bound below it need to have their index incremented by 1.
That will get rid of the error in the title. However, myself and others have mentioned in the comments that there are other problems with the code that will likely lead to different errors once this particular error gets resolved.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论