Missing SQL parameter in a call that's clearly there.

Question

0.00/5 (No votes)

See more:

I've got a small personal project that I've been working on. I've suddenly got a weird error that's been plaguing me. The error is:

'Procedure or function 'RandomWordParts' expects parameter '@LangID', which was not supplied.'

The code (.net 6) calling the proc is:

C#

public List<Syllable> RandomWordParts(int langID, int iter, int syllableCount)
{
    List<Syllable> syllables = new();
    SqlCommand cmd = new($"dbo.RandomWordParts", connection);

    cmd.Parameters.AddWithValue("@LangID", langID);
    cmd.Parameters.AddWithValue("@Iter", iter);
    cmd.Parameters.AddWithValue("@SyllableCount", syllableCount);

    connection.Open();
    SqlDataReader reader = cmd.ExecuteReader();
    while(reader.Read())
        syllables.Add(new Syllable(reader));

    connection.Close();

    return syllables;
}

The stored proc params are:

SQL

ALTER proc [dbo].[RandomWordParts]
	@LangID int,
	@Iter int,
	@SyllableCount int
as
...

It even works with a direct call, such as:

SQL

exec dbo.RandomWordParts
	@LangID = 1,
	@Iter = 0,
	@SyllableCount = 2

What I have tried:

I've tried specifying the command type as StoredProc, but that doesn't work. I've tried renaming @LangID in both proc and code, and that didn't work. I've checked the Parameters collection and it's correct.

Currently doing this, which I hate:

C#

public List<Syllable> RandomWordParts(int langID, int iter, int syllableCount)
    {
        List<Syllable> syllables = new();
        SqlCommand cmd = new($"exec dbo.RandomWordParts {langID}, {iter}, {syllableCount} ", connection);
/*
        cmd.Parameters.AddWithValue("@LangID", langID);
        cmd.Parameters.AddWithValue("@Iter", iter);
        cmd.Parameters.AddWithValue("@SyllableCount", syllableCount);
 */      
        connection.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        while(reader.Read())
            syllables.Add(new Syllable(reader));

        connection.Close();

        return syllables;
    }

Posted 12-Nov-22 7:21am

Kuroi Kenjin

Updated 15-Nov-22 15:10pm

Add a Solution

Comments

PIEBALDconsult 12-Nov-22 23:01pm

My first thought was "it's NULL" as well.
But maybe you shouldn't put the @ in the name of the parameter? That usually doesn't cause a problem, but maybe it is.
Also, I expect you are using the SqlClient classes, but I see no clear indications that you are.

Kuroi Kenjin 12-Nov-22 23:58pm

The @ is correct. I have a few other places where it works, but this and one other are causing problems. I even renamed @LangID to @lang_id since LangID itself appears to be a keyword and that didn't help. Yes, this is using the System.Data.SqlClient objects.

So, these worked:

    public void RandomizePhonemes(int langID, 
int regularConsonantsMin, 
int regularConsonantsMax, 
int irregularConsonantsMin, 
int irregularConsonantsMax, 
int vowelsMin, 
int vowelsMax)
    {
        SqlCommand cmd = new("dbo.Phoneme_Randomize", connection);
        cmd.Parameters.AddWithValue("@lang_id", langID);
        cmd.Parameters.AddWithValue("@reg_const_low", regularConsonantsMin);
        cmd.Parameters.AddWithValue("@reg_const_high", regularConsonantsMax);
        cmd.Parameters.AddWithValue("@iregg_const_low", irregularConsonantsMin);
        cmd.Parameters.AddWithValue("@iregg_const_high", irregularConsonantsMax);
        cmd.Parameters.AddWithValue("@vowel_low", vowelsMin);
        cmd.Parameters.AddWithValue("@vowel_high", vowelsMax);

        connection.Open();
        cmd.ExecuteNonQuery();
        connection.Close();
    }

    
    public List<IPA> LoadIPA()
    {
        List<IPA> lst = new();
        SqlCommand cmd = new("select * from dbo.IPA (nolock)", connection);

        connection.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        while(reader.Read())
            lst.Add(new(reader));
        
        connection.Close();

        return lst;
    }

    public List<(byte first, byte follow)> GetInvalidPhonemePairs(int langID, int itr)
    {
        List<(byte first, byte follow)> pairs = new();
        SqlCommand cmd = new("select [First], Follow from dbo.InvalidPhonemePairs (nolock) where LangID = @LangID and Iter = @Iter", connection);

        cmd.Parameters.AddWithValue("@LangID", langID);
        cmd.Parameters.AddWithValue("@Iter", itr);

        connection.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
            pairs.Add((reader.Get<byte>("First"), reader.Get<byte>("Follow")));

        connection.Close();

        return pairs;
    }

    public List<string> GetSyllablePatterns(int langID, int itr)
    {
        List<string> patterns = new();
        SqlCommand cmd = new("select Pattern from dbo.SyllablePatterns (nolock) where LangID = @langID and Iter = @Iter", connection);

        cmd.Parameters.AddWithValue("@LangID", langID);
        cmd.Parameters.AddWithValue("@Iter", itr);

        connection.Open();

        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
            patterns.Add(reader.Get<string>("Pattern"));

        connection.Close();
        return patterns;
    }

but the above and this doesn't:

    public bool WordExists(int langID, int iter, List<byte> word)
    {
        SqlCommand cmd = new($"dbo.WordExists", connection);

        cmd.Parameters.AddWithValue("@LangID", langID);
        cmd.Parameters.AddWithValue("@Iter", iter);
        cmd.Parameters.AddWithValue("@Word", word.ToArray());

        connection.Open();
        bool result = (bool) cmd.ExecuteScalar();
        connection.Close();
        return result;
    }

and of course, it won't format code in replies.. odd.

PIEBALDconsult 13-Nov-22 10:02am

Yeah, I don't know. It seems that putting the @ in the name is optional, so I omit it. I assume the framework removes it anyway.

George Swan 14-Nov-22 0:47am

I would make sure that all the parameters are not null. The SQL parser does not always correctly identify the source of the problem

Kuroi Kenjin 14-Nov-22 7:13am

All 3 are non-nullable int's and have been verified in the parameters list.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2022-11-12T20:24:00

Solution 2

Try setting the CommandType property for the cmd object to CommandType.StoredProcedure

Posted 12-Nov-22 20:24pm

Wendelius

Comments

Kuroi Kenjin 13-Nov-22 9:22am

As per the "What I have tried" section, I've already tried that to no effect.

OriginalGriff · Answer 2 · 2022-11-12T08:01:00

Solution 1

The most obvious reason would be that the langID variable you are using contains no data: it's null.

Use the debugger to check exactly what it contains, and then start working back from there to find out why it isn't what you think it should be.

Posted 12-Nov-22 8:01am

OriginalGriff

Updated 12-Nov-22 9:10am

v2

Comments

Kuroi Kenjin 12-Nov-22 18:38pm

Its not null. It's an integer (and not an int?). The value has been verified in the parameters collection. I've also tried hardcoding it to 1, and still no change.

It would also not work with the current hacky work-around if the int was null.

Kuroi Kenjin · Answer 3 · 2022-11-15T15:10:00

I think I found the issue. It might be related to this article:
@@LANGID (Transact-SQL) - SQL Server | Microsoft Learn[^]

I used @LangID in another place, but used in an ad-hoc as @langID. I think I'll need to go Rust style like I did an earlier call (also been doing some Rust on the side, so snake-case got stuck in my head).

Missing SQL parameter in a call that's clearly there.

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us