Click here to Skip to main content
14,545,210 members
Rate this:
Please Sign up or sign in to vote.
SELECT DISTINCT CHARGE, NO FROM student, teacher WHERE CHARGE = ID  and NO = '" & myDataNvocc.Tables(0).Rows(y).Item("ID") & "' and CHARGE is not null

Btw the
'" & table1.Tables(0).Rows(y).Item("ID") & "'
is from dataset and i want use this in stored procedure

y is from this
For y = 0 To table1.Tables(0).Rows.Count.ToString() - 1

What I have tried:

Pls help me i really have no idea
Updated 18-May-20 22:09pm
Richard Deeming 19-May-20 8:27am
Once again, DO NOT remove the content of your question after it has been answered!
Rate this:
Please Sign up or sign in to vote.

Solution 1

Never, ever, like that. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
A perfectly valid "delete the table" command
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

It doesn't matter if that code is a SP or a SqlCommand: your whole approach - your whole app - is wrong and needs to be fixed.
Rate this:
Please Sign up or sign in to vote.

Solution 2

In addition to Solution 1, a lot of computer work means you need the ability to search/re-search for yourself, since you come here with code - and we dont do people's work for them ...

Here are some URL's to get you started

SQL Server Stored Procedures Tutorial[^]

Create a Stored Procedure - SQL Server | Microsoft Docs[^]

SQL Server stored procedures for beginners[^]
Rate this:
Please Sign up or sign in to vote.

Solution 3

Yes, and I would encourage it simply for the fact that it should get you out of that nasty habit of creating queries by piecing together commands and variables; which is the number one reason that people get SQL Injected, which is still in the top 10 vulnerabilities list after being over 20 years old.

While this is neither a code translation nor generation website; what I am providing you below is simple boiler plate code and I would recommend you review the referenced articles at the end of this post.

I would also recommend that your Stored Procedure should be written with a proper JOIN and specifically identifying the fields in use. I would encourage you to format it over several lines for readability purposes.
SELECT a.RecordName, b.DetailName
FROM Records a
JOIN Details b ON a.RecordID = b.RecordID
WHERE a.RecordID   = @RecordID
AND   b.DetailName IS NOT NULL

Now onto creating an SP

You simply need to wrap that line of SQL into a CREATE PROCEDURE statement; and define the @Parameter that will be used within it
CREATE PROCEDURE [SchemaName].[MyStoredProcedure] (
   @No INT 
) AS

   FROM student
   ,    teacher

   and   NO     = @No
   and   CHARGE is not null
And then modify your SqlCommand code to reference this procedure, define this as a Stored Procedure, and add the Parameter into it.
You will still run this via ExecuteReader() and you can iterate through the records as normal.
Please Note: VB is not my native tongue so there may be errors as this was done in Notepad
Using connection As New SqlConnection(connectionString)
     Dim cmd As New SqlCommand("SchemaName.MyStoredProcedure", connection)

     cmd.CommandType = CommandType.StoredProcedure
     cmd.Parameters.AddWithValue("@No", myDataNvocc.Tables(0).Rows(y).Item("ID") )

          Dim sdr as SqlDataReader = cmd.ExecuteReader()

          ' Go through your dataset

     Catch ex As Exception
     End Try
End Using
MS Docs => SqlCommand.Parameters Property[^]
Member 14832867 15-May-20 22:40pm
Is it possible to fill the result into dataset? Thanks for your help really appreciate it!

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