Problem with "converting the nvarchar value 'xxxxxID' to a column of data type int".

Hi,

I have a webform with a drop-down listbox, binded to a table and a GridView, binded to another table. My goal is when I select a value (country in my case) in the drop-down listbox to change what is shown in the GridView, based on the ID that I get from the drop-down listbox.

Here is the code (the important parts...):

Select a Country : <asp:DropDownList ID="CountriesDropDownList" runat="server" DataSourceID="SqlDataSource2" DataTextField="Name" DataValueField="CountryID" Width="220px" AutoPostBack="True">

</asp:DropDownList>

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"

SelectCommand="SELECT [CountryID], [Name] FROM [DS_Country]">

</asp:SqlDataSource>

<asp:GridView ID="ProvincesGridView" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ProvinceID,CountryID,Name,ShortName,Description" DataSourceID="SqlDataSource1" BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3" HorizontalAlign="Left" >

<Columns>

..............................

</Columns>

</

asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DineSelectConnectionString %>"

DeleteCommand="DELETE FROM DS_Province WHERE ProvinceID=@ProvinceID"

SelectCommand="SELECT ProvinceID,CountryID,Name,ShortName,Description FROM DS_Province WHERE CountryID=@ddlCountryID"

UpdateCommand="UPDATE DS_Province SET CountryID=@CountryID,Name=@Name,ShortName=@ShortName,Description=@Description WHERE ProvinceID=@ddlCountryID"

InsertCommand="INSERT INTO DS_Province (CountryID,Name,ShortName,Description) VALUES (@CountryID,@Name,@ShortName,@Description)">

<SelectParameters>

<asp:ControlParameter Name="ddlCountryID" ControlID="CountriesDropDownList" PropertyName="DataValueField" />

</SelectParameters>

</asp:SqlDataSource>

 

When I run it I get this error "Syntax error converting the nvarchar value 'CountryID' to a column of data type int.". If I specify the Type="Int" in <asp:ControlParameter>..</> I get "Input string was not in a correct format. "

This is supposed to be easy, but I can't make it work ! Any suggestions will be more than welcome.

Thanks.

0
Nick1234
1/24/2007 4:17:59 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

10 Replies
642 Views

Similar Articles

[PageSpeed] 22

try this:

SelectCommand="SELECT ProvinceID,CountryID,Name,ShortName,Description FROM DS_Province WHERE CountryID=@CountryID"

<SelectParameters>

<asp:ControlParameter ControlID="CountriesDropDownList" Name="CountryID" PropertyName="SelectedValue" Type="String" />

</SelectParameters>

Your DataKeyNames don't need include all your columns, just key or keys.

If your countryid column is integer, you need change type to  Type="Int".


Limno

0
limno
1/24/2007 5:10:00 AM

Hi,

Thanks for the reply.

I do not know how the Name change will help me, but I followed your suggestion anyway. And I changed the Type to Int16, Int32 and Int64 - none of them worked for me. As I said - I already did this, before I posted my question here in the forum.

Anyway, the error that I get when I put Type ="Int16|Int32|Int64" is

"Input string was not in a correct format."

Thanks.

0
Nick1234
1/24/2007 2:51:20 PM

Your PropertyName in your ControlParamter is wrong.

"<SelectParameters>

 

<asp:ControlParameter Name="ddlCountryID" ControlID="CountriesDropDownList" PropertyName="DataValueField" />

 

</SelectParameters> "
Limno

0
limno
1/24/2007 2:54:47 PM

Thanks for your reply,

This SOLVED my problem! Thank you for your help. It is priceless to have another pair of eyes to look in your code :)!

Thanks again.

0
Nick1234
1/24/2007 3:09:54 PM

Hi,

I have another problem now. The UPDATE command doesn;t update. If I run it in the Query analyzer it runs ok. What do I miss?

Here is the command:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DineSelectConnectionString %>"

SelectCommand="SELECT * FROM [DS_Province] WHERE CountryID=@CountryID"

UpdateCommand="UPDATE DS_Province SET CountryID=@CountryID,Name=@Name,ShortName=@ShortName,Description=@Description WHERE ProvinceID=@ProvinceID">

<SelectParameters>

<asp:ControlParameter Name="CountryID" ControlID="CountriesDropDownList" PropertyName="SelectedValue" />

</SelectParameters>

</asp:SqlDataSource>

Thanks.

0
Nick1234
1/25/2007 3:59:47 AM
Could you post your gridview code here too? I cannot tell from what you post here. Thanks

Limno

0
limno
1/25/2007 4:24:55 AM

Thank you Limno,

Here it is:

<

asp:GridView ID="ProvincesGridView" runat="server" AllowPaging="True" AllowSorting="True"

AutoGenerateColumns="False" DataKeyNames="ProvinceID,CountryID,Name,ShortName,Description" DataSourceID="SqlDataSource1"

BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3"

HorizontalAlign="Left" >

<Columns>

<asp:CommandField ShowSelectButton="True" ShowDeleteButton="True" ShowEditButton="True" />

<asp:BoundField DataField="ProvinceID" HeaderText="ProvinceID"

InsertVisible="False" ReadOnly="True" SortExpression="ProvinceID" />

<asp:BoundField DataField="CountryID" HeaderText="CountryID" ReadOnly="True" SortExpression="CountryID" />

<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />

<asp:BoundField DataField="ShortName" HeaderText="ShortName" SortExpression="ShortName" />

<asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />

</Columns>

<FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C" />

<RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />

<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />

<PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />

<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />

<AlternatingRowStyle BackColor="#F7F7F7" />

</asp:GridView>

 

 

0
Nick1234
1/25/2007 4:30:29 AM

Hello:

DataKeyNames="ProvinceID,CountryID,Name,ShortName,Description" 

Change to DataKeyNames="ProvinceID"

Other parts look fine too me.

If this doesn't fix the problem, you can recreate the whole thing from scratch. Grab a gridview and configure your datasource and add your select parameter. 


Limno

0
limno
1/25/2007 6:08:13 AM

Yes, it worked. And Yes, I had to re-create the whole thing. It would be nice to know why I needed to recreate it, but it works now and I am happy.

Thank you Limno, you are a great help!

0
Nick1234
1/25/2007 8:30:49 PM
I had that kind of problem before. Redo fixed it. I don't want to mess up with the why for this, but I do know your code looks right. Move on. Enjoy.
Limno

0
limno
1/25/2007 9:18:19 PM
Reply:

Similar Artilces:

Getting Server Error "Syntax error converting the nvarchar value 'Sonoma' to a column of data type int."
Hi, all I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code. First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]"> </asp:SqlDataSource>  And the dropdown list's code:<asp:DropDownList ID="ddlPayrol...

Exception {"Conversion failed when converting the nvarchar value 'Chuck Designs' to data type int."}
I have the below methods. They generate the error above at the sqlreader.read(); line. Please help.  All I want is a simple true false check to see if a value is already in the db.public static void InsertVendor(string vendor) {if (!CheckVendorValue(vendor) == true) {using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["VendorEvaluationConnectionString"].ConnectionString)) { conn.Open();SqlCommand cmd = new SqlCommand("Insert into tblVendors values (@vendor_name)", conn); cmd.Parameters.AddWithValue("@vendor_name", ve...

Syntax error converting the nvarchar value 'no data' to a column of data type int
Hi all Here is an error I'm getting that I hope you can help with. Exception Details: System.Data.SqlClient.SqlException: Syntax error converting the nvarchar value 'no data' to a column of data type int. basically I'm trying to update a record using the Insert template in FormVeiw. The problem field is related to a  dropdownlist field that works as follows <asp:Label ID="LabelSchemeArea" runat="server" Visible="False" /> 'labelSchemeArea contains a value such as 1 or 2 or 3 <td style="width: 209px">Scheme...

Null values and Profile data "Unable to cast object of type 'System.DBNull' to type 'System.String'"
I've made my own ProfileProvider but when I am trying to read null values by txt.Text = Profile.FirstName I get the error "Unable to cast object of type 'System.DBNull' to type 'System.String'."This is how the code (which I don't write by myself looks like): How to fix the problem??public virtual string FirstName { get { return ((string)(this.GetPropertyValue("FirstName"))); } set { this.SetPropertyValue("FirstName", value); } }  You can check for a null value as sh...

Syntax error converting the nvarchar value '*' to a column of data type int.
This error is pointing to the dashed line, but where is this trying to convert this into an integer. the only one of these fields that have a "*" is the account name. Dim Cmd as SQLCommand Cmd = New SQLCommand( "SELECT DISTINCT dbo.AccountsInfo.AccountID, dbo.AccountsToMove.Move, dbo.AccountsInfo.AccountName, dbo.AccountsInfo.City, dbo.AccountsInfo.State, dbo.AccountsToMove.TerritoryID, dbo.AccountsToMove.SalesLastYear FROM dbo.AccountsToMove INNER JOIN dbo.AccountsInfo ON dbo.AccountsToMove.AccountID = dbo.AccountsInfo.AccountID Where dbo.Accountstomove.TerritoryID = ...

error in SQL Store Proc ''Conversion failed when converting the varchar value to data type int'
Hi , I am having problem and error of 'Conversion failed when converting the varchar value to data type int ' CREATE PROC dbo.testingloop (DECLARE @testid varchar(max))ASBEGINSET NOCOUNT ONDECLARE @SQL varchar(600)SET @testvalue = CURSOR FORSELECT [test ID])  FROM Data42 where [test ID] = 36OPEN @testvalue      FETCH NEXT     FROM @testvalue INTO @testid      WHILE @@FETCH_STATUS = 0       BEGIN      ' My update statement here  ...

Syntax error converting the varchar value 'MatterID.Value' to a column of data type int.
I'm getting this error when I pass a value via querystring into a second page and try to use that value in my sql select statement. ... just passing a value and trying to use it to select.  In the db, MatterID is an int.  How do I pass it as an int instead of a varchar?  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadMatterID.Value = Request.QueryString("MatterID")End Sub Protected Sub CalculateRunningTotal()Dim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionString As...

Update error , "Unable to cast object of type 'System.DBNull' to type 'System.String'" , using DataSet as a datasource
Hi All, The following sample code is for testing dw.UpdateData using dataset as the data source. The data retrieval and display part under Form1_Load works fine. However, inputting some values in a new row and calling dw.UpdateData causes the following error: "Unable to cast object of type 'System.DBNull' to type 'System.String'" The error seems to occur when a datawindow column value has been changed from null value. Does anyone know how to fix the error ? Thank you for your advice. I use DW2.5 (build 8542), VS2005 pro, MS SQL Express. ...

System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type int
 I am using  a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure;  SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@op...

Can't get away from: "Could not open in Design View. Quote values differently inside a '<% ... "value" ... %>' block."
I keep getting this error message when I try to switch to design view. This is the code that is causing it. OnClick="javascript:void(0);ToggleRowDisplay(this,'row<%# DataBinder.Eval(Container.DataItem, "Equipment_ID") %>');" When I take out this section I can go into design view without a problem. Any ideas?? is the word row has to be there?mess with the best...die like the rest...

Error "CS0234: The type or namespace name 'Data' does not exist in the namespace 'System'"
Dear all,the following web site ran for months without troubles http://www.fotovoltaico.sanzeno.org/StoricoRS.aspxSuddenly yesterday I started getting the error you can see switching to that site. I am really getting crazy wondering what happened, but I have no idea. Do you have any suggestion? Thank you in advancePierluigi ...

"cast from type 'dbnull' to type 'string' is not valid"
I keep getting the following error in my code: "cast from type 'dbnull' to type 'string' is not valid"My code is as follows:Do While DR.Read tbxRefNo.Text = Trim(DR("Ref")) tbxTitle.Text = Trim(DR("Title")) If Not IsDBNull(DR("Signed")) Then tbxDateSigned.Text = DR("Signed") Else tbxDateSigned.Text = "" End If tbxStatus.Text = DR("StatusName") tbxSignedBy.Text = DR("SignedBy") LoopI am reading the values from a database using the loop but I was getting a problem when I read a record that had a NULL value in the "Signed" field. I changed the code to put in the I Not IsDBNull...&nbs...

Stuck between "Cannot insert the value NULL into column 'ID'" and "Violation of PRIMARY KEY constraint"
Cannot find an answer to this in previous posting, though there are similar topics. My primary key "ID" requires a value (is not nullable), and not explictly providing it with one when I update a new record gives the following error: Cannot insert the value NULL into column 'ID', table 'AdClub.mediaq.News'; column does not allow nulls. INSERT fails. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: S...

Message="Conversion from type 'DBNull' to type 'Integer' is not valid."
I have this code in Dardanelle.aspx page. 12. Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) 13. Dim pages As Integer14. pages = CInt(e.Command.Parameters("@pageCount").Value) 15. Pn2.Count = pages 16. End Sub The error below refers to line 14 above System.InvalidCastException was unhandled by user code Message="Conversion from type 'DBNull' to type 'Integer' is not valid." Source="Microsoft.VisualBasic" StackTrace: at Microsoft.VisualBasic.CompilerServ...

Web resources about - Problem with "converting the nvarchar value 'xxxxxID' to a column of data type int". - asp.net.sql-datasource

Resources last updated: 12/27/2015 2:42:22 PM