Solved

Debugging Stored Procedure - SqlDataSource InsertCommand .net

Posted on 2009-07-01
4
1,050 Views
Last Modified: 2012-05-07
I have a SqlDataSource Insert command with parameters declared in the <InsertParameters> tag.  Previously I was using an insert command directly in the SqlDataSource tag (which was working fine), now I'm running it as a stored procedure.  When I run the stored procedure from the database, it works and inserts correctly.  When I run it from the page I get a "format of input string is not correct" error.  The parameters have not changed since I replaced the insert sql with the stored procedure.  All of the DB fields accept null values and all the types seem to be declared correctly.  I've even tried setting all the defaults to NULL.

How can I debug this?  The stack analysis means nothing to me, and when I try to run a debug on the project I get an error that the BIOS limit on my network has been exceeded.  Is there some way to write out the command being sent to SQL server?

Apologies for the stupid newbie question but it's my first shot at a stored procedure and I'm pretty stumped here.
Stored procedure:
/***************************************************/
USE [LOCAL_CERVINO]
GO
/****** Oggetto:  StoredProcedure [dbo].[InsertOfferta]    Data script: 07/01/2009 14:24:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[InsertOfferta] 
	@Codice varchar(30) = NULL,
    @Numero_Offerta int = NULL,
    @Sede_Cervino varchar(10) = NULL,
	@Anno_Offerta int = NULL,
    @ID_Stato_Offerta int = NULL,
    @ID_Anagrafica_Cliente int = NULL,
    @ID_Vettore int = NULL,
    @ID_Anagrafica_Responsabile int = NULL,
    @Tipo_Offerta varchar(20) = NULL,
    @Periodicita int = NULL,
    @Termine_Disdetta int = NULL,
    @Importo_Iniziale decimal(8, 2) = NULL,
    @Importo_Finale decimal(8, 2) = NULL,
    @Data_Scadenza smalldatetime = NULL,
    @Data_Apertura smalldatetime = NULL,
    @Data_Trasmissione smalldatetime = NULL,
    @Data_Esito smalldatetime = NULL,
    @Note varchar(1000) = NULL,
    @ID_Promoter int = NULL,
    @Provvigione_Percentuale decimal(5, 2) = NULL,
    @Provvigione_Euro decimal(5, 2) = NULL,
	@NewID_Offerta int OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    INSERT INTO CER_Offerta
         (
            Codice, 
            Numero_Offerta,
			Sede_Cervino,
			Anno_Offerta,
			ID_Stato_Offerta,
			ID_Anagrafica_Cliente,
			ID_Vettore,
			ID_Anagrafica_Responsabile,
			Tipo_Offerta,
			Periodicita,
			Termine_Disdetta,
			Importo_Iniziale,
			Importo_Finale,
			Data_Scadenza,
			Data_Apertura,
			Data_Trasmissione,
			Data_Esito,
			Note                   
         )
    VALUES 
         ( 
			@Codice, 
            @Numero_Offerta,
			@Sede_Cervino,
			@Anno_Offerta,
			@ID_Stato_Offerta,
			@ID_Anagrafica_Cliente,
			@ID_Vettore,
			@ID_Anagrafica_Responsabile,
			@Tipo_Offerta,
			@Periodicita,
			@Termine_Disdetta,
			@Importo_Iniziale,
			@Importo_Finale,
			@Data_Scadenza,
			@Data_Apertura,
			@Data_Trasmissione,
			@Data_Esito,
			@Note
         ) ; 
	SET @NewID_Offerta = SCOPE_IDENTITY()
END
 
SQL Data source tag:
/***********************************************************/
<asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:connString %>" ID="sdsScheda"
				    runat="server" 
				    InsertCommandType="StoredProcedure" InsertCommand="InsertOfferta">
 
<InsertParameters>
				        <asp:Parameter Name="Codice" Type="String" DefaultValue="test" />
					    <asp:Parameter Name="Numero_Offerta" Type="Int32" DefaultValue="NULL" />
					    <asp:Parameter Name="Sede_Cervino" Type="String" DefaultValue="NULL" />
					    <asp:Parameter Name="Anno_Offerta" Type="Int32" DefaultValue="NULL" />
					    <asp:Parameter Name="ID_Stato_Offerta" Type="Int32" DefaultValue="NULL" />
					    <asp:Parameter Name="ID_Anagrafica_Cliente" Type="Int32" DefaultValue="NULL" />
					    <asp:Parameter Name="ID_Vettore" Type="Int32" DefaultValue="NULL" />
                        <asp:Parameter Name="ID_Anagrafica_Responsabile" Type="Int32" DefaultValue="NULL" />
                        <asp:Parameter Name="Tipo_Offerta" Type="String" DefaultValue="NULL" />
                        <asp:Parameter Name="Periodicita" Type="Int32" DefaultValue="NULL" />
					    <asp:Parameter Name="Termine_Disdetta" Type="Int32" DefaultValue="NULL" />
                        <asp:Parameter Name="Importo_Iniziale" Type="Double" DefaultValue="NULL" />
                        <asp:Parameter Name="Importo_Finale" Type="Double" DefaultValue="NULL" />
                        <asp:Parameter Name="Data_Scadenza" Type="DateTime" DefaultValue="NULL" />
                        <asp:Parameter Name="Data_Apertura" Type="DateTime" DefaultValue="NULL" />
                        <asp:Parameter Name="Data_Trasmissione" Type="DateTime" DefaultValue="NULL" />
                        <asp:Parameter Name="Data_Esito" Type="DateTime" DefaultValue="NULL" />
                        <asp:Parameter Name="Note" Type="String" DefaultValue="NULL" />
                        <asp:Parameter Name="ID_Promoter" Type="Int32" DefaultValue="NULL" />
                        <asp:Parameter Name="Provvigione_Percentuale" Type="Double" DefaultValue="NULL" />
                        <asp:Parameter Name="Provvigione_Euro" Type="Double" DefaultValue="NULL" />
                        <asp:Parameter Direction="Output" Name="NewID_Offerta" Type="Int32" />
				    </InsertParameters>

Open in new window

0
Comment
Question by:AX_User
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 3

Accepted Solution

by:
Dov_Landau earned 250 total points
ID: 24754334
if you have access to the SQL Server you can run SQL Profiler to see the call to Stored Procedure and the parameters passed into it. But I think you need server admin role to be able to profile the server.
 
0
 
LVL 15

Expert Comment

by:spprivate
ID: 24754351
For integers give DB.Null instead of "Null"
0
 

Author Comment

by:AX_User
ID: 24754756
Ok, I've tried setting all the integer to DB.Null instead of Null and it hasn't made any difference.

Dov Landau: I've downloaded AnjLab Sql Profiler and when I run the SP from the DB I can see it profiled, but when I run it from the page I can't which means it isn't getting called.  Can't figure out what's wrong on my aspx page though.

I'm calling fvEdit.InsertItem(true); from codebehind instead of using the automatically generated insert button, this shouldn't make a difference though right?
0
 

Author Comment

by:AX_User
ID: 24755045
I've pared the code down to an absolute minimum, duplicated the SP so that it only inserts 2 columns, and now I'm getting an error saying "too many arguments specified for the procedure or function InsertOfferta1" (or similar, I'm translating here).  Is it possible to use insert parameters in tags with an SP?  all the examples I can find on the web set the parameters in code behind.

Here's the pared down version:
<asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:connString %>" ID="sdsScheda" runat="server"
SelectCommand="SelectOfferta" SelectCommandType="StoredProcedure"				    InsertCommandType="StoredProcedure" InsertCommand="InsertOfferta1">
				    
				    <SelectParameters>
					    <asp:ControlParameter ControlID="GridView1" Name="ID_Offerta" PropertyName="SelectedValue"
						    Type="String" />
				    </SelectParameters>
				    <InsertParameters>
				       <asp:Parameter Name="Sede_Cervino" Type="String" />
					    <asp:Parameter Name="Anno_Offerta" Type="Int32" />
                        <asp:Parameter Direction="Output" Name="NewID_Offerta" Type="Int32" />
				    </InsertParameters>
			    </asp:SqlDataSource>

Open in new window

0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question