jazjef
asked on
Use Textbox1 to search SQL table for presence of a value, then populate Textbox2 with the value if it exists?
I have two textboxes on an asp.net form. The user enters a name into TextBox1 and then clicks a button. An SQL database called 'members' has a table called 'names', and a column called 'name'....the user requested name from the SQL table will then populate textbox 2... if nothing matches, a message box comes up and says 'name not found'.
What is the problem dude
Query would be something like this-
SELECT name
FROM Names
Where exists(select top 1 1 from names where name like '%' +textbox1.text + '%')
You need to handle multiple records logic here ..................
SELECT name
FROM Names
Where exists(select top 1 1 from names where name like '%' +textbox1.text + '%')
You need to handle multiple records logic here ..................
This question is not very clear,
You enter the name and want to populate what data in textBox 2 ???. Well, but you can do it like:
Connection.Open();
string query = "SELECT name From [Names] Where name = ' " + textbox1.Text + " ' ";
OleDbCommand myCommand = new OleDbCommand(query, Connection);
string name = myCommand.ExecuteScalar(). ToString() ; //this return the first column of the fist row
...
//when there is nothing matchedshow message:
if(name == "") // or in case this doesn't work: if( name == null )
Respone.Write("<script> alert("are you kidding me? there is no name like that... check again");</script>"
Make sure you open close database connection without problem.
Hope this help
JINN ^^
You enter the name and want to populate what data in textBox 2 ???. Well, but you can do it like:
Connection.Open();
string query = "SELECT name From [Names] Where name = ' " + textbox1.Text + " ' ";
OleDbCommand myCommand = new OleDbCommand(query, Connection);
string name = myCommand.ExecuteScalar().
...
//when there is nothing matchedshow message:
if(name == "") // or in case this doesn't work: if( name == null )
Respone.Write("<script> alert("are you kidding me? there is no name like that... check again");</script>"
Make sure you open close database connection without problem.
Hope this help
JINN ^^
Awww, I forgot.
I would be in doubt using the LIKE thing in SQL statement of answer_me (it says depend on how you wanna find the name). Because LIKE means you gonna search for any name that partly contains the input value
So if you enter NN then JINN, JINNY, JENNY, LEARNN ... always match. (it means always find some record)
It's good when you do the search function, but I believe not this case.
^^ Greeeting JINN
I would be in doubt using the LIKE thing in SQL statement of answer_me (it says depend on how you wanna find the name). Because LIKE means you gonna search for any name that partly contains the input value
So if you enter NN then JINN, JINNY, JENNY, LEARNN ... always match. (it means always find some record)
It's good when you do the search function, but I believe not this case.
^^ Greeeting JINN
SQL Statement:
Create Procedure GetName
(
@name varchar(50)
)
as
begin
Select [name] from Names where Name=@name
end
.Net Application
'Import system.data.sqlclient
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Page.IsPostBack Then
If getName(Me.TextBox1.Text) = "" Then
Me.TextBox2.Text = ("Name not found")
Else
Me.TextBox2.Text = getName(Me.TextBox1.Text)
End If
End If
End Sub
Private Function getName(ByVal Name As String) As String
Dim conn As SqlConnection = New SqlConnection("Server=serv ername;Uid =myusernam e;Password =mypasswor d;Initial Catalog=Members")
Dim cmd As SqlCommand = New SqlCommand("GetName", conn)
cmd.CommandType = CommandType.StoredProcedur e
Dim prm(1) As SqlParameter
prm(0) = New SqlParameter("@name", SqlDbType.VarChar, 50)
prm(0).Value = Name
cmd.Parameters.Add(prm(0))
Dim strVal As String
conn.Open()
strVal = cmd.ExecuteScalar()
conn.Close()
Return strVal
End Function
HTML
<HTML>
<HEAD>
<title>WebForm1</title>
<meta name="vs_snapToGrid" content="False">
<meta name="vs_showGrid" content="False">
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScri pt" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout ">
<form id="Form1" method="post" runat="server">
<asp:TextBox id="TextBox1" style="Z-INDEX: 101; LEFT: 10px; POSITION: absolute; TOP: 8px" runat="server"></asp:TextB ox>
<asp:TextBox id="TextBox2" style="Z-INDEX: 102; LEFT: 9px; POSITION: absolute; TOP: 36px" runat="server"></asp:TextB ox>
<asp:Button id="Button1" style="Z-INDEX: 103; LEFT: 45px; POSITION: absolute; TOP: 73px" runat="server"
Text="Button"></asp:Button >
</form>
</body>
</HTML>
Create Procedure GetName
(
@name varchar(50)
)
as
begin
Select [name] from Names where Name=@name
end
.Net Application
'Import system.data.sqlclient
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Page.IsPostBack Then
If getName(Me.TextBox1.Text) = "" Then
Me.TextBox2.Text = ("Name not found")
Else
Me.TextBox2.Text = getName(Me.TextBox1.Text)
End If
End If
End Sub
Private Function getName(ByVal Name As String) As String
Dim conn As SqlConnection = New SqlConnection("Server=serv
Dim cmd As SqlCommand = New SqlCommand("GetName", conn)
cmd.CommandType = CommandType.StoredProcedur
Dim prm(1) As SqlParameter
prm(0) = New SqlParameter("@name", SqlDbType.VarChar, 50)
prm(0).Value = Name
cmd.Parameters.Add(prm(0))
Dim strVal As String
conn.Open()
strVal = cmd.ExecuteScalar()
conn.Close()
Return strVal
End Function
HTML
<HTML>
<HEAD>
<title>WebForm1</title>
<meta name="vs_snapToGrid" content="False">
<meta name="vs_showGrid" content="False">
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScri
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout
<form id="Form1" method="post" runat="server">
<asp:TextBox id="TextBox1" style="Z-INDEX: 101; LEFT: 10px; POSITION: absolute; TOP: 8px" runat="server"></asp:TextB
<asp:TextBox id="TextBox2" style="Z-INDEX: 102; LEFT: 9px; POSITION: absolute; TOP: 36px" runat="server"></asp:TextB
<asp:Button id="Button1" style="Z-INDEX: 103; LEFT: 45px; POSITION: absolute; TOP: 73px" runat="server"
Text="Button"></asp:Button
</form>
</body>
</HTML>
--------------SQL Statement:---------------- ---
Create Procedure GetName
(
@name varchar(50)
)
as
begin
Select [name] from Names where Name=@name
end
-----------------Code Behind:------------------- ------
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Page.IsPostBack Then
If getName(Me.TextBox1.Text) = "" Then
Me.TextBox2.Text = ("Name not found")
Else
Me.TextBox2.Text = getName(Me.TextBox1.Text)
End If
End If
End Sub
Private Function getName(ByVal Name As String) As String
Dim conn As SqlConnection = New SqlConnection("Server=serv ername;Uid =myusernam e;Password =mypasswor d;Initial Catalog=Members")
Dim cmd As SqlCommand = New SqlCommand("GetName", conn)
cmd.CommandType = CommandType.StoredProcedur e
Dim prm(1) As SqlParameter
prm(0) = New SqlParameter("@name", SqlDbType.VarChar, 50)
prm(0).Value = Name
cmd.Parameters.Add(prm(0))
Dim strVal As String
conn.Open()
strVal = cmd.ExecuteScalar()
conn.Close()
Return strVal
End Function
-------------------------- -----HTML: ---------- ---------- -------
<HTML>
<HEAD>
<title>WebForm1</title>
<meta name="vs_snapToGrid" content="False">
<meta name="vs_showGrid" content="False">
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScri pt" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout ">
<form id="Form1" method="post" runat="server">
<asp:TextBox id="TextBox1" style="Z-INDEX: 101; LEFT: 10px; POSITION: absolute; TOP: 8px" runat="server"></asp:TextB ox>
<asp:TextBox id="TextBox2" style="Z-INDEX: 102; LEFT: 9px; POSITION: absolute; TOP: 36px" runat="server"></asp:TextB ox>
<asp:Button id="Button1" style="Z-INDEX: 103; LEFT: 45px; POSITION: absolute; TOP: 73px" runat="server"
Text="Button"></asp:Button >
</form>
</body>
</HTML>
Create Procedure GetName
(
@name varchar(50)
)
as
begin
Select [name] from Names where Name=@name
end
-----------------Code Behind:-------------------
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Page.IsPostBack Then
If getName(Me.TextBox1.Text) = "" Then
Me.TextBox2.Text = ("Name not found")
Else
Me.TextBox2.Text = getName(Me.TextBox1.Text)
End If
End If
End Sub
Private Function getName(ByVal Name As String) As String
Dim conn As SqlConnection = New SqlConnection("Server=serv
Dim cmd As SqlCommand = New SqlCommand("GetName", conn)
cmd.CommandType = CommandType.StoredProcedur
Dim prm(1) As SqlParameter
prm(0) = New SqlParameter("@name", SqlDbType.VarChar, 50)
prm(0).Value = Name
cmd.Parameters.Add(prm(0))
Dim strVal As String
conn.Open()
strVal = cmd.ExecuteScalar()
conn.Close()
Return strVal
End Function
--------------------------
<HTML>
<HEAD>
<title>WebForm1</title>
<meta name="vs_snapToGrid" content="False">
<meta name="vs_showGrid" content="False">
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScri
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout
<form id="Form1" method="post" runat="server">
<asp:TextBox id="TextBox1" style="Z-INDEX: 101; LEFT: 10px; POSITION: absolute; TOP: 8px" runat="server"></asp:TextB
<asp:TextBox id="TextBox2" style="Z-INDEX: 102; LEFT: 9px; POSITION: absolute; TOP: 36px" runat="server"></asp:TextB
<asp:Button id="Button1" style="Z-INDEX: 103; LEFT: 45px; POSITION: absolute; TOP: 73px" runat="server"
Text="Button"></asp:Button
</form>
</body>
</HTML>
to poupulate with tablename use this query
select *
from database.information_schem a.tables
and then use select * from textbox1.Text query
select *
from database.information_schem
and then use select * from textbox1.Text query
ASKER
ieciep:
I get the following errors in the code behind:
Type 'SQL Parameter' is not defined
Type 'SQL Command' is not defined
Type 'SQL Connection' is not defined
I get the following errors in the code behind:
Type 'SQL Parameter' is not defined
Type 'SQL Command' is not defined
Type 'SQL Connection' is not defined
OMG...
I guess you have to paste your code here. But I assume you have to know how to make the connection running... (did you just paste the code from here???)
Well in that case: you have to initialize your connection first (depend on if you use MS Access or SQL server ...) for example
SqlConnection Connection = new SqlConnection();
Connection.ConnectionStrin g = "your connection string";
Connection.Open();
string query = "SELECT name From [Names] Where name = ' " + textbox1.Text + " ' ";
SqlCommand myCommand = new SqlCommand(query, Connection);
string name = myCommand.ExecuteScalar(). ToString() ; //this return the first column of the fist row
...
Connection.Close();
//when there is nothing matchedshow message:
if(name == "") // or in case this doesn't work: if( name == null )
Respone.Write("<script> alert("are you kidding me? there is no name like that... check again");</script>"
Please make sure you are able to connect and interact with database before you do the otherthing.
JINN
I guess you have to paste your code here. But I assume you have to know how to make the connection running... (did you just paste the code from here???)
Well in that case: you have to initialize your connection first (depend on if you use MS Access or SQL server ...) for example
SqlConnection Connection = new SqlConnection();
Connection.ConnectionStrin
Connection.Open();
string query = "SELECT name From [Names] Where name = ' " + textbox1.Text + " ' ";
SqlCommand myCommand = new SqlCommand(query, Connection);
string name = myCommand.ExecuteScalar().
...
Connection.Close();
//when there is nothing matchedshow message:
if(name == "") // or in case this doesn't work: if( name == null )
Respone.Write("<script> alert("are you kidding me? there is no name like that... check again");</script>"
Please make sure you are able to connect and interact with database before you do the otherthing.
JINN
this is the aspx file:
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextB ox>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
<asp:TextBox ID="TextBox2" runat="server"></asp:TextB ox>
</div>
</form>
</body>
</html>
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
this is the code behind file
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
using System;
using System.Data;
using System.Configuration;
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 ;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection cnn = new SqlConnection("Data Source=Your_computer_Name; Initial Catalog=members;Integrated Security=True");
SqlDataAdapter da = new SqlDataAdapter("Select name from names where name='" + TextBox1.Text + "'", cnn);
DataSet ds = new DataSet();
cnn.Open();
da.Fill(ds);
cnn.Close();
if (ds.Tables[0].Rows.Count != 0)
{
TextBox2.Text = ds.Tables[0].Rows[0][0].To String();
}
else
{
Response.Write("<script language=javascript>alert( 'name not found');</script>");
}
}
}
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
-
--------------------------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextB
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
<asp:TextBox ID="TextBox2" runat="server"></asp:TextB
</div>
</form>
</body>
</html>
--------------------------
this is the code behind file
--------------------------
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.
using System.Web.UI.HtmlControls
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection cnn = new SqlConnection("Data Source=Your_computer_Name;
SqlDataAdapter da = new SqlDataAdapter("Select name from names where name='" + TextBox1.Text + "'", cnn);
DataSet ds = new DataSet();
cnn.Open();
da.Fill(ds);
cnn.Close();
if (ds.Tables[0].Rows.Count != 0)
{
TextBox2.Text = ds.Tables[0].Rows[0][0].To
}
else
{
Response.Write("<script language=javascript>alert(
}
}
}
--------------------------
-
be carefull to write your computer name in connection string
So what is your problem now ?
ASKER
I get the following errors in the code behind:
Type 'SQL Parameter' is not defined
Type 'SQL Command' is not defined
Type 'SQL Connection' is not defined
Type 'SQL Parameter' is not defined
Type 'SQL Command' is not defined
Type 'SQL Connection' is not defined
ASKER
Clarification of the question:
Here's what I would like to have:
I would like to have two textboxes on an asp.net form. The user enters a name into TextBox1 and then clicks a button. An SQL database called 'members' has a table called 'names', and a column called 'name'....the user requested name from the SQL table will then populate textbox 2... if nothing matches, a message box comes up and says 'name not found'.
How do I do this in ASP.net VB.net?
Right now I have a completely blank project.
Here's what I would like to have:
I would like to have two textboxes on an asp.net form. The user enters a name into TextBox1 and then clicks a button. An SQL database called 'members' has a table called 'names', and a column called 'name'....the user requested name from the SQL table will then populate textbox 2... if nothing matches, a message box comes up and says 'name not found'.
How do I do this in ASP.net VB.net?
Right now I have a completely blank project.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.