CODEWITHSUNDEEP
c#asp.netsql-serversqldatasourcesqlexception
John Wesley Gordon
John Wesley Gordon

Reputation: 910

The conversion of the varchar value overflowed an int column ASP.NET

I have a SqlDataSource in ASP.NET that is returning an error

unhandled System.Data.SqlClient.SQLException, The conversion of the varchar value '5157964168' overflowed an int column

I am not sure why it is complaining about an int column as nothing in my code is an int column. What should I look for?

protected void SqlDataSourceA_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
 e.Command.Parameters["@ID"].Value = DropDownListID.SelectedValue;
}

protected void Page_Load(object sender, EventArgs e)
{
  SqlDataSourceA.SelectCommand = "SELECT ID, FolderName FROM DR_Folders where parentID=CONVERT(BIGINT,@ID) AND parentID!=0 order by FolderName ASC";
}

<asp:SqlDataSource ID="SqlDataSourceA" runat="server" 
    ConnectionString="<%$ ConnectionStrings:AConnectionString %>" 

        SelectCommand="">
        <SelectParameters>
          <asp:Parameter Name="ID" DbType="String" />           
        </SelectParameters>
</asp:SqlDataSource>

Stepping through I see 5157964168 in DropDownListID.SelectedValue. I don't understand why it doesn't go into @ID and execute properly. Isn't @ID a string?

I've also tried Type="String" instead of DbType because I'm not sure of the difference.

Upvotes: 0

Views: 2990

Answers (3)

John Wesley Gordon
John Wesley Gordon

Reputation: 910

Well, I got the error to go away by changing the database columns from BIGINT to VARCHAR and changing the SQL to

SqlDataSourceA.SelectCommand = "SELECT ID, FolderName FROM DR_Folders where parentID=@ID AND parentID!='0' order by FolderName ASC";

While this doesn't really answer why I had all the problems with BIGINT perhaps it is better that they are text the whole way through.

Upvotes: 0

Martin Noreke
Martin Noreke

Reputation: 4146

The SqlParameter class allows you to define the SqlDataType when creating them as shown in this constructor reference on MSDN (https://msdn.microsoft.com/en-us/library/h8f14f0z%28v=vs.110%29.aspx). This can also be set after the fact as shown in this MSDN article (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.dbtype%28v=vs.110%29.aspx)

Here is an example of how you could update your code to ensure it is set:

protected void SqlDataSourceA_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
 e.Command.Parameters["@ID"].Value = DropDownListID.SelectedValue; // May need parsing to Int64 here
 e.Command.Parameters["@ID"].DbType = DbType.Int64;
}

By explicitly setting the parameter to BIGINT, you don't have to do a convert in your SQL, so your command would be as follows:

  SqlDataSourceA.SelectCommand = "SELECT ID, FolderName FROM DR_Folders where parentID=@ID AND parentID!=0 order by FolderName ASC";

Upvotes: 1

garryp
garryp

Reputation: 5776

Rather than converting @ID to a BIGINT convert parentID to a VARCHAR instead:

SqlDataSourceA.SelectCommand = "SELECT ID, FolderName FROM DR_Folders where CONVERT(VARCHAR, parentID)=@ID AND parentID!=0 order by FolderName ASC";

Though comparing INTs to VARCHAR is probably asking for trouble whatever approach you take.

Upvotes: 2

Related Questions