set parameter ms access and ?

  • Thread starter Thread starter Miro
  • Start date Start date
M

Miro

I am stuck onto something as to how to set a parameter in asp.net
vb.net I am fine but here is what I did.

I have a table in my msaccess database called "users" and within it a column
called emailaddr

I dropped the AccessDataSource onto the webform and it created this:
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/PokerSettings.mdb"
SelectCommand="SELECT COUNT(*) AS Expr1 FROM Users where emailaddr =
?">
<SelectParameters>
<asp:Parameter Name="?" />
</SelectParameters>
</asp:AccessDataSource>

Notice the select parameter and the ?' in the selectcommand.

Now on my submit button, i want to populate that ? with a value from a
textbox.
I almost want to do something like this:
Dim userCount As Integer = 0
AccessDataSource1.Parameters.Add("?", Email1.Text)
userCount = AccessDataSource1.Select()

Im going in circles looking for the right syntax in vb.net on how to do this
in asp.net

Thanks,

Miro
 
WHERE emailaddr={0};

or maybe put single quotes around the value in the string

where emailaddr = '?'

Or try something besides a ?...


where emailaddr = ABC
where emailaddr = 'ABC'


....

I don't know , I'm guessing.
 
I think you missed my question... (hard to explain what I am looking for)

I cannot figure out how to shoot my value of my textbox into the? as a
parameter.

In vb.net ( forms )...it was simple...
I had a Table adapter defined in my dataset so I would do something like
this:

me.MyTableAdapter.AdapterName( DataSetTofill, txtParameter.text )

The table adapter was predifined in the dataset with a query that used
question marks because it was an access table.

What I cannot figureout is how to "Call" the AccessDataSource from the vb
code within asp.net.
The AccessDataSource has the sql query setup with the question marks.
I just cant figure out how to do the same as I would with the
myTableAdapter.

Thanks,

Miro
 
In a SQLDataSource, for SQL Server, you would use something like this. It
might be similar for AccessDataSource.

<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/PokerSettings.mdb"
SelectCommand="SELECT COUNT(*) AS Expr1 FROM Users where emailaddr =
@emailAddress>
<SelectParameters>
<asp:ControlParameter Name="emailAddress" Type="String" Size="100"
ControlID="Email1" PropertyName="Text" />
</SelectParameters>
</asp:AccessDataSource>

If your parameter value is in a text box, you can use a ControlParameter, as
indicated above.
 
Lets say you want to "call" your "select" or whatever from your datasource
directly from vb.
And you do not have your "value" not in a textbox, but in a localized DIM'd
Variable.

That is what I am trying to figure out.

For example... here is another example:
How would I put this select to hit from a button, but pass in the
EmailAddress parameter by code.
That is what I am banging my head on right now.

The accessdatasource seems to be just like a tableadapter setup. But I just
cant figure out how to properly call it with the right parameters.
Im sure I cannot be the first newbie to try something like this.

The reason I am using an ms access database vs an sql one is because I test
my project 1 step farther and test to deploy it online on my dummy online
asp hosting account.
MsAccess db's are free... i need to pay more for sql.
Also, I find I learn a lot more quirks when learning a bit of both at the
same time.

In this below example, I have a simple access database and I want to just
list only certain email addresses when a button is clicked.
It would be nice if the "call" would be simillar to a tableadapter. But its
not.
accessDataSource2.select( _Param1, Param2.... )
So all I can figure out is i can call an accessDataSource2.select( ) but I
get runtime errors.


<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="UserID" DataSourceID="AccessDataSource2">
<Columns>
<asp:BoundField DataField="UserID" HeaderText="UserID"
InsertVisible="False"
ReadOnly="True" SortExpression="UserID" />
<asp:BoundField DataField="EmailAddr" HeaderText="EmailAddr"
SortExpression="EmailAddr" />
<asp:BoundField DataField="SpecialULKey"
HeaderText="SpecialULKey"
SortExpression="SpecialULKey" />
<asp:BoundField DataField="AccountUserKey"
HeaderText="AccountUserKey"
SortExpression="AccountUserKey" />
</Columns>
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource2" runat="server"
DataFile="~/App_Data/PokerSettings.mdb"

SelectCommand="SELECT [UserID], [EmailAddr], [SpecialULKey],
[AccountUserKey] FROM [Users] WHERE ([EmailAddr] = ?)">
<SelectParameters>
<asp:Parameter Name="EmailAddr" Type="String" />
</SelectParameters>
</asp:AccessDataSource>


Miro
 
In the event handler of the event in which you want to cause the
SelectCommand to be executed (page_Load or button click event or whatever)
do the following:

AccessDataSource2.SelectParameter("EmailAddr").defaultvalue= myVar

Note that you can (and should, in my opinion) use the SqlDataSource control
with Access if you want to use DataSource controls at all.

http://www.mikesdotnetting.com/Article.aspx?ArticleID=78

--
Mike Brind
MVP ASP/ASP.NET


Miro said:
Lets say you want to "call" your "select" or whatever from your datasource
directly from vb.
And you do not have your "value" not in a textbox, but in a localized
DIM'd Variable.

That is what I am trying to figure out.

For example... here is another example:
How would I put this select to hit from a button, but pass in the
EmailAddress parameter by code.
That is what I am banging my head on right now.

The accessdatasource seems to be just like a tableadapter setup. But I
just cant figure out how to properly call it with the right parameters.
Im sure I cannot be the first newbie to try something like this.

The reason I am using an ms access database vs an sql one is because I
test my project 1 step farther and test to deploy it online on my dummy
online asp hosting account.
MsAccess db's are free... i need to pay more for sql.
Also, I find I learn a lot more quirks when learning a bit of both at the
same time.

In this below example, I have a simple access database and I want to just
list only certain email addresses when a button is clicked.
It would be nice if the "call" would be simillar to a tableadapter. But
its not.
accessDataSource2.select( _Param1, Param2.... )
So all I can figure out is i can call an accessDataSource2.select( ) but I
get runtime errors.


<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="UserID" DataSourceID="AccessDataSource2">
<Columns>
<asp:BoundField DataField="UserID" HeaderText="UserID"
InsertVisible="False"
ReadOnly="True" SortExpression="UserID" />
<asp:BoundField DataField="EmailAddr" HeaderText="EmailAddr"
SortExpression="EmailAddr" />
<asp:BoundField DataField="SpecialULKey"
HeaderText="SpecialULKey"
SortExpression="SpecialULKey" />
<asp:BoundField DataField="AccountUserKey"
HeaderText="AccountUserKey"
SortExpression="AccountUserKey" />
</Columns>
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource2" runat="server"
DataFile="~/App_Data/PokerSettings.mdb"

SelectCommand="SELECT [UserID], [EmailAddr], [SpecialULKey],
[AccountUserKey] FROM [Users] WHERE ([EmailAddr] = ?)">
<SelectParameters>
<asp:Parameter Name="EmailAddr" Type="String" />
</SelectParameters>
</asp:AccessDataSource>


Miro

Paul Shapiro said:
In a SQLDataSource, for SQL Server, you would use something like this. It
might be similar for AccessDataSource.

<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/PokerSettings.mdb"
SelectCommand="SELECT COUNT(*) AS Expr1 FROM Users where emailaddr =
@emailAddress>
<SelectParameters>
<asp:ControlParameter Name="emailAddress" Type="String" Size="100"
ControlID="Email1" PropertyName="Text" />
</SelectParameters>
</asp:AccessDataSource>

If your parameter value is in a text box, you can use a ControlParameter,
as indicated above.
Click to expand...
Click to expand...
 
I didnt know you can use the sqldatasource on an access datatable.
I thought the accessdatasource was "tweeked" to run better with an access
datafile than the sql one.

Thank you for that info...

and many thanks for the page_load .default value code.
I was going nuts trying to figure out how to assign that value.

Miro

Mike Brind said:
In the event handler of the event in which you want to cause the
SelectCommand to be executed (page_Load or button click event or whatever)
do the following:

AccessDataSource2.SelectParameter("EmailAddr").defaultvalue= myVar

Note that you can (and should, in my opinion) use the SqlDataSource
control with Access if you want to use DataSource controls at all.

http://www.mikesdotnetting.com/Article.aspx?ArticleID=78

--
Mike Brind
MVP ASP/ASP.NET


Miro said:
Lets say you want to "call" your "select" or whatever from your
datasource directly from vb.
And you do not have your "value" not in a textbox, but in a localized
DIM'd Variable.

That is what I am trying to figure out.

For example... here is another example:
How would I put this select to hit from a button, but pass in the
EmailAddress parameter by code.
That is what I am banging my head on right now.

The accessdatasource seems to be just like a tableadapter setup. But I
just cant figure out how to properly call it with the right parameters.
Im sure I cannot be the first newbie to try something like this.

The reason I am using an ms access database vs an sql one is because I
test my project 1 step farther and test to deploy it online on my dummy
online asp hosting account.
MsAccess db's are free... i need to pay more for sql.
Also, I find I learn a lot more quirks when learning a bit of both at the
same time.

In this below example, I have a simple access database and I want to just
list only certain email addresses when a button is clicked.
It would be nice if the "call" would be simillar to a tableadapter. But
its not.
accessDataSource2.select( _Param1, Param2.... )
So all I can figure out is i can call an accessDataSource2.select( ) but
I get runtime errors.


<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"
DataKeyNames="UserID" DataSourceID="AccessDataSource2">
<Columns>
<asp:BoundField DataField="UserID" HeaderText="UserID"
InsertVisible="False"
ReadOnly="True" SortExpression="UserID" />
<asp:BoundField DataField="EmailAddr" HeaderText="EmailAddr"
SortExpression="EmailAddr" />
<asp:BoundField DataField="SpecialULKey"
HeaderText="SpecialULKey"
SortExpression="SpecialULKey" />
<asp:BoundField DataField="AccountUserKey"
HeaderText="AccountUserKey"
SortExpression="AccountUserKey" />
</Columns>
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource2" runat="server"
DataFile="~/App_Data/PokerSettings.mdb"

SelectCommand="SELECT [UserID], [EmailAddr], [SpecialULKey],
[AccountUserKey] FROM [Users] WHERE ([EmailAddr] = ?)">
<SelectParameters>
<asp:Parameter Name="EmailAddr" Type="String" />
</SelectParameters>
</asp:AccessDataSource>


Miro

Paul Shapiro said:
In a SQLDataSource, for SQL Server, you would use something like this.
It might be similar for AccessDataSource.

<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/PokerSettings.mdb"
SelectCommand="SELECT COUNT(*) AS Expr1 FROM Users where emailaddr =
@emailAddress>
<SelectParameters>
<asp:ControlParameter Name="emailAddress" Type="String"
Size="100"
ControlID="Email1" PropertyName="Text" />
</SelectParameters>
</asp:AccessDataSource>

If your parameter value is in a text box, you can use a
ControlParameter, as indicated above.

I think you missed my question... (hard to explain what I am looking
for)

I cannot figure out how to shoot my value of my textbox into the? as a
parameter.

In vb.net ( forms )...it was simple...
I had a Table adapter defined in my dataset so I would do something
like this:

me.MyTableAdapter.AdapterName( DataSetTofill, txtParameter.text )

The table adapter was predifined in the dataset with a query that used
question marks because it was an access table.

What I cannot figureout is how to "Call" the AccessDataSource from the
vb code within asp.net.
The AccessDataSource has the sql query setup with the question marks.
I just cant figure out how to do the same as I would with the
myTableAdapter.

Thanks,

Miro

WHERE emailaddr={0};

or maybe put single quotes around the value in the string

where emailaddr = '?'

Or try something besides a ?...


where emailaddr = ABC
where emailaddr = 'ABC'


...

I don't know , I'm guessing.





I am stuck onto something as to how to set a parameter in asp.net
vb.net I am fine but here is what I did.

I have a table in my msaccess database called "users" and within it a
column called emailaddr

I dropped the AccessDataSource onto the webform and it created this:
<asp:AccessDataSource ID="AccessDataSource1" runat="server"
DataFile="~/App_Data/PokerSettings.mdb"
SelectCommand="SELECT COUNT(*) AS Expr1 FROM Users where
emailaddr = ?">
<SelectParameters>
<asp:Parameter Name="?" />
</SelectParameters>
</asp:AccessDataSource>

Notice the select parameter and the ?' in the selectcommand.

Now on my submit button, i want to populate that ? with a value from
a textbox.
I almost want to do something like this:
Dim userCount As Integer = 0
AccessDataSource1.Parameters.Add("?", Email1.Text)
userCount = AccessDataSource1.Select()

Im going in circles looking for the right syntax in vb.net on how to
do this in asp.net

Thanks,

Miro
Click to expand...
Click to expand...
Click to expand...
 
Back
Top