OleDBCommand and Repeater

Hi,

I'm trying to fill my Repeater control with the contents of a query
performed on an OleDB connected database. I keep getting the error...

"No value given for one or more required parameters." - but I can't work out
what parameters where. Can someone help?

...



<%# DataBinder.Eval(Container.DataItem, "site_url") %>



c#...

OleDbConnection dbConn = new
OleDbConnection(ConfigurationSettings.AppSettings.Get("Syste m_ConnectionString"));
dbConn.Open();
OleDbCommand siteCmd = new OleDbCommand("SELECT site_name, site_url FROM
sites WHERE band_id = " + bandid, dbConn);
OleDbDataReader siteDR = siteCmd.ExecuteReader();
rptLinks.DataSource = siteDR;
rptLinks.DataBind();
dbConn.Close();

Thanks for any help!

Re: OleDBCommand and Repeater

"musosdev" wrote in message
news:BD57ED95-2CDA-4516-8365-A70D25D83923@microsoft.com...

> OleDbCommand siteCmd = new OleDbCommand("SELECT site_name, site_url FROM
> sites WHERE band_id = " + bandid, dbConn);

Put a breakpoint on the above line and inspect the value of bandid


--
Mark Rae
ASP.NET MVP
http://www.markrae.net

RE: OleDBCommand and Repeater

You need to invest in taking your programming expertise to the next level by
starting to use parameterized queries instead of concatenating textual Sql
statements.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
MetaFinder: http://www.blogmetafinder.com


"musosdev" wrote:

> Hi,
>
> I'm trying to fill my Repeater control with the contents of a query
> performed on an OleDB connected database. I keep getting the error...
>
> "No value given for one or more required parameters." - but I can't work out
> what parameters where. Can someone help?
>
> ...
>
>
>
> <%# DataBinder.Eval(Container.DataItem, "site_url") %>
>
>
>
> c#...
>
> OleDbConnection dbConn = new
> OleDbConnection(ConfigurationSettings.AppSettings.Get("Syste m_ConnectionString"));
> dbConn.Open();
> OleDbCommand siteCmd = new OleDbCommand("SELECT site_name, site_url FROM
> sites WHERE band_id = " + bandid, dbConn);
> OleDbDataReader siteDR = siteCmd.ExecuteReader();
> rptLinks.DataSource = siteDR;
> rptLinks.DataBind();
> dbConn.Close();
>
> Thanks for any help!

Re: OleDBCommand and Repeater

Thanks Mark


"Mark Rae [MVP]" wrote:

> "musosdev" wrote in message
> news:BD57ED95-2CDA-4516-8365-A70D25D83923@microsoft.com...
>
> > OleDbCommand siteCmd = new OleDbCommand("SELECT site_name, site_url FROM
> > sites WHERE band_id = " + bandid, dbConn);
>
> Put a breakpoint on the above line and inspect the value of bandid
>
>
> --
> Mark Rae
> ASP.NET MVP
> http://www.markrae.net
>
>

RE: OleDBCommand and Repeater

Peter,

Fair enough. What are the advantages to doing it this way, as opposed to the
way I'm doing it currently? Could you point me at a descent tutorial?

Also, would I not be better switching to linq?!

Thanks,


Dan

"Peter Bromberg [C# MVP]" wrote:

> You need to invest in taking your programming expertise to the next level by
> starting to use parameterized queries instead of concatenating textual Sql
> statements.
> -- Peter
> Site: http://www.eggheadcafe.com
> UnBlog: http://petesbloggerama.blogspot.com
> MetaFinder: http://www.blogmetafinder.com
>
>
> "musosdev" wrote:
>
> > Hi,
> >
> > I'm trying to fill my Repeater control with the contents of a query
> > performed on an OleDB connected database. I keep getting the error...
> >
> > "No value given for one or more required parameters." - but I can't work out
> > what parameters where. Can someone help?
> >
> > ...
> >
> >
> >
> > <%# DataBinder.Eval(Container.DataItem, "site_url") %>
> >
> >
> >
> > c#...
> >
> > OleDbConnection dbConn = new
> > OleDbConnection(ConfigurationSettings.AppSettings.Get("Syste m_ConnectionString"));
> > dbConn.Open();
> > OleDbCommand siteCmd = new OleDbCommand("SELECT site_name, site_url FROM
> > sites WHERE band_id = " + bandid, dbConn);
> > OleDbDataReader siteDR = siteCmd.ExecuteReader();
> > rptLinks.DataSource = siteDR;
> > rptLinks.DataBind();
> > dbConn.Close();
> >
> > Thanks for any help!

Re: OleDBCommand and Repeater

"musosdev" wrote in message
news:945F3B8A-B29A-45B9-970B-4C3E11D78A8C@microsoft.com...

> What are the advantages to doing it this way, as opposed to the way
> I'm doing it currently?

Do a Google search for "SQL injection"

> Also, would I not be better switching to linq?!

Not for simply querying a database, IMO...


--
Mark Rae
ASP.NET MVP
http://www.markrae.net

RE: OleDBCommand and Repeater

Hello,

I reproduced the issue "No value given for one or more required parameters"
by passing "?" to the bandid parameter in the given sample code, where the
OleDbProvider thinks there is a parameter in your SQL command "SELECT
site_name, site_url FROM sites WHERE band_id=?". Do any of your inputs for
bandid contain a question mark, which stands for a parameter in OLeDb?

The form of code
SELECT site_name, site_url FROM sites WHERE band_id = " + bandid
tends to be called "SQL Injection" attack. To conquer the attack, we need
to:
(1) Constrain input.
(2) Use parameters with stored procedures
(3) Use parameters with dynamic SQL

Please refer to the MSDN article
[How to: Protect From SQL Injection in ASP.NET]
http://msdn2.microsoft.com/en-us/library/ms998271.aspx
and
[SQL Injection]
http://msdn2.microsoft.com/en-us/library/ms161953.aspx
for more details.

For instance, in order to improve the code you posted, we can:
(1) Check if the parameter 'bandid' is an integer value
if (int.TryParse(bandid, out id))
{ // it is a integer (valid parameter), parse it to the sql command.}
else
{ // it is not a valid parameter, throw errors to inform the user. }
(2) Use parameterized sql command
OleDbCommand siteCmd = new OleDbCommand("SELECT site_name, site_url
FROM sites WHERE band_id = @band_id", dbConn);
siteCmd.Parameters.Add("@band_id", OleDbType.Integer).Value = bandid;

Hope it helps. If you have any other concerns or need anything else, please
feel free to let me know.

Regards,
Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/de fault.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx .
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

RE: OleDBCommand and Repeater

Hello,

Would you mind letting me know the result of the suggestions? If you need
further assistance, feel free to let me know. I will be more than happy to
be of assistance.

Have a great day!

Regards,
Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.