Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB.NET
Hi all!
I have a table with (StudentID,Physics,Chemistry,Total,Average)
I want to pull values of Physics,Chemistry and calculate Total and then Average for a specific StudentID. With the following code:
 
SELECT SUM (IIF(ISNULL([Physics]),0,[Physics])) As Phy, SUM (IIF(ISNULL([Chemistry]),0,[Chemistry])) As Chem, SUM([Physics])+Sum([Chemistry]) as Total FROM tblForm1 WHERE StudentID = '" & Trim(Me.txtSearch.Text) & "'" 
 
I get the following:
If all the values exists in the table, everything is fine, it fetches the values and calculates the sum. But if one or both values are null (not filled) they are replaced with '0' but it does not do the Sum (calculate the total).
 
Can someone please show me the way through this? I want it to calculate the sum even if one or both values are null (the total will then be '0')
 
I will appreciate any inputs please.(I use an Access .mdb database)
Posted 14-Nov-12 3:22am
Edited 14-Nov-12 3:59am
v5
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
You are doing so much its simple.
 
 
select	ISNULL(Physics,0) as Physics,
        ISNULL(Chemistry,0) as Chemistry,
        SUM(ISNULL(Physics,0)+ISNULL(Chemistry,0)) as Total,
        (SUM(ISNULL(Physics,0)+ISNULL(Physics,0))/2) as [Avg]
FROM	tblForm1 
WHERE	StudentID = '" & Trim(Me.txtSearch.Text) & "'"
 
 
Hope, It will help you.
Thanks
  Permalink  
v2
Comments
savedlema at 16-Nov-12 5:28am
   
Thanks Ved, I have a question please;
I want to INSERT the result of this query (Total) into a field called "TotalMarks" found in the same table. How to I do that?
I tried INSERT INTO TotalMarks SELECT...(previous solution query) but did not work.
I also tried INSET INTO tblForm1.TotalMarks but never worked.
 
Any idea please?
 
Thanks a lot so far.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi
 
Try This
 
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim dbTableName As String
Dim sql1 As String
Dim MaxRows As Integer
 
dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = "
'Define these two variables
dbSource = "C:/Marks.mdb"
dbTableName = "Science_Marks"
 
con.ConnectionString = dbProvider & dbSource
 
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
sql1="SELECT * FROM " & dbTableName
 

con.Open()
da = New OleDb.OleDbDataAdapter( sql, con )
da.Fill(ds, "FancyName")
MaxRows = ds.Tables("FancyName").Rows.Count
con.Close()
 
For i As Integer = 0 To MaxRows - 1
	Dim physics As Double = 0
	Dim chemistry As Double = 0
	Try
physics = ds.Tables("FancyName").Rows(i).Item(1)
	Catch exp As Exception
	End Try
	Try
chemistry = ds.Tables("FancyName").Rows(i).Item(2)
	Catch exp2 As Exception
	End Try
	Dim total As Double = physics + chemistry
	Dim average As Double = total / 2
'Do Whatever with the total and average
Next
 
This will stop the sql injection problem.
 
Jacques
  Permalink  
v3
Comments
savedlema at 16-Nov-12 5:30am
   
Thanks Jacques, Your insert is new to me, so I will have to study it, I know its gonna help me a great deal.
Thanks.
Jacques Loubser at 16-Nov-12 7:40am
   
There is no insert code. To Insert the values to the table do the following:
Replace the 'Do Whatever with:
ds.Tables("FancyName").Rows(i).Item(3) = average.toString();
ds.Tables("FancyName").Rows(i).Item(4) = total.toString();
After the Next put:
da.Update(ds, "FancyName")
Move the con.Close() to the end & then add
con.Dispose()
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this, I am not sure if this will work in Access, as I am far more used to MSSQL2005 and above.
 
SELECT
   SUM(ISNULL([Physics],0)) AS Phy,
   SUM(ISNULL([Chemistry],0)) AS Chem,
   SUM(ISNULL([Physics],0)) + SUM (ISNULL([Chemistry],0)) AS Total
FROM tblForm1
WHERE RTRIM(StudentID) = 'STUDENT1'
GROUP BY [Physics]
         ,[Chemistry]
 

Just paste that into your SQL and then replace 'STUDENT1' with your '" & Trim(Me.txtSearch.Text) & "'" but.... I would be a bit concerned about using it as Dynamic SQL from the VB, as the user could enter SQL Injection to cause some trouble!
 
Try and use Stored Procedures with Parameters... As I am lead to believe this is safer....
  Permalink  
v2
Comments
savedlema at 16-Nov-12 5:31am
   
Thanks Rob,
 
I have a question please;
I want to INSERT the result of this query (Total) into a field called "TotalMarks" found in the same table. How to I do that?
I tried INSERT INTO TotalMarks SELECT...(the select query) but did not work.
I also tried INSET INTO tblForm1.TotalMarks SELECT .... but never worked.
 
Any idea please?
 
Thanks a lot so far.
Rob@Love2Code at 16-Nov-12 8:25am
   
I would rather have a table of marks against that user /student.
So MarkID, StudentID, Mark
 
and then do a sum based on that table.
 
That is how I would do it.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Thank you all. I have solved the problem.
Many blessings to you.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 DamithSL 390
1 Maciej Los 217
2 OriginalGriff 213
3 BillWoodruff 130
4 Garth J Lancaster 90
0 OriginalGriff 7,953
1 DamithSL 6,139
2 Sergey Alexandrovich Kryukov 5,449
3 Maciej Los 5,293
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 16 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100