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
Posted
Updated 18-May-20 22:09pm
v4
Comments
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
DROP TABLE MyTable;
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
BEGIN
   SELECT DISTINCT CHARGE, NO

   FROM student
   ,    teacher

   WHERE CHARGE = ID
   and   NO     = @No
   and   CHARGE is not null
END
GO
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") )

     Try
          connection.Open()
          Dim sdr as SqlDataReader = cmd.ExecuteReader()

          ' Go through your dataset

     Catch ex As Exception
          Console.WriteLine(ex.Message)
     End Try
End Using
References:
MS Docs => CREATE PROCEDURE[^]
MS Docs => SqlCommand.Parameters Property[^]
   
Comments
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