CODEWITHSUNDEEP
c#sqlasp.netodbcparameter-passing
Hytek
Hytek

Reputation: 758

ASP.NET ODBC Query with String parameter

I am using a tableadapter to query a database using ODBC. The query is being created by the VS2015 Query Builder. If I make the SQL statement with a variable parameter then the result comes back blank. If I replace the parameter (the question-mark) with a hardcoded parameter then the result comes back fine. Below is my code for the example where the result comes back blank.

SQL Statement:

SELECT HPD_Help_Desk.Incident_Number, HPD_Help_Desk.Assigned_Group
FROM   HPD_Help_Desk HPD_Help_Desk
WHERE  (HPD_Help_Desk.Incident_Number > 'INC100000230000')
AND    (HPD_Help_Desk.Assigned_Support_Organization LIKE '%?%')

I have set the parameter member details using the Parameter Collection Editor and set DbType to String, which defaults the ProviderType to NVarChar. The rest of the fields I have left as is.

Default.aspx.cs:

public partial class Incidents : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    string s = "Copenhagen POD";
    HPD_Help_DeskTableAdapter incidentsAdapter = new HPD_Help_DeskTableAdapter();        
    GridView1.DataSource = incidentsAdapter.GetIncidentsByAssigned_Support_Organization(s);
    GridView1.DataBind();
    }
}

Default.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" inherits="Incidents" %> 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
<link href="Styles.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
    <h1>Incidents</h1>
    <p>
        <asp:GridView ID="GridView1" runat="server" CssClass="DataWebControlStyle">
           <HeaderStyle CssClass="HeaderStyle" />
           <AlternatingRowStyle CssClass="AlternatingRowStyle" />
        </asp:GridView>
        &nbsp;</p>
</div>
</form>
</body>
</html>

So what am I missing? What should I change to make the SQL statement work properly using the parameter I specify in Default.aspx.cs (Copenhagen POD)?

Upvotes: 0

Views: 495

Answers (1)

Hytek
Hytek

Reputation: 758

By following the suggestion fom René Vogt I instead got a "No mapping exists from DbType Object to a known OdbcType"-error when finishing the TableAdapter Query Configuratino Wizard and also when trying to build the solution. I was setting the parameter using the Parameters Collection Editor.

Still following René Vogt's suggestion, the parameter DbType set itself to Object thus giving the above error. To fix this I opened up the Parameters Collection Editor again and saw that a lot more fields had been auto filled so I went ahead and changed the DbType from Object to String and now it works. So it was using René Vogt's suggestion and making an additional change to the DbType that solved my problem. As a week has almost passed and René Vogt has not written his above comment as an answer to my question, making it possible to give him the credit he deserves, then I have opted to instead add the answer to my own question.

Upvotes: 2

Related Questions