Why am I getting an InvalidCastException from NVARCHAR to string?

Question

I am trying to retrieve some values from my SQL database, but for some reason I am getting InvalidCastException when trying to retrieve some (apparently not all) of my NVARCHAR values.

The method for retrieving looks like this (it's a bit messy, I know):

public PatientDTO DownloadPrivateData(string cpr)
{
    _patient = new PatientDTO();

    using (SqlConnection conn = new SqlConnection(_connectionstring))
    {
        conn.Open();

        using (SqlCommand cmd = new SqlCommand(_testquery, conn))
        {
            cmd.Parameters.AddWithValue("@cpr", cpr);

            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    _patient.Beskrivelse = (string)rdr["borger_beskrivelse"];
                    _patient.CPR = cpr;
                    _patient.Efternavn = (string)rdr["borger_efternavn"];
                    _patient.Fornavn = (string)rdr["borger_fornavn"];
                    _patient.Maalinger = new List<EKGMaalingDTO>()
                        {
                            new EKGMaalingDTO()
                            {
                                EKGMaaleId = (long) rdr["ekgmaaleid"],
                                Dato = (DateTime) rdr["dato"],
                                AntalMaalinger = (int) rdr["antalmaalinger"],
                                MaaltagerFornavn = (string) rdr["sfp_maaltagerfornavn"],
                                MaaltagerEfternavn = (string) rdr["sfp_maltagerefternavn"],
                                MaaltagerMedarbejderNr = (string) rdr["sfp_maaltagermedarbjnr"],
                                MaaltagerOrg = (string) rdr["sfp_mt_org"],
                                MaaltagerKommentar = (string) rdr["sfp_mt_kommentar"],
                                AnsvarsFornavn = (string) rdr["sfp_ansvfornavn"],
                                AnsvarsEfternavn = (string) rdr["sfp_ansvefternavn"],
                                AnsvarsMedarbejderNr = (string) rdr["sfp_ansvrmedarbjnr"],
                                AnsvarsOrg = (string) rdr["sfp_ans_org"],
                                Ansvarskommentar = (string) rdr["sfp_anskommentar"],
                                EKGData = new List<EKGDataDTO>()
                                {
                                    new EKGDataDTO()
                                    {
                                        EKGDataId = (int) rdr["ekgdataid"],
                                        RaaData = (byte[]) rdr["raa_data"],
                                        SampleRateHz = (float) rdr["samplerate_hz"],
                                        IntervalSec = (int) rdr["interval_sec"],
                                        IntervalMin = (float) rdr["interval_min"],
                                        DataFormat = (string) rdr["data_format"],
                                        BinEllerTekst = (char) rdr["bin_eller_tekst"],
                                        MaaleFormatType = (string) rdr["maaleformat_type"],
                                        StartTid = (DateTime) rdr["start_tid"],
                                        Kommentar = (string) rdr["kommentar"],
                                        EKGMaaleId = (long) rdr["ekgmaaleid"],
                                        MaaleEnhed = (string) rdr["maalenehed_identifikation"]
                                    }
                                }
                            }
                        };
                }
            }
        }
    }

    return _patient;
}

The database has been created using these two queries:

CREATE TABLE EKGMAELING 
(
    ekgmaaleid BIGINT IDENTITY(1,1) NOT NULL,
    dato DATETIME NOT NULL,
    antalmaalinger INT NOT NULL,
    sfp_maaltagerfornavn NVARCHAR(MAX) NULL,
    sfp_maltagerefternavn NVARCHAR(MAX) NULL,
    sfp_maaltagermedarbjnr NVARCHAR(MAX) NULL,
    sfp_mt_org NVARCHAR(MAX) NULL,
    sfp_mt_kommentar NTEXT NULL,
    sfp_ansvfornavn NVARCHAR(MAX) NULL,
    sfp_ansvefternavn NVARCHAR(MAX) NULL,
    sfp_ansvrmedarbjnr NVARCHAR(50) NOT NULL,
    sfp_ans_org NVARCHAR(MAX) NOT NULL,
    sfp_anskommentar NVARCHAR(MAX) NULL,
    borger_fornavn NVARCHAR(MAX) NULL,
    borger_efternavn NVARCHAR(MAX) NULL,
    borger_beskrivelse NVARCHAR(MAX) NULL,
    borger_cprnr   NVARCHAR(MAX) NULL,

    CONSTRAINT pk_EKGMAELING 
         PRIMARY KEY CLUSTERED (ekgmaaleid)
)


CREATE TABLE EKGDATA 
(
    ekgdataid INT IDENTITY(1,1) NOT NULL,
    raa_data VARBINARY NOT NULL,
    samplerate_hz FLOAT NOT NULL,
    interval_sec BIGINT NOT NULL,
    interval_min FLOAT NULL,
    data_format NVARCHAR(MAX) NOT NULL,
    bin_eller_tekst CHAR(1) NOT NULL,
    maaleformat_type NVARCHAR(MAX) NOT NULL,
    start_tid DATETIME NOT NULL,
    kommentar TEXT NULL,
    ekgmaaleid BIGINT NULL,
    maalenehed_identifikation NVARCHAR(MAX) NULL,

    CONSTRAINT pk_EKGDATA 
        PRIMARY KEY CLUSTERED (ekgdataid),

    CONSTRAINT fk_EKGDATA 
        FOREIGN KEY (ekgmaaleid) REFERENCES EKGMAELING (ekgmaaleid)
          ON UPDATE CASCADE
)

Seemingly I am getting an InvalidCastException on the following:

sfp_maaltagerfornavn, sfp_maaltagermedarbjnr, sfp_ansvrmedarbjnr, 
samplerate_hz, bin_eller_tekst, maaleformat_type, maalenehed_identifikation.

And, for some reason, here as well:

When creating the list, and I don't understand how that can even throw this exception.

The properties I am trying to cast the values to are these:

public class PatientDTO
{
    private string _cpr;
    public List<EKGMaalingDTO> Maalinger { get; set; }
    public string Fornavn { get; set; }
    public string Efternavn { get; set; }

    public string CPR
    {
        get { return _cpr; }
        set
        {
            if (value.Length == 10)
            {
                _cpr = value;
            }
        } 
    }

    public string Beskrivelse { get; set; }
}

public class EKGMaalingDTO
{
    public List<EKGDataDTO> EKGData { get; set; }
    public long EKGMaaleId { get; set; }
    public DateTime Dato { get; set; }
    public int AntalMaalinger { get; set; }
    public string MaaltagerFornavn { get; set; }
    public string MaaltagerEfternavn { get; set; }
    public string MaaltagerMedarbejderNr { get; set; }
    public string MaaltagerOrg { get; set; }
    public string MaaltagerKommentar { get; set; }
    public string AnsvarsFornavn { get; set; }
    public string AnsvarsEfternavn { get; set; }
    public string AnsvarsMedarbejderNr { get; set; }
    public string AnsvarsOrg { get; set; }
    public string Ansvarskommentar { get; set; }
}

public class EKGDataDTO
{
    public byte[] RaaData { get; set; }
    public float SampleRateHz { get; set; }
    public int IntervalSec { get; set; }
    public float IntervalMin { get; set; }
    public string DataFormat { get; set; }
    public char BinEllerTekst { get; set; }
    public string MaaleFormatType { get; set; }
    public DateTime StartTid { get; set; }
    public string Kommentar { get; set; }
    public long EKGMaaleId { get; set; }
    public string MaaleEnhed { get; set; }
    public int EKGDataId { get; set; }
}

Can your field be null? SQLClient returns a special value called DBNull when it encounter a null value in a table. Maybe thats what's caussing the exception — litelite, May 10 '17 at 12:39
Well have you hit the "View more detail" link? My *guess* is that the value is null, so you're casting from `DBNull` to `string`, but you're in a much better position to check that than we are. — Jon Skeet, May 10 '17 at 12:39
Okay, so if my value from the table is null, it will throw this exception when trying to cast it to string? And how would I circumvent this problem, when I don't always know if the value I'm trying to retrieve is null or not? — sunero4, May 10 '17 at 12:41
Include just enough code to allow others to reproduce the problem. For help with this, read http://stackoverflow.com/help/mcve. — jarlh, May 10 '17 at 12:41
Try this: http://stackoverflow.com/questions/11942073/converting-nvarcharmax-data-type-to-string-in-java — user3805841, May 10 '17 at 12:42
Why are you casting instead of using the built in reader methods to fetch specific types? `GetString`, `GetInt` etc... would be much safer than casting. — DigiFriend, May 10 '17 at 12:42
`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) — marc_s, May 10 '17 at 13:20

DigiFriend · Accepted Answer · 2017-05-10T12:53:35.150

4

You shouldn't be casting the returned values from the reader - use the Get* methods defined on SqlDataReader - these will do the correct conversions for you and be type safe as well.

Use:

Kommentar = rdr.GetString(rdr.GetOrdinal("kommentar"))

Instead of:

Kommentar = (string) rdr["kommentar"]

edited May 10 '17 at 12:53

answered May 10 '17 at 12:45

DigiFriend

1,164
5
10

`SqlDataReader` does not accept column names as parameter in the `GetString()` etc. methods only column indexes. – Adwaenyth May 10 '17 at 12:49
Doesn't seem to accept column names, but works perfectly with indexes! Thanks a lot :) – sunero4 May 10 '17 at 12:53
@Adwaenyth - yes, correct. I forgot to add a call to `GetOrdinal` in order for `GetString` to get the right column. – DigiFriend May 10 '17 at 12:54
@sunero4 - yes, I've updated my answer - using `GetOrdinal` will get the right index (instead of having you guess, or things going wrong with the indexed if you change the query) – DigiFriend May 10 '17 at 12:54
Oh great, that makes the program a bit more readable too :) I'll do that – sunero4 May 10 '17 at 13:02

Why am I getting an InvalidCastException from NVARCHAR to string?

1 Answers1