Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mattmoss
Starting Member
4 Posts |
 Posted - 2008-06-16 : 11:48:45
|
| I'm populating a gridview with the following code and all works fine until i try to use an input parameter(see bold below)If I remove the bold text to get all news all works well.Any help much appreciated.Cheers. private DataTable getTable() { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["blabla"].ToString()); SqlDataAdapter adap = new SqlDataAdapter("SELECT newsItemID, LEFT(newsItemTitle, 25) + '...' FROM newsItems WHERE active='True' AND newsCat=@newsCat ORDER BY dateTimeStamp DESC", conn); SqlParameter newsCat = new SqlParameter("@newsCat", SqlDbType.VarChar, 50); newsCat.Direction = ParameterDirection.Input; newsCat.Value = "Staff"; DataTable dt = new DataTable(); adap.Fill(dt); return dt; } |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-06-16 : 12:02:20
|
| You need to create a SqlCommand object and add your parameter to the Parameters collection of that object.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
mattmoss
Starting Member
4 Posts |
Posted - 2008-06-17 : 05:24:48
|
| Thanks Jeff,I've made the change you mentioned , see below, but still get the same error: Must declare the scalar variable "@newsCat"I must still be doing something wrong, any more ideas?Cheers.private DataTable getTable() { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["blabla"].ToString()); SqlDataAdapter adap = new SqlDataAdapter("SELECT newsItemID, LEFT(newsItemTitle, 25) + '...' FROM newsItems WHERE active='True' AND newsCat=@newsCat ORDER BY dateTimeStamp DESC", conn); SqlCommand comm = new SqlCommand(); SqlParameter newsCat = comm.Parameters.Add("@newsCat", SqlDbType.VarChar); newsCat.Direction = ParameterDirection.Input; newsCat.Value = "Staff"; DataTable dt = new DataTable(); adap.Fill(dt); return dt; } |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-17 : 05:55:01
|
quote:
Originally posted by mattmoss
Thanks Jeff,I've made the change you mentioned , see below, but still get the same error: Must declare the scalar variable "@newsCat"I must still be doing something wrong, any more ideas?Cheers.private DataTable getTable() { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["blabla"].ToString()); SqlDataAdapter adap = new SqlDataAdapter("SELECT newsItemID, LEFT(newsItemTitle, 25) + '...' FROM newsItems WHERE active='True' AND newsCat=" & @newsCat & " ORDER BY dateTimeStamp DESC", conn); SqlCommand comm = new SqlCommand(); SqlParameter newsCat = comm.Parameters.Add("@newsCat", SqlDbType.VarChar); newsCat.Direction = ParameterDirection.Input; newsCat.Value = "Staff"; DataTable dt = new DataTable(); adap.Fill(dt); return dt; }
may be this |
|
|
|
mattmoss
Starting Member
4 Posts |
Posted - 2008-06-17 : 06:15:09
|
| Thanks, but it won't even build with that, throwing:name 'newsCat' does not exist in the current context |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-06-17 : 08:50:27
|
| don't cut and paste, I am just typing this by hand, but this should give you the idea. If you are not using at least .NET 2.0, let me know, since this code has some methods only available in 2.0. (and, as a rule of thumb, remember to always specify information like that when asking for help)SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["blabla"].ToString());SqlCommand c = new SqlCommand(" -- your select here with @newsCat param -- ", conn);c.Parameters.AddWithValue("@newsCat","Staff"); DataTable dt = new DataTable();dt.Load(c.ExecuteReader());conn.Close();return dt;- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
mattmoss
Starting Member
4 Posts |
Posted - 2008-06-17 : 09:39:15
|
| Thanks Jeff, that sorted the problem. |
|
|
|
|
|
|
|
|
DataTable Parameter problem