|
|
I am trying to create a Product display using a Stored Procedure and a Datareader bound to a DataList to display just the product category but unfortunately I keep getting an error that I do not understand at all.The error is as follows..
==============================================================
Error 1
Value of type 'System.Data.SqlClient.SqlDataReader' cannot be converted to '1-dimensional array of System.Data.SqlClient.SqlDataReader'.
BELOW is my CODE
================================================================
Imports System.Data
Imports System.Data.SQLClient
Partial Class Ch8Case2
Inherits System.Web.UI.Page
Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connectionString, cats As String
connectionString = "server=(local)/SqlExpress; database=Ch8Case2"
Dim connection As New SqlConnection(connectionString)
Dim cmd As New SqlCommand("ProductCategoryList", connection)
Dim sAdapt As New SqlDataAdapter(cmd)
cmd.CommandType = CommandType.StoredProcedure
'create the DataReader
Dim reader As SqlClient.SqlDataReader()
'Execute and get a single result set
connection.Open()
' Data is accessible through the DataReader object here.
reader = cmd.ExecuteReader()
reader.Close()
connection.Close()
MyList.DataSource = reader
MyList.DataBind()
End Sub
End Class
================================================================
|
|
|
|
You have several erros here.
You closed the reader so that you can no longer get data from it.
Also you want to put your SQL Statement into a function and return and SQLDatareader from the function.
Than in the page load you can bind the data. Also you have a dataadapter in there that you don't need.
Try something like this. Assuming your SQL is correct it should work.
Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
MyList.DataSource = GetData()
MyList.DataBind()
End Sub
Private Function GetData() As SqlDataReader
'## Create Instance of Connection and Command Object
Dim objConn As SqlConnection = New SqlConnection("server=(local)/SqlExpress; database=Ch8Case2")
Dim objCmd As SqlCommand = New SqlCommand("ProductCategoryList", objConn)
objCmd.CommandType = CommandType.StoredProcedure
Dim objRdr As SqlDataReader
objConn.Open()
objRdr = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
Return objRdr
End Function
|
|
|
|
You closed the reader so that you can no longer get data from it.
Also you want to put your SQL Statement into a function and return and SQLDatareader from the function.
Than in the page load you can bind the data. Also you have a dataadapter in there that you don't need.
Try something like this. Assuming your SQL is correct it should work.
Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
=========================================================
I don't mean to bug you but I would really love it if you would please help me understand why I got the error I did.... Actually, the code I posted from straight from the text I was using to learn.
It did have all the information in the Page_load method and I have used similar method in the past and it worked but for some reason, in this particular case it did not and the error was from this statement
reader = cmd.ExecuteReader()
in the code. I did try using
objRdr = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
earlier but it still posted an error, that it was not valid and I want to know what I can do to avoid that happening in the future.
I would also like to say that I did get another wierd error when I tried using the code you provided me. the error is associated with this line of code
objConn.Open()
and this is the error....
Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
The Datebase test was successful so I don't understand what happened here and the SQL statement is valid. Could you please explain your answer please... cause from what I know, the books don't cover them all
|
|
|
|
You ask alot of questions an I hope I get to them. If I miss one let me know and I will try to help.
It did have all the information in the Page_load method and I have used similar method in the past and it worked but for some reason, in this particular case it did not and the error was from this statement
|
|
Look at the order of your code...
You have:
reader = cmd.ExecuteReader()
reader.Close()
connection.Close()
MyList.DataSource = reader
MyList.DataBind()
Should be:
reader = cmd.ExecuteReader()
MyList.DataSource = reader
MyList.DataBind()
reader.Close()
connection.Close()
You cannot close a datareader and then expect to get data from it.
Next...
and this is the error....
Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
|
|
I did this code from the top of my head so you may be using bits and pices of each. I would use your original code for Connection object and string which is:
Dim connectionString, cats As String
connectionString = "server=(local)/SqlExpress; database=Ch8Case2"
Dim connection As New SqlConnection(connectionString)
And then use the following to oppen your connection.
connection.Open()
Lastly:
| Actually, the code I posted from straight from the text I was using to learn. |
|
Either you didn't copy the code exactly, or the code example you are using is wrong.
Hope that helps.
JB
|
|
|
|
I tried all the ideas I got and came up with this code but it still does nothing and I have no clue why? I have run debugger and no error popped up, I have tried building the page and still nothing seems to be happening. Could you please look through my code this time and see if I did it wrong??
---------------------------------------------------------
<%@ Page Language="VB" AutoEventWireup="false"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script language="VB" runat="server">
Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim connString As String = "Server=(local);Database=Ch8Case2"
'define the command query
'specify the data source
Dim conn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(connString)
Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("ProductCategoryList", conn)
command.CommandType = CommandType.StoredProcedure
Try
'open the connection
conn.Open()
Dim reader As Data.SqlClient.SqlDataReader = command.ExecuteReader()
' bind the datalist to the reader
DataList1.DataSource = reader
DataList1.DataBind()
Catch ex As Exception
' Exception handling code goes here
Finally
' close the connection
conn.Close()
End Try
Dim CatID As Integer = CInt(Request.Params("CatID"))
conn.Open()
Dim comm2 As SqlCommand
comm2 = New SqlCommand("DisplayProducts", conn)
comm2.CommandType = CommandType.StoredProcedure
Dim pCatID As SqlParameter
pCatID = New SqlParameter("@CategoryID", SqlDbType.Int, 4)
pCatID.Value = CatID
comm2.Parameters.Add(pCatID)
Dim reader2 As SqlDataReader
reader2 = comm2.ExecuteReader(CommandBehavior.CloseConnection)
DataList2.DataSource = reader2
DataList2.DataBind()
reader2.Close()
conn.Close()
End Sub
Protected Sub DataList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataList1.SelectedIndexChanged
DataList2.SelectedIndex = DataList1.SelectedIndex
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Binding Data to DataList with Data Reader</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<asp:DataList ID="DataList1" runat="server" CellPadding="4" ForeColor="#333333"
Style="z-index: 16; left: 28px; position: absolute; top: 59px">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<SelectedItemStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<AlternatingItemStyle BackColor="White" ForeColor="#284775" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<ItemTemplate>
<asp:Label id="lblCat"
Text='<%# DataBinder.Eval(Container.DataItem, "CategoryName") %>'
runat="server" />
</ItemTemplate>
<ItemStyle BackColor="#F7F6F3" ForeColor="#333333" />
</asp:DataList>
</div>
<asp:DataList ID="DataList2" runat="server" CellPadding="4" ForeColor="#333333"
Style="z-index: 101; left: 0px; position: absolute; top: 0px">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<SelectedItemStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<AlternatingItemStyle BackColor="White" ForeColor="#284775" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<ItemStyle BackColor="#F7F6F3" ForeColor="#333333" />
</asp:DataList>
</form>
</body>
</html>
|
|
|
|
|
|
|
|
Reaction
