Question Setting SqlCommand with parameters is not working

[GolferGuy45]

Here is my code to setup the SqlCommand with a parameter:

      Dim mySqlCommand As New SqlCommand()
      Dim mySqlDataAdapter As New SqlDataAdapter()
      mySqlCommand.CommandText = myCommandText
      mySqlCommand.Connection = SecurityRolesConnection
      mySqlCommand.Parameters.Add("@EmployeeID", SqlDbType.BigInt)
      Long.TryParse(idTextBox.Text, myBigInt)
      mySqlCommand.Parameters("@EmployeeID").Value = myBigInt
      mySqlCommand.Prepare()
      mySqlDataAdapter.SelectCommand = mySqlCommand
      mySqlDataAdapter.Fill(myDataSet)
      myTableView = myDataSet.Tables(0).DefaultView

The program fails at the "mySqlDataAdapter.Fill(myDataSet)" statement with this error message:
"Procedure or function 'AllSecurityRolesAndSelected' expects parameter '@EmployeeID', which was not supplied."
When I change the StoredProcedure to not look for this parameter, and hard code a valid EmployeeID into the StoredProcedure, and change the VB code to not try to pass a parameter, the StoredProcedure and VB are able to communicate, and the DataGrid on my form fills with the correct data.
I just have no idea why this code is not working to actually add the @EmployeeID parameter to the SqlCommand.
Any help with what I need to do would be greatly appreciated.
Vic

 

[jmcilhinney]

First things first, you are using Long.TryParse and ignoring the result. If the user enters an invalid value then TryParse will return False and 'myBigInt' will be zero. Do you really want to carry on with the query in that case?

Secondly, your code is overly verbose. While what you have is not incorrect, this:

      Dim mySqlCommand As New SqlCommand()
      Dim mySqlDataAdapter As New SqlDataAdapter()
      mySqlCommand.CommandText = myCommandText
      mySqlCommand.Connection = SecurityRolesConnection
      mySqlCommand.Parameters.Add("@EmployeeID", SqlDbType.BigInt)
      Long.TryParse(idTextBox.Text, myBigInt)
      mySqlCommand.Parameters("@EmployeeID").Value = myBigInt
      mySqlCommand.Prepare()
      mySqlDataAdapter.SelectCommand = mySqlCommand

could be replaced with this:

      Dim mySqlDataAdapter As New SqlDataAdapter(myCommandText, SecurityRolesConnection)
      With mySqlDataAdapter.SelectCommand
          Long.TryParse(idTextBox.Text, myBigInt)
          .Parameters.AddWithValue("@EmployeeID", myBigInt)
          .Prepare()
      End With

Finally, with regards to the question, the error message says that the error occurs when you call the procedure or function AllSecurityRolesAndSelected. That's not happening in your VB code though, so your VB code is not the issue. That must be happening in your SQL code, so your SQL code is wrong. You haven't shown us your SQL code so we can only guess but I would say that you are not using your @EmployeeID parameter to pass the value to that procedure or function.

 

[GolferGuy45]

Here is my entire Sub routine

Sub RefreshDataView()

    Dim SecurityRolesConnection As New SqlConnection
    If Environment.UserName.ToString = "Vic" Then
      SecurityRolesConnection.ConnectionString = VicConnectionString
    Else
      SecurityRolesConnection.ConnectionString = OthersConnectionString
    End If
    SecurityRolesConnection.Open()
    Dim myCommandText As String
    Dim myDataSet As New DataSet
    Dim myTableView As DataView
    Dim myBigInt As Long

    myCommandText = "dbo.AllSecurityRolesAndSelected"

    Try
      Dim mySqlCommand As New SqlCommand()
      Dim mySqlDataAdapter As New SqlDataAdapter()
      mySqlCommand.CommandText = myCommandText
      mySqlCommand.CommandType = CommandType.StoredProcedure
      mySqlCommand.Connection = SecurityRolesConnection
      mySqlCommand.Parameters.Add("@EmployeeID", SqlDbType.BigInt)
      Long.TryParse(idTextBox.Text, myBigInt)    [COLOR="#008000"]'idTextBox.Text gets it data from a drop down list[/COLOR]
      mySqlCommand.Parameters("@EmployeeID").Value = myBigInt
      mySqlCommand.Prepare()
      mySqlDataAdapter.SelectCommand = mySqlCommand
      mySqlDataAdapter.Fill(myDataSet)
      myTableView = myDataSet.Tables(0).DefaultView

    Catch ex As Exception
      Throw ex
    End Try
    Return myTableView

  End Sub

And then my stored procedure and the function that it calls.

ALTER PROCEDURE [dbo].[AllSecurityRolesAndSelected] 
	@EmployeeID bigint 
AS
BEGIN
	SET NOCOUNT ON;

	SELECT isnull(srEmp.EmployeeID, 0)
	AS SecurityRoleSelected, sr.Name, sr.ID
	FROM dbo.SecurityRole sr
		LEFT JOIN dbo.SecurityRolesByAnEmployee(@EmployeeID) srEmp 
			ON sr.ID = srEmp.SecurityRoleID;
END

ALTER Function [dbo].[SecurityRolesByAnEmployee] 
(
	@EmployeeID bigint
)
RETURNS 
@t TABLE 
(
	SecurityRoleID bigint,
	EmployeeID bigint
)
AS
BEGIN
	INSERT @t (SecurityRoleID, EmployeeID)
		SELECT esrJoin.SecurityRoleID, esrJoin.EmployeeID
		FROM EmployeeSecurityRoleJoin esrJoin
		WHERE esrJoin.EmployeeID = @EmployeeID	
	RETURN 
END

I'm brand new at VB.net so thank you very much for your help, including the "slimming" down of my code. I'll address your other two points/questions here.

With the TryParse, the BigInt is coming straight from the ID field (the record key) in a record, no user input here at all, except for the selecting of a particular employee from a drop down list. I'm not too often into checking that type of possible error. If that number is not a BigInt, I have a lot bigger problem than this little routine. If I would have documented my code it would have been available for you to see where this number was really coming from.

The stored procedure "AllSecurityRolesAndSelected" is being used as the provider of a data table from SQL Server. I have also included it here too so you can see that it is expecting the @EmployeeID as an input parameter. It works great when I test it from SQL Server, but I'm so new at VB.net I don't know of any way to test getting these records returned to my program. But I'm slowly learning.

I'm trying to have the datagridview display the detail records that are associated with any specific Employee record. So, the EmployeeID is obtained from the record selected after selecting an employee in a drop down list. I don't know how to pass that number into the "AllSecurityRolesAndSelected" stored procedure that is supplying the detail records for the specific (current) Employee record.

I am finding VB.net a little tougher to learn than VBA for Access was. "It was so easy in Access." But Access wouldn't do what I want to get done, so learning VB is a must. Thanks for your help!

To repeat the actual question I have at this point: How do I get the datagridview (my detail records) to show the detail records that are associated with the employee that has been selected to be displayed on the main form?
Vic

 

[cjard]

Do this:

In your prject, add a new DataSet
Open the dataset in designer
Right click the surface and choose Add.. Query
Go through the wizard, choosing all the relevant options to attach to your AllSecurityRolesAndSelected stored procedure
A device called QueriesTableAdapter appears, inside is a row relating to your sproc

An alternative way to do this is to make a new connection to your DB in the SERVER EXPLORER window, expand the conenction, expand the functions/storedprocedures node and drag the relevant stored procedure onto your dataset designer surface

then In code:

Dim qta As New DataSet1.QueriesTableAdapter 'or whatever you called your dataset
Dim something as SOMETHING = qta.AllSecurityRolesAndSelected(EMPLOYEE_ID_HERE) 'i say something because I've no idea what will be returned here.. Int? Long? Double? choose a suiable one yourself



You should prefer this way of doing data access because it results in better encapsulated programs- writing database access code in button click handlers is NOT a good way to program