How to avoid T-SQL error: conversion from type DBNull to type String is not valid

Question

I used the following T-SQL to insert data to an application which is build by C# Some records works fine but some give the error below.

INSERT INTO [MITESTCO].[dbo].[MIQSUP] ([itemId], [suplProdCode], [suplId])  
    SELECT
        [itemId], [suplId], [suplProdCode]  
    FROM 
        [table 28]   
    WHERE  
        [itemId] NOT IN (SELECT [itemId] FROM [MIQSUP]);

See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text ************** System.InvalidCastException: Conversion from type 'DBNull' to type 'String' is not valid. at Microsoft.VisualBasic.CompilerServices.Conversions.ToString(Object Value) at MISys.Client.MIUltraDataGrid.MIUltraDataGrid.MIUltraDataGrid_InitializeRow(Object sender, InitializeRowEventArgs e) at Infragistics.Win.UltraWinGrid.InitializeRowEventHandler.Invoke(Object sender, InitializeRowEventArgs e) at Infragistics.Win.UltraWinGrid.UltraGrid.OnInitializeRow(InitializeRowEventArgs e) at Infragistics.Win.UltraWinGrid.UltraGrid.FireEvent(GridEventIds id, EventArgs e) at Infragistics.Win.UltraWinGrid.UltraGrid.FireInitializeRow(InitializeRowEventArgs e) at Infragistics.Win.UltraWinGrid.UltraGridRow.FireInitializeRow() at Infragistics.Win.UltraWinGrid.RowsCollection.FireInitializeRow(IList rows) at Infragistics.Win.UltraWinGrid.RowsCollection.InitNonGroupByRows(IList fireInitializeRowOnTheseRows) at Infragistics.Win.UltraWinGrid.RowsCollection.SyncRowsHelper(IList boundList) at Infragistics.Win.UltraWinGrid.RowsCollection.SyncRows() at Infragistics.Win.UltraWinGrid.RowsCollection.EnsureNotDirty() at Infragistics.Win.UltraWinGrid.RowsCollection.GetEnumerator() at MISys.Client.MIUltraDataGrid.MIUltraDataGrid.RefreshGrid() at MISysForms.MIDetailsTemplate.RefreshGrids(Control myparent) at MISysForms.MIDetailsTemplate.RefreshGrids(Control myparent) at MISysForms.MIDetailsTemplate.RefreshGrids(Control myparent) at MISysForms.MIDetailsTemplate.RefreshGrids(Control myparent) at MISysForms.MIDetailsTemplate.RecordChanged() at MISysForms.MIItemDetails.RecordChanged() at MISysForms.MIDetailsTemplate.GetPreviousRecord() at MISysForms.MIDetailsTemplate.UltraToolbarsManager1_ToolClick(Object sender, ToolClickEventArgs e) at Infragistics.Win.UltraWinToolbars.UltraToolbarsManager.OnToolClick(ToolClickEventArgs e) at ....

If you click on details does it tell you more specifically where that error is coming from? — Tim Freese
– Tim Freese, Commented May 19, 2016 at 21:04
Here is the problem. You posted a dotnet error message and the only code you have shown us is a query. You can't get that error message from sql. You need to show us the dotnet code if you want any real chance at finding solution. — Sean Lange
– Sean Lange, Commented May 19, 2016 at 21:34

Glenn Cuevas · Accepted Answer · 2016-05-19 21:17:43Z

2

Ask your datarow if the value isn't null, for example, then you can cast it fine:

object Value = dataRow[columnName];
if (Value != DBNull.Value)

answered May 19, 2016 at 21:17

Glenn Cuevas

1645 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Roger Barreto · Accepted Answer · 2016-05-19 21:22:02Z

0

You can use the above answer or if you dont have access to the code, change your SELECT to insert blank instead of null's, assuming you have a SQL Server

INSERT INTO [MITESTCO].[dbo].[MIQSUP] ([itemId], [suplProdCode], [suplId])  
SELECT
    IsNull([itemId],''), IsNUll([suplId], ''), ISNull([suplProdCode],'')  
FROM 
    [table 28]   
WHERE  
    [itemId] NOT IN (SELECT [itemId] FROM [MIQSUP]);

answered May 19, 2016 at 21:22

Roger Barreto

2,2841 gold badge19 silver badges24 bronze badges

Comments

Vahid Farahmandian · Accepted Answer · 2016-05-19 21:33:30Z

0

One of your columns is returning NULL value and as indicated in stack trace, calling ToString() function raise that error. You can rewrite your SELECT statement using TSQL, ISNULL function.

INSERT INTO [MITESTCO].[dbo].[MIQSUP] ([itemId], [suplProdCode], [suplId])  
SELECT
    [itemId], [suplId], ISNULL([suplProdCode],'') as suplProdCode
FROM 
    [table 28]   
WHERE  
    [itemId] NOT IN (SELECT [itemId] FROM [MIQSUP]);

answered May 19, 2016 at 21:33

Vahid Farahmandian

6,6227 gold badges48 silver badges68 bronze badges

Collectives™ on Stack Overflow

How to avoid T-SQL error: conversion from type DBNull to type String is not valid

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related