0

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)?

Improve this question
4
  • 1
    Just guessing: the statement should be ... LIKE ?) and your parameter value should be %<yourvalue>%. I don't think ODBC takes a question mark inside '..' as parameter placeholder. Commented Mar 23, 2016 at 11:37
  • I followed your suggestion but instead I get 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. Commented Mar 23, 2016 at 12:50
  • 1
    Can't see where you set the parameter. So I keep guessing: can you set the DbType property of the parameter to DbType.String (which actually should be default, but anyway). Commented Mar 23, 2016 at 12:53
  • I am setting the parameter using the Parameters Collection Editor. Following your suggestion the parameter DbType set itself to Object thus giving the error. 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 your suggestion and making an additional change to the DbType that solved my problem. If you write your first comment as an answer to my question then I can select it as the correct answer and give you the credit you deserve. Commented Mar 23, 2016 at 13:18

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.