Solved

-2147217913 [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type char to int.

Posted on 2004-09-17
12
2,036 Views
Last Modified: 2008-01-09
I am getting this error:

-2147217913 [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type char to int.

Any idea how to fix it?
0
Comment
Question by:zachvaldez
  • 7
  • 2
  • 2
  • +1
12 Comments
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12088545
Are you getting this error as the result of trying to Insert data into a table?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 12088595
how to fix depends on what you're trying to do post a bit or statement when it happened and what you're trying to do
0
 
LVL 7

Assisted Solution

by:ChrisFretwell
ChrisFretwell earned 125 total points
ID: 12088805
If you're inserting data into a table it means that you are attempting to insert at least one item that has a non-numeric value into a column that is integer only

If you're selecting data with a where clause like WHERE field > 10 it means that the column field is not a numeric data type and at least one row in your table has a value in field that is not an integer

If you're trying to do math on a field that has character data you'll have the smae problem.

You can create the same type of problem in a case statement.

How to solve it, depends on where you problem comes from.

If its the insert, you have a choice of excluding non-numeric rows or changing your destination data type.
If its the where clause putting quotes around the comparision would help (provided your data supports the results) where field > '10'
If its math, you need to exclude non numeric rows
If its a case statement you need to rethink it
0
 

Author Comment

by:zachvaldez
ID: 12095607
I added another field and it got more worse. Let me do some work and following all your suggestions,I'll get back and report on my findings.
0
 

Author Comment

by:zachvaldez
ID: 12105331
I am passing this thru ADO.

 Set prmId = CmdEquipItems.CreateParameter("@ID", adInteger, adParamInput, 4, lblmodID.Caption)
    CmdEquipItems.Parameters.Append prmId

when it hits this line ,produces runtime errror 3421-Application uses value of the wrong type for the current operation.

I am not getting the error in the UPdate process, just when inserting a new record....




My stored procedure


CREATE PROCEDURE [spu_TestInsertUpdateCatalog]
      @publisher      varchar(40),
      @book      varchar(20),
      @description      varchar(8000) ,
      @cost            float,
      @list            float,      
                @id            int,
      @user_name      char(20),

AS
     SET NOCOUNT ON
 
     if exists(select ID  from books where id = @id)
 begin
             UPDATE Books
             SET   publisher = @publisher
                            book      = @book,
                         descrip = @description,
                         cost =@cost,
                         list =@list,
                         vendid = @vendid,
                        Changed = GetDate(),
                         RowLastUpdtByOp = @user_name,
               RowLastUpdtDtTi = GetDate()
                 
     WHERE
                id = @id
end
     ELSE
Begin        
                 
 INSERT INTO Books
                     (
                     publisher,
                     Book,
            descrip,
                     cost,
                     list,
                               Changed,
                        RowLastUpdtByOp,
                    CreatedDtTi      
                     )
          VALUES
                     (
                     @publisher
                     @book,
                     @description,
                     @cost,
                     @list,
             GetDate(),
                     @user_name,
                     GetDate()
                     )
End
GO
0
 
LVL 7

Accepted Solution

by:
ChrisFretwell earned 125 total points
ID: 12105368
what are the datatypes in your db for publisher, book, desc and cost and RowLastUpdtByOp?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 44

Expert Comment

by:bruintje
ID: 12105382
the label caption that's passed in is an implicit conersion to string what happens when you convert it first?

Set prmId = CmdEquipItems.CreateParameter("@ID", adInteger, adParamInput, 4, CInt(lblmodID.Caption))
    CmdEquipItems.Parameters.Append prmId
0
 

Author Comment

by:zachvaldez
ID: 12105418
publisher -nvarchar
book -nvarchar
desc-ntext
cost-float
rowlatupdtbyop-char(20)
0
 

Author Comment

by:zachvaldez
ID: 12105624
I get 'OVERFLOW' using the Cint(lblmodID.caption) when doing the edit/updates.

If I leave as is lblmodID.caption- It's oK no error.
0
 

Author Comment

by:zachvaldez
ID: 12106095
I fixed it by changing the label to a textbox to read as

    Set prmId = CmdEquipItems.CreateParameter("@ID", adInteger, adParamInput, 4, txtID)
    CmdEquipItems.Parameters.Append prmId

This is OK now but the problem now is that it not writing/adding  the new ID and Publisher name in the Book table.
The rest of the data are entered/written to the Database though..
0
 

Author Comment

by:zachvaldez
ID: 12106485
I was able to get the publisher in but not the most important--ID!
0
 

Author Comment

by:zachvaldez
ID: 12108293
I fixed the problem in the ADO side.
Thanks with all your help!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now