VB.NET ITEXTSHARP PDF REPORTS - Database Discussion Boards

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?

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

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.

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

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.

it worked very well...thanks alot

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

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()

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

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.

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

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.

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

Not a good idea.

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.

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

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

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.

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.

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.

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

Here's[^] the fiddle.

Wrong is evil and must be defeated. - Jeff Ello

Thank you for the reply. I have been pulled off for the crisis of the day, but will get back to you when I can.

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

That does it.
Took me a minute to understand your logic, but it makes sense once I got it in my head (very thick you know).

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

Glad to be of service.

Wrong is evil and must be defeated. - Jeff Ello

Very simple.
You should try this my query.and dont use temp Table #MyTestCounts

SQL

UPDATE #MyTestBase
SET TestID = (SELECT COUNT(1) NumOfPoint
			  FROM #MyTestBase AS mtb
			  WHERE mtb.ExampleID = #MyTestBase.ExampleID
			  GROUP BY mtb.ExampleID)