Database

Re: Inserting a new record into 2 tables at the same time

17-Mar-14 19:48

You either do this as a 2 statement operation or you create a stored procedure - the SP path is by far the simplest an most robust.

I presume you have an IDENTITY field the Movie table. So when a record is inserted the system puts the new ID into a variable called @Scope_Identity you can get the value from there.

I used stored procs for EVERYTHING, the UI/Service NEVER talks directly to a table/view, so I have a code generator to build my CRUD procs. So I am not going to be able to help with string inserts Blush | :O

Never underestimate the power of human stupidity
RAH

jkirkerx19-Mar-14 13:21

jkirkerx

19-Mar-14 13:21

Well thanks for the confirmation on that. I guess I'll take a stab on stored procedures then.

Re: Inserting a new record into 2 tables at the same time

Mycroft Holmes19-Mar-14 14:10

Re: Inserting a new record into 2 tables at the same time

19-Mar-14 14:10

So here is some pseudo code for that.

Declare incoming variables
Insert into student table
Get Scope_Identity into a variable
Check for existing course - this may be done in the client
Insert missing course
Get Scope_Identity into another variable
Insert variables into link table (this is a many to many table linking the course and the student records)
Return the student record - I do this so the client has all the information about the student including the ID value.

Never underestimate the power of human stupidity
RAH

jkirkerx19-Mar-14 16:26

jkirkerx

19-Mar-14 16:26

OK
I can swap out the words like student on that

I'll write one and post it to see how I did on it.

Thanks!

Structure Database

sbangani15-Mar-14 5:29

sbangani

15-Mar-14 5:29

How do I structure my database tables so that it can allow many users to login simultaneously in my chat Application. While testing it in my IE and Crome in my PC.
Thanks.

Richard Andrew x6415-Mar-14 10:55

Richard Andrew x64

15-Mar-14 10:55

What is it about the structure of the database that would prevent many users from logging in simultaneously? What problem are you facing?

The difficult we do right away...
...the impossible takes slightly longer.

sbangani16-Mar-14 12:05

sbangani

16-Mar-14 12:05

I'm creating a chat App that will run online its includes signalR for real time messages. So now I'm trying to check if its can allow multiple users to log in in the in the same time using two browsers on my computer. when I check that I found that its override the first user that has being logged in first.

Eddy Vluggen16-Mar-14 0:51

Eddy Vluggen

16-Mar-14 0:51

sbangani wrote:
How do I structure my database tables so that it can allow many users to login simultaneously

A database-server is designed to cater to multiple clients.

Simply do not use a desktop database (like MSAccess) and it should work out-of-the-box.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Get Result according to comma seperated sequence

jschell17-Mar-14 8:37

jschell

17-Mar-14 8:37

I doubt you can design a database that doesn't allow multiple users.

You can certainly design/implement an application that uses a database which precludes that.

Rupesh Kumar Swami15-Mar-14 4:04

Rupesh Kumar Swami

15-Mar-14 4:04

Hi All,
I want to get the result according to comma seperated value in field. First let me explain the table and their data

I have one table which contain following fields and data

file sequence || Cat_Id || Sequence

4,1,5,8,9,10,20 || 41 || 2
18,17,19,24,25 || 48 || 9
19,21,22,23,24,25,26 || 49 || 10

above is the sample data structure which contains the data in above format. Now I want to create a query which rerurn the value of file_sequence field in following format

4,1,5,8,9,10,20,18,17,19,24,25,21,22,26

Here one thing Which I want to notify
1. above table is result of a query which is sorted on the base of Sequence
2. Now we pick the result of field_sequence from the start in the same order.
3. now we pick the 4,1,5,8,9,10,20 then 18,17,19,24,25 and then 21,22,26
4. duplicate value removed (in above example 19,24,25 are duplicate)

so in this way I want to get the comma seperated value
4,1,5,8,9,10,20,18,17,19,24,25,21,22,26

Please suggest/guide how can i achieve this result

rup28aug@yahoo.co.in
Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

Company - ISOL, India

Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Re: Get Result according to comma seperated sequence

Jörgen Andersson15-Mar-14 9:07

Jörgen Andersson

15-Mar-14 9:07

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[^]

get day wise records seperated by id

Member 1026351913-Mar-14 20:23

Member 10263519

13-Mar-14 20:23

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.

Re: get day wise records seperated by id

Mycroft Holmes14-Mar-14 0:50

Re: get day wise records seperated by id

14-Mar-14 0:50

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

Bernhard Hiller14-Mar-14 1:12

Bernhard Hiller

14-Mar-14 1:12

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

query for monthly report

Member 1026351913-Mar-14 18:23

Member 10263519

13-Mar-14 18:23

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

Re: query for monthly report

Mycroft Holmes13-Mar-14 19:58

Re: query for monthly report

13-Mar-14 19:58

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

Member 1026351913-Mar-14 20:11

Member 10263519

13-Mar-14 20:11

thanks

how to insert data in foreign key using jsp

Faisal_Abdullah13-Mar-14 10:43

Faisal_Abdullah

13-Mar-14 10:43

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

_AK_13-Mar-14 10:52

_AK_

13-Mar-14 10:52

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.

Faisal_Abdullah14-Mar-14 11:29

Faisal_Abdullah

14-Mar-14 11:29

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.

_AK_19-Mar-14 6:00

_AK_

19-Mar-14 6:00

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.

Jörgen Andersson19-Mar-14 10:30

Jörgen Andersson

19-Mar-14 10:30

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[^]

Mycroft Holmes13-Mar-14 14:12

13-Mar-14 14:12

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