CODEWITHSUNDEEP
c#t-sqlwebmatrix
Hecatonchires
Hecatonchires

Reputation: 1071

Webmatrix QueryValue call raising SQLException "Incorrect syntax near '0'."

The following method looks up an Id for an IMEI code 

public static int GetId(string imei)
{
    int returnVal = 0;
    string sqlGetVehicle = "sp_Scalar_VehicleId_For_IMEI @IMEI = {0}";

    using (var db = Database.Open("ClientApp"))
    {
        returnVal = db.QueryValue(sqlGetVehicle, imei);
    }

    return returnVal;
}

The sp it calls is set to return a single value. When run in SMO the procedure works fine. Give it an IMEI string, and if it's in the table it will return the vehicleId. If it isn't, it returns a 0.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      *trim*
-- Create date: 2017-05-12
-- Description: Returns the VehicleId Associated with an IMEI
-- =============================================
ALTER PROCEDURE [dbo].[sp_Scalar_VehicleId_For_IMEI]
    @IMEI varchar(20)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @VehicleId int

    SELECT @VehicleId = v.VehicleId
    FROM   Vehicles v
    WHERE v.TabletIMEI = @IMEI


    -- ISNULL() in query wasn't working
    IF @VehicleId IS NULL
    BEGIN
        SET @VehicleId = 0
    END

    SELECT @VehicleId

END

When run from code, the procedure throws System.Data.SqlClient.SqlException: Incorrect syntax near '0'. It was throwing this before I removed the ISNULL() check, and after I replaced it with the IF statement. What is the root cause of this error?

[SqlException (0x80131904): Incorrect syntax near '0'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2442126
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5736904
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +628
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +3731
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +58
   System.Data.SqlClient.SqlDataReader.get_MetaData() +89
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +379
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2026
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +375
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
   System.Data.SqlClient.SqlCommand.ExecuteScalar() +271
   WebMatrix.Data.Database.QueryValue(String commandText, Object[] args) +111
   *trim*.Forms.*trim*.Vehicle.GetId(String imei) in C:\Users\*trim*\Documents\Visual Studio 2017\WebSites\*trim*\App_Code\Vehicle.cs:258
   ASP.Functions.UpdateSessionVehicle() in C:\Users\*trim*\Documents\Visual Studio 2017\WebSites\*trim*\App_Code\Functions.cshtml:148
   ASP._Page_Default_cshtml.Execute() in C:\Users\*trim*\Documents\Visual Studio 2017\WebSites\*trim*\Default.cshtml:8
   System.Web.WebPages.WebPageBase.ExecutePageHierarchy() +198
   System.Web.WebPages.WebPage.ExecutePageHierarchy(IEnumerable`1 executors) +69
   System.Web.WebPages.WebPage.ExecutePageHierarchy() +131
   System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage) +78
   System.Web.WebPages.WebPageHttpHandler.ProcessRequestInternal(HttpContextBase httpContext) +116

Upvotes: 1

Views: 108

Answers (1)

Corak
Corak

Reputation: 2798

The placeholder for Webmatrix seems to be @0 instead of {0}. Also, to actually execute a stored procedure, it should be called with "Exec".

With that in mind, the command text should be defined as:

string sqlGetVehicle = "Exec sp_Scalar_VehicleId_For_IMEI @IMEI = @0";

According to this post another way could be:

returnVal = db.Query("Exec sp_Scalar_VehicleId_For_IMEI", imei);

Upvotes: 1

Related Questions