Page Layout

Posted on 2011-09-22
Medium Priority
Last Modified: 2012-05-12
Can one of you .net experts help out on this, please?

I have this query:

SELECT a.deptcode, c.deptname, em.check, em.cash,a.empl_first,a.empl_last, e.[e_mail],a.employee_id  
FROM ctable c,[e_mail] e,Aempl a,EMamts em WHERE c.divisioncode = em.divisioncode  
AND a.empid = e.empid  
AND e.empid = em.empid   
AND em.empid = '"&empid&"'  
AND (isnull(em.check, 0)<> 0  
OR isnull(em.cash, 0)<> 0) 

Open in new window

which gives me the output below. This is the output we desire:

DeptCode   DeptName            Check   Cash        FirstName  LastName     EmpId 
1540         IT                 0         12          John       Doe    19765507     
1540         HR                 0         13          John       Doe    19765507 
1540        Communications      1          0          John       Doe    19765507 
1540        Mental Health       11         0          John       Doe    19765507 

Open in new window

However, we would like to have layout similar to this one below:

Name:         John     Doe 
DeptCode:     1540 
EmpId:        19765507 
IT                                         $12.00 
HR                                         $13.00 
Communications                             $1.00 
Mental Health                              $11.00 
Total                                      $37.00 

Open in new window

I have played around with this all day but no luck.

Basically, we have an email component written in vb.net. This email will be used to send out the above data on that layout format.

I would appreciate any assistance.
Question by:sammySeltzer
  • 11
  • 4
  • 4
LVL 40
ID: 36583480
In what type of data or container do you get the "output below"? String array, DataGridView, DataTable, other?
LVL 29

Author Comment

ID: 36583501
I get that ouput when I use SQL Server Management Studio to run the query.

Every research I have done so far indicates that it can only be done using front end like .net but I am lost as to how.

Thanks for your response.
LVL 40
ID: 36583777
Management Studio has a fixed output when you look at the tables.

If you want to have it formatted otherwise, you need effectively a font end.

That could be a report created in any report generator, that could be a .NET other application when you take the data and format it.

In the .NET application, you have to know at least a bit about ADO.NET or one of the other technologies (LINQ, Data Entities) in order to be able to get the data in the application first.

If in ADO.NET, you need to decide how to fetch the data: DataReader or DataAdapter and a DataTable.

Once the data is in the application, then it can be formatted.

Is there any of those technologies that have meaning for you?

Starting in .NET from scratch is a lot of stuff to learn. A report generator might be easier to use.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 29

Author Comment

ID: 36584206
I know .net enough to write programs.

Those recommendations you are making have been made before and I stated that at the beginning of my post.

I was hoping that someone would give me a headstart  or perhaps a link that has examples closer to what I am trying to accomplish.

THen I can follow the example to get this done.

I have to complete this before Monday next week.

LVL 40
ID: 36584657
Maybe those recommendations have been made because they are the way to go.

You do not learn to program with database in .NET in 2 days. After 2 days of instensive training, professional programmers usually just begin to grasp what this is all about.

I could guide you if you already had a headstart in ADO.NET, but I won't write that type of code for you. Maybe some brave other soul will; this is a one or two hours of work for somebody who knows that stuff. And this stuff requires some time to learn, understand and use correctly.

If you do not already have some basic experience in ADO.NET, what you are asking is akind to asking how to read a text file when you do not know what a variable is. There are prerequisites to any question.

Check the documentation about ADO.NET to get an overall idea of the concepts. Just type ADO.NET in the index of the documentation. If you do not care about understanding what you are doing, from the index, go straight to DataTable...creating. You will get the minimum required for what you need to do.

Then it might be time to start answering questions about how to access the data.

And once you have the data in memory, we could go about how you could format it, in a way that you component will accept it.

Good luck for the monday deadline. I do not want to sound pessimistic, just realist. Help is harder to get during the weekend.
LVL 29

Author Comment

ID: 36584811
When I posted this question, I posted for anyone who *understands* my need and *could* help.

I am reasonably confident that you have no clue what I am after.

Ado.net is NOT my issue. I know that.

This is one or many ways you could use it.

' Open a connection to the database.
Dim conn As String = _
   "Data Source=localhost; Initial Catalog=myDB;" _
   & "Integrated Security=True"
Dim cn As SqlConnection = New SqlConnection(conn)
cn.Open(  )
' Set up a data set command object.
Dim strSelect As String = "SELECT a.deptcode, c.deptname, em.check, em.cash,a.empl_first,a.empl_last, e.[e_mail],a.employee_id " & _ 
"FROM ctable c,[e_mail] e,Aempl a,EMamts em WHERE c.divisioncode = em.divisioncode  
"AND a.empid = e.empid AND e.empid = em.empid AND em.empid = '"&empid&"'  
"AND (isnull(em.check, 0)<> 0 OR isnull(em.cash, 0)<> 0)"

 Dim dscmd As New SqlDataAdapter(strSelect, cn)

Open in new window

*AND* my question is more of layout than connection to the database.

Thank you for your response but allow those who could assist to try.

LVL 40
ID: 36584905
It is not a question of not understanding, I understand very well what you want to do, it is basic stuff that I was doing 30 years ago. But to show you how to do it, I need not see a display, I need to know in which type of container or class you are holding the data. You do not format a display on a screen, you format data.

But when asked In what type of data or container do you get the "output below"?, you answered that you got it from SQL Server Management Studio. One cannot manipulate the data returned in SQL Server Management Studio. Anybody who knows a little would have thought as I did, that you do not know A from B about ADO.NET. Otherwise you would have presented your problem as formatting the data that you have in a DataTable or a DataGridView. Not as formatting a display out of nowhere as far as coding is concerned.

People who answer here give you their personal time freely. They try hard to answer the best as they could to questions that are too often presented with incomplete information. Before accusing them of not understanding you, put yourself in their places, and look at how you present the problem, and try to understand why they do not seem to understand.

I am reasonably confident that you have no clue what I am after.

I hope for you that somebody else will try to help you, knowing that they can be treated like that for their effort.
LVL 29

Author Comment

ID: 36586511
Please understand that I have helped a ton of people here myself.

All you need to do is look at my profile,

I have sometimes spent hours helping people.

I have been patient trying to understand their need *IF* I think I can help solve their problem.

Rather than trying to judge what i know or do not know, try and see how you can help me resolve this problem.

I wouldn't be this frustrated if I had been this judgemental of people that come here asking for my assistance which I give freely as well.
LVL 83

Expert Comment

ID: 36592392
>However, we would like to have layout similar to this one below:
That can be done using Nested repeaters.

>Basically, we have an email component written in vb.net. This email will be used to send out the above data on that layout format.

If its the email that you want to send out in that format, you would need to construct it using HTML and tables.

Please clarify which one if you need further assistance.
LVL 29

Author Comment

ID: 36592777
Hi CodeCruiser,

Thank you very much for trying help.

If it is easier to handle with html and tables, that would be great.

If nested repeater is easier, great as well.

I would like to give you just a brief history of why I am in this mess.

The app was written by someone else who is no longer with us.

There are two components of this app, the insert and the update.

When a record is inserted into the db the first time, I am able to do exactly what I am asking you guys to help me now.

The reason I am having difficulty replicating it this time is the update component was done differently, making it difficult for me to replicate the code I used for inserting.

For instance, after a record is created, the user receives an email giving the layout I posted earlier.

Then they came back during a meeting that if the user decides to modify his/her record, that user needs to get an email with latest updates.

That's why I am stuck.

If I were to have done it from the beginning, I would have done it differently.

Thank you so much.
LVL 83

Expert Comment

ID: 36592968
Right. I am still confused a bit now that you mentioned insert and update. Do you need this layout for updating or for sending the email? Is this one master and many detail rows or many master and many details records?
LVL 29

Author Comment

ID: 36593007
I need it for sending emails.

The way the update code works currently is that a user can edit his or record and submit the changes back to the db.

So, what I am trying to do is use the query I posted earlier and grab the records from the db, hopefully manipulate it to get the layout that I am hoping to get assistance for, then send an email back to the user letting the user know the new details of his or record after the update.

I am hoping to do all of this in code behind.

Hopefully, I am not approaching the wrong way.

Thanks very much again.
LVL 83

Accepted Solution

CodeCruiser earned 2000 total points
ID: 36593769
Ok. You would compose the body of the email something like below

Dim BodyText as String = "<html><body><table><tr><td>Name:</td><td>" & name & "</td></tr><tr><td>Dept Code:</td><td>" & deptcode & "</td></tr><tr><td>EmpID:</td><td>" & empid & "</td></tr>"

'Note that i have used name, deptcode and empid variables above as place holders. This info would come from db

BodyText &= "<tr><td colspan=" & chr(34) & "2" & chr(34) & ">__________________________________________________________</td></tr>"

While dbreader.Read()
    BodyText &= "<tr><td>" & dbreader.Item(0) & "</td><td>" & dbreader.item(1) & "</td></tr>"

BodyText &= "</table></body></html>"

Now set this as email body text and send the email.

Bare in mind that you would need to split your query into 2. One to get the "Master" details such as name, deptcode and empid and a second query to get the details records.

LVL 29

Author Comment

ID: 36593841
Thank you so very much CodeCruiser.

Let me work with what you have given me and I will get back with what I have so far.
LVL 29

Author Comment

ID: 36596838
Hi CodeCruiser,

I think I am very, very close to getting it to atleast work.

Right now, I am trying to test it but it isn't firing.

I mean, I click Update, the value gets updated but the email doesn't go out so I can atleast test if the layout is working.

My question is this:

I have the following subs:

page_load event,
gvOnDataBound2, which comes from this markup:
        <asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource2"
            OnDataBound = "gvOnDataBound2">

gvRowDataBound, which comes from this markup:
        <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
            AutoGenerateColumns="False" DataKeyNames="ID" AllowPaging="True"
            CellPadding="4" ForeColor="#333333" GridLines="None" Visible="True"
            OnRowDataBound="gvRowDataBound" AutoGenerateEditButton="True">

and my email sub called btnSendEmail_Click():

I tried invoking this email sub from any of the subs I mentioned above but it isn't firing.

Do you have any idea what needs to change?

On the insert sub, I there is no problem there because all I needed to do was invoke it from buttonInsert() sub and it works.

Not sure where to invoke it or atleast integrate with any of the subs I mentioned above.

Sorry, if I am not explaining it well.
LVL 29

Author Comment

ID: 36597170
I also tried onrowUpdating="btnSendEmail_Click()"
LVL 29

Author Comment

ID: 36599820
Ok, I am able to get the email working, as in sending.

However, the values are blank. Not even the client's name is being displayed.

I think I got this jumbled up pretty badly.

I am working on since I still have a couple of hours before we meet.

However, I am trying not to take any chances.

Can you please see where and how I screwed things up?

Thanks a lot for your help.

I think we are very, very close.

    Protected Sub btnSendEmail_Click()
        Dim cnn As SqlConnection
        'Dim param As SqlParameter
        Dim cmd As SqlCommand

        Dim sqlStr As String = ""
        Dim sqlStrD As String = ""

        Dim connStr As String = ConfigurationManager.ConnectionStrings("Database_DBConnectionString").ConnectionString

        sqlStr = "SELECT a.empl_first +' '+ a.empl_last as fname, a.dept_code,a.empid  FROM Atable a WHERE a.empid = '" & tEmplID & "' "

        sqlStrD = "SELECT c.charity_name, " & _
        "em.check_amt, em.cash, e.[e_mail] " & _
        "FROM Ctable c,[empE] e,Atable a," & _
        "EMAmounts em " & _
        "WHERE(c.charity_code = em.charity_code) " & _
        "AND a.employee_id = e.empid " & _
        "AND e.empid = em.empid " & _
        "AND em.empid = '" & tEmplID & "' " & _
        "AND (isnull(em.check_amt, 0)<> 0 " & _
        "OR isnull(em.cash, 0)<> 0)"

        cnn = New SqlConnection(connStr)
        cmd = New SqlCommand(sqlStr, cnn)
        cmd = New SqlCommand(sqlStrD, cnn)

        Dim BodyText As String = ""
        Dim dreader = cmd.ExecuteReader()

        If dreader.Read() Then
            BodyText = "<html><body><table><tr><td>Name:</td><td>" & dreader.Item(0) & "</td></tr><tr><td>Dept Code:</td><td>" & dreader.Item(1) & "</td></tr><tr><td>EmpID:</td><td>" & dreader(2) & "</td></tr>"
        End If

        'Note that i have used name, deptcode and empid variables above as place holders. This info would come from db
        BodyText &= "<tr><td colspan=" & Chr(34) & "2" & Chr(34) & ">__________________________________________________________</td></tr>"

        Dim dr = cmd.ExecuteReader()
        If dr.Read() Then
            BodyText &= "<tr><td>" & dr.Item(0) & "</td><td>" & dr.Item(1) & "</td></tr>"
        End If

        BodyText &= "</table></body></html>"

       'code for email requests
        Dim objSmtpClient As SmtpClient = New SmtpClient("mycompanyrelay.com", 25)
        Dim objSender As MailAddress = New MailAddress("sammyseltzer@company.com", "sammyseltzer@company.com")
        Dim objMail As MailMessage = New MailMessage("sammyseltzer@company.com", "sammyseltzer@company.com")
        objMail.Subject = "Summary Of Your Company's contributions"
        objMail.Body = "This is a computer generated email message.<br> Please do NOT use the REPLY button above to respond to this email.<br><br>  Dear<b> " & fullname & "</b> <br>You recently made changes to your Contributions. <hr> Below are new details of your contributions:<br><br>" & BodyText
        objMail.IsBodyHtml = True

Open in new window

LVL 29

Author Comment

ID: 36601561
Got everything working now.

Thanks very much for your time and unbelievable help.

The reason it took this long was that I didn't realize the value for the tEmplID param wasn't getting passed.

Again, thanks alot CodeCruiser
LVL 83

Expert Comment

ID: 36601610
Glad that you got it sorted and apologies for not being able to respond.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Through the video, you can check the migration process of Outlook PST file to PDF. Kernel for Outlook to PDF tool can convert Outlook emails with all attributes like Subject, To, From, Cc, Bcc and other folders such as Inbox, Outbox, Sent Items, Jun…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question