Stored Proc Not Running On ButtonClick

[dwayned]

Hi,

I am having problems inserting data into a table using a stored procedure. When I click the button to insert the data nothing happens at all. I added a server.transfer after the insert to see if it was falling over before the end of the query/insert.

My code behind the button to insert the data is as follows...

Imports System.Data
Imports System.Data.SqlClient

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim ConnString As New SqlConnection
        ConnString.ConnectionString = "Data Source=.\SQLEXPRESS2008;Initial Catalog=mkb;Integrated Security=True"
        ConnString.Open()

        Dim SQLCmd As New SqlCommand
        SQLCmd.CommandText = "usp_insertError" 'name of SP
        SQLCmd.CommandType = CommandType.StoredProcedure
        SQLCmd.Connection = ConnString 'Active SQL Connection to DB

        SQLCmd.Parameters.AddWithValue("errorTitle", TxtBoxTitle.Text)
        'SQLCmd.Parameters.AddWithValue("errorProg", DDListProg.SelectedValue)
        'SQLCmd.Parameters.AddWithValue("errorCat", DDListCat.SelectedValue)
        'SQLCmd.Parameters.AddWithValue("errorSubCat", DDListSubCat.SelectedValue)
        SQLCmd.Parameters.AddWithValue("errorDesc", TxtBoxDesc.Text)
        SQLCmd.Parameters.AddWithValue("errorCreated", Date.Now)
        'SQLCmd.Parameters.AddWithValue("errorAuthor", errorTitle.text)
        'SQLCmd.Parameters.AddWithValue("errorSeverity", RadioListSeverity.SelectedValue)

        SQLCmd.ExecuteNonQuery()
        ConnString.Close()

I have commented a couple of items out as I am working on them at the moment, instead I have set their values in the Stored Procedure so it should work anyway.

Stored Proc is as follows;

ALTER PROCEDURE [dbo].[usp_insertError]

	@errorTitle varchar(150),
	@errorProg int,
	@errorCat int,
	@errorSubCat int,
	@errorDesc text,
	@errorCreated date,
	@errorAuthor int,
	@errorSeverity int

AS
SET @errorProg='1'
SET @errorCat='3'
SET @errorSubCat='2'
SET @errorSeverity='2'
	

INSERT INTO errors(errorTitle, errorProg, errorCat, errorSubCat, errorDesc, 
					errorCreated, errorSeverity) 
					
VALUES (@errorTitle, @errorProg, @errorCat, @errorSubCat, @errorDesc, 
					GETDATE(), @errorSeverity)

thanks in advance

[dnanetwork]

try executing store proc..
check if it runs correctly...

[dwayned]

try executing store proc..
check if it runs correctly...

Hi dnanetwork,

thanks for the prompt reply, when I tested the SQL it inserted the info into DB, but I am now trying to run SP as below but getting an error "Error converting Data Type nvarchar to date" however I am not using any nvarchar in the DB.

use mkb

exec usp_insertError @errorTitle='test', @errorProg='1', @errorCat='3', @errorSubCat='2', 
						@errorDesc='test test ets test', @errorCreated=getdate, @errorAuthor=null, 
						@errorSeverity='2'

[dnanetwork]

Date is a problem

i believe getdate is a function..

try using getdate()

or the best option provided by SQL itself is default value which you can specify to your column..

so in your insert query , you dnt have to pass the date..

hope that helps..

[dwayned]

Hi Dnanetwork,

I amended the SP as below...

ALTER PROCEDURE [dbo].[usp_insertError]

	@errorTitle varchar(150),
	@errorProg int,
	@errorCat int,
	@errorSubCat int,
	@errorDesc text,
	@errorCreated date,
	@errorAuthor int,
	@errorSeverity int

AS
	SET @errorCreated=GETDATE()

INSERT INTO errors(errorTitle, errorProg, errorCat, errorSubCat, errorDesc, 
					errorCreated, errorAuthor, errorSeverity) 
					
VALUES (@errorTitle, @errorProg, @errorCat, @errorSubCat, @errorDesc, 
					@errorCreated, @errorAuthor, @errorSeverity)

I tested this by commenting out the @errorCreated in the SP and then running below in SQL Server Mgt Studio by passing in the date as '2010-01-01'. This inserted the row into the database ok. However when I uncommented the @errorCreated and tried running from ASP.net page nothing happens.

exec usp_insertError @errorTitle='Monitor Broke', @errorProg='1', @errorCat='3', @errorSubCat='2', @errorDesc='Stabbed with pen', @errorCreated='2010-01-01', @errorAuthor=NULL, @errorSeverity='2'

[dnanetwork]

i mean when you create a table..
for every column SQL server allows you to set the default value

so i suggested for column name errorCreated set the default value to
getDate() and

change the query to

exec usp_insertError @errorTitle='Monitor Broke', @errorProg='1', @errorCat='3', @errorSubCat='2', @errorDesc='Stabbed with pen', @errorAuthor=NULL, @errorSeverity='2'

try this ...u'll get the correct o/p..

[dwayned]

it won't allow me to run using GETDATE()

keeps saying "Incorrect Syntax Near ")", Expecting INTEGER, ID or Quoted ID"

[rohand]

I say put the "@" in your parameter for all the parameters you are using in button click event..
SQLCmd.Parameters.AddWithValue("@errorTitle", TxtBoxTitle.Text)
SQLCmd.Parameters.AddWithValue("@errorProg", DDListProg.SelectedValue)
and so on...

you don't need to make any changes in your SP. it's looking good

hope this will help you

[kim1987]

Hi,

Mostly when you are developing in your local system, the events do work well. But once you move the code to production, the button click events may not fire at all. This might rise after you install the Microsoft .NET Framework Service Pack 1, which will stop the PostBack Events on client side validation. To resolve this issue, the aspnet_client folder needs to be reinstalled. To do that type the following from command prompt:-
%windir%\Microsoft.NET\Framework\v1.1.4322\aspnet_regiis.exe

Thanks
David
<FAKE SIGNATURE>

[dwayned]

I say put the "@" in your parameter for all the parameters you are using in button click event..
SQLCmd.Parameters.AddWithValue("@errorTitle", TxtBoxTitle.Text)
SQLCmd.Parameters.AddWithValue("@errorProg", DDListProg.SelectedValue)
and so on...

you don't need to make any changes in your SP. it's looking good

hope this will help you

Hi rohand,

thanks but it made no difference. I tried this at home last night on a test DB using exact same code and it worked fine so I am beginning to think that it is something to do with my connection to my DB??

I am using Web Server 2008 to develop and test this app.

thanks again for the reply, this is really driving me up the wall.

[dwayned]

Hi,

Mostly when you are developing in your local system, the events do work well. But once you move the code to production, the button click events may not fire at all. This might rise after you install the Microsoft .NET Framework Service Pack 1, which will stop the PostBack Events on client side validation. To resolve this issue, the aspnet_client folder needs to be reinstalled. To do that type the following from command prompt:-
%windir%\Microsoft.NET\Framework\v1.1.4322\aspnet_regiis.exe

Thanks
David
<FAKE SIGNATURE>

Hi David,

tried to do that but there is no file called aspnet_regiis.exe in that directory, the file is however in the 2.0 dir.

ran it, but made no difference.

[dnanetwork]

ok try one last thing from myside..

in your code ...instead of writing Date.Now

try DateTime.Now

hope things might work this time...

[dwayned]

ok try one last thing from myside..

in your code ...instead of writing Date.Now

try DateTime.Now

hope things might work this time...

hi dnanetwork,

I actually removed that variable from the SP altogether, so I am not passing date from the webform, instead I am using GETDATE() in the INSERT in the Stored Procedure. I think it is a problem with my system as the syntax that I used is the same as on my home machine and it works fine.

HOME Machine = Win7
Dev Machine = Web Server 2008

thanks

[dnanetwork]

cool :)

[dwayned]

ok guys....

I created a blank "test.aspx" page and placed all the controls on the form and added the codebehind and stored procedure and it worked!!

So the problem must lie in my page, I was using a master page for this, so I am going to recreate the page from scratch and see if it works. I will keep you updated...

[dwayned]

Hi Guys,

I recreated the page but it is still no working, everything renders ok on webpage but buttons will not respond when clicked etc. My code for the page is below. The Stored procedure and code under the buttonClick should be ok as it was tested on a blank page. I created this page as a new "ajax web form".

<%@ Page Title="" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="newerror.aspx.vb" Inherits="newerror" %>

<asp:Content ID="Content1" ContentPlaceHolderID="content" Runat="Server">
    
    <h1>Add a New Error To The Knowledge Base</h1><br />
        <table>
        <tr>
            <td>Title</td>
            <td></td>
            <td><asp:TextBox ID="txtBTitle" runat="server" Width="500px"></asp:TextBox></td>
        </tr>
        <tr>
        <td>&nbsp</td>
        <td></td>
        <td></td>
        </tr>
        <tr>
            <td>Program (1)</td>
            <td></td>
            <td><asp:TextBox ID="txtBProg" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>Category (3)</td>
            <td></td>
            <td><asp:TextBox ID="txtBCat" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>SubCategory (2)</td>
            <td></td>
            <td><asp:TextBox ID="txtBSubCat" runat="server"></asp:TextBox></td>
        </tr>
        <tr>
            <td>&nbsp</td>
            <td></td>
            <td></td>
        </tr>
        <tr>
            <td valign="top">Description</td>
            <td></td>
            <td><asp:TextBox ID="txtBDesc" runat="server" TextMode="MultiLine" Width="500px" Rows="10"></asp:TextBox></td>
        </tr>
         <tr>
            <td>Severity (2)</td>
            <td></td>
            <td><asp:TextBox ID="txtBSeverity" runat="server"></asp:TextBox></td>
        </tr>
         <tr>
            <td></td>
            <td></td>
            <td><asp:Button ID="btnSubmit" runat="server" Text="Insert Error" /></td>
        </tr>
        </table>

</asp:Content>