how to insert data in foreign key using jsp - Database Discussion Boards

Maybe not the answer you're looking for, but your table doesn't even conform to the first normal form.
Normalize it, for example like this:

SQL

CREATE TABLE MyTable
	([fileid] int, [filesequence] int, [catid] int, [catsequence] int)
;
	
INSERT INTO MyTable
	([fileid], [filesequence], [catid], [catsequence])
VALUES
	(4, 1, 41, 2),
	(1, 2, 41, 2),
	(5, 3, 41, 2),
	(8, 4, 41, 2),
	(9, 5, 41, 2),
	(10, 6, 41, 2),
	(20, 7, 41, 2),
	(18, 1, 48, 9),
	(17, 2, 48, 9),
	(19, 3, 48, 9),
	(24, 4, 48, 9),
	(25, 5, 48, 9),
	(19, 1, 49, 10),
	(21, 2, 49, 10),
	(22, 3, 49, 10),
	(23, 4, 49, 10),
	(24, 5, 49, 10),
	(25, 6, 49, 10),
	(26, 7, 49, 10)
;

Now you can Query it like this:

SQL

select  fileid
from    mytable
group by fileid
order by min(catsequence),min(filesequence)

And the result will look like this:

It's easy enough to make that into a comma separated file if you would really need to.
But that task actually belongs to another layer than the database.

Wrong is evil and must be defeated.
- Jeff Ello[^]

hi,

am using query as
Select machine_id as MachineID,item_name as Name,SUM(item_quantity) as Quantity,SUM(billed_amount) as BilledAmount,SUM(received_amount) as ReceivedAmount from transaction where DATE(paid_date)='" + rdate + "'Group by machine_id,item_name

here rdate , am collecting from UI,

string rdate = dateTimePicker1.Value.ToString("yyyy-MM-dd");

MachineID Name Quantity BilledAmount ReceivedAmount
V0001 petrol 61 244 244
V0002 Diesel 186 726 726
V0002 petrol 155 605 605
Total 402 1575 1575

But i want for every machine id ,total row

V0001 - - - -
total - - -
V0001 - - - -
total - - -

modified 14-Mar-14 2:33am.

You are trying to make the database do the work of the UI, wrong tool for the job. You either need to use a reporting tool that will format the totals or do it yourself. This depends on your consumer.

Good to see you asking a more sensible question.
Do some research into parameterised queries and "Little Bobby Tables". One will make you life easier and the other may save you from disaster.

Never underestimate the power of human stupidity
RAH

Your GUI is written in C#, isn't it? So I'd suggest to do that extra calculation and formatting there.

hi,

i need to get data from database on monthly basis.i need to select month from UI, based on that , i want the records in database.
am using Mysql database in c#,
any suggestions

Member 10263519 wrote:
any suggestions

Sure, craft a select query either as a stored procedure or a string query (remember to use parameters), make a connection to the database, and either execute your query string orthe stored procedure. Bind the results to your display control.

Just what are you expecting as an answer to a request for suggestions?

Never underestimate the power of human stupidity
RAH

thanks

XML

<%--
    Document   : check
    Created on : Mar 10, 2014, 7:52:19 PM
    Author     : Sahara
--%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Student Information</title>
    </head>
    <body>
        <h3><%
            String nameHeader = request.getParameter("name");
            out.print(nameHeader);
        %></h3>
         <%@page import = "java.sql.*" %>
        <%@page import = "java.io.*" %>
        <%@page import = "com.mysql.jdbc.Driver" %>
        <?xml version="1.0" ?>
        <%
            String name = request.getParameter("name");
            String subject = request.getParameter("subject");
            String roll = request.getParameter("roll");
           // int roll = Integer.parseInt((String)request.getAttribute("roll"));
            String Student = "INSERT INTO student(roll,name) VALUES('"+roll+"','"+name+"')";
            String Subject = "INSERT INTO subject(name) VALUES('"+subject+"')";
            try{
            Connection connection = null;
            Statement statement = null;
            ResultSet result = null;
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/normalization","root","");
            statement = connection.createStatement();
            statement.executeUpdate(Student);
            statement.executeUpdate(Subject);
            }catch(SQLException e){
                out.println("Error: Connection To Database...");
                out.println("Error:"+e);
            }
            %>
    </body>
</html>

Error:java.sql.SQLException: Field 'idfk' doesn't have a default value
it is the error which i am facing it from last 3 months. I have created the tables fully correct

Faisal Abdullah

Faisal_Abdullah wrote:
Error:java.sql.SQLException: Field 'idfk' doesn't have a default value

It looks like you have a column which requires data and you are not providing it. Which table has the column "dfk"?

.AK.

dear i have two tables one is student and second is subject. the student is having columns: id(primary key), roll, name. and the subject table is having columns: id(primary key), idfk(foreign key), name.
The problem is that which i wrote query in my code it is fully correct because the student table is being update, but the subject table is not being update due to foreign key of the student which is idfk.
So my question is that. which query should i write in my jsp code, which when execute and it should update the table without any error.

Faisal_Abdullah wrote:
the student is having columns: id(primary key), roll, name. and the subject
table is having columns: id(primary key), idfk(foreign key), name

What is the parent for "idfk(foreign key)" key. You need to include this ID in your INSERT query if something like below:

"INSERT INTO subject(idfk, name) VALUES('"+value+"','"+subject+"')";

.AK.

If you're using an AUTO_INCREMENT column in MySQL you can retrieve that value using Last_Insert_ID[^] and then use that ID as fkid in the insert to the subject table.

Wrong is evil and must be defeated.
- Jeff Ello[^]

By the error I suspect you need to set the ID field in Student to an IDENTITY (assuming sql server).

You seem to be inserting a new subject for every student. I suspect your table design is crap and needs to be redone.

Never underestimate the power of human stupidity
RAH

dear friend i am using mysql and oracle. would you create the tables and send me their code here. Please because i am searching this problem solution from last 3 months. Please i humbly request you help me.

yes you are absolutly right, but how i can do it in oracle and mysql?

Faisal_Abdullah wrote:
yes you are absolutly right, but how i can do it in oracle and mysql?

Get a book, follow some examples, learn, no one here is going to do your work for you.

Never underestimate the power of human stupidity
RAH

Your code is subject to SQL Injection[^]. You should fix that ASAP.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Hi,

I want to ask about indexing.

I have employee table and department table with employee_id and department_id primary keys. Also I have department_id in the employee table.

Do I set the employee.department_id as a foreign key for a better performance?

Thanks,
Jassim

Technology News @ www.JassimRahma.com

modified 13-Mar-14 15:58pm.

Creating a foreign key is required for refrential integrity and is normally a good practice. It is also true that a foreign key has a say inperformance. To make it better what you can do is to create index on the foreign keys.

.AK.

I did indexed it but do I still need (or is it better) to declare it as foreign key?

Technology News @ www.JassimRahma.com

As I mentioned creating a foreign key constraint is required for refrential integrety and normally is a good practice. It do have an impact on performance which can be reduced by creating an index. If you craete the index on foreign key it will reduce the lookups.

.AK.

so declaring it as foreign key is a good practice and will have an impact on the performance but after declaring it do you still recommend to index it or declaring is enough?

Technology News @ www.JassimRahma.com

Creating a foreign key is not mandatory but is advised to create. Here[^] is a link which tells about the importance of it.
Now here[^] is another article which tells about refrential integrity and performance degradation which might give you a bit more idea about this.

.AK.

Great

Thanks

Technology News @ www.JassimRahma.com

You are welcome.

.AK.