Friday, February 24, 2012

almost done but stuck

Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so

created a new database with text indexing enabled and the following table

createtable support
(problemIdVARCHAR(50)NOTNULLPRIMARYKEY,
problemTitlevarchar(50)NOTNULL,
problemBodytextNOTNULL,
linkOnevarchar(50),
linkTwovarchar(50),
linkThreevarchar(50),
linkFourvarchar(50),ftid int NOT NULL)

next

createfulltextcatalog remoteSupportCatalog

createuniqueindex ui_remotesupportON support(ftid)

then

createfulltextindexon support(problemBody)keyindex PK__support__7C8480AEon remoteSupportCatalog

---

I then populated some rows and issues a quesry

Select * from support where freetext(problemBody, 'test database')

it works pulls back all the data I expected it to pull back

In my asp page I created a database connection with the folling select command

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @.srchBox)">

created the search parameter

<SelectParameters>

<asp:ControlParameterControlID="srchBox"PropertyName="Text"Type="String"Name="srchBox"/> //this is a text box that is searchable with a button

</SelectParameters>

and it doesnt give me back an error or data it does nothing. What am I missing???

here is the whole asp page

<%@.PageLanguage="C#" %>

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

<scriptrunat="server">

</script>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<bodybgcolor="#e4e4e4">

<formid="form1"runat="server">

<div>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE freetext(problemBody, @.srchBox ) ">

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

</asp:SqlDataSource>

<tablestyle="z-index: 100; left: 107px; position: absolute; top: 144px; width: 695px; height: 443px;"bgcolor="#000000">

<tr>

<tdstyle="width: 475px; height: 125px">

<asp:DetailsViewID="DetailsView1"runat="server"AllowPaging="True"AutoGenerateRows="False"

CellPadding="4"DataKeyNames="problemId"DataSourceID="SqlDataSource1"ForeColor="#333333"

GridLines="None"Height="52px"Width="679px">

<FooterStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<CommandRowStyleBackColor="#C5BBAF"Font-Bold="True"/>

<EditRowStyleBackColor="#7C6F57"/>

<RowStyleBackColor="#E3EAEB"/>

<PagerStyleBackColor="#666666"ForeColor="White"HorizontalAlign="Center"/>

<Fields>

<asp:BoundFieldDataField="problemId"HeaderText="Id:"ReadOnly="True"SortExpression="problemId">

<ItemStyleWidth="600px"BorderColor="White"BorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemTitle"HeaderText="Description:"SortExpression="problemTitle">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemBody"HeaderText="Resolution:"SortExpression="problemBody">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkOne"HeaderText="Links:"SortExpression="linkOne">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkTwo"HeaderText="linkTwo"SortExpression="linkTwo"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkThree"HeaderText="linkThree"SortExpression="linkThree"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkFour"HeaderText="linkFour"SortExpression="linkFour"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

</Fields>

<FieldHeaderStyleBackColor="#D0D0D0"Font-Bold="True"/>

<HeaderStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<AlternatingRowStyleBackColor="White"/>

</asp:DetailsView>

<asp:LabelID="Label1"runat="server"Font-Bold="True"ForeColor="White"Style="z-index: 103;

left: 13px; position: absolute; top: 59px"Width="521px"></asp:Label>

<asp:TextBoxID="srchBox"runat="server"Style="z-index: 101; left: 11px; position: absolute;

top: 31px"Width="520px"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Style="z-index: 102; left: 555px; position: absolute;

top: 31px"Text="Search It"/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"></asp:SqlDataSource>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

|||

here is the whole asp page

<%@.PageLanguage="C#" %>

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

<scriptrunat="server">

</script> <htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title> </head>

<bodybgcolor="#e4e4e4">

<formid="form1"runat="server">

<div>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"

SelectCommand="SELECT * FROM support WHERE freetext(problemBody, @.srchBox ) ">

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

</asp:SqlDataSource>

<tablestyle="z-index: 100; left: 107px; position: absolute; top: 144px; width: 695px; height: 443px;"bgcolor="#000000">

<tr>

<tdstyle="width: 475px; height: 125px">

<asp:DetailsViewID="DetailsView1"runat="server"AllowPaging="True"AutoGenerateRows="False"

CellPadding="4"DataKeyNames="problemId"DataSourceID="SqlDataSource1"ForeColor="#333333"

GridLines="None"Height="52px"Width="679px">

<FooterStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<CommandRowStyleBackColor="#C5BBAF"Font-Bold="True"/>

<EditRowStyleBackColor="#7C6F57"/>

<RowStyleBackColor="#E3EAEB"/>

<PagerStyleBackColor="#666666"ForeColor="White"HorizontalAlign="Center"/>

<Fields>

<asp:BoundFieldDataField="problemId"HeaderText="Id:"ReadOnly="True"SortExpression="problemId">

<ItemStyleWidth="600px"BorderColor="White"BorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemTitle"HeaderText="Description:"SortExpression="problemTitle">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="problemBody"HeaderText="Resolution:"SortExpression="problemBody">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkOne"HeaderText="Links:"SortExpression="linkOne">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkTwo"HeaderText="linkTwo"SortExpression="linkTwo"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkThree"HeaderText="linkThree"SortExpression="linkThree"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

<asp:BoundFieldDataField="linkFour"HeaderText="linkFour"SortExpression="linkFour"ShowHeader="False">

<ItemStyleBorderStyle="Solid"BorderWidth="1px"/>

<HeaderStyleBorderStyle="Solid"BorderWidth="1px"/>

</asp:BoundField>

</Fields>

<FieldHeaderStyleBackColor="#D0D0D0"Font-Bold="True"/>

<HeaderStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/>

<AlternatingRowStyleBackColor="White"/>

</asp:DetailsView>

<asp:LabelID="Label1"runat="server"Font-Bold="True"ForeColor="White"Style="z-index: 103;

left: 13px; position: absolute; top: 59px"Width="521px"></asp:Label>

<asp:TextBoxID="srchBox"runat="server"Style="z-index: 101; left: 11px; position: absolute;

top: 31px"Width="520px"></asp:TextBox>

<asp:ButtonID="Button1"runat="server"Style="z-index: 102; left: 555px; position: absolute;

top: 31px"Text="Search It"/>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"></asp:SqlDataSource>

</td></tr>

</table>

</div>

</form> </body>

</html>

|||

quarinteen:

<SelectParameters>

<asp:ParameterName="srchBox"/>

</SelectParameters>

Have you tried to use Control parameter ? One more thing, what is the code you have written to execute the select method ? Can you post the code behind code also ?

|||

There is not any c# or vb to control the select statement. Just the asp.

|||

What I think is that you must be executing the datasource.select() method on the button click event, otherwise when would you bind the data to your grid after the user writes something in the textbox ? You've created everything and shown everything to the user.

Now, when user writes something in the textbox and presses the "Search It" button, you must execute the datasource.select() method. This is my assumption after reading your code. Post some more details as to what you are doing now and what is happing ( does any error occur ? ) so that I can help you better.

|||

no there is no c# or vb on this page that I have writter. Also I know varyations of this works I I put in the select statement "Select * from support where ((problemBody '%' + @.srchBox + '%') OR @.srchBox IS NULL)" it brings back the data

|||

Take the page you posted in the 2nd message. Change the parameter to a control parameter like you posted in the first message. Then change the sqldatasource, so that the "CancelSelectOnNullParameter" property is set to false. Change the search textbox, so it's autopostback property is set to true.

You may also need to change the parameter's properties to keep it from changing empty string to null, depending on how the freetext function interprets null parameters. For that matter, it might not like the empty string either.

No comments:

Post a Comment