c# program and ODBC command

[gurupot]

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.

[gurupot]

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

[mcmcom]

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

[gurupot]

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.

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.

[mcmcom]

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

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

hth,
mcm

[gurupot]

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.

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.

[mcmcom]

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

[gurupot]

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

[mcmcom]

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

[mcmcom]

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

hth,
mcm

[gurupot]

Here is my 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'

[mcmcom]

swap these lines

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

[gurupot]

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.

[mcmcom]

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

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

[gurupot]

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.