Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Web application design query Expand / Collapse
Author
Message
Posted Thursday, January 31, 2013 2:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:16 AM
Points: 558, Visits: 1,737
pwalter83 (1/30/2013)
Sean Lange (1/29/2013)
pwalter83 (1/29/2013)


Thanks Sean,

I have come to a point where I need to add the sql code within this:

---------------------------------------
Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click



End Sub
---------------------------------------

The SQL code would be as you suggested- "select Columns from MyTable where Col1 = Combobox 1 and Col2 = Combobox 2" but I dont know what vb.net syntax I need to enter this in.

Any suggestions would be welcome. Thanks,
Paul


I would make two suggestions. First give your button a name that means something. Button1 is like calling a column Column1 in sql, maybe something like btnSearch. Not naming your controls is a habit that is really tough to break unless you start early.

Secondly, don't run that sql code. Instead execute a stored procedure and pass the combobox values as parameters. Look up the syntax with google/bing/whatever. There are a number of ways to query data from the database with .NET.


Slowly but not steadily, I have reached here now:

---------------------------------------------------------------

using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Globalization;
using System.Text.RegularExpressions;
using System.Security.Principal; // here is the security namespace you need
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();

SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE");
connection.Open();
SqlCommand sqlCmd = new SqlCommand("SELECT Office_cd,Trade_cd, system_name, interface_direction, last_update_dt FROM header WHERE office_cd = @Value1 AND trade_cd =@Value2", connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

sqlCmd.Parameters.AddWithValue("@Value1", Office_cd.SelectedItem.Text);
sqlCmd.Parameters.AddWithValue("@Value2", trade_cd.SelectedItem.Text);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
TextBox1.Text = dt.Rows[0]["Office_cd"].ToString();
//Where ColumnName is the Field from the DB that you want to display
TextBox2.Text = dt.Rows[0]["Trade_cd"].ToString();
}
connection.Close();

}

------------------------------------------------------------

However, I am now getting this error on Datatable- Error 1 The type or namespace name 'DataTable' could not be found (are you missing a using directive or an assembly reference?) . Inspite of including 'using System.Data;' reference, I get this error, would you know how it can be corrected ?

Thanks



I am able to display the values in the gridview now but it shows up all the values that are in the table. My requirement is to only show data based upon the values selected by the user from the 2 drop down lists and then pressing the 'Find' button.

The code in the click event of the 'Find' button seems to be okay to display the filtered values but it still doesnt work. Please find the code attached.
using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Globalization;
using System.Text.RegularExpressions;
using System.Security.Principal; // here is the security namespace you need
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace GISA
{
public partial class _Default : System.Web.UI.Page
{

private string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["GISAConnectionString"].ConnectionString;
}
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();

SqlConnection connection = new SqlConnection(GetConnectionString());
connection.Open();
SqlCommand sqlCmd = new SqlCommand("SELECT Office_cd,Trade_cd, system_name, interface_direction, last_update_dt FROM header WHERE office_cd = @Value1 AND trade_cd =@Value2", connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

sqlCmd.Parameters.AddWithValue("@Value1", Office_cd.SelectedItem.Text);
sqlCmd.Parameters.AddWithValue("@Value2", trade_cd.SelectedItem.Text);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();

}
connection.Close();

}


protected void Button2_Click(object sender, EventArgs e)
{

}

protected void Button3_Click(object sender, EventArgs e)
{

}

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{

}

protected void Office_cd_SelectedIndexChanged(object sender, EventArgs e)
{

}

protected void Office_cd0_SelectedIndexChanged(object sender, EventArgs e)
{

}

protected void Find_Command(object sender, CommandEventArgs e)
{

}




}
}


Thanks.

Post #1413939
Posted Thursday, January 31, 2013 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168

The code in the click event of the 'Find' button seems to be okay to display the filtered values but it still doesnt work. Please find the code attached.


What do you mean it doesn't work? Does it display the wrong data? Too much? Not enough? I don't see anything horribly wrong with your code. You should look at disposing your objects though so you don't have lots of work for GC to handle.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1414155
Posted Thursday, January 31, 2013 8:13 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:16 AM
Points: 558, Visits: 1,737
Sean Lange (1/31/2013)

The code in the click event of the 'Find' button seems to be okay to display the filtered values but it still doesnt work. Please find the code attached.


What do you mean it doesn't work? Does it display the wrong data? Too much? Not enough? I don't see anything horribly wrong with your code. You should look at disposing your objects though so you don't have lots of work for GC to handle.


Thanks Sean. Sorry for the incomplete info. Actually my requirement is to stick the results to a gridview based on the values the users select from the 2 dropdown boxes and after they press the 'Find' button.

However, when I view the result in a browser, it displays all the data from the table in the gridview and doesnt filter out based on the values from the 2 dropdown boxes. I think somewhere there is a link missing between the click event code of the button and the gridview. Because when I run the query in SQL, it works correctly and displays filtered data.
Post #1414163
Posted Thursday, January 31, 2013 8:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:05 AM
Points: 290, Visits: 1,671
pwalter83 (1/31/2013)


However, when I view the result in a browser, it displays all the data from the table in the gridview and doesnt filter out based on the values from the 2 dropdown boxes. I think somewhere there is a link missing between the click event code of the button and the gridview. Because when I run the query in SQL, it works correctly and displays filtered data.


Is it possible that your current version isn't building properly, and you are running an older version of the code? If a solution doesn't build, VS will ask you if you want to run the last buildable version; you can answer Yes and opt to not be shown that warning again. This might explain why it does not appear to be processing your WHERE clause correctly.

To be honest, the code behind your click event looks right to me. You could replace the references to @Value1/2 with hard-coded values to see if that returns the desired results; if it does, then something's wrong with the way you're populating the parameter values (though it seems to me that would result in 0 rows returned, not All).
Post #1414181
Posted Thursday, January 31, 2013 9:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
Have you run this in the debugger? You can check the visible rowcount of the grid at the beginning of your method and then again at the end. Also setup a trace so you can see the actual query being sent.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1414195
Posted Thursday, January 31, 2013 9:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:16 AM
Points: 558, Visits: 1,737
Sean Lange (1/31/2013)
Have you run this in the debugger? You can check the visible rowcount of the grid at the beginning of your method and then again at the end. Also setup a trace so you can see the actual query being sent.


Actually when I try to select 2 values from the dropdown list which has matching rows in the table, it gives an error:



Both DataSource and DataSourceID are defined on 'GridView1'. Remove one definition.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Both DataSource and DataSourceID are defined on 'GridView1'. Remove one definition.

Source Error:



Line 43: {
Line 44: GridView1.DataSource = dt;
Line 45: GridView1.DataBind(); Line 46:
Line 47: }








Post #1414232
Posted Thursday, January 31, 2013 9:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:05 AM
Points: 290, Visits: 1,671
pwalter83 (1/31/2013)

Both DataSource and DataSourceID are defined on 'GridView1'. Remove one definition.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.



Sounds like you've defined a DataSource for the GridView in the aspx source code, apart from the binding that you've coded for the button's click event. I would leave the GV unbound in the aspx code and do late binding only when the button is clicked.
Post #1414237
Posted Thursday, January 31, 2013 9:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
pwalter83 (1/31/2013)
Sean Lange (1/31/2013)
Have you run this in the debugger? You can check the visible rowcount of the grid at the beginning of your method and then again at the end. Also setup a trace so you can see the actual query being sent.


Actually when I try to select 2 values from the dropdown list which has matching rows in the table, it gives an error:



Both DataSource and DataSourceID are defined on 'GridView1'. Remove one definition.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Both DataSource and DataSourceID are defined on 'GridView1'. Remove one definition.

Source Error:



Line 43: {
Line 44: GridView1.DataSource = dt;
Line 45: GridView1.DataBind(); Line 46:
Line 47: }


The error message is pretty self explanatory. You should read up on DataSource and DataSourceID. You can't use both. DataSourceID is the objectID to use as a datasource. You don't have one of those. Remove that definition and you should be ok.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1414238
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse