Solved

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

Posted on 2014-02-27
6
10,371 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
  • 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

747 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

14 Experts available now in Live!

Get 1:1 Help Now