Database

GuyThiebaut12-Feb-15 5:18

12-Feb-15 5:18

Your table aliases confused me and they may have confused MySQL too, try:

create view vb as SELECT t1.id, t1.variety, (SELECT COUNT(*) FROM t WHERE id < t1.id) +1 AS NUM FROM t t1

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

KipkoechE12-Feb-15 5:23

12-Feb-15 5:23

thanks so much for taking your time to kind me...God's Blessings. i Have learned a lot

GuyThiebaut12-Feb-15 5:28

12-Feb-15 5:28

Less of the 'God' please although I am grateful for the Blessings part Smile | :)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

KipkoechE12-Feb-15 6:05

Add allowance to employee table is wrong or true according to my case

12-Feb-15 6:05

finally the ranking is on ascending order and need it to be in such a way that the highest marks/value takes position 1:
tried this:
create view View_Positioning as SELECT t1.`SAdmNo`, t1.`YearAdmitted`,t1.`TermAdmitted`,t1.`CLASSAdmitted` ,t1.`StreamAdmitted`,t1.`OutOfMarks`,t1.`ENGLISH`,t1.`KISWAHILI`,t1.`MATHEMATICS`,t1.`SCIENCE`,t1.`SSR`,t1.`Average`,(SELECT COUNT(*) FROM total_termaverage_view WHERE `Average` IS NOT NULL AND`Average` < t1.`Average`) +1 AS Position FROM total_termaverage_view t1 order by Average ASC ;

which gives:
Average position
324 1
345 2

ahmed_sa11-Feb-15 21:52

ahmed_sa

11-Feb-15 21:52

Hi guys I have problem i need to make ERD relation entity
between employee and allowance
Employee table
Name
address
Basic Salary
Bonus

Allowance table
House rent
Food Allowance
Moving Allowance

Basic Salary is monthly and fixed
Bonus is monthly and fixed
food allowance is monthly and fixed for married employee
House rent is monthly and fixed for some employee and some employee take house rent two time in year every 6 month
every employee married take 3 months salary from basic salary in year
suppose i m married and i take basic salary 5000
i will take rent 5000 x 3=15000/12=1250 monthly
some employee take rent every half year meaning every 6 month
meaning 15000/2=7500

My question according to my case above
Which is best put allowance in table allowance or put allowance(food,housing,moving)
in employee table and what relation between two tables

Re: Add allowance to employee table is wrong or true according to my case

David Mujica12-Feb-15 3:49

David Mujica

12-Feb-15 3:49

Some things to consider in your design ...

How about a design like this:

employee Key
Allowance Code
Effective_Start_Date
Effective_End_Date
Allowance Rate

This may not be complete, but what it does for you is that it allows for many types of allowance codes to be associated with an employee, each allowance code has an effective date range with an associated rate. This also allows for 2 employee who have the same allowance code to be compensated differently.

You would then need another table which translates the Allowance Code to a description.

I use a similar approach when dealing with prices, you have the history of the prices over time ...

Just a thought.
Good luck. Thumbs Up | :thumbsup:

Re: Add allowance to employee table is wrong or true according to my case

Mycroft Holmes12-Feb-15 12:01

Mycroft Holmes

12-Feb-15 12:01

What David has described is a many to many relationship, an allowance can be used by many employees and an employee can have many allowances. This is the correct data structure for the requirements.

Never underestimate the power of human stupidity
RAH

Triggers

Member 1116162511-Feb-15 19:19

Member 11161625

11-Feb-15 19:19

HI,

EveryBody,

I am new in sql server,i want to know what is trigger? and What are the different types of trigger and What it's use with sample example???Can you provide me a link where there are good examples about triggers???

Thanks...
IN Advance...

Re: Triggers

Kornfeld Eliyahu Peter11-Feb-15 19:37

Kornfeld Eliyahu Peter

11-Feb-15 19:37

All you need is Google...
There are endless articles about triggers...Start here:
http://en.wikipedia.org/wiki/Database_trigger[^]
Overview of SQL Server database Triggers[^]

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Re: Triggers

John C Rayan12-Feb-15 3:32

John C Rayan

12-Feb-15 3:32

Have a look at our code project article
Overview of SQL Server database Triggers[^]

RANKING IN VB.NET MYSQL

KipkoechE10-Feb-15 22:27

10-Feb-15 22:27

I have Records which are supposed to be assigned Position/Be Ranked e.g
RegNo Marks Position
1 300
3 301
4 403
5 345

My Problem is How to rank/give position according to marks attained such that the results will be displayed as:
RegNo Marks Position
1 300 4
3 301 3
4 403 1
5 345 2
using VB.NET MYSQL

Richard MacCutchan11-Feb-15 0:06

Richard MacCutchan

11-Feb-15 0:06

KipkoechE wrote:
how i will generate the position based on marks

Probably by some calculation and sorting. But if you want some more useful help then please edit your question and explain the problem in proper detail. Also, is this really a Database question or a VB one?

KipkoechE11-Feb-15 1:32

11-Feb-15 1:32

Richard MacCutchan11-Feb-15 1:36

Richard MacCutchan

11-Feb-15 1:36

You don't need the position field as that depends on the Marks. Just select your data using the ORDER BY clause[^] to sort them.

KipkoechE11-Feb-15 1:41

11-Feb-15 1:41

but that will only sort it...What about the position

GuyThiebaut11-Feb-15 2:11

11-Feb-15 2:11

A bit of google fu[^]gave me this:

SQL

SELECT    
RegNo,
Marks,
@Position := @Position + 1 AS Position 
FROM results , (SELECT @Position:= 0) r
ORDER BY  Marks desc;

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

modified 11-Feb-15 9:01am.

KipkoechE11-Feb-15 2:23

11-Feb-15 2:23

it worked very well...thanks alot

GuyThiebaut11-Feb-15 2:28

VB.NET ITEXTSHARP PDF REPORTS

11-Feb-15 2:28

Glad it worked Thumbs Up | :thumbsup:

I used the following google fu[^]

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

KipkoechE10-Feb-15 6:38

MySQL for MFC application

10-Feb-15 6:38

am creating pdf reports in vb.net Mysql Database but have problems retrieving more than more record...ANy suggestion Here is the code i have tried:

#query database

getClientDataFromTable()
#Add Another Table Here

Dim widths() As Single = {50, 70, 100, 100, 100, 100}
Dim ColumnHeaders() As String = {"No.", "ACCNO", "NAME", "COUNTY", "OCCUPATION", "PHONE"}
Dim ColumnDataExample() As String = {CUSTNO, CUSTName, COUNTY, DATEREGISTERED, PHONE}

Dim tbOther As New PdfPTable(widths)
With tbOther
.TotalWidth = 520
.LockedWidth = True ' lock width to prevent spreading out
.HorizontalAlignment = 1 ' centre align content
.SpacingAfter = 0
.SpacingBefore = 10
End With

'#Add Headers
For i As Integer = 0 To UBound(ColumnHeaders)
cell = New PdfPCell(New Phrase(ColumnHeaders(i), Fontwhite))
cell.BackgroundColor = New BaseColor(20, 120, 120)
cell.BorderColor = New BaseColor(0, 0, 0)
If (i > 3) Then
cell.HorizontalAlignment = 2
Else
cell.HorizontalAlignment = 0
End If
tbOther.AddCell(cell)
Next i

'# Add Data
For j As Integer = 0 To 3 - 1
cell = New PdfPCell(New Phrase((j + 1).ToString(), FontNormal))
tbOther.AddCell(cell)
For i As Integer = 0 To UBound(ColumnDataExample)
cell = New PdfPCell(New Phrase(ColumnDataExample(i), FontNormal))
cell.BorderColor = New BaseColor(20, 120, 120)
If (i > 3) Then
cell.HorizontalAlignment = 2
Else
cell.HorizontalAlignment = 0
End If
tbOther.AddCell(cell)
Next i
Next j

'#Add A Blank Line ---------It spans across all 5 columns and borderless i.e border=0
cell = New PdfPCell(New Phrase())
cell.Border = 0
cell.FixedHeight = 30
cell.Colspan = 6
tbOther.AddCell(cell)
'# Add A Separator Line
Dim separate As New PdfPCell()
separate.FixedHeight = 7 'Set Fixed Height To 7
separate.BorderWidthBottom = 4
'separate.BorderWidthTop = 0
separate.BorderColorTop = BaseColor.GREEN
separate.Colspan = 6 '#Use This to span across 3 columns
tbOther.AddCell(separate)
'add line
cell = New PdfPCell(New Phrase())
cell.Border = 2
cell.Colspan = 0
tbOther.AddCell(cell)

cell = New PdfPCell(New Phrase())
cell.Border = 2
cell.Colspan = 0
tbOther.AddCell(cell)

'# Add A line with a label and and line after
cell = New PdfPCell(New Phrase("Signature:", FontBold))
cell.Border = 0
cell.Colspan = 0
tbOther.AddCell(cell)

cell = New PdfPCell(New Phrase())
cell.Border = 2
cell.Colspan = 0
tbOther.AddCell(cell)
' another(line)
cell = New PdfPCell(New Phrase("Official Stamp:", FontBold))
cell.Border = 0
cell.Colspan = 1
tbOther.AddCell(cell)
cell = New PdfPCell(New Phrase())
cell.Border = 2
cell.Colspan = 4
tbOther.AddCell(cell)

doc.Add(tbOther)

'#Close PDF Document and Launch Document
doc.Close()

Anu_Bala10-Feb-15 0:14

Anu_Bala

10-Feb-15 0:14

0 down vote favorite

I have installed WAMP server in my PC for web application projects. Now i want to develop MFC application that uses mysql database in Visual Studio 2008.

Whether MySQL 5.6(WAMP server) available in my PC is enough for this MFC development.

Or

What i have to install for this Requirements?

When i searched in google i got this two.

1.MySQL Connector 2.MySQL for Visual Studio.

Please guide me on this

Anu

Re: MySQL for MFC application

Kornfeld Eliyahu Peter10-Feb-15 2:37

Kornfeld Eliyahu Peter

10-Feb-15 2:37

MFC can use ODBC data sources: https://msdn.microsoft.com/en-us/library/w2c4cthk.aspx[^]
MySQL has ODBC drivers for C++ (good for MFC): http://dev.mysql.com/downloads/connector/cpp/[^]
What else you need?

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

vb.net with mysql

KipkoechE8-Feb-15 17:04