Pass an instruction from sql to an asp: SqlDataSource

Question

Pass an instruction from sql to an asp: SqlDataSource

Navigation

#1 by (1 votes)

0

I have the following statement tested on the sql server:

select AsignaturaNombre from Asignatura where CodigoAsignatura=(SELECT [idMateria] FROM [Permisos] WHERE ([idProfesor] = 19398676) union SELECT [idMateria] FROM [Permisos] WHERE ([idAlumno] = 19398676))

But at the moment of passing sentence to sqldatasource in the following way:

<h5>
    <asp:Label ID="Label4" runat="server" Text="Selecione la materia "></asp:Label>
    <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource1" DataTextField="idMateria" DataValueField="idMateria">
    </asp:DropDownList>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:UMAConnectionString %>" SelectCommand="select AsignaturaNombre from Asignatura where CodigoAsignatura=(SELECT [idMateria] FROM [Permisos] WHERE ([idProfesor] = @idAlumno) union SELECT [idMateria] FROM [Permisos] WHERE ([idAlumno] = @idAlumno))">
    <SelectParameters>
    <asp:SessionParameter Name="idAlumno" SessionField="Codigo" Type="Double" />
    </SelectParameters>
    </asp:SqlDataSource>
</h5>

But he tells me he does not recognize the instruction and jumps an exception:

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'idMateria'.

How can I place that sentence?

sql sql-server asp.net webforms

asked by Sebastian Mateus Villegas 24.03.2017 в 15:27

source

1 answer

Make an alert with php after a form hover effect when passing over an element

score 1 · Accepted Answer

There are several problems:

1. In the configuration properties DataTextField and DataTextField refer to idMateria , which do not exist in your statement.

<asp:DropDownList ID="DropDownList2" runat="server" 
    DataSourceID="SqlDataSource1" 
    DataTextField="idMateria" 
    DataValueField="idMateria">
</asp:DropDownList>

2. In your sentence you only return the name of the subject and do not return the column CodigoAsignatura with the alias idMateria .

SELECT AsignaturaNombre
FROM Asignatura
WHERE CodigoAsignatura =
      (
          SELECT [idMateria]
          FROM [Permisos]
          WHERE ([idProfesor] = 19398676)
          UNION
          SELECT [idMateria]
          FROM [Permisos]
          WHERE ([idAlumno] = 19398676)
      );

Likewise, internally you should be generating the error:

The subquery has returned more than one value, which is not correct when it goes after =,! =, & lt ;, < =, & gt ;, or when it is used as an expression.

To fix

Update your sentence:

SELECT CodigoAsignatura [idMateria]
FROM Asignatura
WHERE CodigoAsignatura IN
      (
          SELECT [idMateria]
          FROM [Permisos]
          WHERE ([idProfesor] = 19398676)
          UNION
          SELECT [idMateria]
          FROM [Permisos]
          WHERE ([idAlumno] = 19398676)
      );