dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Using Stored Procedures with SQLDataAdapter?

  1. #1
    Join Date
    Dec 2003
    Posts
    83

    Using Stored Procedures with SQLDataAdapter?

    I'm stumped. To prevent SQL injection in my app I need to use "parameterized" stored procedures instead of dynamically created SQL statements. I'd also like to keep using SQLDataAdapter because it's so easy to work with. It makes selecting, updating, deleting, inserting a breeze!

    I've googled extensively and got lots of information about how easy it is to use stored procedures with SQLDataAdapters, why it's a good idea vs. dynamically built SQL statements, etc. etc. but nowhere I've looked has told me how it's actually done and I've looked so long my eyes are all red and scratchy

    Here is an example of the current code:
    Code:
    Dim strConn as String = "user id=user;password=password;server=server;database=database"
    Dim strCommand As String = "SELECT * FROM TABLE1 WHERE DATA1 = '" & Whatever & "'"
    Dim DA As New SqlDataAdapter(strCommand, strConn)
    DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
    Dim DS As New DataSet
    DA.Fill(DS)
    This works perfectly (except for the SQL injection vulnerability).

    Here is what I'm trying to do to mimic it using a stored procedure:

    Code:
    Dim strConn as String = "user id=user;password=password;server=server;database=database"
    Dim conn As New SqlConnection(strConn)
    Dim cmd As SqlCommand = conn.CreateCommand
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "MyStoredProcedure"
    Dim pData As SqlParameter = cmd.Parameters.Add("@pdata", SqlDbType.VarChar, 100)
    pData.Value = Whatever
    Dim DA As New SqlDataAdapter(cmd)
    DA.MissingSchemaAction = MissingSchemaAction.AddWithKey
    Dim DS As New DataSet
    Dim CB As New SqlCommandBuilder(DA)
    DA.Fill(DS)
    Here is the stored procedure that is being called:
    Code:
    CREATE PROCEDURE dbo.MyStoredProcedure
    	@pdata varchar(100),
    AS
    	SELECT * FROM TABLE1 WHERE (PDATA = '@pdata') 
    GO
    This returns no data at all. No error either. Nothing...

    Can anyone point out the (probably obvious) problem here?

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    I Googled on "sqldataadapter stored procedure northwind" and found this article: http://www.csharpfriends.com/Article...x?articleID=78 . Based on that article, your code should look like this:
    Code:
    Dim conn As New SqlConnection(strConn)
    Dim cmd As New SqlCommand("MyStoredProcedure", conn)
    With cmd
        .CommandType = CommandType.StoredProcedure
        .Parameters.Add("@pdata", SqlDbType.VarChar, 100)
        .Parameters("@pdata").Value = "Whatever"
    End With
    Dim da As New SqlDataAdapter(cmd)
    Dim ds As New DataSet
    da.Fill(ds)
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    May 2005
    Location
    UK
    Posts
    278
    HI
    I dont think the problem is in the code, it is in the stored procedure
    This is your query
    SELECT * FROM TABLE1 WHERE (PDATA = '@pdata')
    The query is using a constant @pdata to compare with the PDATA column, to specify the value in @pdata remove the single quotes around the parameter,
    Sri

  4. #4
    Join Date
    Dec 2003
    Posts
    83
    Thanks srinivas_s. That was it. Thank you to Phil as well for a "cleaner" (in my opinion) way to write the code.

  5. #5
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Good catch, Sri! Sorry I missed it.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

Similar Threads

  1. C++, Ole Db, stored procedures
    By Nick in forum Database
    Replies: 1
    Last Post: 05-09-2002, 07:18 AM
  2. DB Independent Application..What abt Stored Procedures
    By Richard Hsu in forum VB Classic
    Replies: 0
    Last Post: 12-05-2001, 05:26 AM
  3. Stored procedures and multiple databases
    By Col in forum VB Classic
    Replies: 0
    Last Post: 10-25-2001, 08:34 PM
  4. Replies: 3
    Last Post: 03-01-2001, 11:49 PM
  5. Stored procedures and Active X Objects
    By Nicole Hagler in forum Database
    Replies: 0
    Last Post: 09-06-2000, 09:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center