Must declare the scalar variable - Regex Problem

[demiryasinoruc]

Microsoft Windows 10 Pro N .Net 4.6.1 Asp.Net Mvc

Hi, when i run to ExecuteSql method i am getting 'Must declare the scalar variable' error

My code is

int result = ConnectionFactory.GetConnection().ExecuteSql("INSERT INTO Contact (FullName, Message, IPAddress, StatusTypeID, LanguageCode) VALUES (@FullName, @Message, @IPAddress, @StatusTypeID, @LanguageCode)",
    new Test
    {
        FullName = "Test Test",
        Message = "Test mesajı",
        IPAddress = "000.000.00.000",
        StatusTypeID = 101,
        LanguageCode = "tr"
    });

I look to source code and problem is inside this method (InsightDbProvider.cs, line 171)

public virtual void DeriveParametersFromSqlText(IDbCommand command)
{
     if (command == null) throw new ArgumentNullException("command");
     var parameters = _parameterRegex.Matches(command.CommandText)
                .Cast<Match>()
                .Select(m => m.Groups[1].Value);
     if (!HasPositionalSqlTextParameters)
     parameters = parameters.Distinct(StringComparer.OrdinalIgnoreCase);
     foreach (var p in parameters.Select(
          p =>
                {
                    var dbParameter = (IDataParameter)command.CreateParameter();
                    dbParameter.ParameterName = p;
                    return dbParameter;
                }))
      command.Parameters.Add(p);
}

But i am running this code on dotnetfiddle.net and i can see 5 parameters properly (FullName, Message, IpAddress, StatusTypeID, LanguageCode)

When i run the same code on my pc result is not right (FullName, Message, StatusType, LanguageCode) (StatusType should be StatusTypeId)

And this is the Sql code

exec sp_executesql N'INSERT INTO Contact (FullName, Message, IPAddress, StatusTypeID, LanguageCode) VALUES (@FullName, @Message, @IPAddress, @StatusTypeID, @LanguageCode)',N'@FullName nvarchar(14),@Message nvarchar(27),@LanguageCode nvarchar(2)',
@FullName=N'Test Test',@Message=N'Test mesajı',@LanguageCode=N'tr'

[demiryasinoruc]

When i try to this code block program working well

System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en");
Regex _parameterRegex = new Regex("[?@:]([a-zA-Z0-9_]+)", RegexOptions.IgnoreCase | RegexOptions.Compiled);
 var parameters = _parameterRegex.Matches("INSERT INTO Contact (FullName, Message, IPAddress, StatusTypeID, LanguageCode) VALUES (@FullName, @Message, @IPAddress, @StatusTypeID, @LanguageCode")
                    .Cast<Match>()
                    .Select(m => m.Groups[1].Value);
parameters = parameters.Distinct(StringComparer.OrdinalIgnoreCase);
foreach (var p in parameters)
{
     Console.WriteLine(p);
}
Console.ReadLine();

[Jaxelr]

Theres a bit of missing information that would be helpful to help on the diagnosis. But i can tell you that using the following code it worked as expected on your same .net fx version:

Given the following Table:

CREATE TABLE Contact
(
    ContactId INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(64),
    Message NVARCHAR(64),
    IPAddress NVARCHAR(14),
    StatusTypeID INT,
    LanguageCode NVARCHAR(2)
);

The following Test gave successful results (Nunit):

    [TestFixture]
    public class UnitTest1
    {
        [TestCase]
        public void InsightInsert()
        {
            var c = new SqlConnection("connectionstringhere"); //Removed for brevity.

            //Added truncate for quick testing purposes.
            int result = c.ExecuteSql(@"
                TRUNCATE TABLE Contact 
                INSERT INTO Contact(FullName, Message, IPAddress, StatusTypeID, LanguageCode) 
                VALUES(@FullName, @Message, @IPAddress, @StatusTypeID, @LanguageCode)",
            new Test
            {
                FullName = "Test Test",
                Message = "Test mesajı",
                IPAddress = "000.000.00.000",
                StatusTypeID = 101,
                LanguageCode = "tr"
            });

            Assert.AreEqual(result, 1);
        }

        private class Test
        {
            public string FullName { get; set; }
            public string Message { get; set; }
            public string IPAddress { get; set; }
            public int StatusTypeID { get; set; }
            public string LanguageCode { get; set; }
        }
    }

If you think something there's different from my sample in regards to your lemme know.

[demiryasinoruc]

I added a new code block, could you run the test code like this?

[TestFixture]
public class UnitTest1
{
    [TestCase]
    public void InsightInsert()
    {
        // I added this code block because my operating system is Turkish, so I think you will get the error I got
        System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("tr");
        var c = new SqlConnection("connectionstringhere"); //Remove for brevity.
        //Added truncate for quick testing purposes.
        int result = c.ExecuteSql(@"
        TRUNCATE TABLE Contact 
        INSERT INTO Contact(FullName, Message, IPAddress, StatusTypeID, LanguageCode) 
        VALUES(@FullName, @Message, @IPAddress, @StatusTypeID, @LanguageCode)",
        new Test
        {
            FullName = "Test Test",
            Message = "Test mesajı",
            IPAddress = "000.000.00.000",
            StatusTypeID = 101,
            LanguageCode = "tr"
        });

        Assert.AreEqual(result, 1);
    }

    private class Test
    {
        public string FullName { get; set; }
        public string Message { get; set; }
        public string IPAddress { get; set; }
        public int StatusTypeID { get; set; }
        public string LanguageCode { get; set; }
    }
}

[Jaxelr]

After fiddling around it seems the problem is with the Capital I on the variables (? ) Must be something specific in regards to the language, this worked for me:

    [TestFixture]
    public class UnitTest
    {
        [TestCase]
        public void InsightInsert()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("tr");
            var c = new SqlConnection("connectionstringhere"); //Remove for brevity.

            int result = c.ExecuteSql(@"
                TRUNCATE TABLE Contact
                INSERT INTO Contact(FullName, Message, IPAddress, StatusTypeID, LanguageCode) 
                VALUES(@FullName, @Message, @ipAddress, @StatusTypeid, @LanguageCode)",
            new Test
            {
                FullName = "Test Test",
                Message = "Test mesajı",
                IPAddress = "000.000.00.000",
                StatusTypeId = 101,
                LanguageCode = "tr"
            });

            Assert.AreEqual(result, 1);
        }

        private class Test
        {
            public string FullName { get; set; }
            public string Message { get; set; }
            public string IPAddress { get; set; }
            public int StatusTypeId { get; set; }
            public string LanguageCode { get; set; }
        }
    }

[demiryasinoruc]

it seems the problem is with the Capital I on the variables

When the letter Capital I is translated to lowercase in Turkish, it is "ı", not "i"

When i change the this code block

Regex _parameterRegex = new Regex("[?@:]([a-zA-Z0-9_]+)", RegexOptions.IgnoreCase | RegexOptions.Compiled);

to

Regex _parameterRegex = new Regex("[?@:]([a-zA-Z0-9_]+)", RegexOptions.Compiled);

Code working well.

[jonwagner]

It looks like the correct solution is to use:

RegexOptions.IgnoreCase | RegexOptions.CultureInvariant | RegexOptions.Compiled

See: https://docs.microsoft.com/en-us/dotnet/standard/base-types/regular-expression-options#comparison-using-the-invariant-culture

Can you test with those options? If that works, I'll kick out a few more bug fixes and do a build.

[demiryasinoruc]

Yes it works.

Other Turkish characters have problems, but they should not be used when coding

Other characters (just for information): "Ğ", "ğ", "Ü", "ü", "Ö", "ö", "Ç", "ç", "Ş", "ş"

[jonwagner]

This is fixed in the 6.1 branch

[jonwagner]

This is fixed in 6.1.0-alpha1. (Posting shortly.)

Please let me know if it works for you.