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>
CubbybulinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.