Type mismatch in .net query

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi - I tried this in VS.Net, and also in the Web Matrix code below: -
but I am getting a type mismatch error.

The sql statement runs perfectly from within the Access Query Designer.

Can anyone verify the syntax, and that my .net code is ok - the type
mismatch is happening at: DataGrid1.DataSource =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)

Thanks in advance,




<%@ Page Language="VB" debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDB" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Dim ConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data
Source=C:\dbweb\clearblue2.mdb"
Dim CommandText As String = "SELECT
IIf(Sum(G1_PossScore)=0,0,Int(Sum(G1_Score)/Sum(G1_PossScore)*100)) AS
G1, Temps.Name FROM tblkbmonitor LEFT JOIN Temps ON
tblkbmonitor.ddlAuthor = Temps.T_ID GROUP BY Temps.Name;"
Dim myConnection As New OleDBConnection(ConnectionString)
Dim myCommand As New OleDBCommand(CommandText, myConnection)
myConnection.Open()
DataGrid1.DataSource =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataBind()
End Sub
</script>

<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
<h2>Simple Data Report
</h2>
<hr size="1" />
<form runat="server">
<asp:datagrid id="DataGrid1" runat="server" CellSpacing="1"
GridLines="None" CellPadding="3" BackColor="White" ForeColor="Black"
EnableViewState="False">
<HeaderStyle font-bold="True" forecolor="white"
backcolor="#4A3C8C"></HeaderStyle>
<ItemStyle backcolor="#DEDFDE"></ItemStyle>
</asp:datagrid>
</form>
</body>
</html>




Stacktrace:

[OleDbException (0x80004005): Type mismatch in expression.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr) +41

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPA
RAMS dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult) +92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior
behavior) +69
ASP.test3_aspx.Page_Load(Object Sender, EventArgs E) in
C:\Inetpub\wwwroot\aspxtest\test3.aspx:20
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()
 
Mark said:
Hi - I tried this in VS.Net, and also in the Web Matrix code below: -
but I am getting a type mismatch error.

The sql statement runs perfectly from within the Access Query Designer.

Can anyone verify the syntax, and that my .net code is ok - the type
mismatch is happening at: DataGrid1.DataSource =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)

Thanks in advance,




<%@ Page Language="VB" debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDB" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Dim ConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data
Source=C:\dbweb\clearblue2.mdb"
Dim CommandText As String = "SELECT
IIf(Sum(G1_PossScore)=0,0,Int(Sum(G1_Score)/Sum(G1_PossScore)*100)) AS
G1, Temps.Name FROM tblkbmonitor LEFT JOIN Temps ON
tblkbmonitor.ddlAuthor = Temps.T_ID GROUP BY Temps.Name;"
Dim myConnection As New OleDBConnection(ConnectionString)
Dim myCommand As New OleDBCommand(CommandText, myConnection)
myConnection.Open()
DataGrid1.DataSource =
myCommand.ExecuteReader(CommandBehavior.CloseConnection)
DataGrid1.DataBind()
End Sub
</script>

<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
<h2>Simple Data Report
</h2>
<hr size="1" />
<form runat="server">
<asp:datagrid id="DataGrid1" runat="server" CellSpacing="1"
GridLines="None" CellPadding="3" BackColor="White" ForeColor="Black"
EnableViewState="False">
<HeaderStyle font-bold="True" forecolor="white"
backcolor="#4A3C8C"></HeaderStyle>
<ItemStyle backcolor="#DEDFDE"></ItemStyle>
</asp:datagrid>
</form>
</body>
</html>




Stacktrace:

[OleDbException (0x80004005): Type mismatch in expression.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32
hr) +41

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPA
RAMS dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&
executeResult) +92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior
behavior, Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior
behavior) +69
ASP.test3_aspx.Page_Load(Object Sender, EventArgs E) in
C:\Inetpub\wwwroot\aspxtest\test3.aspx:20
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()
Click to expand...

Try using ";" instead of "," in the IIF function for separating arguments
 
Back
Top