How to print aditional columns in search output ?

method · May 1st, 2005, 03:00 PM

How to print payment,penalty_date,amount columns in the seach output in addition to
   playerno column? The code blow only outputs the playerno. I be happy if some one show me where should i mention the name of other columns.Thanks

<%@ Page Language="VB" Debug="True" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">

    Sub btnSearch_OnClick(sender as Object, e as EventArgs)
        Dim objConnection As SqlConnection
        Dim objCommand As SqlCommand
        Dim objAdapter As SqlDataAdapter
        Dim objDataSet As DataSet
        Dim strSearch As String
        Dim strSQLQuery As String

        ' Get Search
        strSearch = txtSearch.Text

        ' If there's nothing to search for then don't search
        ' o/w build our SQL Query and execute it.
        If Len(Trim(strSearch)) > 0 Then
            ' Set up our connection.
            objConnection = New SqlConnection("Data Source=(local);" _
                & "Initial Catalog=teniss2;User Id=web;Password=web;" _
                & "Connect Timeout=15;Network Library=dbmssocn;")

            ' Set up our SQL query text.
            strSQLQuery = "SELECT playerno" _
                & "FROM players " _
                & "WHERE playerno LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
                & "ORDER BY playerno;"

            ' Create new command object passing it our SQL query
            ' and telling it which connection to use.
            objCommand = New SqlCommand(strSQLQuery, objConnection)

            ' Get a DataSet to bind the DataGrid to
            objAdapter = New SqlDataAdapter(objCommand)
            objDataSet = New DataSet()
            objAdapter.Fill(objDataSet)

            ' DataBind DG to DS
            dgPaging.DataSource = objDataSet
            dgPaging.DataBind()

            objConnection.Close()
        Else
            txtSearch.Text = "Enter Search Here"
        End If
    End Sub

</script>
<html>
<head>
  <title>ASP.NET Database Search</title>
</head>
<body>

<form runat="server">

<p>Search our sample db by first or last name. (% returns all)</p>

<asp:TextBox id="txtSearch" runat="server" />

<asp:Button id="btnSearch" runat="server"
    Text = "Search"
    OnClick = "btnSearch_OnClick"
/>

<p>[Try 'am' or 'er' for an example]</p>

<asp:DataGrid id="dgPaging" runat="server"
    HeaderStyle-Font-Bold="True"
/>

</form>

<p>
Click <a href=".asp">here</a>
to read about and download the source code.
</p>

</body>

raj_makro · May 2nd, 2005, 07:09 AM

Use the following string for strSQLQuery
strSQLQuery = "SELECT payment, penalty_date, amount, playerno" _
                & "FROM players " _
                & "WHERE playerno LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
                & "ORDER BY playerno;"

Regards
Raj

method · May 2nd, 2005, 07:36 AM

Quote:

quote:Originally posted by raj_makro
Use the following string for strSQLQuery
strSQLQuery = "SELECT payment, penalty_date, amount, playerno" _
                & "FROM players " _
                & "WHERE playerno LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
                & "ORDER BY playerno;"

Regards
Raj

thank u for u reply. I made the changes :

strSQLQuery = "SELECT PAYMENTNO,PLAYERNO,PEN_DATE,AMOUNT " _
& "FROM penalities " _
& "WHERE playerno LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY playerno;"

and when i do a search for playerno 27 which exists in the database i get this error:

Server Error in '/asp' Application.
--------------------------------------------------------------------------------

Invalid column name 'PAYMENTNO'.
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.Data.SqlClient.SqlException: Invalid column name 'PAYMENTNO'.

Source Error:

Line 38: objAdapter = New SqlDataAdapter(objCommand)
Line 39: objDataSet = New DataSet()
Line 40: objAdapter.Fill(objDataSet)
Line 41:
Line 42: ' DataBind DG to DS

Source File: C:\aspscripts\aspx\tennisdbedit\db_search.aspx Line: 40

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

This is line 40 : objAdapter.Fill(objDataSet)

i be happy if u help me correct this erro and being to able to search for records. This is the way all the feilds are placed in sql server 2000:
penalties table:

PAYMENTNO,PLAYERNO,PEN_DATE,AMOUNT (Only 4 feilds)

Thank u and looking forward to your reply.