Solved

ASP.net MVC 4 getting data from SQL and displaying in list

Posted on 2014-02-27
6
10,884 Views
Last Modified: 2014-03-03
I am new to the MVC world and struggling.
In asp.net webforms I always worked with raw SQL queries to have the most control of what I need done. With that said I am trying to get a list of Customers from an SQL database table and display it in a <ul></ul>.
Struggling with getting the Model, Controller and View to all work together in VB.

Here is my Model code (CustomerModels.vb)
....................................
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Data.SqlClient

Public Class CustomerModels
    Public Function Index() As ViewResult
        Dim MyConnectionString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
        Dim cnn As New System.Data.SqlClient.SqlConnection(MyConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand("", cnn)
        Dim result As New List(Of String)()
        cmd.CommandText = "select customer from customers order by customer"
        cnn.Open()
        Using reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                result.Add(reader.GetString(0))
            End While
        End Using
        cnn.Close()

        Return View(result)
    End Function

    Private Function View(result As List(Of String)) As ActionResult
        Throw New NotImplementedException
    End Function
End Class

Here is my Controller code (CustomerController.vb)
................................................
Namespace IBSIwebPortal
    Public Class CustomerController
        Inherits System.Web.Mvc.Controller
        ' GET: /Customer

        Function Index() As ActionResult
            Return View()
        End Function
    End Class

End Namespace

Here is my View code (Index.vbhtml)
................................................
@ModelType IEnumerable(Of IBSIwebPortal.CustomerModels)

@Code
    ViewData("Title") = "Customer"
End Code

<h2>Customer</h2>

<ul>

@For Each item In Model
        Dim currentItem = item
    @
<li>@Html.DisplayFor(Function(modelItem) currentItem.Index)</li>

Next
</ul>
0
Comment
Question by:Cubbybulin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 39894311
It looks like you've got a little mixed up with where you have your code. The Model is essentially a data container that holds the information that your View. The Controller handles HTTP requests and either does the work that loads your model(s) with data, or delegates that task to another tier, and returns it to a View.

Based on what you have posted, you would be looking at something closer to:

Firstly a simple Model:
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Data.SqlClient

Public Class CustomerModel
    '// Automatic property
    Public Property CustomerName As String
End Class

Open in new window

Next the Controller:
Namespace IBSIwebPortal
    Public Class CustomerController
        Inherits System.Web.Mvc.Controller
        ' GET: /Customer

        Function Index() As ActionResult

            '// define a list of CustomerModel objects
            Dim list As List(Of CustomerModel) = New List(Of CustomerModel)()
            Dim model As CustomerModel
    
            '// populate a list of CustomerModel objects from database
        Dim MyConnectionString As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
        Dim cnn As New System.Data.SqlClient.SqlConnection(MyConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand("", cnn)
        Dim result As New List(Of String)()
        cmd.CommandText = "select customer from customers order by customer"
        cnn.Open()
        Using reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                model = New CustomerModel
                model.CustomerName = reader.GetString(0)
                list.Add(model)
            End While
        End Using
        cnn.Close()

            '// return the list of CustomerModel objects to our View
            Return View(list)
        End Function
    End Class

End Namespace

Open in new window

And finally the View:
@ModelType IEnumerable(Of IBSIwebPortal.CustomerModel) 

@Code
    ViewData("Title") = "Customer"
End Code

<h2>Customer</h2>

<ul>

@For Each item In Model

       <li>@Html.DisplayFor(item.CustomerName)</li>

Next
</ul>

Open in new window


I've knocked this together from memory, so there may be a few syntax errors in there, and the structure could be improved, but hopefully it might serve to clarify things a little for you.
0
 

Author Closing Comment

by:Cubbybulin
ID: 39894554
Can't thank you enough for this help!
I see now my confusion with the Model and Controller.
This worked great!!!

I did have to make a change on this line though
<li>@Html.DisplayFor(item.CustomerName)</li>

It was giving an error "Attribute specifier is not a complete statement..."
Thru some googling I came up with...
 @:<li>@Html.DisplayFor(Function(model) item.CustomerName)</li>

I don't completely understand it, but it worked. Is this correct or is there a better way?

Thanks again!
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 39894563
There are numerous methods for displaying data, depending on what you want to achieve. The simplest form should just be:
@item.CustomerName

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Cubbybulin
ID: 39894638
Great, thank you!
0
 

Author Comment

by:Cubbybulin
ID: 39897933
Carl,
Having another issue. I will post new question if need be, but figured you already now my layout.
I have populated my list as you have helped with above. When I click on a customer in the list I get the ID of the customer using jquery and with that I want to go get the address info for that specific customer and fill a div next to my list with the address info. I do not want to go to a different page. I have tried some ajax calls but not having any success. Based on my code from above, I added in a new class in my Model page.

Public Class CustAddressModel
    Public Property Addr1 As String
    Public Property Addr2 As String
    Public Property City As String
    Public Property State As String
    Public Property Zip As String
    Public Property Phone As String
    Public Property PhoneAlt As String
    Public Property Fax As String
End Class

In my Controller page I added in a new function. Trying to use the ViewData but not sure about it.

 Public Function CustAddress(ByVal id As Integer) As ActionResult
            Dim list As List(Of CustAddressModel) = New List(Of CustAddressModel)()
            ' Dim model2 As CustAddressModel
            Dim result As New List(Of String)()
            cmd.CommandText = "select addr1,addr2,city,state,zip,phone,phonealt,fax from customers where custid=" + id.ToString
            cnn.Open()
            Using reader As System.Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
                While reader.Read()
                    ViewData("addr1") = New CustAddressModel
                    ViewData("Addr1") = reader.GetString(0)
                    ViewData("Addr2") = reader.GetString(1)
                    ViewData("City") = reader.GetString(2)
                    ViewData("State") = reader.GetString(3)
                    ViewData("Zip") = reader.GetString(4)
                    ViewData("Phone") = reader.GetString(5)
                    ViewData("PhoneAlt") = reader.GetString(6)
                    ViewData("Fax") = reader.GetString(7)
                End While
            End Using
            cnn.Close()
            Return View()
        End Function
    End Class

In the View page in my jquery function I have

$('.dropdown li').click(function () {
  var SelID = $(this).attr("id");

        // not sure what to try here to call the CustAddress (SelID) function to get the ViewData returned.

  });

Have tried numerous code examples but no luck. Have used AJAX calls in webforms before to a support page, but not sure how to apply in MVC model, controller, view world.
Any help appreciated.
0
 

Author Comment

by:Cubbybulin
ID: 39901365
Whew! figured it out!
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?

733 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