Help on ASP.NET Core NET 5.0 Using ADO.NET

Question

Hi Guys,

I need help on my first ASP.NET Core MVC Using ADO.NET, I can't pass the error message store procedure not found

A Simple Helpdesk Ticket System that collects info from the client and sends an email to tech support.

Here it is the whole project which is available to the public --> Simple Helpdesk Ticket System

github.com/netstair/NetstairHelpdeskV5_ADONET

Thanks

J.Saiz

Amit Mohanty · Answer

Try like this once: string sqlQuery = "spGetTicketData"; SqlCommand cmd = new SqlCommand(sqlQuery, con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@TicketId", SqlDbType.Int)).Value = 0; con.Open(); SqlDataReader rdr = cmd.ExecuteReader();

Jignesh Kumar · Answer

Hello Jose Siaz, Your store procedure expecting @TicketId as parameter, You need to pass parameter in your code, CREATE PROCEDURE [dbo].[spGetTicketData] @TicketId int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF @TicketId>0 BEGIN SELECT [ID],[date_submitted],[business_name],[business_phone],[business_location],[contact_person],[contact_email], [priority],[category],[assigned],[ticket_number],[status] FROM Tickets WHERE ID=@TicketId END ELSE BEGIN SELECT [ID],[date_submitted],[business_name],[business_phone],[business_location],[contact_person],[contact_email], [priority],[category],[assigned],[ticket_number],[status] FROM Tickets END END Please have a look at below code public IEnumerable GetAllTickets() { int ticketId = 0; // set your parameter from here List lstticket = new List (); using (SqlConnection con = new SqlConnection(GetConnectionString())) { string sqlQuery = "dbo.spGetTicketData"; SqlCommand cmd = new SqlCommand(sqlQuery, con); cmd.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter("@TicketId", ticketId)); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Ticket ticket = new Ticket(); ticket.ID = Convert.ToInt32(rdr["ID"]); ticket.Businees_Name = rdr["businees_name"].ToString(); ticket.Telephone = rdr["business_phone"].ToString(); ticket.Location = rdr["business_location"].ToString(); ticket.Contact_Person = rdr["contact_person"].ToString(); ticket.Personal_Email = rdr["contact_email"].ToString(); ticket.Priority = rdr["priority"].ToString(); ticket.Category = rdr["category"].ToString(); ticket.Assigned_To = rdr["assigned"].ToString(); ticket.Number = rdr["ticket_number"].ToString(); ticket.Status = rdr["status"].ToString(); lstticket.Add(ticket); } con.Close(); } return lstticket; }

Jignesh Kumar · Answer

Hello, Are you getting error in GetAllTickets() method. please do change in this line only, string sqlQuery = "[dbo].[spGetTicketData] 0"; // need to replace with string sqlQuery = "dbo.spGetTicketData"; // if you are using commandText = commandType.StoreProcedure then you need to pass only store procedure name. public IEnumerable GetAllTickets() { List lstticket = new List (); using (SqlConnection con = new SqlConnection(GetConnectionString())) { string sqlQuery = "dbo.spGetTicketData"; SqlCommand cmd = new SqlCommand(sqlQuery, con); cmd.CommandType = CommandType.StoredProcedure; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Ticket ticket = new Ticket(); ticket.ID = Convert.ToInt32(rdr["ID"]); ticket.Businees_Name = rdr["businees_name"].ToString(); ticket.Telephone = rdr["business_phone"].ToString(); ticket.Location = rdr["business_location"].ToString(); ticket.Contact_Person = rdr["contact_person"].ToString(); ticket.Personal_Email = rdr["contact_email"].ToString(); ticket.Priority = rdr["priority"].ToString(); ticket.Category = rdr["category"].ToString(); ticket.Assigned_To = rdr["assigned"].ToString(); ticket.Number = rdr["ticket_number"].ToString(); ticket.Status = rdr["status"].ToString(); lstticket.Add(ticket); } con.Close(); } return lstticket; } Still you are getting error, then please run this store procedure in SQL Query window using exec 'dbo.spGetTicketData'

Jose Saiz · Answer

Thank you much Amit Mohanty, finally It worked, I was very fustrated with .NET Core that I was giving up, I've been developing ASP.NET WebForm for a while now and I wanted to convert some of my project to .NET Core I tried converting straight from Microsoft suggestion that did not work, then rewrite to .NET Core 2.0 some project worked and now trying to be at least close to the game with .NET Core 5 even though Microsoft is not supporting and found this wall that stopped me but thanks to you I can continue and try to make it happen, my next challance will be the Entity Framework which I believe is better using than ADO.NET, I am afraid to ask why this didnot work string sqlQuery = "dbo.spGetTicketData 0"; it worked like a charm in ASP.NET Webform. Thank you for all your help.

Jose Saiz · Answer

The whole project along with database, tables and store proc are part of the public github source https://github.com/netstair/NetstairHelpdeskV5_ADONET/blob/master/HelpDeskDb_Database_Script.sql Thanks

Jignesh Kumar · Answer

Could you please share your store procedure ? either your store procedure has parameter which we need to pass from back end but for method GetAllTickets() should not have any parameter as per name it should return all tickets.

Jose Saiz · Answer

Thank you Jignesh Kumar for the reply, but I still get the same error message with both solution provided Suggested using exec 'dbo.spGetTicketData' SqlException: Could not find stored procedure 'exec 'dbo.spGetTicketData' '. Suggested using "spGetTicketData"' SqlException: Procedure or function 'spGetTicketData' expects parameter '@TicketId', which was not supplied. Or when using "spGetTicketData 0"' SqlException: Could not find stored procedure 'spGetTicketData 0'. I am not sure the MS-SQL environment has anything to do with, I am using MS-SQL 2008 R2, meanwhile If I drop the .NET core to version 2.0 it works, I understand Core 2.0 is old and no longer supported, that is why I am using .NET Core 5.0 Thanks

Jose Saiz · Answer

Is there a way that I can have my connection string either on the appsettings.json and retrieve it from the appsettings.json instead of having on every model datalayer as it reads on my class? Please provide sample code how to retrieve from the appsettings.json and use it my datalayer. Thanks