Oracle query validation

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

Answer 1

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

Answer 2

User1864322503 posted

Hi Lannie, thanks for your time. 

Giving your advice a shot now. 

Thanks --

Nick

Answer 3

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.