• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 134
  • Last Modified:

Syntax error in Stored Procedure

I continue beating my head against the wall with this issue and time is critical for me to get an update into my customer's database/program.  I continue to get the following error:

Incorrect syntax near 'SPI_AddStatusRec_Truck'.

Below is VB.NET executenonquery code and my stored procedure.  The issue I'm wondering deals with the SET @xCount = SELECT COUNT(1) entry.

Regardless I'm without resources in my brain at this point to resolve.  I also don't know how to view an error WITHIN the stored procedure to debug it.

Please help!

Sincerely,
Phil Tate
- VB.NET
 
da.SelectCommand.CommandText = "SPI_AddStatusRec_Truck"
da.SelectCommand.Parameters.Add(New SqlParameter("@AddStatus", "NewLoad"))
da.SelectCommand.Parameters.Add(New SqlParameter("@CompanyID", glCompany))
da.SelectCommand.Parameters.Add(New SqlParameter("@LoadID", 0))
da.SelectCommand.Parameters.Add(New SqlParameter("@LoadTypeID", glLoadTypeID))
da.SelectCommand.Parameters.Add(New SqlParameter("@LoadTypeDescription", txtLoadType.Text))
da.SelectCommand.Parameters.Add(New SqlParameter("@LoadStatusID", glLoadStatus))
da.SelectCommand.Parameters.Add(New SqlParameter("@StatusID", glLoadStatus))
da.SelectCommand.Parameters.Add(New SqlParameter("@StatusDescription", txtLoadStatus.Text))
da.SelectCommand.Parameters.Add(New SqlParameter("@OldTruckID1", xOldTruck1))
da.SelectCommand.Parameters.Add(New SqlParameter("@TruckID1", xNewTruck1))
da.SelectCommand.Parameters.Add(New SqlParameter("@TruckDescription1", txtTruck1.Text))
da.SelectCommand.Parameters.Add(New SqlParameter("@TrailerID1", glTrailerID1))
da.SelectCommand.Parameters.Add(New SqlParameter("@TrailerDescription1", txtTrailer1.Text))
da.SelectCommand.Parameters.Add(New SqlParameter("@DriverID1", glDriverID1))
da.SelectCommand.Parameters.Add(New SqlParameter("@DriverDescription1", txtDriver1.Text))
da.SelectCommand.Parameters.Add(New SqlParameter("@TruckAndDriver", txtTruck1.Text))
da.SelectCommand.Parameters.Add(New SqlParameter("@AvailableDate", System.DBNull.Value))
da.SelectCommand.Parameters.Add(New SqlParameter("@Notes", txtMiscellaneousNotes.Text.Replace("'", "''")))
da.SelectCommand.ExecuteNonQuery()
 
-Current Values are as follows:
"NewLoad"
1
0
2
"Tanker"
2
2
"Tentative"
0
320
"275 - Mason Keller"
8
"157"
391
"Mason Keller"
"275 - Mason Keller"
System.DBNull.Value
""
 
-Stored Procedure
 
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SPI_AddStatusRec_Truck]
@AddStatus varchar(10), 
@CompanyID bigint,
@LoadTypeID int,
@LoadTypeDescription varchar(100),
@LoadStatusID int, 
@StatusID int,
@StatusDescription varchar(100),
@OldTruckID1 bigint, 
@TruckID1 bigint,
@TruckID2 bigint,
@TruckDescription1 varchar(100),
@TruckDescription2 varchar(100),
@DriverID1 bigint,
@DriverID2 bigint,
@DriverDescription1 varchar(100),
@DriverDescription2 varchar(100),
@TrailerID1 bigint,
@TrailerID2 bigint,
@TrailerDescription1 varchar(100),
@TrailerDescription2 varchar(100),
@TruckAndDriver varchar(100),
@AvailableDate datetime,
@Notes varchar(8000)
AS
DECLARE @xCount bigint
SET @xCount = 0
IF @AddStatus = 'NewLoad'
	Begin
	IF @StatusDescription = 'Tentative' OR @StatusDescription = 'Complete'
		Begin
		DELETE FROM truck_status WHERE company_id = @CompanyID AND load_id = 0 AND load_type_id = @LoadTypeID AND truck_id1 = @TruckID1
		SELECT @xCount = COUNT(1) FROM truck_status WHERE company_id = @CompanyID AND load_id > 0 AND load_type_id = @LoadTypeID AND truck_id1 = @TruckID1 GROUP BY company_id, load_type_id, truck_id1
		IF @xCount = 0 OR @xCount IS NULL /*  No Dispatched Rec(s) exist...Add truck status rec for 'Available'  */
			Begin
			INSERT INTO truck_status
				(company_id, load_id, load_type_id, load_type_description, load_status_id, status_id, status_description, 
				truck_id1, truck_description1, trailer_id1, trailer_description1, driver_id1, driver_description1, 
				truck_and_driver1, pay_num, paperwork_received, notes)
			VALUES
				(@CompanyID, 0, @LoadTypeID, @LoadTypeDescription, @LoadStatusID, @StatusID, @StatusDescription, 
				@TruckID1, @TruckDescription1, @TrailerID1, @TrailerDescription1, @DriverID1, @DriverDescription1, 
				@TruckAndDriver, '', '', @Notes) 
			End
		End
	IF @StatusDescription = 'Dispatched'
		Begin
		DELETE FROM truck_status WHERE company_id = @CompanyID AND load_id = 0 AND load_type_id = @LoadTypeID AND truck_id1 = @TruckID1
		End
	End

Open in new window

0
TSFLLC
Asked:
TSFLLC
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you forgot to tell .net that you want to run a stored procedure:
da.SelectCommand.CommandText = "SPI_AddStatusRec_Truck"
da.SelectCommand.CommandType = CommandType.StoredProc

Open in new window

0
 
TSFLLCAuthor Commented:
Angel,

I inadvertently forgot to past the "CommandType" into my posting......however, I just re-reviewed my code and realized I had CommandType set to Text instead of StoredProcedure.

I was in the process of logging back in to delete the question but you're took quick for me.  Based on the
comment, although easy to see the lack of the CommandType, I'll give the points and close.

I appreciate the promptness nontheless.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> although easy to see the lack of the CommandType
the CommandType, if not specified at all, is Text by default.

glad I could "help" :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now