ExecuteNonQuery not executing - vb.net

Question

Hi

I'm trying to execute a SQL command, but I just can't find out why this is not working. This is how I defined the "execute" function in my class named "clsSQL":

    ''#...
    Private m_sConnectionString As String = String.Empty
    ''#...
    Friend WithEvents m_objConnection As SqlConnection
    Friend WithEvents m_objCommand As SqlCommand
    ''#...

        Public Function OpenConnection() As Boolean
            Try
                m_objConnection = New SqlConnection(m_sConnectionString)
                m_objConnection.Open()
                Select Case m_objConnection.State
                    Case Data.ConnectionState.Open : Return True
                    Case Else : Return False
                End Select
            Catch ex As Exception
                 RaiseEvent OnError("OpenConnection", ex)
            End Try
        End Function


        Public Function Execute(ByVal sQuery As String) As Boolean
                Try
        #If DEBUG_MODE Then
                    Debug.WriteLine(sQuery)
        #End If
                    m_objCommand = New SqlCommand(sQuery, m_objConnection)
                    m_objCommand.ExecuteNonQuery()
                    m_objCommand = Nothing
                    Return True
                Catch ex As Exception
                    RaiseEvent OnError("Execute", ex)
                End Try
            End Function
''#..
''#...

This is how I'm calling it:

        Using oSQL As New clsSQL(My.Settings.projectConnectionString)
            If oSQL.OpenConnection Then
                strSQL = "INSERT INTO ... blablabla..." 
                oSQL.Execute(strSQL)
            End If
        End Using

The code raises no error, it is just not saving the data in the database. The error is not in the SQL command, I've manually tested it ;)

For instance, I can execute perfectly the next function without any kind of problems:

    Public Function ToDataGrid(ByVal oDataGrid As DataGridView, _
                               ByVal sQuery As String, _
                      Optional ByVal sTable As String = "") As Boolean
        Try
#If DEBUG_MODE Then
            Debug.WriteLine(sQuery)
#End If
            Dim objDataSet As New DataSet
            objDataSet = ToDataSet(sQuery, sTable)
            oDataGrid.DataSource = objDataSet.Tables(0)
            objDataSet.Dispose()
            objDataSet = Nothing
            Return True
        Catch ex As Exception
            RaiseEvent OnError("ToDataGrid", ex)
        End Try
    End Function

And this is how I'm calling it:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Using oSQL As New clsSQL(My.Settings.projectConnectionString)
        If oSQL.OpenConnection Then
            oSQL.ToDataGrid(Me.DataGridView, "select * from table")
        End If
    End Using
End Sub

Probably, I just need another pair of eyes, 'cause I can't see what am I doing wrong :|

Thanks

Answer 1

I'm not sure if this is the cause of error, just curious about where did you set this variable?

m_objConnection

For the sake of debugging, you may comment you try/catch statement. IMO

Answer 2

Your code looks ok, can you show us the INSERT statement. Also have you tried using SQL Profiler to see if your INSERT hits the database at all?

Here is an SQL class I wrote a while back that you can use:

using System;
using System.Data.SqlClient;

namespace SAPCommonData
{
    public class SQLClass : IDisposable
    {
     private String connString;
     private SqlCommand SQLCmd;
     private SqlConnection SQLConn;

     public void Dispose()
     {
      if (SQLCmd != null)
      {
       SQLCmd.Dispose();
       SQLCmd=null;
      }

      if (SQLConn != null)
      {
       SQLConn.Dispose();
       SQLConn = null;
      }
     }
     public String SQLConnString
     {
      get{ return connString; }
      set{ connString = value; }
     }
     private String GetSQLConnString()
     {
      String strConn;

      try
      {
       strConn = System.Configuration.ConfigurationSettings.AppSettings.Get("SQL_CONN");
      }

      catch (Exception ex)
      {
      throw (new System.Exception(ex.Message.ToString()));
      }

      return strConn;
     }
     public void SQLExecuteNonQuery()
     {
      if (SQLCmd == null)
      {
       throw (new System.Exception("Must use SetSQLCommand to initialize SQLCommand object!")); 
      }

      if (SQLConn == null)
      {
       OpenSQLDB();
      }

      try
      {
       SQLCmd.ExecuteNonQuery();
      }
      catch (Exception ex)
      {
       throw (new System.Exception(ex.Message.ToString()));
      }
      finally
      {
       SQLCmd.Dispose();
       SQLCmd=null;
      }
     }
     public SQLClass()
     {   
      try {
       connString =  GetSQLConnString();
       }
       catch (Exception e)
       {
       throw new System.Exception(e.Message.ToString());
       }
      try
      {
       SQLConn = new SqlConnection(connString);
       SQLConn.Open();
      }
      catch (Exception e) 
      {
       throw (new System.Exception(e.Message.ToString()));
      }  
     }
     public void OpenSQLDB()
     {
      if (IsOpen())
      {
       //connection state open already
      }
      else
      {
       if (connString.Length == 0)
       {
        try 
        {
         connString =  GetSQLConnString();
        }
        catch (Exception e)
        {
         throw new System.Exception(e.Message.ToString());
        }
       }

       try
       {
        SQLConn = new SqlConnection(connString);
        SQLConn.Open();
       }
       catch (Exception e) 
       {
        throw (new System.Exception(e.Message.ToString()));
       }
      }
     } 
     public bool IsOpen()
     {
      return (SQLConn.State == System.Data.ConnectionState.Open);
     }
     public void CloseSQLDB()
     {
      if (IsOpen())
      {
       SQLConn.Dispose();
       SQLConn.Close();
       SQLConn=null;
      }
     }
     public String SQLDBUsed()
     {
      return SQLConn.Database;
     }
     public void SetSQLCommand(String proc)
     {
      if (proc.Length == 0)
      {
       throw new System.Exception("Procedure must be specified when calling SetSQLCommand!");
      }

      if (SQLConn == null)
      {
       OpenSQLDB();
      }

      SQLCmd = new SqlCommand(proc, SQLConn);
      SQLCmd.CommandType = System.Data.CommandType.StoredProcedure;
     }
     public void AddSQLCmdParameter(String pName, System.Data.SqlDbType pType, object pVal)
     {
      if (SQLCmd == null)
      {
       throw (new System.Exception("Must use SetSQLCommand to initialize SQLCommand object!"));  
      }

      if (SQLConn == null)
      {
       OpenSQLDB(); 
      }

      try
      {
       SQLCmd.Parameters.Add(pName, pType).Value = pVal;
      }
      catch (Exception ex)
      {
       throw (new System.Exception(ex.Message.ToString()));
      }
     }
     public void ClearSQLCmdParameters()
     {
      if (SQLCmd != null)
      {
       SQLCmd.Parameters.Clear();
      }
     }
     public void DisposeSQLCmd()
     {
      if (SQLCmd != null) 
      {
       SQLCmd.Dispose();
      }
     }
     public System.Data.SqlClient.SqlDataReader SQLExecuteReader()
     {
      System.Data.SqlClient.SqlDataReader dr;

      if (SQLCmd == null)
      {
       throw (new System.Exception("Must use SetSQLCommand to initialize SQLCommand object!")); 
      }

      if (SQLConn == null)
      {
       OpenSQLDB();
      }

      try
      {
       dr = SQLCmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
      }
      catch (Exception ex)
      {
       throw (new System.Exception(ex.Message.ToString()));
      }
      finally
      {
       SQLCmd.Dispose();
       SQLCmd=null;
      }
      return dr;
     }
    }
}

Then you can reuse this object like so:

public void ProcessCustomerData(DataTable dt)
 {
  //we have a valid connection to the database
  if (dt.Rows.Count > 0)
  {
   try 
   {
    s=new SQLClass();   
   }
   catch (Exception e) 
   {
    throw new System.Exception(e.Message.ToString());
   }
   foreach(DataRow dr in dt.Rows)
   {
    tw.WriteLine("Processing customer: " + dr["NAME1"].ToString());
    Console.WriteLine("Processing customer: " + dr["NAME1"].ToString());
    s.SetSQLCommand("insCustomer");
    s.AddSQLCmdParameter("@ClientID", System.Data.SqlDbType.Int, dr["MANDT"]);
    s.AddSQLCmdParameter("@CustomerID", System.Data.SqlDbType.BigInt, dr["KUNNR"]);
    s.AddSQLCmdParameter("@CustomerName1", System.Data.SqlDbType.VarChar, ((string)dr["NAME1"]==String.Empty ? DBNull.Value : dr["NAME1"]));
    s.AddSQLCmdParameter("@CustomerName2", System.Data.SqlDbType.VarChar, ((string)dr["NAME2"]==String.Empty ? DBNull.Value : dr["NAME2"]));
    s.AddSQLCmdParameter("@Country", System.Data.SqlDbType.VarChar, ((string)dr["LAND1"]==String.Empty ? DBNull.Value : dr["LAND1"]));
    s.AddSQLCmdParameter("@Region", System.Data.SqlDbType.VarChar, ((string)dr["REGIO"]==String.Empty ? DBNull.Value : dr["REGIO"]));
    s.AddSQLCmdParameter("@City", System.Data.SqlDbType.VarChar, ((string)dr["ORT01"]==String.Empty ? DBNull.Value : dr["ORT01"]));
    s.AddSQLCmdParameter("@ZipCode", System.Data.SqlDbType.VarChar, ((string)dr["PSTLZ"]==String.Empty ? DBNull.Value : dr["PSTLZ"]));
    s.AddSQLCmdParameter("@Address", System.Data.SqlDbType.VarChar, ((string)dr["STRAS"]==String.Empty ? DBNull.Value : dr["STRAS"]));
    s.AddSQLCmdParameter("@Telephone", System.Data.SqlDbType.VarChar, ((string)dr["TELF1"]==String.Empty ? DBNull.Value : dr["TELF1"]));
    s.AddSQLCmdParameter("@Fax", System.Data.SqlDbType.VarChar, ((string)dr["TELFX"]==String.Empty ? DBNull.Value : dr["TELFX"]));
    s.AddSQLCmdParameter("@DateAdded", System.Data.SqlDbType.DateTime, System.DateTime.Today);
    s.AddSQLCmdParameter("@DateModified", System.Data.SqlDbType.DateTime, System.DateTime.Today);
    s.AddSQLCmdParameter("@AddedBy", System.Data.SqlDbType.VarChar, DBNull.Value);
    s.AddSQLCmdParameter("@ModifiedBy", System.Data.SqlDbType.VarChar, DBNull.Value);
    s.SQLExecuteNonQuery();
    Console.WriteLine("Processed customer: " + dr["NAME1"].ToString());
    tw.WriteLine("Processed customer: " + dr["NAME1"].ToString());
   }
   s.CloseSQLDB();
   s.Dispose();
  }
 }

Answer 3

Is the Catch : End Try in OpenConnection masking an exception and hiding the fact that you never connect to the database and therefore never execute the SQL?

Also, it doesn't look like you ever close the connection.

Answer 4

My guess would be that there's a condition on the SQL statement that is preventing the insert from occuring.

Given that no exceptions are being thrown, I would bet real money that the statement is, in fact, being processed, but that a WHERE clause is preventing any rows from being inserted.

I'd look into that.

Answer 5

Where's your New() function in your class? Can you debug m_sConnectionString from inside your OpenConnection() function in your class? Are you getting a True return value from that function? You don't have an 'Else' clause in case your oSQL.OpenConnection fails so you don't really know if you're connecting.

Answer 6

Visual Studio creates a copy of the database file, adds it to your project, and modifies the connection so that it now points to the database in your project.

http://msdn.microsoft.com/en-us/library/ms246989%28VS.80%29.aspx