I've got an issue with a SELECT statement. I'm selecting records based on date - the year and month is specified in the query string. I have this;
(C#)
dbAdapter = new OleDbDataAdapter("Select * from tableName WHERE Month(WrittenDate)=" + monthNumber + " AND Year(WrittenDate)=" + Request.QueryString["year"] + " ORDER BY WrittenDate DESC", dbConn);
// Fill the adapter with the data retrieved
dbAdapter.Fill(dsPageData, "tableName");
The issue is with the year - when I run the above, I get a error at the dbAdapter line "No value given for one or more required parameters". When I write in the year 2007 directly instead of pulling from the query string, it's fine. I've echoed out the value
for Request.QueryString["year"] in the output window and it's getting picked up fine. It just doesn't like getting inserted to the SELECT statement.
Any ideas...!? Thanks.
P.S. On another note, if anyone has any good Injection protection examples for something like the above in c#, that'd be appreciated too - I'm a PHP'er usually.
Response.Write("Select * from tableName WHERE Month(WrittenDate)=" + monthNumber +
" AND Year(WrittenDate)=" + Request.QueryString["year"] +
" ORDER BY WrittenDate DESC");
Response.End();
This will write out the Sql statement in full so you can see if there are missing values. If there aren't, double check the names of your fields.
As far as Sql Injection is concerned, since you appear to be using Access (assumed from the fact that your post appeared in the Access forum), you won't be able to use stored procedures as escrew suggested. Access doesn't support them. You can used saved
parameter queries, or you can use parameter markers.
With the saved parameter query, you would have to open up Access, and in the query designer, switch to SQL view and enter:
Select * from tableName WHERE Month(WrittenDate)=@MonthNumber AND Year(WrittenDate)=@Year ORDER BY WrittenDate DESC
Save the query as GetRecordsByMonthAndYear (or something suitable), then in your code-behind:
int monthnumber = <source for value>;
int year =Convert.ToInt32(Request.QueryString["year"]);
string ConnStr = WebConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
using (OleDbConnection conn = new OleDbConnection(ConnStr))
{
using (OleDbCommand cmd = new OleDbCommand("GetRecordsByMonthAndYear", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("monthnumber", monthnumber);
cmd.Parameters.AddWithValue("year", year);
conn.Open();
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
......process values here
}
}
}
}
Using parameter markers is almost identical, except that you have to pass the sql to the command instead of the name of the query, and use ? as parameter markers:
Select * from tableName WHERE Month(WrittenDate)=? AND Year(WrittenDate)=? ORDER BY WrittenDate DESC
Then you have to specify the CommandType as Text. One thing to be careful of - with OLEDB, you have to make sure you add the parameters to the collection in the same order they appear in the SQL.
Thanks for your replies. I'm using a codebehind so have output the string to the Debugger (guessing this will have the same effect as what you're suggesting?):
Debug.Listeners.Add(new TextWriterTraceListener(Console.Out));
Debug.AutoFlush = true;
Debug.Indent();
Debug.WriteLine("Select * from tableName WHERE Month(WrittenDate)=" + monthNumber + " AND Year(WrittenDate)=" + Request.QueryString["year"] + " ORDER BY tableName.WrittenDate DESC");
In the output window, this gives;
Select * from tableName WHERE Month(WrittenDate)=4 AND Year(WrittenDate)=2007 ORDER BY tableName.WrittenDate DESC
Select * from tableName WHERE Month(WrittenDate)=1 AND Year(WrittenDate)=stylesnewsstyle.css ORDER BY tableName.WrittenDate DESC
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
On observation, I'm getting two outputs - one with 2007 correctly inserted in there, and the second with the name of the stylesheet...!!? Any ideas...??
Thanks for your replies. I'm using a codebehind so have output the string to the Debugger (guessing this will have the same effect as what you're suggesting?):
Well, yes. Response.Write outputs it to the browser. One day I will learn how to use the debugger... [;)]
ska_mna
In the output window, this gives;
Select * from tableName WHERE Month(WrittenDate)=4 AND Year(WrittenDate)=2007 ORDER BY tableName.WrittenDate DESC
Select * from tableName WHERE Month(WrittenDate)=1 AND Year(WrittenDate)=stylesnewsstyle.css ORDER BY tableName.WrittenDate DESC
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
On observation, I'm getting two outputs - one with 2007 correctly inserted in there, and the second with the name of the stylesheet...!!? Any ideas...??
Oh, we both posted at the same time! Hold on this, as it looks like it's something to do with my URL rewrite as this;
news.aspx?filterby=month&year=2007&month=April
works while;
archive/2007/April/
doesn't... I'm looking into this myself now.
LOL. That would explain it, although I don't know enough about URL re-writing to be able to understand the process at work here, but it certainly looks like there are 2 page requests in effect. Possibly you could set a bool that gets set to true after
the first SQL statement is created or the parameter variables are passed a value to prevent the second one from occurring, although there is probably a more elegant solution than that. Let us know what you end up doing.
ska_mna
Member
10 Points
39 Posts
"No value given for one or more required parameters" - SELECT statement from query string
Apr 19, 2007 10:18 PM|LINK
Hi guys,
I've got an issue with a SELECT statement. I'm selecting records based on date - the year and month is specified in the query string. I have this;
(C#)
dbAdapter = new OleDbDataAdapter("Select * from tableName WHERE Month(WrittenDate)=" + monthNumber + " AND Year(WrittenDate)=" + Request.QueryString["year"] + " ORDER BY WrittenDate DESC", dbConn); // Fill the adapter with the data retrieved dbAdapter.Fill(dsPageData, "tableName");The issue is with the year - when I run the above, I get a error at the dbAdapter line "No value given for one or more required parameters". When I write in the year 2007 directly instead of pulling from the query string, it's fine. I've echoed out the value for Request.QueryString["year"] in the output window and it's getting picked up fine. It just doesn't like getting inserted to the SELECT statement.
Any ideas...!? Thanks.
P.S. On another note, if anyone has any good Injection protection examples for something like the above in c#, that'd be appreciated too - I'm a PHP'er usually.
e_screw
All-Star
19530 Points
3894 Posts
Re: "No value given for one or more required parameters" - SELECT statement from query string
Apr 19, 2007 10:55 PM|LINK
Are you sure you are getting a value in Request.Querystring["year"]? Better always check the null values
if ( Request.QueryString["year"] != null)
{
// some statement
}
To avoid injection attacks you always need to use Strored Procedures.
Thanks
Electronic Screw
Website||Blog||Dub@i.net
Mikesdotnett...
All-Star
154927 Points
19867 Posts
Moderator
MVP
Re: "No value given for one or more required parameters" - SELECT statement from query string
Apr 20, 2007 07:30 AM|LINK
Try:
Response.Write("Select * from tableName WHERE Month(WrittenDate)=" + monthNumber + " AND Year(WrittenDate)=" + Request.QueryString["year"] + " ORDER BY WrittenDate DESC");
Response.End();
This will write out the Sql statement in full so you can see if there are missing values. If there aren't, double check the names of your fields.
As far as Sql Injection is concerned, since you appear to be using Access (assumed from the fact that your post appeared in the Access forum), you won't be able to use stored procedures as escrew suggested. Access doesn't support them. You can used saved parameter queries, or you can use parameter markers.
With the saved parameter query, you would have to open up Access, and in the query designer, switch to SQL view and enter:
Select * from tableName WHERE Month(WrittenDate)=@MonthNumber AND Year(WrittenDate)=@Year ORDER BY WrittenDate DESC
Save the query as GetRecordsByMonthAndYear (or something suitable), then in your code-behind:
int monthnumber = <source for value>;
int year =Convert.ToInt32(Request.QueryString["year"]);
string ConnStr = WebConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
using (OleDbConnection conn = new OleDbConnection(ConnStr))
{
using (OleDbCommand cmd = new OleDbCommand("GetRecordsByMonthAndYear", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("monthnumber", monthnumber);
cmd.Parameters.AddWithValue("year", year);
conn.Open();
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
......process values here
}
}
}
}
Using parameter markers is almost identical, except that you have to pass the sql to the command instead of the name of the query, and use ? as parameter markers:
Select * from tableName WHERE Month(WrittenDate)=? AND Year(WrittenDate)=? ORDER BY WrittenDate DESC
Then you have to specify the CommandType as Text. One thing to be careful of - with OLEDB, you have to make sure you add the parameters to the collection in the same order they appear in the SQL.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
ska_mna
Member
10 Points
39 Posts
Re: "No value given for one or more required parameters" - SELECT statement from query string
Apr 20, 2007 09:34 AM|LINK
Thanks for your replies. I'm using a codebehind so have output the string to the Debugger (guessing this will have the same effect as what you're suggesting?):
Debug.Listeners.Add(new TextWriterTraceListener(Console.Out)); Debug.AutoFlush = true; Debug.Indent(); Debug.WriteLine("Select * from tableName WHERE Month(WrittenDate)=" + monthNumber + " AND Year(WrittenDate)=" + Request.QueryString["year"] + " ORDER BY tableName.WrittenDate DESC");In the output window, this gives;
Select * from tableName WHERE Month(WrittenDate)=4 AND Year(WrittenDate)=2007 ORDER BY tableName.WrittenDate DESC
Select * from tableName WHERE Month(WrittenDate)=1 AND Year(WrittenDate)=stylesnewsstyle.css ORDER BY tableName.WrittenDate DESC
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
On observation, I'm getting two outputs - one with 2007 correctly inserted in there, and the second with the name of the stylesheet...!!? Any ideas...??
ska_mna
Member
10 Points
39 Posts
Re: "No value given for one or more required parameters" - SELECT statement from query string
Apr 20, 2007 10:09 AM|LINK
Mikesdotnett...
All-Star
154927 Points
19867 Posts
Moderator
MVP
Re: "No value given for one or more required parameters" - SELECT statement from query string
Apr 20, 2007 10:09 AM|LINK
Well, yes. Response.Write outputs it to the browser. One day I will learn how to use the debugger... [;)]
Not without seeing what else your code does.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
ska_mna
Member
10 Points
39 Posts
Re: "No value given for one or more required parameters" - SELECT statement from query string
Apr 20, 2007 10:13 AM|LINK
Oh, we both posted at the same time! Hold on this, as it looks like it's something to do with my URL rewrite as this;
news.aspx?filterby=month&year=2007&month=April
works while;
archive/2007/April/
doesn't... I'm looking into this myself now.
Mikesdotnett...
All-Star
154927 Points
19867 Posts
Moderator
MVP
Re: "No value given for one or more required parameters" - SELECT statement from query string
Apr 20, 2007 11:30 AM|LINK
LOL. That would explain it, although I don't know enough about URL re-writing to be able to understand the process at work here, but it certainly looks like there are 2 page requests in effect. Possibly you could set a bool that gets set to true after the first SQL statement is created or the parameter variables are passed a value to prevent the second one from occurring, although there is probably a more elegant solution than that. Let us know what you end up doing.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
ska_mna
Member
10 Points
39 Posts
Re: "No value given for one or more required parameters" - SELECT statement from query string
Apr 20, 2007 01:40 PM|LINK
The month was coming out fine, but the year wasn't, looking something like this in my variable;
2007/April/garbagegarbage
So I've cheated as I'm on a tight timescale;
YearS = Request.QueryString[
"year"];YearS = YearS.Substring(0, 4);
I'll try and find out why this was happening at a later date!
e_screw
All-Star
19530 Points
3894 Posts
Re: "No value given for one or more required parameters" - SELECT statement from query string
Apr 20, 2007 03:28 PM|LINK
It could be because of url rewriting. Check how the url is re-rewriting that you are getting a garbage value into the variable.
Thanks
Electronic Screw
Website||Blog||Dub@i.net