Insert Into Statement doesn't throw an error but Data isn't inserted into table

Question

I can't find the answer to this problem anywhere. I'm trying to insert data into a SQL Server table. Everything seems to be OK with the statement, no errors throw when the command executes, but data isn't updated into the table...

Here is my code:

    public Boolean InsertRecordsToDB(string sColumns, String sParameters, string sValues, string sTable)
    {
        /// Split the parameter holding the values for the sql command parameters by the tab character and fill the appropriate arra\
        String[] sMyCols;
        sMyCols = sColumns.Split('\t');
        List<String> lstCols = new List<String>();
        for (int ii = 0; ii < sMyCols.Length; ii++)
        {
            lstCols.Add(sMyCols[ii]);
        }

        String[] sMyParams;
        sMyParams = sParameters.Split('\t');
        List<String> lstParams = new List<String>();
        for (int ii = 0; ii < sMyParams.Length; ii++)
        {
            lstParams.Add(sMyParams[ii]);
        }

        /// Split the values param and fill the array.  Using the parameter methodology as opposed to concatenating SQL strings prevents
        /// SQL injection
        string[] sMyValues;
        sMyValues = sValues.Split('\t');
        List<String> lstValues = new List<String>();
        for (int ii = 0; ii < sMyValues.Length; ii++)
        {
            lstValues.Add(sMyValues[ii]);
        }

        ///  Default connection string for the library project to be opened by the connection object
        String sConnString = LibraryProject.Properties.Settings.Default.db_ConnectionString;

        ///First build the Sql string based on the criteria passed in
        String sSql = "";
        sSql = "Insert Into [" + sTable + "] (";
        for (int j = 0; j < sMyCols.Length; j++)
        {
            int iParamLen = sMyCols.Length;
            int iParamMinusOne = iParamLen - 1;

            if (j.Equals(iParamMinusOne))
            {
                sSql += sMyCols[j] + ")";
            }
            else
            {
                sSql += sMyCols[j] + ",";
            }

            if (sSql.Substring(sSql.Length - 1).Equals(")"))
            {
                break;
            }
        }

        sSql += " Values(";
        for (int ii = 0; ii < sMyParams.Length; ii++)
        {
            int iParamLen = sMyParams.Length;
            int iParamLenMinusOne = iParamLen - 1;
            if (ii.Equals(iParamLenMinusOne))
            {
                sSql += sMyParams[ii].Substring(2) + ")";
            }
            else
            {
                sSql += sMyParams[ii].Substring(2) + ",";
            }

            if (sSql.Substring(sSql.Length - 1).Equals(")"))
            {
                break;
            }
        }

        ///  Create the connection object
        using (SqlConnection oConn = new SqlConnection(sConnString))
        {
            oConn.Open();
            {


                try
                {
                    /// With the connection open instantiate an Sql command object
                    SqlCommand oMyCmd = new SqlCommand(sSql, oConn);
                    oMyCmd.Connection = oConn;
                    oMyCmd.CommandType = CommandType.Text;
                    oMyCmd.CommandText = sSql;
                    int i = 0;

                    /// Assign the correct SQLDbType based on the preceding character of the parameter passed in (e.g. s_ = String/Text:  b_ = Boolean: d_ = Date)
                    foreach (String sParam in sMyParams)
                    {
                        switch (sParam.Substring(0, 2))
                        {
                            case "s_":
                                oMyCmd.Parameters.Add(sParam.Substring(2), SqlDbType.Text);

                                break;

                            case "b_":
                                oMyCmd.Parameters.Add(sParam.Substring(2), SqlDbType.Bit);
                                break;

                            case "d_":
                                oMyCmd.Parameters.Add(sParam.Substring(2), SqlDbType.Date);
                                break;

                            default:
                                break;
                        }

                        oMyCmd.Parameters[sParam.Substring(2)].Value = lstValues[i];
                        i++;
                    }
                    oMyCmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception e)
                {
                    e.ToString();
                }
                return true;
            }

        }

    }

Somebody please help me, I'm banging my head off of the wall here.

Here is the actual SQL statement after it's built:

Insert Into [tblBook] (ISBN, SERIAL_NUM, SUBJECT, TITLE, AUTHOR, PUBLISHER, GENRE)
Values(@ISBN, @SERIAL_NUM, @SUBJECT, @TITLE, @AUTHOR, @PUBLISHER, @GENRE)

The statement value was copied from the immediate window. I stopped execution at the oMyCmd.ExecuteNotQuery(); line.

Answer 1

The whole User Instance and AttachDbFileName= approach is flawed - at best! When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .\bin\debug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close() call - and then inspect the .mdf file with SQL Server Mgmt Studio Express - I'm almost certain your data is there.

The real solution in my opinion would be to

1. install SQL Server Express (and you've already done that anyway)

2. install SQL Server Management Studio Express

3. create your database in SSMS Express, give it a logical name (e.g. MyDatabase)

4. connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:

   Data Source=.\\SQLEXPRESS;Database=MyDatabase;Integrated Security=True
   

   and everything else is exactly the same as before...