Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > ASP.NET 3.5 Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 3.5 Basics If you are new to ASP or ASP.NET programming with version 3.5, this is the forum to begin asking questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 27th, 2008, 11:49 PM
Friend of Wrox
 
Join Date: Sep 2007
Location: , , .
Posts: 169
Thanks: 7
Thanked 2 Times in 2 Posts
Default How do I make a placeholder parameter for Ado.net

Hi

I usually use Sql server 2005 but I have to right now have to use access 2007 and I am wondering how do I make a parameter placeholder?

Like if this was sql server 2005 I would do something like this

SELECT StudentID, FirstName, LastName
FROM Students
WHERE (StudentID = '@StudentID')

* I am making this with a typed dataset(dataset.xsd file). So I usually would type that into the design builder and then execute it. Once I hit the execute the button it would then popup and ask to put some data in as the parameter.

When I try the exact same statement with an access 2007 db it just runs the statement and seems to ignore that it's a placeholder parameter and tries to use it as part of the where clause so I always get no results.

  #2 (permalink)  
Old November 28th, 2008, 12:17 AM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

You use a question mark:

SELECT StudentId, FirstName, Lastname FROM Students WHERE StudentID = ?

_________________________________

Visit my blog at http://leedumond.com
  #3 (permalink)  
Old November 28th, 2008, 12:30 AM
Friend of Wrox
 
Join Date: Sep 2007
Location: , , .
Posts: 169
Thanks: 7
Thanked 2 Times in 2 Posts
Default

So you can't give it a name?

Also where do you find ado.net stuff + access stuff I am having a hard time finding information on it.

  #4 (permalink)  
Old November 29th, 2008, 01:13 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

You can use the EXACT same approach to executing Access "stored procedures" as you can executing Sql Server stored procedures. Here is how its done. Since you are using Access 2007 you are using the Jet 4.0 engine whichh support Sql Server Compatible Syntax. But first you have to enable it. In your Access database do the following:

Tools --> Options --> Tables/Queries

In the Sql Server Compatible Syntax (ANSI 92) group check 'This database'

Now create a data definition query in the query designer SQL view:

Code:
CREATE PROCEDURE GetStudentById
(
[@studentId] int
)
AS
SELECT
   StudentID,
   FirstName,
   LastName
FROM
  Students
WHERE
  StudentID = [@studentId];
The barckets around the parameter a required!

Now run the query. You'll now see a saved, parameterized query named GetStudentById back in the Database Objects windows. Its SQL is:

Code:
PARAMETERS [@studentId] Long;
SELECT Students.StudentID, Students.FirstName, Students.LastName
FROM Students
WHERE (((Students.StudentID)=[@studentId]));
You now have a saved Access "stored procedure to work with just as you would work with a Sql Server Stored Procedure. The ADO.NET code to use it is IDENTICAL, except that you are of course using OleDb objects and the OleDb provider in your connection string. For example, here's a little Console app:

Code:
using System;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            GetStudentById(1);
        }

        public static void GetStudentById(int studentID)
        {
            using (OleDbConnection connection = new OleDbConnection
                 (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Students.mdb;Persist Security Info=True"))
            {
                connection.Open();
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "GetStudentById";

                    command.Parameters.AddWithValue("@studentID", studentID);

                    using (OleDbDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Trace.WriteLine(reader.GetInt32(0));
                                Trace.WriteLine(reader.GetString(1));
                                Trace.WriteLine(reader.GetString(2));
                            }
                        }
                    }
                }
            }
        }
    }
}
This approach allows you to essnetially reuse you Sql Server stored procedures and ADO.NET code when migrating between Sql Server and Access.

HTH,

Bob



  #5 (permalink)  
Old November 29th, 2008, 12:00 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

I don't work with typed datasets much, and even less with the "builders" and such, so I'm not quite sure what "I usually would type that into the design builder and then execute it" means. But I thought I'd add that using a named parameter (e.g., "@studentID") in code works fine with datasets too, if that would be at all helpful:

Code:
public static void GetStudentById(int studentID)
        {
            using (OleDbConnection connection = new OleDbConnection
                 (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Students.mdb;Persist Security Info=True"))
            {
                connection.Open();
                using (OleDbCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "GetStudentById";

                    command.Parameters.AddWithValue("@studentID", studentID);

                    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                    adapter.TableMappings.Add("Table", "Students");

                    StudentsDataSet dataset = new StudentsDataSet();

                    adapter.Fill(dataset);

                    DataSetDump.ShowDSInOutputWindow(dataset);

                }
            }
        }
If you used the code approach, however, your project can't maintain an open Data Connection to the .mdb, or you'll be locked out. The connection has to be opened and closed in code as well.

Here's the dump routine if you want to check out the DataSet contents:

Code:
class DataSetDump
    {
        // Call with: DataSetDump.ShowDSInOutputWindow(ds);
        // using directives: System.Data, System.Data.OleDb & System.Diagnostics
        static public void ShowDSInOutputWindow(DataSet ds)
        {
            DataTable table = ds.Tables[0];

            Debug.WriteLine("---------------------------------------");
            Debug.WriteLine("\t\t DataSet Table: " + table.ToString());

            bool firstRow = true;

            foreach (DataRow row in table.Rows)
            {
                if (firstRow)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        if (column.DataType.Name != "Byte[]")
                        {
                            Debug.Write(column.ColumnName + "\t");
                        }
                    }
                    Debug.WriteLine(String.Empty);
                    firstRow = false;
                }
                foreach (DataColumn column in table.Columns)
                {
                    if (column.DataType.Name != "Byte[]")
                    {
                        Debug.Write(row[column] + "\t");
                    }
                }
                Debug.WriteLine(String.Empty);
            }
        }
    }
  #6 (permalink)  
Old November 29th, 2008, 05:09 PM
Friend of Wrox
 
Join Date: Sep 2007
Location: , , .
Posts: 169
Thanks: 7
Thanked 2 Times in 2 Posts
Default

Hi

where is tool->options->Table/Queries on the ribbon?

With this "I usually would type that into the design builder and then execute it"

What I mean is when you go through a typed dataset wizard it has a sql query builder where you can just click some options and it would generate the sql for you. It also has an execute button that allows you try the statements out so you can see if it's what you want to return.


I was using that to try my sql queries out since I did not know access had it's own tester.

  #7 (permalink)  
Old November 30th, 2008, 01:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

My apologies for pretty much missing the boat on this one. I didn't realize until after my second post that you were probably talking about the Table Adapter Query Configuration Wizard. Haven't used it since last December, and that was against a Sql Server database. Never tried using a typed dataset against Access, so generally speaking, I can't be of any help here (though you got me curious and I'll probably have to play with it tomorrow).

tool->options->Table/Queries refers to the main menu in Access. I focus all my limited data access attention on using custom business objects populated by SqlDataReaders and occassionally OleDbDataReaders. The code I posted would be useful if you were taking this approach, but is generally useless to you if you are using a disconnected typed dataset.

Bob

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Placeholder grstad ASP.NET 2.0 Basics 6 May 23rd, 2008 09:03 AM
Difference between ADO and ADO.NET rakeshclose2u ADO.NET 2 April 23rd, 2007 03:57 AM
Parameter queries and make tables deanm5 Access VBA 1 April 13th, 2007 11:32 AM
ADO AND ADO.NET royalsurej ADO.NET 1 November 8th, 2004 08:28 AM
MSDE and SQL CE (using VB.NET and ADO.NET) LEGS ADO.NET 0 July 12th, 2003 11:27 AM



All times are GMT -4. The time now is 12:18 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.