Avatar of zimmer9
zimmer9Flag for United States of America asked on

Why does a field not create a SQL statement value of single quotes if I assign spaces to it in C# using VS2010?

I am developing a C# application using VS2010.

I have the attached input file that I am using to read records from. There is only 1 record in this input file.

My application builds up a SQL Statement based on the input fields that are read by my application but I don't understand why the 8th field (field titled recordnum) does not generate single quotes in the SQL Statement that is built since there is a null value in the input field. The first field (bankNum) does not have a value in the input file yet it generated single quotes in the SQL Statement as I suspected it would.

To recap, the 1st and 8th field both have no value yet the 1st field is converted to single quotes in the build SQL Statement while the 8th field does not generate single quotes. The output is blank (not single quotes) for the 8th field and thus creates an erroneous SQL Statement.

INSERT INTO tbl_SMR_OI_DRS (bankNum, transDtYR, transDtMnth, transDtday, officeNum, checkNum, transType, recordNum, processDTYear) VALUES ('    ', 2013, 08, 21, '007', '211503', 'TTT',     , '2013')

Do you know how I can resolve this issue?

A snippet of my code is as follows:

                    sArray = new string[9];
                    sArray[0] = bankNum.ToLower() == "null" ? " ": bankNum;
                    sArray[1] = transDtYR;
                    sArray[2] = transDtMnth;
                    sArray[3] = transDtday;
                    sArray[4] = officeNum;
                    sArray[5] = checkNum;
                    sArray[6] = tranType;
                    sArray[7] = recordNum.ToLower() == "null" ? " ": recordNum;
                    sArray[8] = processDTYear;
                    sb.AppendFormat(myInsert, sArray);
                    string cmdText = sb.ToString();
C#.NET Programming

Avatar of undefined
Last Comment

8/22/2022 - Mon
Miguel Oz

Please post your SQL table creation ( I would like to see column types and check columns(bankNum, recordNum) are  marked "Allow null".)

.........................Data Type                  Allow Nulls    
                               -------------                  ---------------      
bankNum                nvarchar(4)                checked (selected)
recordNum             nvarchar(4)                checked (selected)
Miguel Oz

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Error      1      'string' does not contain a definition for 'HasValue' and no extension method 'HasValue' accepting a first argument of type 'string' could be found (are you missing a using directive or an assembly reference?)      \\msad\root\na\ny\users\zimmermj\visual studio 2010\Projects\SMRWebForm\SMRWebForm\SMRWebForm1.aspx.cs      119      43      SMRWebForm
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.