Solved

Use Textbox1 to search SQL table for presence of a value, then populate Textbox2 with the value if it exists?

Posted on 2007-04-02
15
778 Views
Last Modified: 2012-08-13
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'.
0
Comment
Question by:jazjef
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
15 Comments
 
LVL 10

Expert Comment

by:answer_me
ID: 18841387
What is the problem dude
0
 
LVL 10

Expert Comment

by:answer_me
ID: 18841395
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 ..................
0
 
LVL 10

Expert Comment

by:jinn_hnnl
ID: 18841712
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 ^^
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 10

Expert Comment

by:jinn_hnnl
ID: 18841724
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
0
 
LVL 3

Expert Comment

by:vncmrc12
ID: 18842017
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=servername;Uid=myusername;Password=mypassword;Initial Catalog=Members")
        Dim cmd As SqlCommand = New SqlCommand("GetName", conn)
        cmd.CommandType = CommandType.StoredProcedure
        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_defaultClientScript" 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:TextBox>
                  <asp:TextBox id="TextBox2" style="Z-INDEX: 102; LEFT: 9px; POSITION: absolute; TOP: 36px" runat="server"></asp:TextBox>
                  <asp:Button id="Button1" style="Z-INDEX: 103; LEFT: 45px; POSITION: absolute; TOP: 73px" runat="server"
                        Text="Button"></asp:Button>
            </form>
      </body>
</HTML>
0
 
LVL 3

Expert Comment

by:vncmrc12
ID: 18842025
--------------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=servername;Uid=myusername;Password=mypassword;Initial Catalog=Members")
        Dim cmd As SqlCommand = New SqlCommand("GetName", conn)
        cmd.CommandType = CommandType.StoredProcedure
        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_defaultClientScript" 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:TextBox>
                  <asp:TextBox id="TextBox2" style="Z-INDEX: 102; LEFT: 9px; POSITION: absolute; TOP: 36px" runat="server"></asp:TextBox>
                  <asp:Button id="Button1" style="Z-INDEX: 103; LEFT: 45px; POSITION: absolute; TOP: 73px" runat="server"
                        Text="Button"></asp:Button>
            </form>
      </body>
</HTML>


0
 
LVL 3

Expert Comment

by:ieciep
ID: 18842343
to poupulate with tablename use this query
select *
from database.information_schema.tables

and then use select * from textbox1.Text query
0
 
LVL 4

Author Comment

by:jazjef
ID: 18842633
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

0
 
LVL 10

Expert Comment

by:jinn_hnnl
ID: 18842857
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.ConnectionString = "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

0
 
LVL 3

Expert Comment

by:ieciep
ID: 18843122
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:TextBox>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
   
    </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].ToString();
        }
        else
        {
            Response.Write("<script language=javascript>alert('name not found');</script>");
        }
    }
}
-----------------------------------------------------------------------------------------------------------------------------
-
0
 
LVL 3

Expert Comment

by:ieciep
ID: 18843129
be carefull to write your computer name in connection string
0
 
LVL 10

Expert Comment

by:jinn_hnnl
ID: 18843190
So what is your problem now ?
0
 
LVL 4

Author Comment

by:jazjef
ID: 18844331
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
0
 
LVL 4

Author Comment

by:jazjef
ID: 18844359
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.

0
 
LVL 3

Accepted Solution

by:
vncmrc12 earned 500 total points
ID: 18847716
Imports System.Data.SqlClient
Public Class WebForm1
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
    Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    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
                Response.Write("<script language=javascript>alert('name not found');</script>")
            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=yourserver;Uid=myusername;Password=mypassword;Initial Catalog=Members")
        Dim cmd As SqlCommand = New SqlCommand("GetName", conn)
        cmd.CommandType = CommandType.StoredProcedure
        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
End Class







<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="Testing.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<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_defaultClientScript" 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:TextBox>
                  <asp:TextBox id="TextBox2" style="Z-INDEX: 102; LEFT: 9px; POSITION: absolute; TOP: 36px" runat="server"></asp:TextBox>
                  <asp:Button id="Button1" style="Z-INDEX: 103; LEFT: 45px; POSITION: absolute; TOP: 73px" runat="server"
                        Text="Button"></asp:Button>
            </form>
      </body>
</HTML>












**********Copy and paste this to your sql query analyzer*****************

CREATE Procedure GetName
(
      @name varchar(50)
)
as
begin
Select [name] from Names where Name=@name
end
GO



0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Correct and Effective way to generate pdf files online via JavaScript or ASP.Net? 6 94
Aspx calendar pop up control 3 27
SQL- GROUP BY 4 23
Multi-Column Repeater 3 24
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question