Hi
I want to know if I can search my database, with the help of a text box. When the search button is clicked, the results is displayed in a list box so that the user then can select the result that suits him and then add that text to the final text box.
What I would like to know is how can I use a sql query in this type of way?
The column I will basically search for is the JobCode column.
Any help?
Have you ever worked with retrieving data from dataBase, using sqlCOnnection?
What you have to do is:
Ok I have done a simple example of how to use parameters - pass them between methods and populate contols with them.
private void button1_Click(object sender, EventArgs e)
{
string item = this.textBox1.Text;
if (item.Length > 0)
{
this.listBox1.Items.Clear();
DataTable table = GetDataFromDB(item);
foreach (DataRow dr in table.Rows)
this.listBox1.Items.Add(dr[0].ToString());
}
}
private static string dbSqlConn = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString;
//"sqlConnectionString" is a name of your connection string
private void GetDataFromDB(string item)
{
using (DataTable table = new DataTable("ListOfCities"))
{
using (SqlConnection sqlConn = new SqlConnection())
{
string sqlQuery = @"SELECT City FROM Cities WERE Country = @country";
using (SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
da.Fill(table);
}
}
return table;
}
}name of the connection string you find in the file app.Config. If oyu have no clue refer HERE.
Hope this helps a bit... just keep it slow.
Mitja
I work with connection most of the time, but this type I'm battling with :)
Thanks for the sample code, I implemented it to my program, but now I get an error that says: "Cannot implicitly convert type 'void' to 'System.Data.DataTable'".
This is my code:
private void searchJobTitleBtn_Click(object sender, EventArgs e)
{
string item = this.searchJobTitleTxtBox.Text;
if (item.Length > 0)
{
this.jobTitleListBox.Items.Clear();
System.Data.DataTable table = GetDataFromDB(item);
foreach (DataRow dr in table.Rows)
this.jobTitleListBox.Items.Add(dr[0].ToString());
}
}
SqlConnection sqlConn = new SqlConnection("Data Source=DEWALD-PC;Initial Catalog=SurveyDataDatabase;Persist Security Info=True;User ID=sa;Password=123");
//"sqlConnectionString" is a name of your connection string
private void GetDataFromDB(String item)
{
using (DataTable table = new DataTable("Y2010"))
{
using (sqlConn)
{
string sqlQuery = "SELECT * FROM Y2010 WERE JobCode = '" + jobCodeTxtBox.Text + "'";
using (SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
da.Fill(table);
}
}
return table;
}
}If your code is a retun type (on the button of the method "GetDataFromDB" you return table object), the method has to include the same return keyword, which you declare just before the method`s name.
So, you have to change the code like:
private [B]DataTable[/B] GetDataFromDB(String item)
{
//code here
}This should do the trick. And sorry, I did a mistake when giving you the code - the code was written by heart (not on in Visual Studio), so errors can happen. It really wasn`t intentional. Sorry ones more.
Hope it helps,
Mitja
Ahhh will try this first thing in the morning :)
Thank you so much for all the help, really learned some new stuff :) will let you know in the morning if it worked.
No problem, we all make some logic errors time to time.
Thanks again!
No problem mate, anytime, if only I have time to help you guys out.
bye,
Mitja
Thank you for this script, i've tried using it but I'm getting an error saying:
The typre or namespace name 'sqlCommand' could not be found (you are missing directive or ....)
Can I get help with this.
Do you perhaps have this on the top of all code where all the using statements are declared:
using System.Data.SqlClient;when I include this: using System.Data.SqlClient
I get this error: The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid arguments
The error is not coming up because of "using System.Data.SqlClient" namespace. But the error is clearly saying that you have passed some wrong parameters/arguments in SqlCommand() constructor. That's why it's throwing error.
Can you please show your code where you are using SqlCommand() and getting this error ?
Thanks,
Rohan
when I include this: using System.Data.SqlClient
I get this error: The best overloaded method match for 'System.Data.SqlClient.SqlCommand.SqlCommand(string, System.Data.SqlClient.SqlConnection)' has some invalid arguments
The code is the same as the one that was posted:
protected void btnSearch_Click(object sender, EventArgs e)
{
string item = this.txtSearchSite.Text;
if (item.Length > 0)
{
this.ListBox1.Items.Clear();
System.Data.DataTable table = GetDataFromDB(item);
foreach (DataRow dr in table.Rows)
this.ListBox1.Items.Add(dr[0].ToString());
}
}
private static string dbSqlConn = ConfigurationManager.ConnectionStrings["CommondataConnectionString"].ConnectionString;
//"sqlConnectionString" is a name of your connection string
private DataTable GetDataFromDB(string item)
{
using (DataTable table = new DataTable("SITE"))
{
using (dbSqlConn)
{
string sqlQuery = "SELECT SITE_NAME FROM SITE WHERE SITE_NAME = @site";
using (SqlCommand cmd = new SqlCommand(sqlQuery, dbSqlConn))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
da.Fill(table);
}
}
return table;
}
}
}
hey it should be thorws the error because there is mistake in your code..What you are doing is, in your GetDataFromDB( ) method, you are passing connection string itself in the SqlCommand( ) constructor instead of object of SqlConnection. The first argument is fine sqlQuery but the dbSqlConn is string not the SqlConnection object. And that's why it's throwing.
Your code looks like below..See my changes in Red highlighted portion :
using (DataTable table = new DataTable("SITE"))
{
using (SqlConnection objCon = new SqlConnection(dbSqlConn)) {
string sqlQuery = "SELECT SITE_NAME FROM SITE WHERE SITE_NAME = @site";
using (SqlCommand cmd = new SqlCommand(sqlQuery, objCon))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
da.Fill(table);
}
}
return table;The code is the same as the one that was posted:
protected void btnSearch_Click(object sender, EventArgs e)
{
string item = this.txtSearchSite.Text;
if (item.Length > 0)
{
this.ListBox1.Items.Clear();
System.Data.DataTable table = GetDataFromDB(item);
foreach (DataRow dr in table.Rows)
this.ListBox1.Items.Add(dr[0].ToString());
}
}
private static string dbSqlConn = ConfigurationManager.ConnectionStrings["CommondataConnectionString"].ConnectionString;//"sqlConnectionString" is a name of your connection string
private DataTable GetDataFromDB(string item)
{
using (DataTable table = new DataTable("SITE"))
{
using (dbSqlConn)
{
string sqlQuery = "SELECT SITE_NAME FROM SITE WHERE SITE_NAME = @site";using (SqlCommand cmd = new SqlCommand(sqlQuery, dbSqlConn))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
da.Fill(table);
}
}
return table;
}
}
}
I'm getting another error that says:
da.Fill(table);
InvalidOperationException was unhandler by user
My code is like this now:
protected void btnSearch_Click(object sender, EventArgs e)
{
string item = this.txtSearchSite.Text;
if (item.Length > 0)
{
this.ListBox1.Items.Clear();
System.Data.DataTable table = GetDataFromDB(item);
foreach (DataRow dr in table.Rows)
this.ListBox1.Items.Add(dr[0].ToString());
}
}
string SqlConn = ConfigurationManager.ConnectionStrings["CommondataConnectionString"].ConnectionString;
//"sqlConnectionString" is a name of your connection string
private DataTable GetDataFromDB(string item)
{
using (DataTable table = new DataTable("SITE"))
{
using (SqlConnection sqlConn = new SqlConnection())
{
string sqlQuery = "SELECT SITE_NAME FROM SITE WHERE SITE_NAME = '" + txtSearchSite.Text + "'";
using (SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
da.Fill(table);
}
}
return table;
}
}
}But Im getting an error that says:
da.Fill(table);
InvalidOperationException was unhandler by user
Why you are not passing connection string (SqlConn) in SqlConnection( ) constructor in using statement.
modify your code
using (SqlConnection sqlConn = new SqlConnection(SqlConn))
{
//your rest of code as it is..
}
My code is like this now:
protected void btnSearch_Click(object sender, EventArgs e) { string item = this.txtSearchSite.Text; if (item.Length > 0) { this.ListBox1.Items.Clear(); System.Data.DataTable table = GetDataFromDB(item); foreach (DataRow dr in table.Rows) this.ListBox1.Items.Add(dr[0].ToString()); } } string SqlConn = ConfigurationManager.ConnectionStrings["CommondataConnectionString"].ConnectionString; //"sqlConnectionString" is a name of your connection string private DataTable GetDataFromDB(string item) { using (DataTable table = new DataTable("SITE")) { using (SqlConnection sqlConn = new SqlConnection()) { string sqlQuery = "SELECT SITE_NAME FROM SITE WHERE SITE_NAME = '" + txtSearchSite.Text + "'"; using (SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn)) { using (SqlDataAdapter da = new SqlDataAdapter(cmd)) da.Fill(table); } } return table; } } }But Im getting an error that says:
da.Fill(table);
InvalidOperationException was unhandler by user
This code is not searching from the database, is just take what I've typed in the textbox and displaying in my list box.
How come?
Sorry I'm new in C#
Hi Rohan, thank you for helping so far, I'm new in C# so I'm going to be send e-mails after emails with error.
Please can you help me with this error:
The best overloaded method match for 'System.Data.SqlClient.SqlConnection.SqlConnection(string)' has some invalid arguments
My code is as follows:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class SessionSetup : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSearch_Click(object sender, EventArgs e)
{
string item = this.txtSearchName.Text;
if (item.Length > 0)
{
this.ListBox1.Items.Clear();
System.Data.DataTable table = GetDataFromDB(item);
foreach (DataRow dr in table.Rows)
this.ListBox1.Items.Add(dr[0].ToString());
}
}
SqlConnection sqlConn =
new SqlConnection("Data Source=(local);Database='Commondata';" +
"Integrated Security=true");
//"sqlConnectionString" is a name of your connection string
private DataTable GetDataFromDB(string item)
{
using (DataTable table = new DataTable("dbo.SITE"))
try
{
using (SqlConnection sqlConn = new SqlConnection(sqlConn))
{
string strSelect =
string.Concat("SELECT * FROM dbo.SITE WHERE SITE_NAME = '",
this.txtSearchName.Text, "';");
SqlCommand cmdDatabase = new SqlCommand(strSelect, sqlConn);
{
sqlConn.Open();
using (SqlDataAdapter da = new SqlDataAdapter(cmdDatabase))
da.Fill(table);
sqlConn.Close();
}
}
}
catch
{
}
return table;
}
}Why you have defined two sqlConn variable in your code. One outside the method and one inside the method. Define only once. Also why you have used Single Quote and Plus sign in your connection string. You can't use this ..
Modify your connectionstring and use only one sqlConn variable..
SqlConnection sqlConn = new SqlConnection("Data Source=(local);Database=Commondata;Integrated Security=true");
Also the error is coming up because of below line :
using (SqlConnection sqlConn = new SqlConnection(sqlConn))
What you are doing here , you are passing the SqlConnection object itself in SqlConnection( ) constructor. There is not constructor/method accept object of SqlConnection as argument.. So you need to pass your connection string itself in Constructor instead of sqlConn object..
Hi Rohan, thank you for helping so far, I'm new in C# so I'm going to be send e-mails after emails with error.
Please can you help me with this error:
The best overloaded method match for 'System.Data.SqlClient.SqlConnection.SqlConnection(string)' has some invalid arguments
My code is as follows:
using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class SessionSetup : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnSearch_Click(object sender, EventArgs e) { string item = this.txtSearchName.Text; if (item.Length > 0) { this.ListBox1.Items.Clear(); System.Data.DataTable table = GetDataFromDB(item); foreach (DataRow dr in table.Rows) this.ListBox1.Items.Add(dr[0].ToString()); } } SqlConnection sqlConn = new SqlConnection("Data Source=(local);Database='Commondata';" + "Integrated Security=true"); //"sqlConnectionString" is a name of your connection string private DataTable GetDataFromDB(string item) { using (DataTable table = new DataTable("dbo.SITE")) try { using (SqlConnection sqlConn = new SqlConnection(sqlConn)) { string strSelect = string.Concat("SELECT * FROM dbo.SITE WHERE SITE_NAME = '", this.txtSearchName.Text, "';"); SqlCommand cmdDatabase = new SqlCommand(strSelect, sqlConn); { sqlConn.Open(); using (SqlDataAdapter da = new SqlDataAdapter(cmdDatabase)) da.Fill(table); sqlConn.Close(); } } } catch { } return table; } }
Hi Rohan,
Thank you for your help so far, can you also help me with this error please:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class SessionSetup : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
pnlSite.Visible = false;
}
protected void DropDownList3_SelectedIndexChanged(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
}
protected void btnSearch_Click(object sender, EventArgs e)
{
string item = this.txtSearchName.Text;
if (item.Length > 0)
{
this.ListBox1.Items.Clear();
System.Data.DataTable table = GetDataFromDB(item);
foreach (DataRow dr in table.Rows)
this.ListBox1.Items.Add(dr[0].ToString());
}
}
SqlConnection sqlConn = new SqlConnection("Data Source=(local);Database=Commondata;Integrated Security=true");
//"sqlConnectionString" is a name of your connection string
private DataTable GetDataFromDB(string item)
{
using (DataTable table = new DataTable("dbo.SITE"))
{
using (sqlConn)
{
string strSelect =
string.Concat("SELECT * FROM dbo.SITE WHERE SITE_NAME = '",
this.txtSearchName.Text, "';");
SqlCommand cmdDatabase = new SqlCommand(strSelect, sqlConn);
{
using (SqlDataAdapter da = new SqlDataAdapter(cmdDatabase))
da.Fill(table);
}
}
return table;
pnlSite.Visible = true;
}
}
}A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
How to search database with the help of text box using storedprocedure and display a radio list result
could someone help me with the same issue?, but in Visual Basic and not with C#
Hello Jordan, Daniweb has a section specifically for VB.NET. You can find it by hovering over the "Software Development" button and then selecting VB.NET.
Also, if you cannot find a thread that is currently under discussion that pertains to or solves your issue, please start a new thread :)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.