Solved

Page Layout

Posted on 2011-09-22
19
264 Views
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.
0
Comment
Question by:sammySeltzer
  • 11
  • 4
  • 4
19 Comments
 
LVL 40
ID: 36583480
In what type of data or container do you get the "output below"? String array, DataGridView, DataTable, other?
0
 
LVL 28

Author Comment

by:sammySeltzer
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.
0
 
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
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.

0
 
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
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)
etc
etc

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.

0
 
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.
 
0
 
LVL 28

Author Comment

by:sammySeltzer
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.
0
 
LVL 83

Expert Comment

by:CodeCruiser
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.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 28

Author Comment

by:sammySeltzer
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.
0
 
LVL 83

Expert Comment

by:CodeCruiser
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?
0
 
LVL 28

Author Comment

by:sammySeltzer
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.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 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>"
End

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.

0
 
LVL 28

Author Comment

by:sammySeltzer
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.
0
 
LVL 28

Author Comment

by:sammySeltzer
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">
            <ItemTemplate>
...
...

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.
0
 
LVL 28

Author Comment

by:sammySeltzer
ID: 36597170
I also tried onrowUpdating="btnSendEmail_Click()"
0
 
LVL 28

Author Comment

by:sammySeltzer
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)"

        'Response.Write(sqlStr)
        'Response.End()
        cnn = New SqlConnection(connStr)
        cnn.Open()
        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
        dreader.Close()

        '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.Bcc.Add("sammyseltzer@company.com")
        objMail.To.Add("sammyseltzer@company.com")
        'objMail.To.Add(fullname)
        'objMail.CC.Add("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
        objSmtpClient.Send(objMail)
        dr.Close()

Open in new window

0
 
LVL 28

Author Comment

by:sammySeltzer
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
0
 
LVL 83

Expert Comment

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now