Click here to Skip to main content
14,545,215 members
Rate this:
Please Sign up or sign in to vote.
Dim commandText As String = "SELECT DISTINCT BLCHARGE_SOC_COC, BLCHARGE_BL_NO FROM bl_master, bl_charge WHERE BLCHARGE_BL_NO = BL_NO  and BLCHARGE_BL_NO = @BL_NO and BLCHARGE_SOC_COC is not null"
Dim command As SqlCommand = New SqlCommand(commandText, conn)
Dim da77a As SqlDataAdapter = New SqlDataAdapter()
Dim myDataSet77a As New Data.DataSet
command.Parameters.AddWithValue("@BL_NO", myDataNvocc.Tables(0).Rows(y).Item("BL_NO"))
da77a.SelectCommand = command
da77a.Fill(myDataSet77a, "bl_master")


What I have tried:

The data seems to be not filled in the dataset when i move the commandText to stored procedure
Posted
Updated 19-May-20 20:48pm
v3
Comments
Richard MacCutchan 19-May-20 3:33am
   
Please edit your question and show the code you use to call the procedure, and the procedure as written. Also explain what actual results or errors are returned.
Member 14832867 19-May-20 3:41am
   
There is no error but the data is fill is not correct. Btw the myDataNvocc is a dataset

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

An SQL Stored Procedure is just a list of SQL commands to be executed together, and are intended to provide data validation procedures, access control, or just to prevent the same SQL code being needed in multiple places in external apps. Once created, the stored Procedure can be called from SQL code, or executed directly in external code just by using the name of the SP (and supplying any necessary parameters).

So the SP version of a simple command is ... just the simple command, wrapped in SQL to create the procedure:
CREATE PROCEDURE sp_SelectByID 
    @ID INT
AS
BEGIN
	SELECT * FROM MyTable WHERE ID = @ID;
END

Once that code is executed in SQL, you can call sp_SelectByID instead of typing "SELECT * FROM MyTable WHERE ID = @ID" as your SQLCommand text, and telling the SqlCommand instance that this is a procedure call, not a text command (as well as providing the parameter value)

So if your existing - simple SELECT - code works in isolation and fails as an SP, then either you are creating the SP incorrectly, or calling it badly. I'd start by testign the SP in SSMS ("DB ... Programmability ... Stored Procedures ... YourSPName", right click, "Execute Stored Procedure..." will bring up a dialog for you to enter the parameter value) and see what happens there first. When that works, try calling it from VB.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100