"Input string was not in a correct format" when trying to searcg

hi

i have a database with some data in it and iam using full-text-search to search through my data and the search query works fine in sql server manamgnet studio. so my problem is in my web application, i have a textbox where i enter e.g car and click the search button, the button executes the search query but i recive an error"Input string was not in a correct format". here is the code that the button executes:

ProtectedSub searchButton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)

searchSqlDataSource.SelectCommand =

"Select ID, title, FROM myTable WHERE CONTAINS(description, @.search)"

searchSqlDataSource.SelectParameters.Add(

"search", searchTextBox.Text)

searchSqlDataSource.DataBind()EndSub

tnx in advac

can you set the value of a label for instance to the value of the input string so that you can see the actual string? or have you tried stepping through the code and see what the actual string is that is getting sent. if you have access to a sql management studio, you can post the string into the query window and see what is incorrect with the syntax. give those a try and good luck! -- jp|||

I often get that error message when the text contains a semicolon ";". i.e. searchTextBox.text = "johnny went home; vivi left town."

My only resolve was to filter for the semicolon, but I'm sure there's a better way to do it.

Johnny

"Input string was not in a correct format"

I am trying to do an insert into a SQL server table but I am getting an "input string was not in correct format" error. Does this error always refer to SQL string problem? I put in a breakpoint and looked at the SQL and it looks good but until I figure this out I will be tracing through the code. Any input greatly appreciated.

MyCmd.CommandText = sSQL
MyCmd.ExecuteNonQuery() 'Line where I get the error
Thanks,
JoeThis usually indicates there is a data type mismatch problem. You've probably assigned a value to a parameter which was expecting avalue with a different data type.
|||Ok, in response to your feedback, I've shortened the SQL insert string and I plan on trying to insert first just one field and then next two fields and so on so I can determine which field is giving me a problem, however I can't even insert one field. My code:
Try
MyCmd.CommandText = "INSERT INTO tblTest (intProjectID) VALUES (1)"
MyCmd.ExecuteNonQuery()
Catch e As System.Data.SqlClient.SqlException
Response.Write(e.Message)
End Try
Is still giving me the same errror:
"Input string was not in a correct format" for the MyCmd.ExecuteNonQuery line.
intProjectID of tblTest field is type "int". What am I doing wrong?
Thanks in advance,
Joe

help! how to save user inputs from checkbox list to database?

Hi,

I'm new to ASP.NET 2.0. I'll like to ask how do one save user input from txtbox, radiobttnlist or checkboxlist into database?

Im implementing a suvrey form here by the way.

-any reference webs,

-any pointers from experts?

Thank you for your time in reading this email

Kayln

Hi,

here is an example:

Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As eventArgs) Handles btnSubmit.Click

SqlDataSource1.InsertParameters.Add("DataBase_Field_Name", System.TypeCode.String, CheckBoxList1.SelectedValue)

SqlDataSource1.Insert()

End Sub

Hope it helps,

Regards

--------------------------------

Please do not forget to mark as answered the correct answer. Thanks

|||

HiStoian Bucovich,

I do not understand your example. Do i implement it in the source code or do i attach it to what I already have if so where do i place it exactly?

Thnk you for your time

Kayln

|||

HiPrincessKitana,

in my example I am using separate file for code behind (it doesn't realy matter)

- in .aspx file you should have sqldatasource1 connecting you to your database table and allowing you to insert, delete, update and for sure your CheckBoxList1 with its items set by value="1" for example + a button (btnSubmit)

- in your code behind file .aspx.vb you should place the above code to handle after the checkbox was selected and button was clicked event all you have to configure is to set the proper DB column name ("DataBase_Field_Name") and the typecode that you will insert in this example I am showing String

Hope it helps

Regards

------------------------------

Please do not forget to mark as answered the correct answer. Thanks

|||

Dear Stoian,

lets go through this real slowly, cos Im still very green in this.

1) I have tocreate a database table say icecream flavor. so my options can are vanilla, chocolate or strawberry.

2) Ineed to place sqldatasource in my web form to retrieve this database.

3)Go on which my question ie which flavor do you like..then i have a checkboxlist with its datasource set to my sqldatasource.I do not understand "your CheckBoxList1 with its items set by value="1""

4)then I place a button , have it ID property: btnSubmit, Textbutton: Submit

5)then Idble click on the submit button to place an event handler, ie copy and paste below into my event handler?

********Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As eventArgs) Handles btnSubmit.Click

SqlDataSource1.InsertParameters.Add("DataBase_Field_Name", System.TypeCode.String, CheckBoxList1.SelectedValue)

SqlDataSource1.Insert()

End Sub***********

6) I do not understand ""all you have to configure is to set the proper DB column name ("DataBase_Field_Name") and the typecode that you will insert in this example I am showing String"

Distressed Kayln [:( ]

|||

Hi PrincessKitana,

First of all nice nick name, second I am sorry for my late replay, and third the answer:

- first you have to make your database lets name it "flavors" and create table in it lets say its name will be "icecreams" and this table must have the following entries:

1. ID data type "int" allow null "unchecked" set as primary key and autoincrement "yes"

2. Flavors data type "nvarchar(100)" allow null "checked"

Thats all for the data base

So long so good, next is to set the sqldatasource in your lets say Default.aspx file (just drag and drop the control named "SqlDataSource" and select your database and credentials etc. (p.s. if you need help about this please feel free to ask)

Then its time to make your CheckBoxList control and set its values:

<asp:CheckBoxListID="CheckBoxList1"runat="server">

<asp:ListItemSelected="true"Value="Vanilla">Vanilla</asp:ListItem>

<asp:ListItemValue="chocolate">chocolate</asp:ListItem>

<asp:ListItemValue="strawberry">strawberry</asp:ListItem>

</asp:CheckBoxList>

Then build your submit button

<asp:Button ID="btnSubmit" runat="server" Text="Submit" />

Its time to do some work in code behind (I am using VB in this example), double click the submit button in design view and you will see:

Private Sub bntSubmit_Click(ByVal sender As Object .........etc.

End Sub

Paste in it the code that I show you above and change only the field "DataBase_Field_Name" to "Flavors"

Thats it hope it helps

Regards

---------------------------

Please do not forget to mark as "Answer" the correct answer. Thanks

|||

Dear Stoian,

yes thank you for the compliment. anyway thanks to you, i managed to figure out your example but i have this problem that came up.

dun really know what it is??

SqlDataSource1.InsertParameters.Add("flavors", System.TypeCode.String, CheckBoxList1.SelectedValue)

{ SqlDataSource1.Insert() }------>>Inserting is not supported by data source 'SqlDataSource1' unless InsertCommand is specified.

CHeeRs,

Kayln

|||

Hi,

I guess you didn't checked Insert/Delete/Update field when you make your SqlDataSource connection any way, here is the complete code

- Default.aspx

<%@.PageLanguage="VB"AutoEventWireup="false"CodeFile="Default.aspx.vb"Inherits="_Default" %>

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

<headrunat="server">

<title>Untitled Page</title>

</head>

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

<div>

<!-- Here is the sql data source -->

<asp:SqlDataSourceID="SqlDataSource1"

runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

DeleteCommand="DELETE FROM [icecreams] WHERE [ID] = @.ID"

InsertCommand="INSERT INTO [icecreams] ([Flavors]) VALUES (@.Flavors)"

SelectCommand="SELECT * FROM [icecreams]"

UpdateCommand="UPDATE [icecreams] SET [Flavors] = @.Flavors WHERE [ID] = @.ID">

<DeleteParameters>

<asp:ParameterName="ID"Type="Int32"/> </DeleteParameters>

<UpdateParameters>

<asp:ParameterName="Flavors"Type="String"/>

<asp:ParameterName="ID"Type="Int32"/>

</UpdateParameters>

<InsertParameters> </InsertParameters>

</asp:SqlDataSource>

<!-- here is the check box list -->

<asp:CheckBoxListID="CheckBoxList1"runat="server">

<asp:ListItemSelected="true"Value="Vanilla">Vanilla</asp:ListItem>

<asp:ListItemValue="chocolate ">chocolate</asp:ListItem>

<asp:ListItemValue="strawberry">strawberry</asp:ListItem>

</asp:CheckBoxList>

<br/>

<!-- here is the button -->

<asp:ButtonID="btnSubmit"runat="server"Text="Submit"/>

</div>

</form> </body>

</html>

- Default.aspx.vb code

PartialClass _Default

Inherits System.Web.UI.Page

PrivateSub btnSubmit_Click(ByVal senderAsObject,ByVal eAs EventArgs)Handles btnSubmit.ClickSqlDataSource1.InsertParameters.Add("Flavors", System.TypeCode.String, CheckBoxList1.SelectedValue)

SqlDataSource1.Insert()

EndSub

EndClass

- and web.config

<?xmlversion="1.0"?><!--

Note: As an alternative to hand editing this file you can use the

web admin tool to configure settings for your application. Use

the Website->Asp.Net Configuration option in Visual Studio.

A full list of settings and comments can be found in

machine.config.comments usually located in

\Windows\Microsoft.Net\Framework\v2.x\Config

-->

<configuration> <connectionStrings>

<addname="ConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\flavors.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

<appSettings/>

<system.web>

<!--

Set compilation debug="true" to insert debugging

symbols into the compiled page. Because this

affects performance, set this value to true only

during development.

Visual Basic options:

Set strict="true" to disallow all data type conversions

where data loss can occur.

Set explicit="true" to force declaration of all variables.

-->

<compilationdebug="true"strict="false"explicit="true">

</compilation>

<pages>

<namespaces>

<clear/>

<addnamespace="System"/>

<addnamespace="System.Collections"/>

<addnamespace="System.Collections.Generic"/>

<addnamespace="System.Collections.Specialized"/>

<addnamespace="System.Configuration"/>

<addnamespace="System.Text"/>

<addnamespace="System.Text.RegularExpressions"/>

<addnamespace="System.Web"/>

<addnamespace="System.Web.Caching"/>

<addnamespace="System.Web.SessionState"/>

<addnamespace="System.Web.Security"/>

<addnamespace="System.Web.Profile"/>

<addnamespace="System.Web.UI"/>

<addnamespace="System.Web.UI.WebControls"/>

<addnamespace="System.Web.UI.WebControls.WebParts"/>

<addnamespace="System.Web.UI.HtmlControls"/>

</namespaces> </pages>

<!--

The <authentication> section enables configuration

of the security authentication mode used by

ASP.NET to identify an incoming user.

--> <authenticationmode="Windows"/>

<!--

The <customErrors> section enables configuration

of what to do if/when an unhandled error occurs

during the execution of a request. Specifically,

it enables developers to configure html error pages

to be displayed in place of a error stack trace.

<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">

<error statusCode="403" redirect="NoAccess.htm" />

<error statusCode="404" redirect="FileNotFound.htm" />

</customErrors>

--> </system.web>

</configuration>

All you have to do is to make the database as told you above :)

Hope it helps

Regards

Stoian