c# program and ODBC command

Hi,

I am trying to call a stored procedure within a C# program. I get an
error [I]"The type or namespace name 'CommandType' could not be found (are you missing a using directive or an assembly reference?)",[/I] at the line where I am setting the command type as a stored procedure.

m_cmdPINInsert.CommandType = CommandType.StoredProcedure;

Can someone help what I am overlooking here.

Thanks,
Gurupot.

I am using 1.1. .Net version and SQL server.

can you show us the entire block of code making the call to the database?
do you have all the necessary libraries in your "using" statements at the top ?

using System.Data;
using System.Data.SqlClient;
etc...

hth,
mcm

I had been using System.Data.Odbc but not using System.Data.
Adding it fixed the compiler issue.

However I see another problem now. Here is my code.

Code:

---

OdbcCommand m_cmdStoredProc = new OdbcCommand();
               
m_cmdStoredProc.CommandType = CommandType.StoredProcedure;
m_cmdStoredProc.CommandText = "BEGIN " + storProc + "(); END;";
                               
m_oConn.Open();
m_cmdStoredProc.Connection = m_oConn;               

try
{
                                m_cmdStoredProc.ExecuteNonQuery();
        m_oLog.Append("\tTRACE\tSUCCESSFULLY EXECUTED STORED PROCEDURE\t");
                                       
        bReturn = true;
}
catch (Exception ex)
{
        m_oLog.Append("\tSQL EXCEPTION WITH STORED PROC\t" +                        ex.Message);
        bReturn = false;
}
m_oConn.Close();
}

---

It throws an exception while executing the SP and gives me this error
SQL EXCEPTION WITH STORED PROC ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'sp_pinlock'.
where sp_pinlock is the name of my SP.
Thanks for your help.

you dont need to put BEGIN and all that. it knows if its a stored procedure
try

Code:

---

m_cmdStoredProc.CommandType = CommandType.StoredProcedure;
m_cmdStoredProc.CommandText = storProc;

---

hth,
mcm

I have been doing it like that but the problem is the SP did not seem to execute completely. Basically I have a table called PIN with 23 rows in there.
My SP procedure updates a column in this pin table.
When I run the SP in Query analyser, all the pins are updated. However when I call the SP through a C# program only the first 8 pins are updated. Here is what my SP does.

Code:

---

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_pinlock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_pinlock]
GO

CREATE PROCEDURE sp_pinlock AS

DECLARE PIN_UPDATE CURSOR
FOR SELECT DESCR,PIN FROM PIN
OPEN PIN_UPDATE
        DECLARE @@DESCRIPTION NCHAR(150)
        DECLARE @@PIN CHAR(9)
FETCH NEXT FROM PIN_UPDATE INTO @@DESCRIPTION, @@PIN
DECLARE @@BEDNUM CHAR(2)
DECLARE @@BLDG        CHAR(3)
DECLARE @ERRORVAL CHAR(10)
WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @@BEDNUM = RIGHT(@@DESCRIPTION, 94)
  SET @@BEDNUM = LEFT(@@BEDNUM, 2)
  SET @@BLDG = RIGHT(@@DESCRIPTION,68)
  SET @@BLDG = LEFT(@@BLDG, 3)
               
  DECLARE @@GRP INT
  PRINT @@BLDG + ' ' + @@BEDNUM + ' pin: ' + @@PIN
  IF @@BLDG IN ('BRD', 'BKG','REC','TOC')
      BEGIN
        SET @@GRP = (SELECT GRP FROM SITE WITH (NOLOCK) WHERE GNAME = @@BLDG)
        UPDATE PIN SET SITE = @@GRP WHERE PIN = @@PIN
        PRINT 'PIN: ' + @@PIN + 'was in a BKG, BRD, REC, TOC group'
      END
  SET @ERRORVAL = @@ERROR       
                       
  IF @ERRORVAL <> 0
  BEGIN
      UPDATE PIN SET site = 0 WHERE PIN = @@PIN
      PRINT @@PIN + ' WAS SET TO TIMEGROUP 1, LOCATION IS: ' + @@BLDG
      FETCH NEXT FROM PIN_UPDATE INTO @@DESCRIPTION, @@PIN
  END
                               
  FETCH NEXT FROM PIN_UPDATE INTO @@DESCRIPTION, @@PIN
  END
CLOSE PIN_UPDATE
DEALLOCATE PIN_UPDATE

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---

Thanks for your help again.

how many parameters are you passing the Stored Proc from C#?

None. I am just using the SP name to call it with no parameters.

ok show me your whole code block your using to call the SP Again please?

and verify with me you get No errors when you execute it?

mcm

also may want to try "ExecuteUpdate()" if your stored proc is updating (i dont know if it will help though)

hth,
mcm

Here is my code.

Code:

---

try
{
        OdbcCommand m_cmdStoredProc = new OdbcCommand();
                m_cmdStoredProc.CommandType =  CommandType.StoredProcedure;
        m_cmdStoredProc.CommandText = storProc;
        m_cmdStoredProc.CommandTimeout = 0;

        m_oConn.Open();
        m_cmdStoredProc.Connection = m_oConn;               

        try
        {
                int count = 0;
                count = m_cmdStoredProc.ExecuteUpdate();
                m_oLog.Append("\tTRACE\tSUCCESSFULLY EXECUTED STORED PROCEDURE\t");
                                       
                bReturn = true;
        }
        catch (Exception ex)
        {
                m_oLog.Append("\tSQL EXCEPTION WITH STORED PROC\t" + ex.Message);
                        bReturn = false;
        }
        m_oConn.Close();
        }
        catch(System.Data.SqlClient.SqlException e) 
        {
                //usage log error, if we return false from this function our code should already take care of it.
                      m_oLog.Append("\tSQL EXCEPTION\t" + e.Message);
        }

---

Also if I use ExecuteUpdate I am getting the following error.
'System.Data.Odbc.OdbcCommand' does not contain a definition for 'ExecuteUpdate'

swap these lines

Code:

---

m_oConn.Open();
m_cmdStoredProc.Connection = m_oConn;

---

you have to assign the connection to the command object before you open it.

also check this article out, it should help

http://support.microsoft.com/kb/310130

hth,
mcm

That did not make any difference.
Actually I have some print statements in my SP. Can I write those lines to a text file so I can see it after I run the SP from my program to see what was happening.

Thanksagain.

i suppose, although i dont know how to do it.

Also what kind of database are you using? if your finding troubles with ODBC and your using a SQL Database i would suggest using SqlCommand objects instead.

it works almost exactly like ODBC just differnet classes and names.

you use

Code:

---

using System.Data;
using System.Data.SqlClient;

SqlConnection = ODBCConnection
SQLCommand = ODBCCommand

SqlConnection conn = new SqlConnection(etc...);
SqlCommand cmd = new SqlCommand(myStoredProc,conn);
conn.open();
cmd.ExecuteNonQuery();
conn.close();
etc...

---

hth,
mcm

Suprisingly if I use SQL connection it works fine. Not sure what I have been missing with the ODBCConnection.

I have my DSN name as configurable and SQLConnection uses the server name and not the DSN name. Is there any way I could get the server name from the DSN I have or set up SQL connection using the DSN name.

Thanks for your help.

you cant use DSN For SqlConnections (not to my knowledge anyways) but i think the reason SQL works but not ODBC is because of your ODBC Driver versions. We had the same symptoms a while back. The resolution was to get the latest ODBC Drivers and ensure the same assemblies were located on both our servers and DEV machines (because when we debugged them locally it worked fine, but on the server it would not).

I definately would look into making certain you have the correct (and latest) ODBC drivers on all your machines. And ensure your referencing these properly in your application because only ODBC will work with a DSN.

hth,
mcm