wbmorapedi
asked on
Database and Servlets To Msterjev (Values in my Database for the insertion problem )
OrderNumber = autonumber;
ItemCode = text;
Category = text;
and all the other fields have a text value except the currency . There are 8fields and I only want to send 6 of them to the database and leave 2(i.e. OrderNumber,Status )
>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>
I still have a problem with the insertion of other values where the primary key is auto number in the database. The thing is if I assign as below I get the message
You tried to assign the Null value to a variable that is not a Variant data type.
AND
If I leave them blank this is what I get
Syntax error in INSERT INTO statement.
but the insertin part is OKay right.
Could you please help with the sample code of doing this.
String SQL = "INSERT INTO KeepOrdersTable ItemCode,OrderNumber,Categ ory,Animal Type,AgeGr oup,UnitPr ice,Quanti ty,Status VALUES(?,?,?,?,?,?,?,?)";
try{
PreparedStatement stmt = conn.prepareStatement(SQL) ;
stmt.setString(1, null);
stmt.setString(2, ItemCode);
stmt.setString(3, Category);
stmt.setString(4, AnimalType);
stmt.setString(5, AgeGroup);
stmt.setString(6, UnitPrice);
stmt.setString(7, Quantity);
stmt.setString(8, null);
stmt.executeUpdate();
}
>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>
ItemCode = text;
Category = text;
and all the other fields have a text value except the currency . There are 8fields and I only want to send 6 of them to the database and leave 2(i.e. OrderNumber,Status )
>>>>>>>>>>>>>>>>>>>>>>>>>>
I still have a problem with the insertion of other values where the primary key is auto number in the database. The thing is if I assign as below I get the message
You tried to assign the Null value to a variable that is not a Variant data type.
AND
If I leave them blank this is what I get
Syntax error in INSERT INTO statement.
but the insertin part is OKay right.
Could you please help with the sample code of doing this.
String SQL = "INSERT INTO KeepOrdersTable ItemCode,OrderNumber,Categ
try{
PreparedStatement stmt = conn.prepareStatement(SQL)
stmt.setString(1, null);
stmt.setString(2, ItemCode);
stmt.setString(3, Category);
stmt.setString(4, AnimalType);
stmt.setString(5, AgeGroup);
stmt.setString(6, UnitPrice);
stmt.setString(7, Quantity);
stmt.setString(8, null);
stmt.executeUpdate();
}
>>>>>>>>>>>>>>>>>>>>>>>>>>
>DON'T SEND null VALUES INTO THE INSERT STATEMENT!
Curious as to why not? I think I do that someplaces without problems.. but I use setNull method.
CJ
Curious as to why not? I think I do that someplaces without problems.. but I use setNull method.
CJ
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For example:
field1: integer; default value: 0;
etc.
The rule is you never insert autoincrement primary key field (I've alredy answered this question to you). Open MS Access and if you want some field to accept null values check it (but you never send null values into the INSERT statements, just omit this field from INSERT statement). Or, specify some default value for some field if you don't want to use this field into INSERT statement.
DON'T SEND null VALUES INTO THE INSERT STATEMENT!