How do transfer the code into stored procedure

Question

1.00/5 (1 vote)

See more:

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 18-May-20 21:28pm

Member 14832867

Updated 19-May-20 20:48pm

v3

Add a Solution

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

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 · Answer 1 · 2020-05-18T21:49:00

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:

SQL

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.