Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Insert into DB not working 3

Status
Not open for further replies.
dhmfh

dhmfh

Programmer
Nov 28, 2005
69
GB
Hi Guys

I have the following function once the button is clicked

Protected Sub BtnCallBackRequest_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles BtnCallBackRequest.Click

Dim DBConnection As SqlConnection = New SqlConnection(GlobalParameters.DBConnection())
Dim SqlCommand As New SqlCommand("SP_WCallbackRequest", DBConnection)

With SqlCommand
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Callback_FirstName", SqlDbType.VarChar).Value = Request.Form("txtcustomerfirstname")
.Parameters.Add("@Callback_SurName", SqlDbType.VarChar).Value = Request.Form("txtcustomersurname")
.Parameters.Add("@Callback_CustomerTitle_Id", SqlDbType.Int).Value = SelCustomerTitles.SelectedValue
.Parameters.Add("@Callback_TelNo", SqlDbType.VarChar).Value = Request.Form("txtcustomertelno")
.Parameters.Add("@Callback_EMailAddress", SqlDbType.VarChar).Value = Request.Form("txtcustomeremailaddress")
.Parameters.Add("@DBErrMessage", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
.Parameters.Add("@DBErrStatus", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue

End With

DBConnection.Close()

End Sub

Am I missing something to do the insert as it doesn't seem to work, but no error is returned

 
Sort by date Sort by votes
Yep, you're missing a call to tell the command to execute.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 
Upvote 0 Downvote
As a side note, you've asked quite a few questions recently yet you haven't acknowledged any of the answers. To do so, read point 15 from the FAQ in my signature.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 
Upvote 0 Downvote
Hi ca8msm.

Did you receive my acknowledgement?

I have added the call as shown below. No luck. It nows tells me that it expects the SP parameters?

Protected Sub BtnCallBackRequest_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles BtnCallBackRequest.Click

Dim DBConnection As SqlConnection = New SqlConnection(GlobalParameters.DBConnection())
Dim SqlCommand As New SqlCommand("SP_WCallbackRequest", DBConnection)

With SqlCommand
.CommandType = CommandType.StoredProcedure
.Connection.Open()
.Parameters.Add("@Callback_FirstName", SqlDbType.VarChar).Value = Request.Form("txtcustomerfirstname")
.Parameters.Add("@Callback_SurName", SqlDbType.VarChar).Value = Request.Form("txtcustomersurname")
.Parameters.Add("@Callback_CustomerTitle_Id", SqlDbType.Int).Value = SelCustomerTitles.SelectedValue
.Parameters.Add("@Callback_TelNo", SqlDbType.VarChar).Value = Request.Form("txtcustomertelno")
.Parameters.Add("@Callback_EMailAddress", SqlDbType.VarChar).Value = Request.Form("txtcustomeremailaddress")
.Parameters.Add("@DBErrMessage", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
.Parameters.Add("@DBErrStatus", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
.ExecuteReader()
End With

DBConnection.Close()

End Sub
 
Upvote 0 Downvote
What is the exact error? Perhaps you are missing a parameter(s) needed by the stored procedure.
 
Upvote 0 Downvote
HI jbenson001,

Th error I receive is that it is expecting the first parameter in the code above?
 
Upvote 0 Downvote
did you try to run the SQL Profiler? check to see what command is passed to SQL Server...

Known is handfull, Unknown is worldfull
 
Upvote 0 Downvote
ran SQL profiler and I wasn't passsing the values correctly. Thanks for that vbkris
 
Upvote 0 Downvote
just one other point about the above function. For some reason my return paramters don't return
 
Upvote 0 Downvote
You have to make them Output parameters not Return parameters. Also, you need to assign thoes values to a variable to be able to refernce them.

Jim
 
Upvote 0 Downvote
Hi jbenson001,

I have assigned a variable to the one of the output parameters but it seems to return the name of the parameter.

Dim DBConnection As SqlConnection = New SqlConnection(GlobalParameters.DBConnection())
Dim SqlCommand As New SqlCommand("SP_WCallbackRequest", DBConnection)

Dim ErrMessage As SqlParameter

ErrMessage = New SqlParameter("@DBErrMessage", SqlDbType.VarChar)
ErrMessage.Direction = ParameterDirection.ReturnValue

With SqlCommand
.CommandType = CommandType.StoredProcedure
.Connection.Open()
.Parameters.Add("@Callback_FirstName", SqlDbType.VarChar).Value = txtcustomerfirstname.Text
.Parameters.Add("@Callback_SurName", SqlDbType.VarChar).Value = txtcustomersurname.Text
.Parameters.Add("@Callback_CustomerTitle_Id", SqlDbType.Int).Value = SelCustomerTitles.SelectedValue
.Parameters.Add("@Callback_TelNo", SqlDbType.VarChar).Value = txtcustomertelno.Text
.Parameters.Add("@Callback_EMailAddress", SqlDbType.VarChar).Value = txtcustomeremailaddress.Text
.Parameters.Add(ErrMessage)
.Parameters.Add("@DBErrStatus", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
.ExecuteReader()
End With

Response.Write(ErrMessage)

DBConnection.Close()
 
Upvote 0 Downvote
After you execute the SP you need something like this:
Code: 
SomeVar = SqlCommand.Parameters("OutPutParamName").Value
 
Upvote 0 Downvote
Hi jbenson001,

I have added your line - it runs but the value is not returned?

Protected Sub BtnCallBackRequest_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles BtnCallBackRequest.Click

Dim DBConnection As SqlConnection = New SqlConnection(GlobalParameters.DBConnection())
Dim SqlCommand As New SqlCommand("SP_WCallbackRequest", DBConnection)

Dim ErrMessage As SqlParameter

ErrMessage = New SqlParameter("@DBErrMessage", SqlDbType.VarChar)
ErrMessage.Direction = ParameterDirection.ReturnValue

With SqlCommand
.CommandType = CommandType.StoredProcedure
.Connection.Open()
.Parameters.Add("@Callback_FirstName", SqlDbType.VarChar).Value = txtcustomerfirstname.Text
.Parameters.Add("@Callback_SurName", SqlDbType.VarChar).Value = txtcustomersurname.Text
.Parameters.Add("@Callback_CustomerTitle_Id", SqlDbType.Int).Value = SelCustomerTitles.SelectedValue
.Parameters.Add("@Callback_TelNo", SqlDbType.VarChar).Value = txtcustomertelno.Text
.Parameters.Add("@Callback_EMailAddress", SqlDbType.VarChar).Value = txtcustomeremailaddress.Text
.Parameters.Add(ErrMessage)
.Parameters.Add("@DBErrStatus", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
.ExecuteReader()
End With

ErrMessage = SqlCommand.Parameters("@DBErrMessage").Value
Response.Write(ErrMessage)

'Check If Connection is Open
If (DBConnection.State = ConnectionState.Open) Then
DBConnection.Close()
End If

End Sub

Any Ideas?
 
Upvote 0 Downvote
What are you expecting, what is the value? Like I said, you probably want an OUTPUT parameter, not a RETURNVALUE
 
Upvote 0 Downvote
I am expecting a value of either Success or Failure. I have tried using OUTPUT put get the following error

String[6]: the Size property has an invalid size of 0.
 
Upvote 0 Downvote
You need an OutPut parameter then. A ReturnValue just retuns a code returned by sql server that tell you the success or faliure of the SP.

Can you post the code of your SP. I think some changes will be need to be made to get this working for you.

 
Upvote 0 Downvote
Hi jbenson001. Here is the SP

CREATE PROCEDURE dbo.SP_WCallbackRequest
/* Add Any Parameters That Will be Passed To The Stored Procedure Here */
@Callback_FirstName VARCHAR(50),
@Callback_SurName VARCHAR(50),
@Callback_CustomerTitle_Id INT,
@Callback_TelNo VARCHAR(50),
@Callback_EMailAddress VARCHAR(50)
AS

/* Set Procedure Variables */
DECLARE @DBErrMessage VARCHAR(255)
DECLARE @DBErrStatus VARCHAR(10)

/* Ensures The Number Of Rows Affected By A T-SQL Statement Are Not Returned */
SET NOCOUNT ON

/* Run The Insert T-SQL Statement */

BEGIN
/* The Begin Transaction Marks The Starting Point Of An Explicit, Local Transaction */
BEGIN TRANSACTION

/* This Will Insert The Records Into The Database Table */
INSERT INTO Lkp_CallbackRequests
(Callback_FirstName, Callback_SurName, Callback_CustomerTitle_Id, Callback_TelNo, Callback_EMailAddress, Callback_Processed)
VALUES (@Callback_FirstName, @Callback_SurName, @Callback_CustomerTitle_Id, @Callback_TelNo, @Callback_EMailAddress, 0)

/* Check For Any Errors After The Insert Has Completed */
IF @@Error <> 0
BEGIN
/* Rollback The Transaction If An Error Ocurrs */
ROLLBACK TRANSACTION

/* This Will Set The Error Codes To Return To The User */
SET @DBErrMessage = 'An Error Occured Whilst Inserting This Request. Please Try Again.'
SET @DBErrStatus = 'Error'

/* This Will Return The Error Codes Back To The User */
SELECT @DBErrMessage AS DBErrMessage, @DBErrStatus AS DBErrStatus
END
ELSE
BEGIN
/* Commit The Transaction When The Insert Has Succeeded */
COMMIT TRANSACTION

/* This Will Set The Error Codes To Return To The User */
SET @DBErrMessage = 'Your Call back Request has Been Submitted. You Will Be Contacted Shortly.'
SET @DBErrStatus = 'Success'

/* This Will Return The Error Codes Back To The User */
SELECT @DBErrMessage AS DBErrMessage, @DBErrStatus AS DBErrStatus
END
END
GO
 
Upvote 0 Downvote
These are variables, not output parameters:
Code: 
DECLARE    @DBErrMessage     VARCHAR(255)
DECLARE    @DBErrStatus         VARCHAR(10)

You need to declare them as paramaters:
Code: 
CREATE PROCEDURE dbo.SP_WCallbackRequest
    /* Add Any Parameters That Will be Passed To The Stored Procedure Here */
    @Callback_FirstName        VARCHAR(50),
    @Callback_SurName        VARCHAR(50),
    @Callback_CustomerTitle_Id    INT,
    @Callback_TelNo        VARCHAR(50),
    @Callback_EMailAddress    VARCHAR(50),
    [b]@DBErrMessage   VARCHAR(255) OUTPUT[/b],
    [b]@DBErrStatus    VARCHAR(10) OUTPUT[/b]

AS

 
Upvote 0 Downvote
Hi jbenson001,

I now have the following:

CREATE PROCEDURE dbo.SP_WCallbackRequest
/* Add Any Parameters That Will be Passed To The Stored Procedure Here */
@Callback_FirstName VARCHAR(50),
@Callback_SurName VARCHAR(50),
@Callback_CustomerTitle_Id INT,
@Callback_TelNo VARCHAR(50),
@Callback_EMailAddress VARCHAR(50),
@DBErrMessage VARCHAR(255) OUTPUT,
@DBErrStatus VARCHAR(10) OUTPUT
AS

/* Ensures The Number Of Rows Affected By A T-SQL Statement Are Not Returned */
SET NOCOUNT ON

/* Run The Insert T-SQL Statement */

BEGIN
/* The Begin Transaction Marks The Starting Point Of An Explicit, Local Transaction */
BEGIN TRANSACTION

/* This Will Insert The Records Into The Database Table */
INSERT INTO Lkp_CallbackRequests
(Callback_FirstName, Callback_SurName, Callback_CustomerTitle_Id, Callback_TelNo, Callback_EMailAddress, Callback_Processed)
VALUES (@Callback_FirstName, @Callback_SurName, @Callback_CustomerTitle_Id, @Callback_TelNo, @Callback_EMailAddress, 0)

/* Check For Any Errors After The Insert Has Completed */
IF @@Error <> 0
BEGIN
/* Rollback The Transaction If An Error Ocurrs */
ROLLBACK TRANSACTION

/* This Will Set The Error Codes To Return To The User */
SET @DBErrMessage = 'An Error Occured Whilst Inserting This Request. Please Try Again.'
SET @DBErrStatus = 'Error'

/* This Will Return The Error Codes Back To The User */
SELECT @DBErrMessage AS DBErrMessage, @DBErrStatus AS DBErrStatus
END
ELSE
BEGIN
/* Commit The Transaction When The Insert Has Succeeded */
COMMIT TRANSACTION

/* This Will Set The Error Codes To Return To The User */
SET @DBErrMessage = 'Your Call back Request has Been Submitted. You Will Be Contacted Shortly.'
SET @DBErrStatus = 'Success'

/* This Will Return The Error Codes Back To The User */
SELECT @DBErrMessage AS DBErrMessage, @DBErrStatus AS DBErrStatus
END
END

Protected Sub BtnCallBackRequest_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles BtnCallBackRequest.Click

Dim DBConnection As SqlConnection = New SqlConnection(GlobalParameters.DBConnection())
Dim SqlCommand As New SqlCommand("SP_WCallbackRequest", DBConnection)

Dim ErrMessage As String

With SqlCommand
.CommandType = CommandType.StoredProcedure
.Connection.Open()
.Parameters.Add("@Callback_FirstName", SqlDbType.VarChar).Value = txtcustomerfirstname.Text
.Parameters.Add("@Callback_SurName", SqlDbType.VarChar).Value = txtcustomersurname.Text
.Parameters.Add("@Callback_CustomerTitle_Id", SqlDbType.Int).Value = SelCustomerTitles.SelectedValue
.Parameters.Add("@Callback_TelNo", SqlDbType.VarChar).Value = txtcustomertelno.Text
.Parameters.Add("@Callback_EMailAddress", SqlDbType.VarChar).Value = txtcustomeremailaddress.Text
.Parameters.Add("@DBErrMessage", SqlDbType.VarChar).Direction = ParameterDirection.Output
.Parameters.Add("@DBErrStatus", SqlDbType.VarChar).Direction = ParameterDirection.Output
.ExecuteReader()
End With

ErrMessage = SqlCommand.Parameters("@DBErrMessage").Value
Response.Write(ErrMessage)

'Check If Connection is Open
If (DBConnection.State = ConnectionState.Open) Then
DBConnection.Close()
End If

End Sub

And am getting the following error

String[5]: the Size property has an invalid size of 0.
 
Upvote 0 Downvote
YOu need to put your code in a try...catch and get a more exact error.. Catch ex as a sqlexception.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

AhmedAzizBarbary
  • Locked
  • Question
How to pass list of checkboxes not checked on page model razor page?
Replies
0
Views
1K
AhmedAzizBarbary
AhmedAzizBarbary
G12Consult
  • Locked
  • Question
Forms authentication not working
Replies
4
Views
597
Rajkumarrrrr
Rajkumarrrrr
dvannoy
  • Locked
  • Question
Custom Errors Not Working
Replies
6
Views
566
dvannoy
dvannoy
shaunk
  • Locked
  • Question
Web API Routing Not working in Web Site App.
Replies
0
Views
329
shaunk
shaunk
JScannell
  • Locked
  • Question
GridView caption not rendering
Replies
1
Views
384
jbenson001
jbenson001

Part and Inventory Search

Sponsor

Back
Top