Conversion failed when converting the nvarchar value 'xxxxxx' to data type int.

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

I have an SQL 2005 Server. I get an error that says: Conversion failed when converting the nvarchar value 'xxxxxx' to data type int. Is there a way to correct this error?

C#

Server Error in '/' Application.
Conversion failed when converting the varchar value 'Abilene Christian University                                                                                                                                                                                                                                   ' to data type int.
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: System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'Abilene Christian University                                                                                                                                                                                                                                   ' to data type int.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): Conversion failed when converting the varchar value 'Abilene Christian University                                                                                                                                                                                                                                   ' to data type int.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1753986
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5296058
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +558
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1682
   System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) +342
   System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) +227
   System.Data.SqlClient.SqlDataReader.Read() +34
   System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +64
   System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +148
   System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +421
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +173
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +316
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +86
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1482
   System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +101
   System.Web.UI.WebControls.ListControl.PerformSelect() +34
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +30
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +105
   System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +23
   System.Web.UI.Control.PreRenderRecursiveInternal() +83
   System.Web.UI.Control.PreRenderRecursiveInternal() +168
   System.Web.UI.Control.PreRenderRecursiveInternal() +168
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +974

ASPX Code:

C#

 <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                
                    ConnectionString="<%$ ConnectionStrings:PasswordConnectionString %>" 
                    SelectCommand="SELECT * FROM [TableCOCINST] WHERE LongName = INST_ID">

        <SelectParameters>
  <asp:SessionParameter
    Name="INST_ID"
    SessionField="INST_ID"
    DefaultValue="5" />
</SelectParameters>
                </asp:SqlDataSource>

Posted 4-Oct-13 4:01am

Computer Wiz99

Updated 20-Oct-17 10:49am

v3

Add a Solution

Comments

Sergey Alexandrovich Kryukov 4-Oct-13 10:06am

Why, why would you store integer data as characters in first place? If you need to store integers, store integers.
And what's the use of showing an error message without your code?
Probably I'm wasting my time explaining what's wrong with your posts: you already asked 62 questions and I believe there are more which have been removed due to abuse reports. Do answers and comments teach you anything at all?
—SA

Computer Wiz99 4-Oct-13 10:15am

Yes I have learned a lot in here. The thing is that I am working with an old system here. Can't upgrade or anything. So I have to ask questions because I really don't like to go back down on tech. when the next best thing is out there and can do the things I need without looking so hard for it. I got all of the data from an excel spreed sheet. I just imported it. The database is FoxPro. If I knew FoxPro it would be easy to do this work. I am converting FoxPro database to SQL and the only way is to export to an excel file. That is with the tech. I have to work with. The only thing that is somewhat new is VS2010. And I am the only Programmer in this company trying to make things work for them with what they have.

Sergey Alexandrovich Kryukov 4-Oct-13 12:18pm

Kwesi, when I mentioned learning, I did not mean that you are doing something wrong in your work; in that line, I meant the way you ask questions. You could have learned to provide essential detail...
Good luck,
—SA

Richard MacCutchan 4-Oct-13 10:16am

Yes, learn the difference between character data and integers.

ZurdoDev 4-Oct-13 10:59am

So, fix the SQL. Where are you stuck?

Computer Wiz99 4-Oct-13 11:04am

I tried doing that and it would not do it on the server. I even tried to make a new table then import the data and still no luck. The only way I can think of is to make a new table and type in every record by hand but that will take days and months. There are about 1287 records in just one table. 58 columns. Really don't have the time to type all of that stuff in each table. Which are 18 tables. :0

ZurdoDev 4-Oct-13 11:07am

What steps are you doing?

Computer Wiz99 4-Oct-13 11:08am

In SQL Server 2005 or Visual Studio 2010?

ZurdoDev 4-Oct-13 11:15am

You haven't actually explained how you are doing this. What are you doing?

Computer Wiz99 4-Oct-13 11:32am

Ok. I have a dropdownlist that is databound from data on the SQL Server. I was trying to have the ddl to populate only when a user has logon. Right now all of the users can see everyone's information. I am trying to have the user to logon, click the link for the form they have to fill out and the ddl will populate with the right data just for that user. That way it will not show everybody data. So there should be two things in the ddl. Select School and the school name that matches the user. All of the users have user ID and the schools have the same user Id that matches the user. I will update the code in the question. The code is in aspx.

ZurdoDev 4-Oct-13 11:39am

So, SELECT * FROM [TableCOCINST] WHERE LongName = INST_ID is causing the error?

Computer Wiz99 4-Oct-13 11:52am

I think so. It was working before I changed it. Here is the old code I had.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"

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

SelectCommand="SELECT [INST_ID], [LongName] FROM [TableCOCINST]">

ZurdoDev 4-Oct-13 11:54am

I would suggest debugging it in SQL and find what data is causing it.

Computer Wiz99 4-Oct-13 11:59am

Within the error I can see what is causing it and I went to my SQL Server, the table, tried to modify the Data Type. It would change it but could not save it because of the warnings for changing the Data Type.

ZurdoDev 4-Oct-13 12:00pm

Perhaps don't change the data type then but CONVERT it when you are SELECTing.

RedDk 4-Oct-13 12:57pm

What RD means is that an attempt to change the origin tables, and the origin database, will be futile. So create an entirely new target database/set of tables. And using SELECT/INSERT idiom, through TSQL language, copy all that data to the (new) target from the (old) origin.
[edit]
That was quick. I suggest you rightclick a table in Object Explorer of ssmse interface and choose a CREATE TO clipboard and paste what the result of that copy was. Right here.
[end edit]

Computer Wiz99 4-Oct-13 13:06pm

Ok. I will try it. Thanks.

Computer Wiz99 4-Oct-13 13:39pm

Ok. I gave it a go and everything copied over but the same error is coming up.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-10-04T05:02:00

Solution 1

The chances are that you are being lazy: your INSERT statement is not listing the columns you want to put the data in:

SQL

INSERT INTO MyTable VALUES ('a string', 666)

This means that SQL will try to insert the values to the columns in the order they are defined in the database, including any field you cannot set such as an Identity ID field - so it looks, says the ID field is an integer, and tried to convert your string "Abilene Christian University" to an integer and (quite rightly) fails.

Always name the columns: it takes very little extra time, and it makes code a lot more reliable!

SQL

INSERT INTO MyTable (ColledgeName, AgeOfYoungestMember) VALUES ('a string', 666)

Posted 4-Oct-13 5:02am

OriginalGriff

Comments

Computer Wiz99 4-Oct-13 11:07am

OriginalGriff, what do you mean INSERT. I'm not inserting anything right now. This is for a dropdownlist I am working on.

OriginalGriff 4-Oct-13 11:24am

OK - so you have databound the drop down to an SQL table (or the SQL Client stuff wouldn't be appearing in the stack trace you show).

How? And exactly what are you doing when you get the error? Remember, I can't see your screen, or read your HDD so I have no idea what your code looks like!

Computer Wiz99 4-Oct-13 11:34am

Ok. I have a dropdownlist that is databound from data on the SQL Server. I was trying to have the ddl to populate only when a user has logon. Right now all of the users can see everyone's information. I am trying to have the user to logon, click the link for the form they have to fill out and the ddl will populate with the right data just for that user. That way it will not show everybody data. So there should be two things in the ddl. Select School and the school name that matches the user. All of the users have user ID and the schools have the same user Id that matches the user. I will update the code in the question. The code is in aspx.

OriginalGriff 4-Oct-13 11:38am

Good. So how are you binding it?

Computer Wiz99 4-Oct-13 11:43am

I am using the Data Source Configuration Wizard in VS2010 on the form side. Just Drag and drop the SqlDataSource control and the ddl control. Added my data to the SDS control and then databound it to the ddl. In the ddl the select a data field for the value of the DropDownList is UserID. Select a data field to display in the DropDownList is SchoolNames.