• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11482
  • Last Modified:

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

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
Cubbybulin
Asked:
Cubbybulin
  • 4
  • 2
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
CubbybulinAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
CubbybulinAuthor Commented:
Great, thank you!
0
 
CubbybulinAuthor Commented:
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
 
CubbybulinAuthor Commented:
Whew! figured it out!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now