Database Discussion Boards

KipkoechE10-Feb-15 23:27

10-Feb-15 23: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

Re: RANKING IN VB.NET MYSQL

Richard MacCutchan11-Feb-15 1:06

Richard MacCutchan

11-Feb-15 1: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?

Re: RANKING IN VB.NET MYSQL

KipkoechE11-Feb-15 2:32

KipkoechE

11-Feb-15 2:32

Re: RANKING IN VB.NET MYSQL

Richard MacCutchan11-Feb-15 2:36

Richard MacCutchan

11-Feb-15 2: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.

Re: RANKING IN VB.NET MYSQL

KipkoechE11-Feb-15 2:41

KipkoechE

11-Feb-15 2:41

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

Re: RANKING IN VB.NET MYSQL

GuyThiebaut11-Feb-15 3:11

GuyThiebaut

11-Feb-15 3: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.

Re: RANKING IN VB.NET MYSQL

KipkoechE11-Feb-15 3:23

KipkoechE

11-Feb-15 3:23

it worked very well...thanks alot

Re: RANKING IN VB.NET MYSQL

GuyThiebaut11-Feb-15 3:28

GuyThiebaut

11-Feb-15 3: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

VB.NET ITEXTSHARP PDF REPORTS

KipkoechE10-Feb-15 7:38

KipkoechE

10-Feb-15 7: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()

MySQL for MFC application

Anu_Bala10-Feb-15 1:14

Anu_Bala

10-Feb-15 1: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 3:37

Kornfeld Eliyahu Peter

10-Feb-15 3: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 18:04

KipkoechE

8-Feb-15 18:04

I have three tables in the school database: students,exams,subjects. my Problem is how i can take the take the subjects records and admission no from students and use them to create a table call marks at the click of a button. Note: the no of records are unpredictable.
Student Table contains fields: admno,name,form
exam contains fields: ExamName
subjects: SubjectName,SubjectID
The Marks table final result should be
admno,name,form,ExamName,subject1,subject2,...subjectn(Based on the no of subjects in the subject table)
. Am using vb.net with mysql. Kindly assist. it has given me headache for weeks

Re: vb.net with mysql

PIEBALDconsult8-Feb-15 18:33

PIEBALDconsult

8-Feb-15 18:33

KipkoechE wrote:
create a table call marks at the click of a button

I hope you mean inserting records into a table, not creating a table.

Re: vb.net with mysql

KipkoechE8-Feb-15 19:56

KipkoechE

8-Feb-15 19:56

i mean creating a table based on the records in another table

Re: vb.net with mysql

PIEBALDconsult9-Feb-15 4:47

PIEBALDconsult

9-Feb-15 4:47

Not a good idea.

Re: vb.net with mysql

ZurdoDev9-Feb-15 5:01

ZurdoDev

9-Feb-15 5:01

Creating a dynamic table just means you'll have to write code to generate the CREATE TABLE statement and you'll have to do your checking to see how many columns should be included in it.

However, why would you want to create a new table? The design sounds like a bad idea.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: vb.net with mysql

Jörgen Andersson9-Feb-15 5:09

Jörgen Andersson

9-Feb-15 5:09

The teacher just wants to check off the "Create Table AS SELECT" from his list of thing they should know about.

Wrong is evil and must be defeated. - Jeff Ello

Re: vb.net with mysql

Eddy Vluggen9-Feb-15 6:53

Eddy Vluggen

9-Feb-15 6:53

If you can write a query to select the results, then it should be easy to get them in a new table. Right now, there are a few fields lacking - how would you know which student took which exam?

Bastard Programmer from Hell Suspicious | :suss:

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

Populate one table from another

Corporal Agarn6-Feb-15 5:50

Corporal Agarn

6-Feb-15 5:50

Hello, SQL Server 2008R2
I have a list of accounts I want to split between a set of agents. The account table and the agent table have no common value. So I use a while loop to do the populating. As a learning tool I am trying to find a way to update the table with only a query. Here is example code but I am missing the control (see ???):

SQL

CREATE TABLE #MyTestBase (IDX INT, ExampleID INT, TestID INT);
INSERT INTO #MyTestBase (IDX, ExampleID, TestID) VALUES
(132326,406,NULL),(132358,513,NULL),(132367,513,NULL),(132373,513,NULL),(132374,406,NULL),
(132379,406,NULL),(132381,513,NULL),(132405,369,NULL),(132416,406,NULL),(132433,369,NULL),
(132438,369,NULL),(132444,406,NULL),(132457,369,NULL),(132461,406,NULL),(132465,369,NULL),
(132481,369,NULL),(132488,406,NULL),(132494,406,NULL),(132499,513,NULL),(132500,406,NULL),
(132507,406,NULL),(132520,513,NULL),(132521,369,NULL),(132525,369,NULL),(132533,406,NULL),
(132537,369,NULL),(132545,406,NULL),(132551,513,NULL),(132556,513,NULL),(132572,406,NULL),
(132580,369,NULL),(132593,329,NULL),(132600,513,NULL),(132629,513,NULL),(132695,513,NULL),
(132711,406,NULL),(132730,329,NULL),(132781,513,NULL),(132782,369,NULL),(132787,513,NULL),
(132800,406,NULL),(132805,369,NULL),(132813,513,NULL),(132831,369,NULL),(132842,406,NULL),
(132863,513,NULL),(132876,329,NULL),(132880,329,NULL),(132889,329,NULL),(132891,329,NULL),
(132908,329,NULL),(132913,406,NULL);

CREATE TABLE #MyTestCounts (TestID INT, CNT INT);
INSERT INTO #MyTestCounts (TestID, CNT) VALUES
(329,7),
(369,13),
(406,17),
(513,15);

SELECT * FROM #MyTestCounts;
SELECT * FROM #MyTestBase;

UPDATE Base
SET TestID = Counts.TestID
FROM #MyTestBase Base
CROSS APPLY #MyTestCounts Counts
WHERE ??? <= Counts.CNT
AND TestID IS NULL;

In reality these are permanent tables with a lot more information and the update is more complicated but I will worry about that late.

Can someone point me in the right direction?

Thank you for your time,
djj

Mongo: Mongo only pawn... in game of life.

Re: Populate one table from another

Wendelius6-Feb-15 6:23

Wendelius

6-Feb-15 6:23

You said that you currently do the update using a while loop. Can you post that? It would help to understand what's the desired outcome.

Re: Populate one table from another

Corporal Agarn6-Feb-15 7:49

Corporal Agarn

6-Feb-15 7:49

Here is what I currently use:

SQL

DECLARE @TestID INT, @RemCount INT, @irow INT;

IF OBJECT_ID('tempdb..#UpdateAgent') IS NOT NULL DROP TABLE #UpdateAgent;
SELECT TestID, CNT AS RemCount
    ,  ROW_NUMBER() OVER(ORDER BY TestID) AS IDX
INTO #UpdateAgent
FROM #MyTestCounts;

SELECT @MIDX = MAX(IDX) FROM #UpdateAgent

WHILE @irow <= @MIDX
BEGIN
    SELECT @TestID = TestID, @RemCount = RemCount
    FROM #UpdateAgent
    WHERE IDX = @irow;

    IF @RemCount < 0 SET @RemCount = 0;

    UPDATE WQueueCheck
    SET TestID         = @TestID
    FROM #MyTestBase
    INNER JOIN (
            SELECT TOP (@RemCount) TQB.IDX
            FROM #MyTestBase TQB
            WHERE TestID IS NULL
            ORDER BY NEWID() ) X
        ON #MyTestBase.IDX = X.IDX;

    SET @irow += 1;
END

[edit] missed removing a column from production versus test

Mongo: Mongo only pawn... in game of life.

modified 6-Feb-15 13:37pm.

Re: Populate one table from another

Jörgen Andersson9-Feb-15 22:29

Jörgen Andersson

9-Feb-15 22:29

Tough one, it took me a while to understand what you wanted, and I still may have misunderstood the purpose.
Anyway, here's a try, tell me if I'm off the target.

Since there is no UnGroup or UnCount function in SQL we have to create that ourselves.
One way is to use an auxiliary Sequence. Here's a fairly fast way to create a sequence:

SQL

CREATE TABLE numbersequence (Number  int  not null);
INSERT INTO numbersequence(Number)
SELECT  TOP 100 row_number() over(order by t1.number) as N
FROM    master..spt_values t1 
CROSS JOIN master..spt_values t2
;

Adjust for the size you need.
Now we can "Uncount" the table and create a rownumber to join on:

SQL

select  testid
       ,ROW_NUMBER() OVER(order by testid) AS rn
from    MyTestCounts tc,numbersequence n
where   tc.cnt >= n.number

The whole query to connect unrelated IDX to TestID would look like this:

SQL

with counts as (
    select  testid
           ,ROW_NUMBER() OVER(order by testid) AS rn
    from    MyTestCounts tc,numbersequence n
    where   tc.cnt >= n.number
    )
,tqb as (
    SELECT  TOP (select sum(cnt) from MyTestCounts) TQB.IDX
           ,ROW_NUMBER() OVER(order by testid) AS rn
    FROM    MyTestBase TQB
    WHERE   TestID IS NULL
    ORDER BY NEWID()
    )
select  IDX,TestID
from    counts c
join    tqb t
    on  c.rn = t.rn