Must declare the scalar variable "@employeeid"

Question

0.00/5 (No votes)

See more:

VB

Public Sub SqlSelectTimesAwaitingApproval(dgv as datagridview, listOfEmployees As List(Of integer))

    Dim con As New SqlConnection(My.Settings.AlphaCureCRMConnectionString)
    Dim cmd As New SqlCommand()
    Dim ds As New DataSet
    Try
        cmd.Connection = Con
        For Each intId As Integer In listOfEmployees
            Con.Open()
            cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = intId
            cmd.CommandText = "SELECT * FROM tblAttendanceTimes WHERE intApprovedOvertime = 0 AND fkEmployee = @EmployeeID"
            Dim da As New SqlDataAdapter() With
            {.SelectCommand = New SqlCommand(cmd.CommandText, con)}
            da.Fill(ds, "Table")
            con.Close()
            da.Dispose()
        Next
        Dgv.DataSource = ds.Tables("Table")
        ds.Dispose()
    Catch ex As Exception
        MsgBox(Ex.Message)
    Finally
        Cmd.Dispose()
    End Try
End Sub

Cannot figure out why i am getting this error, the parameter is being defined before it's called. I have had this working for a while but something must of changed and now i can't get this to work at all.

What I have tried:

I have tried mostly just changing around the code as it is but to no avail.

Posted 14-Apr-17 8:16am

BeginnerCoderPete

Updated 14-Apr-17 8:41am

v5

Add a Solution

Comments

[no name] 14-Apr-17 14:23pm

"parameter is being defined before it's called", where? It's not in the code posted here. The title of your posting and code here do not match.

BeginnerCoderPete 14-Apr-17 14:27pm

The title is the error message i am receiving from this piece of code. I add the parameter then create the command same as all my other SQL queries but for some reason it's giving me this error.

[no name] 14-Apr-17 14:39pm

"The title is the error message", if that were true, why did you change it? Is the TITLE of your posting where you are supposed to be telling us about your problem? hint: no it is not.

BeginnerCoderPete 14-Apr-17 14:50pm

Thanks for your help.

PIEBALDconsult 14-Apr-17 14:38pm

Well, you're doing it wrong anyway -- you need to move a bunch of that out of the loop.
In the loop, all you need to do is set the Value of the parameter and execute the statement.

Instantiate the command
Set the text
Add the parameters

for ...
{
Set the parameter values
Execute
}

BeginnerCoderPete 15-Apr-17 9:10am

Public Sub SqlSelectTimesAwaitingApproval(dgv as datagridview, listOfEmployees As List(Of integer))

Dim con As New SqlConnection(My.Settings.AlphaCureCRMConnectionString)
Dim cmd As New SqlCommand()
Dim da As New SqlDataAdapter(cmd)
Dim dt As New DataTable
Try
cmd.Connection = Con
cmd.CommandText = "SELECT * FROM tblAttendanceTimes WHERE intApprovedOvertime = 0 AND fkEmployee = @Employee"
cmd.Parameters.Add("@Employee", SqlDbType.Int)
For Each intId As Integer In listOfEmployees
cmd.Parameters("@Employee").Value = intId
con.Open()
da.Fill(dt)
con.Close()
Next
Catch ex As Exception
MsgBox(Ex.Message)
Finally
da.Dispose()
cmd.Dispose()
dgv.DataSource = dt
End Try
End Sub

Any better?

PIEBALDconsult 15-Apr-17 23:18pm

Better, but don't open and close the connection in the loop either.

PIEBALDconsult 14-Apr-17 22:33pm

See also:
https://www.codeproject.com/script/Forums/View.aspx?fid=1649&msg=4052816

BeginnerCoderPete 15-Apr-17 9:04am

Thank you

2 solutions

Solution 3

cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = intId
cmd.CommandText = "SELECT * FROM tblAttendanceTimes WHERE intApprovedOvertime = 0 AND fkEmployee = @EmployeeID"
Dim da As New SqlDataAdapter() With
{.SelectCommand = New SqlCommand(cmd.CommandText, con)}

Maybe you meant...

{.SelectCommand = cmd}

Posted 14-Apr-17 8:41am

PIEBALDconsult

Updated 14-Apr-17 8:43am

v2

Comments

BeginnerCoderPete 14-Apr-17 14:48pm

Yes this seems to of helped me so far, i should be able to work out the rest from here i hope.

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)

OriginalGriff · Accepted Answer · 2017-04-14T08:35:00

Solution 2

You add the command parameter to the original command, not the one you fill your DataSet from via the adapter:

cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = intId
                cmd.CommandText = "SELECT * FROM tblAttendanceTimes WHERE intApprovedOvertime = 0 AND fkEmployee = @EmployeeID"
                Dim da As New SqlDataAdapter(cmd) With 
                {.SelectCommand = New SqlCommand(cmd.CommandText, con)}
                da.Fill(ds, "Table")

Try this:

cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = intId
cmd.CommandText = "SELECT * FROM tblAttendanceTimes WHERE intApprovedOvertime = 0 AND fkEmployee = @EmployeeID"
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "Table")

Posted 14-Apr-17 8:35am

OriginalGriff

Comments

BeginnerCoderPete 14-Apr-17 14:47pm

{ Dim con As New SqlConnection(My.Settings.AlphaCureCRMConnectionString)
Dim cmd As New SqlCommand()
Dim ds As New DataSet
Try
cmd.Connection = Con

For Each intId As Integer In listOfEmployees
Con.Open()
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = intId
cmd.CommandText = "SELECT * FROM tblAttendanceTimes WHERE intApprovedOvertime = 0 AND fkEmployee = @EmployeeID"
Dim da As New SqlDataAdapter(cmd)
da.Fill(ds, "Table")
da.Dispose()
con.Close()
Next
cmd.Dispose()
Dgv.DataSource = ds.Tables("Table")
ds.Dispose()
Catch ex As Exception
MsgBox(Ex.Message)
Finally
Cmd.Dispose()
End Try}

That is how i have it now but now i'm getting the error 'Variable name has been declared' but hopefully i'll get there from here. Thank you. So far this seems to of helped.

PIEBALDconsult 14-Apr-17 21:02pm

Because you are still adding the parameter in the loop -- which is wrong; it leads to duplicate parameters.

BeginnerCoderPete 14-Apr-17 22:03pm

Thank you, i have managed to sort it out now, using the cmd.parameters.clear before it loops again.

PIEBALDconsult 14-Apr-17 22:05pm

No, please don't do that.
Create and add the parameter before the loop, then in the loop just set the Value.

BeginnerCoderPete 14-Apr-17 22:16pm

I wasn't sure how to create the parameters outside of the loop to be honest.
Out of curiosity what is wrong with clearing the parameters?

PIEBALDconsult 14-Apr-17 22:23pm

Inefficient. If you need to loop millions of times you'll find out.

BeginnerCoderPete 15-Apr-17 9:03am

Ah OK, Fortunately it would never reach even more than 50 times really in this case but for sure thanks for the advice, always better to imagine it's being done on a massive scale. I'm all for optimization.

PIEBALDconsult 15-Apr-17 23:18pm

Today, maybe, but maybe not next week.

BeginnerCoderPete 16-Apr-17 3:37am

What do you mean?

OriginalGriff 16-Apr-17 3:51am

He means that projects have a habit of being used for purposes they were never designed for: and what works today may be horribly inefficient for what users do with it next week - so it's worth "doing it right" from day one to avoid horrible changes later on.

BeginnerCoderPete 16-Apr-17 3:54am

Oh OK, yeah I completely agree.