Data Reader Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Data Reader Question

Author

Topic

vk18
Posting Yak Master

146 Posts

Posted - 2008-02-09 : 13:07:54

Hi Guys,
I have a quick question about DataReader, I have a function called "ExportTotal" i am calling this function from another function. what it does is it will put the Total for each Network in the Line 34. Right now what it is doing is It is putting the First Networktotal,2ndNetwork total,3rdNetwork total....... in the line 34. what i want is if the control comes to first network then it has to put only 1st network total and for the 2nd network only 2nd network total and so on. Please see my function below. Can you guys tell me what i am doing wrong?
Thx
Private Function exporttotal() As String
Dim sql As String
Dim dbFunctions As New DatabaseUtilities
Dim tempinvoicetotal As String
'Dim rateactuals As String
Dim filetext As String
Dim tempclientname As String
Dim strconn As String
Dim prev_network As String = ""
Dim current_network As String = ""

strconn = CONNECTIONSTRING

sql = "SELECT CAST(SUM(tblSpot.rateActual) AS int(4)) AS Rateactuals, SUM(tblSpot.rateActual * 0.85) AS netrate, SUM(tblSpot.rateActual * 0.15) AS commrate,TBLCLIENT.CLIENTNAME " & _

"FROM tblSpot INNER JOIN tblContract ON tblSpot.fkContract = tblContract.pkid INNER JOIN " & _
" tblClient ON tblContract.fkClient = tblClient.pkid WHERE tblSpot.fkContractType = 'UNWIRED' AND " & _
"fkInvoiceNumber = '" & Me.txtinvoicenumber.Text & "' GROUP BY TBLCLIENT.CLIENTNAME"

Dim myConn As New SqlConnection(CONNECTIONSTRING)
Dim myCommand As New SqlCommand(sql, myConn)
myConn.Open()
Dim dbreader As SqlDataReader = myCommand.ExecuteReader()
While dbreader.Read()

Try

Dim Rateactuals As String
If dbreader("Rateactuals") Is DBNull.Value Then
Rateactuals = ""
Else
Rateactuals = dbreader("Rateactuals")
tempinvoicetotal = Rateactuals
End If

Dim clientname As String
If dbreader("clientname") Is DBNull.Value Then
clientname = ""
Else
clientname = dbreader("clientname")
tempclientname = clientname
End If

If prev_network = "" Then

filetext = filetext & vbCr & "34;;" & tempinvoicetotal & "00" & ";"

Session("EDIExport4") = filetext
prev_network = tempclientname
Else
current_network = tempclientname
If prev_network <> current_network Then
filetext = filetext & vbCr & "34;;" & tempinvoicetotal & "00" & ";"

Session("EDIExport4") = filetext
prev_network = tempclientname

Else

End If
End If

Catch SqlEx As SqlClient.SqlException
Session("Error") = SqlEx.Message.ToString
Response.Redirect("Error.aspx?Form=" & Request.Path)
Catch Ex As System.Exception
Session("Error") = Ex.Message.ToString
Response.Redirect("Error.aspx?Form=" & Request.Path)
End Try
dbFunctions = Nothing
End While
myConn.Close()
End Function

senthil_mca80
Starting Member

10 Posts

Posted - 2008-02-12 : 17:07:23

change the following line

If prev_network = "" Then

filetext = vbCr & "34;;" & tempinvoicetotal & "00" & ";"

Session("EDIExport4") = filetext
prev_network = tempclientname
Else
current_network = tempclientname
If prev_network <> current_network Then
filetext = vbCr & "34;;" & tempinvoicetotal & "00" & ";"

Session("EDIExport4") = filetext
prev_network = tempclientname

vk18
Posting Yak Master

146 Posts

Posted - 2008-02-14 : 15:55:12

Hi,

I tried this, now it is giving the LastNetwork total for all the Networks. Any Idea
Thx

quote:
Originally posted by senthil_mca80

change the following line

If prev_network = "" Then

filetext = vbCr & "34;;" & tempinvoicetotal & "00" & ";"

Session("EDIExport4") = filetext
prev_network = tempclientname
Else
current_network = tempclientname
If prev_network <> current_network Then
filetext = vbCr & "34;;" & tempinvoicetotal & "00" & ";"

Session("EDIExport4") = filetext
prev_network = tempclientname

vk18
Posting Yak Master

146 Posts

Posted - 2008-02-23 : 17:42:09

Hi Guys,

Can somebody shed some light on this ?
Thx

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-23 : 20:42:31

"lastNetwork Total" isn't reflected in your SQL query or the output from your If statements in the read() actions....what do you mean?

Do you mean that only the last record in the SQL output's value is being shown?

Verify that the SQL output is providing the right data...then make do test using the console.print or message box to make sure the intended results are coming through. Make sure that you have thought the process through completely...the first part is getting the SQL output the way you want it. The next part is interpreting those results. If the SQL part is right, then you need to re-evaluate your thought process and logic on the actions taken on each line of the results. My guess is you need some kind of order by in the SQL query to control the output better.

Poor planning on your part does not constitute an emergency on my part.

vk18
Posting Yak Master

146 Posts

Posted - 2008-02-25 : 13:03:29

Hi,
What i mean is there are 10 Networks in the Database. When the Control Comes first time to this function then it has to pick the first network total and when the control comes 2nd time it has to pick the 2nd network total and so on. Right now what it is doing is it is putting all the networks total side by side
Ex: 34; 2500;8900;6500 and so on
1st 2nd 3rd
I want to put only the 1st Network total above.
Any Idea?
Thx

quote:
Originally posted by dataguru1971

"lastNetwork Total" isn't reflected in your SQL query or the output from your If statements in the read() actions....what do you mean?

Do you mean that only the last record in the SQL output's value is being shown?

Verify that the SQL output is providing the right data...then make do test using the console.print or message box to make sure the intended results are coming through. Make sure that you have thought the process through completely...the first part is getting the SQL output the way you want it. The next part is interpreting those results. If the SQL part is right, then you need to re-evaluate your thought process and logic on the actions taken on each line of the results. My guess is you need some kind of order by in the SQL query to control the output better.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources