0

I've made database of relational tables in access relation of tables are given by following image enter image description here

When i'm trying to insert data to table using following query

 qrY = "INSERT INTO `reg_table` (`Regn_ID`, `Full_name`, `Addr`, `City`, `PIN Code`, `Email`, `Contact_No`, `Fee`, `payment_type`, `checkordraft_No`, `regn_Date`, `conTctID`) VALUES " qrY += "(NULL, " qrY += "'Bb'," qrY += "'Bb'," qrY += "'Bb'," qrY += "'12312'," qrY += "'Bb'," qrY += "'Bb'," qrY += "'Bb'," qrY += "'Bb'," qrY += "'Bb'," qrY += "'18-04-2014'," qrY += "'1'" qrY += " )""

it gives me error "You tried to assign the Null value to a variable that is not a Variant data type." I don't know whats the error and how to mess with so please give me any solution . . . .

Or Just Tell Is it causing because of relational tables or datatypes errors

Improve this question
4
  • Any solution: read about column types supported by ms access and ALWAYS use parameters (any ado.net example should have those), instead of string concatenation for query values. Commented Jan 18, 2014 at 9:05
  • Is Regn_ID an autonumber field? Commented Jan 18, 2014 at 9:08
  • Yes @Steve it's autoincrement Commented Jan 18, 2014 at 9:12
  • Then do not set any value for it and remove the field name in the fieldlist. An autonumber is generated directly by the database code and you should not try to set it to any value Commented Jan 18, 2014 at 9:18

3 Answers 3

Reset to default
1

Regn_ID is Auto increment field which causing the error.

Try like this

qrY = "INSERT INTO `reg_table` (`Full_name`, `Addr`, `City`, `PIN Code`, `Email`, `Contact_No`, `Fee`, `payment_type`, `checkordraft_No`, `regn_Date`, `conTctID`) VALUES " qrY += "('Bb'," qrY += "'Bb'," qrY += "'Bb'," qrY += "'12312'," qrY += "'Bb'," qrY += "'Bb'," qrY += "'Bb'," qrY += "'Bb'," qrY += "'Bb'," qrY += "'18-04-2014'," qrY += "'1'" qrY += " )""
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

If the Regn_ID is an autonumber then do not set any value for it and remove the field name in the fieldlist. An autonumber is generated directly by the database code and you should not try to set it to any value.

However I think that your code has other problems because you should not pass strings where Integer or dates are expected.

The correct syntax should be something like this

qrY = "INSERT INTO reg_table (Full_name, Addr, City, PIN Code, Email, Contact_No," & _ "Fee, payment_type, checkordraft_No, regn_Date, conTctID) VALUES " & _ "'Bb','Bb','Bb',12312,'Bb','Bb','Bb','Bb','Bb',#18-04-2014#,1)"

But this could still be incorrect because I really can't guess what is the datatype of Pin_Code, Contact_No, Fee, payment_type, checkordraft_No. If they are not strings then you don't enclose the value in single quotes.

However this example is really contrieved because you pass test values but in a real work scenario you have variables for the values and you need to use a parameterized query approach where every parameter should be set to the correct datatype required by the database.

Improve this answer

1 Comment

But regn_date is a string in the database?
1

The solutions from Steve and Vignesh are correct, but I would also like to add that you cannot set a Primary Key to NULL, ever.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.