February 13, 2010 at 2:17 am
Hi,
I was writing a stored procedure for retrieving all the records from ALBUMS table on the basis of the supplied ARTIST_ID as follows:
USE [Beat]
GO
/****** Object: StoredProcedure [dbo].[sp_Get_Albums_By_Artist_Id] Script Date: 02/12/2010 23:51:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Get_Albums_By_Artist_Id]
(
@ART_ID int
)
AS
BEGIN
-- Insert statements for procedure here
SELECT * FROM ALBUMS WHERE [ARTIST_ID] =@ART_ID
END
When I called the above stored procedure from the method as mentioned below, an error occurs:
public static List<Hashtable> GetDBRecords(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
try
{
DebugHelper.Debug("Executing the query");
SqlConnection conn = Connection.getSQLConnection(Global.getConnectionString());
SqlDataReader reader = null;
using (SqlCommand sqlCommand = new SqlCommand(commandText, conn))
{
if ((commandType == CommandType.StoredProcedure) && (commandParameters != null))
{
sqlCommand.Parameters.AddRange(commandParameters);
}
//sqlCommand.CommandType = CommandType.Text;
conn.Open();
reader = sqlCommand.ExecuteReader();
}
if (!reader.HasRows)
{
Logger.Log(LogType.INFO, String.Format("No data found in the table"));
return null;
}
List<Hashtable> records = new List<Hashtable>();
while (reader.Read())
{
Hashtable record = new Hashtable();
for (int i = 0; i < reader.FieldCount; i++)
{
DebugHelper.Debug(String.Format("Column Name: {0} , Value: {1}", reader.GetName(i), reader));
record.Add(reader.GetName(i), reader);
}
records.Add(record);
}
DebugHelper.Debug("Total records found: " + records.Count);
return records;
}
catch (Exception ex)
{
Logger.Log(LogType.ERROR, ex.Message);
Logger.Log(LogType.ERROR, ex.StackTrace);
}
return null;
}
The method that calls above method is:
public static List<Hashtable> GetAlbumsByArtistId(int artistId)
{
SqlParameter[] parameters = {new SqlParameter(@"@ART_ID", SqlDbType.Int)};
parameters[0].Value = artistId;
return GetDBRecords(CommandType.StoredProcedure, Global.strSPGetAlbumsByArtistId, parameters);
}
When I checked the SQL Server Profiler, I can see the log which showed the executed query as follows:
exec sp_executesql N'sp_Get_Albums_By_Artist_Id',N'@ART_ID int',@ART_ID=1
If I tried to execute the above statement, it throws an error. But, I couldn't locate where's the problem. The stack trace is:
[ERROR] Incorrect syntax near 'sp_Get_Albums_By_Artist_Id'..
[ERROR] 2/13/2010 12:37:22 AM: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Beat.DBLayer.DBHandler.GetDBRecords(CommandType commandType, String commandText, SqlParameter[] commandParameters) in D:\Root Folder\DBHandler.cs:line 341.
Any help would be highly appreciated.
February 13, 2010 at 10:48 pm
It appears that you have the wrong syntax with the paramater in your .NET Code.
If you copy & paste the output frpm Profiler and get an error than it is not you Stored Procedure.
Do you have a script to create the table and insert some records?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 14, 2010 at 6:40 am
You xcode that you use in your .NET code to generate you paramter collection is flawed and thus generates a syntax error.
Your Stored Procedure if fine.
DECLARE @ART_ID int
SET @ART_ID = 1
EXEC sp_Get_Albums_By_Artist_Id @ART_ID
Artist_ID Artist_Name
----------- --------------------------------------------------
1 Joe Namath
(1 row(s) affected)
--- Or ------------------------------------------
EXEC sp_Get_Albums_By_Artist_Id @ART_ID
Artist_ID Artist_Name
----------- --------------------------------------------------
2 Simon
(1 row(s) affected)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 14, 2010 at 1:09 pm
Thanks guys for the responses. I suddenly realized that I had made a mistake while writing GetDBRecords() method. When I uncommented the commented code in the method and re-wrote as mentioned below, it worked:
sqlCommand.CommandType = commandType
I had passed the CommandType object to this method but never assigned and it was taking CommandType.Text always, I think.
Stupid mistake, but it happens... 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply