Insert data on SQL

Question

I'm trying to insert data to my database on sql and after I run my program an error occured and says :

[Microsoft][ODBC SQL Server Driver][SQL Server]Insert Error: Column name or number of supplied values does not match table definition.

how can I fix it? can anyone tell me what is wrong with my codes?

here is the codes :

private void btnInsertActionPerformed(java.awt.event.ActionEvent evt) {
        try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        String url = "jdbc:odbc:****";
        String user = "****";
        String pass = "****";
        Connection connection = DriverManager.getConnection(url, user, pass);
        Statement statement = connection.createStatement();
        String pIDNo = txtPatientID.getText();
        String pFName = txtpFName.getText();
        String pLName = txtpLName.getText();
        String pMI = txtpMI.getText();

        String sql = "INSERT INTO dbo.Patients VALUES (" + 
                (pIDNo)+",'"+(pFName)+"','"+(pLName)+"','"+(pMI)+"')";
        statement.executeUpdate(sql);
        JOptionPane.showMessageDialog(rootPane, "Patient Added!");
        }catch (Exception ex) {
            JOptionPane.showMessageDialog(this, ex.getMessage());
            System.exit(1);
        }
    }

Here is the table info :

PatientTable

You have to mention the column names also before assigning the values in the query — Jibran Khan
– Jibran Khan, Commented Apr 4, 2013 at 5:17
are you sure pIDNo column of Patients table is String and not an unique id which is auto increment?. — subodh
– subodh, Commented Apr 4, 2013 at 5:20
actually sir subodh the table that your looking was made by my boss, he just asked me to do a program that can manipulate that thing so everything in that table is 'as is'. — Crystal Maiden
– Crystal Maiden, Commented Apr 4, 2013 at 5:23

John Woo · Accepted Answer · 2013-04-04 05:24:47Z

4

When using implicit INSERT statement, the number of values supplied must be equal to the total cumber of columns in the table, eg

INSERT INTO tableName VALUES (val1, val2)

In the query above, the expected number of columns are two because you have supplied two values.

When there is an incrementing field, you must pass null value on it. But if not, you need to specify the column names for which the values will be inserted, eg

INSERT INTO tableName (col1, col2) VALUES (val1, val2)

As a side note, values must be parameterized to avoid from sql injection. See the article below:

Java PreparedStatement

UPDATE 1

Specifying of columns names is required since you are not inserting values on all columns.

INSERT INTO Patients (pIDNo, pFName, pLName, pMI)
VALUES (...)

edited Apr 4, 2013 at 5:24

answered Apr 4, 2013 at 5:16

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Crystal Maiden Over a year ago

I will study the Java PreparedStatement thing, thanks! Glad to know that there is some PH here...

John Woo Over a year ago

@CrystalMaiden hehe..hey what are the columns in your table Patient?

Crystal Maiden Over a year ago

I post it above sir J W can you please take a look at it and tell me what is wrong?

Crystal Maiden Over a year ago

I have created a program just like this but in different langauge. I see that it's the same as your update answer hmmm quite hard to do though, let me re-code my program and tell you what will happen sir JW.

Crystal Maiden Over a year ago

String sql = "INSERT INTO dbo.Patients" + "(pIDNo,pLName,pFName,pMI)" + "VALUES()"; what is next after this?

|

Bryan Wong · Accepted Answer · 2013-04-04 05:16:09Z

1

What is the type of pIDNo, pFName, pLName, pMI?

Change type to varchar to receive text input.

answered Apr 4, 2013 at 5:16

Bryan Wong

6332 gold badges7 silver badges22 bronze badges

1 Comment

Danjor Over a year ago

I'm sorry if I sound like newbie but I am, can you teach me how to do it?

Rob · Accepted Answer · 2013-04-04 05:16:32Z

0

You probably have the ID field set to auto so should not be passing it in the insert? You should list the fields in the insert statement, e.g. INSERT INTO dbo.Patients(column1, column2...). See here.

answered Apr 4, 2013 at 5:16

Rob

11.7k7 gold badges42 silver badges60 bronze badges

Collectives™ on Stack Overflow

Insert data on SQL

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related