Sreeranga Vinjamuri Sreeranga Vinjamuri - 4 months ago 17
SQL Question

How to insert a Null Value to a column if there is no data provided from source file in ASP.NET

I am inserting data from a Text file to table.

Table structure is as follows

enter image description here

Text File will be like this

'RJ21AK16001333','115862','A01.10','Peters SR','06-29-2016 14:16:35','06-29-2016 14:21:52','PASSED'

or

'RJ21AK16001333','115862','A01.10','Peters SR','06-29-2016 14:16:35','06-29-2016 14:21:52','PASSED','Keypad'


and the SPROC is as follows

ALTER PROCEDURE [dbo].[SPROC]
-- Add the parameters for the stored procedure here
@DBName VARCHAR(30),
@FirstLine VARCHAR(MAX)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @InsertQuery NVARCHAR(MAX), @Summary_ID INT

SET @InsertQuery = 'INSERT INTO ' + @DBName + '.[dbo].[Summary] VALUES ( ' + @FirstLine + '); SELECT SCOPE_IDENTITY()'

EXEC SP_EXECUTESQL @InsertQuery


END


Now the thing is, if it has 8 values it will get inserted. If 8 th Value is not there in the text file it is throwing Error column name or number of supplied values does not match table definition

How can i handle this error and If there is no value for 8th column it should insert the value as NULL.

Reading File code:

public static String[] ReadSummary_Into_Array(string filepath)
{
StreamReader sreader = null;
int counter = 0;
try
{
sreader = new StreamReader(filepath);

string line = sreader.ReadLine();
//condition to hanlde empty file
if (line == null) return null;

//condition to hanlde empty first line file
if (line == "") return new String[0];

FirstLine = line;

string cleaned_line = line.Replace("''", "'-'").Replace("','", "''");
string word = "";
List<string> data = new List<string>();

MatchCollection matches = Regex.Matches(cleaned_line, @"'([^']*)");
//String[] words = null;

foreach (Match match in matches)
{
word = match.ToString();
string word_edited = word.Replace("\'", "");

if (word_edited != string.Empty)
{
data.Add(word_edited);
counter++;
}
}

Summary = new String[counter];

Summary = data.ToArray(); //The Summary Line is reconstructed into a String array

return Summary;

}


Insert Code:

public static int? Insert_Summary(string firstline, string dbName) // String Type ADDED By SR
{
try
{
using (SqlCommand cmd = new SqlCommand("USP_SetSummaryData", new SqlConnection(ConfigurationManager.ConnectionStrings["DEV_Local"].ConnectionString)))
{
cmd.Connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@DBName", dbName);
cmd.Parameters.AddWithValue("@FirstLine", firstline);

//cmd.Parameters.Add("@Type", Type == null ? (object)DBNull.Value : Type); // Added BY SR
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
catch (Exception ex)
{
EventLogging.WriteToEventLog("Summary not inserted - " + ex.ToString(), System.Diagnostics.EventLogEntryType.Error);
throw ex;
}
}

Answer

If you dont have value then you must specify the column names.

Try like this

ALTER PROCEDURE [dbo].[SPROC]
    -- Add the parameters for the stored procedure here
    @DBName VARCHAR(30),
    @FirstLine VARCHAR(MAX)
AS
    BEGIN

        SET NOCOUNT ON;

        DECLARE @InsertQuery NVARCHAR(MAX), @Summary_ID INT, @CountS INT

        SET @CountS = len(@FirstLine) - len(replace(@FirstLine, ',', ''))

        IF @CountS >= 7 THEN

              SET @InsertQuery = 'INSERT INTO ' + @DBName + '.[dbo].[Summary] VALUES ( ' + @FirstLine + '); SELECT SCOPE_IDENTITY()'
        ELSE

              SET @InsertQuery = 'INSERT INTO ' + @DBName + '.[dbo].[Summary](SerialNumber,AssetNumber,SoftwareRev,TechName,StartTime,StopTime,Status) VALUES ( ' + @FirstLine + '); SELECT SCOPE_IDENTITY()'    
        END IF 

        EXEC SP_EXECUTESQL @InsertQuery      
    END
Comments