Query Parameter

I have the following code:

            Dim ConnectString As String
            Dim MyDataSet As New DataSet
                                   
            Dim Connect As OleDbConnection = New OleDbConnection
            Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter
           
            ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\website4\app_data\db1.mdb"
            Connect.ConnectionString = ConnectString

            Adapter.SelectCommand = New OleDbCommand("Select * from Bookings", Connect)
            Adapter.SelectCommand.Connection.Open()
                      
            Adapter.Fill(MyDataSet, "Bookings")


I would like to add a parameter to the query - propid which is a numerical value in the database

I think the query should be : Select * from Bookings where propid = @varprop

where and how do i specify the paramater ?

Many thanks

0
guymurray
6/9/2005 9:21:34 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

6 Replies
362 Views

Similar Articles

[PageSpeed] 41

change the following line:

Adapter.SelectCommand = New OleDbCommand("Select * from Bookings", Connect)

to

dim cmd as new oledbcommand("select * from bookings where propid = @varprop",Connect)

adapter.selectcommand = cmd

now you can insert between them
cmd.parameters.add(new oledbparameter("@varprop","42")) 'Where 42 is varprops value

Hope that helps,

Andrew

0
andrew_K
6/9/2005 10:13:16 PM
Thanks Andrew,

I have changed the code to the following:

            Dim cmd As New OleDbCommand("select * from bookings where propid = @varprop", Connect)
            cmd.Parameters.Add(New OleDbParameter("@varprop", Request("propid")))
            Adapter.SelectCommand = cmd

It seems to work and then it does not.

if I visit my page and put ?propid=1 then it works
if I put ?propid=2 it works
but if I put ?propid-3 then it does not work - it includes all records that have an id of 1 or 2 or 3
if i use a propid of 4 that there are no records for in the database then it still shows all records?

Something odd going on or is t something I need to do ?

Thanks

Guy

0
guymurray
6/9/2005 10:38:59 PM
what happens if you hard code in a value that currently isnt working?

So you have 
            Dim cmd As New OleDbCommand("select * from bookings where propid = @varprop", Connect)
            cmd.Parameters.Add(New OleDbParameter("@varprop", 3))
            Adapter.SelectCommand = cmd

I suspect that it maybe to do with the request("propid"), but cant be certain as it has been a long time since i used inline sql.

Maybe using something like convert.tointeger(request.querystring("propid")) might help, assuming that propid is an integer.
0
andrew_K
6/9/2005 10:53:14 PM
Instead of this:
cmd.Parameters.Add(New OleDbParameter("@varprop", Request("propid")))

Try this:
cmd.Parameters.Add(New OleDbParameter("@varprop", OleDbType.Integer)).Value = Request("propid")

This way of doing it explicitly tells the OleDbParameter constructor what the value is, and also what the data type is.  Otherwise, with the way you had it the OleDbParameter constructor can't tell if you are passing a name and a value or a name and a data type enumerator.

Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
6/9/2005 11:01:23 PM
Still having problems.  The entire code is as follows:

<%@ Page Language="vb" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Oledb" %>

<html>
<head>
    <script language="VB" runat="server">
       
       
        Public Shared My_Date_Array(12, 31) As String
       
   
        Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
           
            Calendar2.VisibleDate = DateTime.Today.AddMonths(+1)
            Calendar3.VisibleDate = DateTime.Today.AddMonths(+2)
            Calendar4.VisibleDate = DateTime.Today.AddMonths(+3)
            Calendar5.VisibleDate = DateTime.Today.AddMonths(+4)
            Calendar6.VisibleDate = DateTime.Today.AddMonths(+5)
            Calendar7.VisibleDate = DateTime.Today.AddMonths(+6)
            Calendar8.VisibleDate = DateTime.Today.AddMonths(+7)
            Calendar9.VisibleDate = DateTime.Today.AddMonths(+8)
            Calendar10.VisibleDate = DateTime.Today.AddMonths(+9)
            Calendar11.VisibleDate = DateTime.Today.AddMonths(+10)
            Calendar12.VisibleDate = DateTime.Today.AddMonths(+11)
           
                          
            Dim ConnectString As String
           
                                   
            Dim Connect As OleDbConnection = New OleDbConnection
            Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter
           
            ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\hshome\coast2co\c2cp-rentals.net\database\c2cprentalsdb.mdb"
            Connect.ConnectionString = ConnectString

            Dim cmd As New OleDbCommand("select * from bookings where propid = @varprop", Connect)
            cmd.Parameters.Add(New OleDbParameter("@varprop", OleDbType.VarChar)).Value = Request.QueryString("property")
            Adapter.SelectCommand = cmd
           
            Dim MyDataSet As New DataSet
                      
            Adapter.Fill(MyDataSet, "Bookings")
                            
            Dim my_month As Integer
            Dim my_date As Integer
            Dim MyDataRow As DataRow

            For Each MyDataRow In MyDataSet.Tables("Bookings").Rows
                my_month = (Convert.ToInt32(MyDataRow("Booking_date").Month))
                my_date = Convert.ToInt32(MyDataRow("Booking_date").Day)
                My_Date_Array(my_month, my_date) = "Booked"
            Next
           
            Calendar1.DataBind()
    
        End Sub

        Sub Calendar1_DayRender(ByVal sender As Object, ByVal e As DayRenderEventArgs)

            Dim d As CalendarDay
            Dim c As TableCell
            Dim my_current_date As CalendarDay
           
       

            d = e.Day
            c = e.Cell
            my_current_date = e.Day

            If d.IsOtherMonth Then
                c.Controls.Clear()
            Else
                Try
                    Dim DayIsBooked As String = Convert.ToString(My_Date_Array(d.Date.Month, d.Date.Day))
                    If DayIsBooked = "Booked" Then
                        e.Cell.BackColor = System.Drawing.Color.Maroon
                    Else
                        e.Cell.BackColor = System.Drawing.Color.LightSkyBlue
                       
                       
                    End If
     
                Catch exc As Exception
                    Response.Write(exc.ToString())
                End Try
            End If
        End Sub

        Sub Date_Selected(ByVal sender As Object, ByVal e As EventArgs)
            Label1.Text = "Selected date is: " + Calendar1.SelectedDate.ToShortDateString
        End Sub

    </script>

</head>


Query works sometimes - I have changed the values so that it is using a reference number for the property usign varchar as the data type.  When the page runs and I change the querystring property value it is as if the dataset is not being populate again from scracth as other records are being displayed.  It appears as if the previous dataset is being used aswell - some sort of caching going on ?

0
guymurray
6/10/2005 10:10:27 AM
It's also a good idea not to depend on bugs in OleDb.  OleDb does
not support named parameters, you must use ? as your placeholder.
0
JeffreyABecker
6/10/2005 11:56:26 AM
Reply:

Similar Artilces:

Gridview Search Without Object Datasource Filter ParametersGridview Search Without Object Datasource Filter Parameters
Couple weeks ago I read a article about highlighted search in Gridview  (http://blog.evonet.com.au/post/2008/06/25/Gridview-with-highlighted-search-results.aspx) and in my project I used things I learned but now things changed I have a gridview and in code I give its datasource so because I dont use any objectdatasource I dont know where to write my filtering parameters. Is there anyway to use filtering without objectdatasource, or is there a way to give objectdatasource an source then bind it to my gridview. My code:  //No objectdatasource how can I conduct a search?GridView1.Da...

Passing SQL Parameter Array to Object datasource as parameter
I have a class 'CommonDataAccess' and my method filldataset() accepts a Stored Procedure name and a SQL Parameter Array. This class is to be made as the type for the Objectdatasource which is Bound to a gridview. How do I pass the SQL Parameter Array to this.  Raj Kiran S------------Chennai, India CALLINGBELLS: I have a class 'CommonDataAccess' and my method filldataset() accepts a Stored Procedure name and a SQL Parameter Array. This class is to be made as the type for the Objectdatasource which is Bound to a gridview. How do I pass the SQL Parameter Array to t...

C# Ado.net Sql query parameter query
Hi All, I'm trying to pass in a parameter value from an array in a loop that is used in a sql query and the results are populated to an xml file. The trouble is that I'm only getting the colums values in the outputted xml file. So I feel that the paramter is not being read. So can anyone help as I'm really stuck on this one. The code is as follows : public void DisplayUserInfo() { ArrayList UserIdArrayList = IdentifyUserID(); foreach(string ShowUserIDString in UserIdArrayList) { try { SqlConnection SqlConn = new SqlConnection(DBConnString)...

passing object as a parameter in datasource
How can I pass a custom class (in my case instance of Test) as a parameter in objectdatasource_updating method to update the user information?  Please take a look at the code and I have made the stuff I need, underlined and bold.Public Class Test Private _id As Integer Private _username As String Private _firstname As StringPublic Sub New(ByVal id) Dim test As New Test()Me.getUser(id) End SubPublic Sub New() End SubPublic Property id() GetReturn _id End GetSet(ByVal value) _id = value End Set End PropertyPublic Property UserName() GetReturn _username...

Object datasource parameter question
I have an object data source that is referencing a strongly-typed dataset.  In the dataset I have a couple of queries based on the type of search that a user wants.  How do I reference the GetData query that I created in the dataset?  I was under the impression that I could do: me.objectdatasource.GetDataBy, however I see that I cannot do this.  I have also tried setting the object data source's select method and then adding a parameter, but I am getting an error about "could not find a non-generic method that has parameter...". What I am trying to do is to SELECT data i...

Replace the .NET 1.1 Behavior-Object with the .NET 2.0 Tag-Object , How to?
Hello, to set the Style - Attribute of my custom controls tag in source view from within its designer i use now the following code:Behavior.SetStyleAttribute("Left", false, val, true);This works fine but my visual studio 2005 / asp.net 2.0 says that the Behavior object is obsolete. The recommended alternative is the Tag object. But when i use it to set the Style attribute it doesn't work . I can set all other attributes or even create new ones using this Tag object but it does'nt work for the style attribute at all. What am i doing wrong? If I can't set the style attribute in my custom contr...

Object datasource: Can I programatically set a parameter?
Can I programatically set a parameter? If so how?  I'm asking because I have a form that I want a button to update and pull information from a details view. But the details view doesnt update on the button press. Im kind of stumped as to how the form would opperate independently of the button...   Any help is awsome!  http://www.asp.net/learn/data-access/tutorial-06-cs.aspxRegards Mike [MVP - ASP/ASP.NET]My site Hi there I have programmatically set an objectdatasource parameter before. I used it within an OnUpdated method of a DetailsView control when I wante...

Object Datasource and Custom Object
Hey, I have an object datasource that populates a detailsview.  I know how to update the changes using a BLL method like so: public static UpdateUser(string Username, string Surname); However I would like to use the objects update() method ive created that essentially calls: public static UpdateUser(User user); The question is, how do I access the fields that are contained within the details view?  im guessing .FindControl() but i dont have the ID.  the other way I suppose is to itterate through all the controls within in it?  there must be a better way though? The ...

Parameter query
Hi: I am trying to build maste/detail page.  1.  I have on my page drop down control based on sqldata sources with the concataned fields as follows:   SELECT DISTINCT LastName + ' ' + FirstName AS Expr1 FROM dbo.Table1 It displays First Name space and Last name in the box and works fine. 2. I placed on my form a grIdview In data source I am trying to use parameter filter in SQL select query and the same query as for drop down as follows: SELECT     LastName + ' ' + SiteFirstName AS Expr1, field1, field2, etc.........

Problem in Parameter passing using oledb object in .Net
Hi, Anybody please help me in this problem. I am using ASA 9.0 server version now. i try to connect with OLEDB object with ASA DB it is connecting fine but when i try to send the data to the DB thru an Parameter object in OLEDBCommand object. Data saving as junk character in the database. is there any addtional setting has to be made or not. pls tell me the solution for this. If i am using the ASAClient object i am not able to connect only with provider. thru DSN i am able to connect. i want to know ASA connection object only support DSN connection or the Provider also. if yes means...

Using aggregated objects from Linq query as a GridView DataSource
I'm using Linq to Entities, and am returning a List collection of projected objects as the DataSource for a GridView. In itself, this doesn't pose any problem. However, this time one of the values of the collection is a collection itself. To make that clearer, I have to grab the name of a department, and totals for each new and revision type of project for that department, for each month indicated in the report query. It would look a bit like a table with 2 rows for each department/month intersection, one for new and one for revision. Something like:     &nb...

Gridview Search Without Object Datasource Filter Parameters
Couple weeks ago I read a article about highlighted search in Gridview  (http://blog.evonet.com.au/post/2008/06/25/Gridview-with-highlighted-search-results.aspx) and in my project I used things I learned but now things changed I have a gridview and in code I give its datasource so because I dont use any objectdatasource I dont know where to write my filtering parameters. Is there anyway to use filtering without objectdatasource, or is there a way to give objectdatasource an source then bind it to my gridview. My code:  //No objectdatasource how can I conduct a search?GridView1.Da...

does dw.net 2.x support object datasource
does dw.net 2.x support object datasource thanks vsv No. That is something we are looking at for version 3.0. Regards, Dave Fish Sybase On 3 Aug 2007 12:33:46 -0700, "vsv" <nospam@nospam.com> wrote: >does dw.net 2.x support object datasource > >thanks >vsv > with mapping to Linq & entity framework? or DatwindowObject Source by itself. thanks vsv "Dave Fish [TeamSybase]" <dfish@[N0Spam]sybase.com> wrote in message news:8u57b39g068kbrs7d8rd82f24sem4cn8dp@4ax.com... > No. That is something we are looking at ...

multiple update panel and object datasource insert parameters problem
Hi Guys Newbie using vs2--5, vb.net .net2.0 and ajax with SQL express Ok have a page, the page works fine, accept i added some ajax update panels onto it, this normally isnt a problem but i am also using a freetext box control and when some of my code and validation was firing it was causing an issue if it my controls were all held inside an update panel so i had to create several.  My validation now works fine but when i am trying to use my objectdatasource to add my insert parameters it cannot see the controls as they are now sat in several different update panels, this normally isn...

NCache 3.0 provides Object Queries in Distributed Caching for .NET
Alachisoft has released version 3.0 of NCache, a distributed object cache for .NET. NCache now provides support for object queries through an object query language (OQL). Download a 60-day FREE Trial from http://www.alachisoft.com/rp.php?dest=/download.html. A distributed cache is typically seen as a hashtable by your application. However, there are times when you want to find objects in the cache based on object attributes rather than a key. Object query language provides you an SQL-like syntax to specify your search criteria. The query then returns a collection of objects from all over th...

Web resources about - Query Parameter - asp.net.object-datasource

Parameter - Wikipedia, the free encyclopedia
A parameter (from the Ancient Greek παρά , "para", meaning "beside, subsidiary" and μέτρον , "metron", meaning "measure"), in its common meaning, ...

fb_source Parameter - Facebook-Entwickler
This document lists the complete list of values for the fb_source parameter that is returned with th...

Google / Firefox Parameter - Flickr - Photo Sharing!
HS what? www.seroundtable.com/archives/017105.html

Cat 8: TIA performance standards committee chairman previews parameters of forthcoming twisted-pair cable ...
Category 8, the next-generation twisted-pair cabling specification, is still in development, but the outlook looks quite positive that it will ...

Philippine president asks for 'parameters' of Mayon Volcano tours
Philippine president asks for 'parameters' of Mayon Volcano tours People's Daily Online ... 26, 27, 28, 29, 30, 31. Jan, Feb, Mar, Apr, May, ...

Alternatives to boolean parameters
... ) { PrintLine ( item . Name ); if ( printDetails ) { PrintLine ( item . Description ); } } } Aha! Now can see that the boolean parameter ...

Exporting Error (Error in Parameter...: Apple Support Communities
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability ...

Apple tech monitors device aging, adjusts operating parameters to ensure optimal performance
... describing a method of monitoring the aging of a device's electronics, comparing the data with a global average and modifying operating parameters ...

Is it poor programming practice to pass parameters as objects?
... a free, community-powered network of 100+ Q&A sites . Risser asked: So, we've got a guy who likes to write methods that take Objects as parameters, ...

Parameter Short Film Competition sponsored by Grolsch
Grolsch presents Parameter The A.V. Club Short Film Competition Please Enter Your Date of Birth Ineligible entry Enter For people over the age ...

Resources last updated: 1/16/2016 8:40:12 AM