locked
Oracle query validation RRS feed

  • Question

  • User1864322503 posted

    Would this be a viable Oracle sql query? 

    @"SELECT * FROM NICK_TEST WHERE FIRSTNAME like '%' || :FIRSTNAME || '%' or LASTNAME like '%' || :LASTNAME || '%' ";


    further down page... 

    command.Parameters.Add("FIRSTNAME", FirstNameTextbox.Text);
    command.Parameters.Add("LASTNAME", LastNameTextbox.Text);

    I am having some troubles displaying my data correctly right now, and I want to verify if this is a possible source of error.

    Thank you

    Monday, August 5, 2013 1:49 PM

Answers

  • User269602965 posted

    Better to do your concatenations of percent symbol into the bind variable and just call the bind variable in the SQL such as:

    Imports System.Xml.Linq.XElement
    
    Dim strFirstName As String = "%" & FirstNameTextbox.Text & "%"
    
    Dim connectionString As String = ConfigurationManager.ConnectionStrings("{YourOraConnStringName").ConnectionString
    
    Try
      Dim SQL = <SQL>
                  SELECT PHONE_NUMBER FROM {YourSchemaName}.NAME_LIST
                  WHERE FIRST_NAME LIKE :bindvarFIRST_NAME
                </SQL>
      Using conn As New OracleConnection(connectionString)
        Using cmd As New OracleCommand(SQL.Value, conn)
          cmd.Parameters.Clear()
          cmd.Parameters.Add("bindvarFIRST_NAME", OracleDbType.Varchar2, strFirstName, ParameterDirection.Input)
          conn.Open()
          cmd.ExecuteNonQuery()
        End Using
      End Using
    Catch ex As Exception
    End Try

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 5, 2013 4:09 PM
  • User1864322503 posted

    Hello everyone,

    I wanted to come back and explain how I was able to solve this in case someone ever stumbles upon this wondering the same thing.

    My entire C# code for the button click to 'Filter' data is -

                using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                {
                    string sqlquery = @"SELECT * " + 
                                    "FROM NICK_TEST " +
                                    "WHERE LASTNAME=:LASTNAME";
                    using (OracleDataAdapter oda = new OracleDataAdapter(sqlquery, connection))
                    {
                        connection.Open();
    
                        oda.SelectCommand.BindByName = true;
                        oda.SelectCommand.Parameters.Add(":LASTNAME", LastNameTextbox.Text);
                        DataTable dt = new DataTable();
                        oda.Fill(dt);
                        SearchGridview.DataSource = dt;
                        SearchGridview.DataBind();
    
                    }
                }

    This is functioning just as I wanted it to :)
    *Also*, one major mistake I was making was I had my 

    GridView's DataSourceID set. The fact that I had it set here, and I was also referencing it in the C# code was creating troubles, so be sure to consider that as well.

    Thank you Lannie for your help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 6, 2013 3:36 PM

All replies

  • User269602965 posted

    Better to do your concatenations of percent symbol into the bind variable and just call the bind variable in the SQL such as:

    Imports System.Xml.Linq.XElement
    
    Dim strFirstName As String = "%" & FirstNameTextbox.Text & "%"
    
    Dim connectionString As String = ConfigurationManager.ConnectionStrings("{YourOraConnStringName").ConnectionString
    
    Try
      Dim SQL = <SQL>
                  SELECT PHONE_NUMBER FROM {YourSchemaName}.NAME_LIST
                  WHERE FIRST_NAME LIKE :bindvarFIRST_NAME
                </SQL>
      Using conn As New OracleConnection(connectionString)
        Using cmd As New OracleCommand(SQL.Value, conn)
          cmd.Parameters.Clear()
          cmd.Parameters.Add("bindvarFIRST_NAME", OracleDbType.Varchar2, strFirstName, ParameterDirection.Input)
          conn.Open()
          cmd.ExecuteNonQuery()
        End Using
      End Using
    Catch ex As Exception
    End Try

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 5, 2013 4:09 PM
  • User1864322503 posted

    Hi Lannie, thanks for your time. 

    Giving your advice a shot now. 

    Thanks --

    Nick

    Monday, August 5, 2013 4:24 PM
  • User1864322503 posted

    Hello everyone,

    I wanted to come back and explain how I was able to solve this in case someone ever stumbles upon this wondering the same thing.

    My entire C# code for the button click to 'Filter' data is -

                using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                {
                    string sqlquery = @"SELECT * " + 
                                    "FROM NICK_TEST " +
                                    "WHERE LASTNAME=:LASTNAME";
                    using (OracleDataAdapter oda = new OracleDataAdapter(sqlquery, connection))
                    {
                        connection.Open();
    
                        oda.SelectCommand.BindByName = true;
                        oda.SelectCommand.Parameters.Add(":LASTNAME", LastNameTextbox.Text);
                        DataTable dt = new DataTable();
                        oda.Fill(dt);
                        SearchGridview.DataSource = dt;
                        SearchGridview.DataBind();
    
                    }
                }

    This is functioning just as I wanted it to :)
    *Also*, one major mistake I was making was I had my 

    GridView's DataSourceID set. The fact that I had it set here, and I was also referencing it in the C# code was creating troubles, so be sure to consider that as well.

    Thank you Lannie for your help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 6, 2013 3:36 PM