Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 1.0 and 1.1 Basics ASP.NET discussion for users new to coding in ASP.NET 1.0 or 1.1. NOT for the older "classic" ASP 3 or the newer ASP.NET 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 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 Display Modes
  #1 (permalink)  
Old November 18th, 2003, 03:38 PM
Friend of Wrox
 
Join Date: Oct 2003
Location: Portsmouth, Hampshire, United Kingdom.
Posts: 146
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to aadz5
Default using command objects.

Hi guys,

I am trying to use the command object and datareader object to get a row from the database. The thing is everytime I execute the event handler I get this error: -

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

here is my code: -

private void Click_Button(object sender, System.EventArgs e)
{
  String strConnection = "Provider=Microsoft.Jet.OleDb.4.0; data source = D:\\ineedabook\\Books.mdb;";
  OleDbConnection objConnection = new OleDbConnection(strConnection);

  OleDbCommand objCommand = new OleDbCommand();
  OleDbDataReader objDataReader = null;

  objConnection.Open();
  objCommand.Connection = objConnection;
  objCommand.CommandType = CommandType.Text;
  objCommand.CommandText = "Select * FROM Computing Where BookAuthor = ? AND BookTitle = ?";
  objCommand.Parameters.Add("BookAuthor", OleDbType.VarChar, 32,"BookAuthor");
  objCommand.Parameters.Add("BookName", OleDbType.VarChar, 128, "BookName");
  objCommand.Parameters["BookAuthor"].Value = TextBox1.Text;
  objCommand.Parameters["BookName"].Value = TextBox2.Text;
  objDataReader = objCommand.ExecuteReader();
  if (objDataReader.Read())
  {
    Response.Write("I have found a record with that criteria");
  }
    //'Found a record ...

 }

I think it maybe something to do with how I have set the commandtext attribute of my command object.




Adz - Portsmouth Massive
__________________
Adz - Learning The J2EE Ways.
  #2 (permalink)  
Old November 18th, 2003, 03:59 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Try this... I don't konw if this works the same with the OLEDB stuff, but this is how I do it with the SQLClient objects:

objCommand.CommandText = "Select * FROM Computing Where BookAuthor = @BookAuthor AND BookTitle = @BookName";
objCommand.Parameters.Add("@BookAuthor", OleDbType.VarChar, 32,"BookAuthor");
objCommand.Parameters.Add("@BookName", OleDbType.VarChar, 128, "BookName");
objCommand.Parameters["@BookAuthor"].Value = TextBox1.Text;
objCommand.Parameters["@BookName"].Value = TextBox2.Text;

Peter
------------------------------------------------------
Work smarter, not harder.
  #3 (permalink)  
Old November 20th, 2003, 07:10 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Portsmouth, Hampshire, United Kingdom.
Posts: 146
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to aadz5
Default

Peter Thank you,

Peter my code works without using the parameters attribute of command. Is this an ok way of doing it or is it preferable to use cammand parameterization??: -

 String strConnection = "Provider=Microsoft.Jet.OleDb.4.0; data source = D:\\ineedabook\\Books.mdb;";
       OleDbConnection objConnection = new OleDbConnection(strConnection);
       String strSearch = String.Format("SELECT * FROM Computing WHERE BookAuthor LIKE '{0}' OR BookName LIKE '{1}'", TextBox1.Text, TextBox2.Text);

       OleDbCommand objCommand = new OleDbCommand();
       OleDbDataReader objDataReader = null;

       objConnection.Open();
       objCommand.Connection = objConnection;
       objCommand.CommandType = CommandType.Text;
       objCommand.CommandText = strSearch;//"Select * FROM Computing Where BookAuthor = ? AND BookTitle = ?";
       //objCommand.Parameters.Add("BookAuthor", OleDbType.VarChar, 32,"BookAuthor");
       //objCommand.Parameters.Add("BookName", OleDbType.VarChar, 128, "BookName");

       //objCommand.Parameters["BookAuthor"].Value = TextBox1.Text;
       //objCommand.Parameters["BookName"].Value = TextBox2.Text;
       objDataReader = objCommand.ExecuteReader();
       if (objDataReader.Read())
       {
         Response.Write("I have found a record with that criteria");
       }

I cant seem to get it to work with parameterization?

Any ideas??



Adz - Portsmouth Massive
  #4 (permalink)  
Old November 20th, 2003, 07:25 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

I don't know the syntax for the query when you use OleDB objects so I can't do much for you there. But I'm wondering if your use of the ? is correct in there? When you specify parameters, you provide a variable name. In MS-SQL they are formated like this: @variableName. That's how you use them in the command object parameters (as in my example). Don't know about OleDB objects though.

My understanding of parametered queries is that the classes automatically protect you against query attacks (known as SQL injection). This is where someone manipulates an entry to cause harm to your data. This is a concern with MS-SQL server, and I imagine also with any other. I think that using parameters checks for that kind of stuff.

I can say for sure that using parameters makes it a lot easier to deal with the data types and string formatting. All you do in your query is specify the variable. Then you add parameters. Based on the datatypes of the parameters the command object will structure the query correctly for the datatypes, thus removing what can be a major headache for some people. It's usually much easier to read a query in the code when it just has variables in it instead of a whole lot of " & someCodeVariable & " buried inside. :)

Peter
------------------------------------------------------
Work smarter, not harder.
  #5 (permalink)  
Old November 20th, 2003, 08:37 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Portsmouth, Hampshire, United Kingdom.
Posts: 146
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to aadz5
Default

Thanks Peter,

I will see if anyone knows the syntax for OleDb Command Objects Parameters.



Adz - Portsmouth Massive
  #6 (permalink)  
Old November 20th, 2003, 11:39 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Check our other conversation about this. ;)

Peter
------------------------------------------------------
Work smarter, not harder.
 


Thread Tools
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
Value objects rayback_2 BOOK: Beginning Cryptography with Java 1 May 13th, 2008 05:04 PM
Command text was not set for the command object Sheraz Khan Classic ASP Databases 2 May 29th, 2007 12:57 AM
Command text was not set for the command object. deepa12 BOOK: Beginning ASP 3.0 5 November 2nd, 2004 04:37 PM



All times are GMT -4. The time now is 06:00 PM.


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