Parameters in SQL - Delphi 7

Simply set the query's SQL, and then populate the parameters. Use parameter names that make sense to you, of course; I've just used LastName and FirstName for examples. I've updated to use TADOQuery instead of just TQuery after your edit to the question.

ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('SELECT * FROM MyTable');
ADOQuery1.SQL.Add('WHERE LastName = :LastName AND');
ADOQuery1.SQL.Add('FirstName = :FirstName');

// Populate the parameters and open it
ADOQuery1.Parameters.ParamByName('LastName').Value := 'Jones';
ADOQuery1.Parameters.ParamByName('FirstName').Value := 'James';
ADOQuery1.Open;
// Use query results

ADOQuery1.Close;
// Populate parameters with new values and open again
// Populate the parameters and open it
ADOQuery1.Parameters.ParamByName('LastName').Value := 'Smith';
ADOQuery1.Parameters.ParamByName('FirstName').Value := 'Sam';
ADOQuery1.Open;
// Use new query results

ADOQuery1.Close;

Delphi 7 SQL Parameter found in Select Statement, but not Insert Statement

Your INSERT statement is wrong. You need to add parameters before you can set parameter values. In Delphi, you do that using : before the parameter name in your SQL statement.

In addition, Open is only used when performing a SELECT. INSERT, UPDATE, and DELETE don't return a rowset, and therefore you must use ExecSQL (the dataset method, not your function with the conflicting name) instead.

(While we're at it, never use RecordCount on a SQL dataset - it requires all rows to be retrieved in order to obtain a count, and it's not relevant when performing anything other than a SELECT anyway. Operations performed by ExecSQL should use RowsAffected instead, which tells you the number of rows that were affected by the operation.

(If you need a count of the number of rows, perform a SELECT COUNT(*) AS NumRecs FROM YourTable WHERE <some condition> instead, and access the NumRecs field using FieldByName.)

Change your function that returns the INSERT statement to something like this (the #13 in Result is a carriage return, which prevents having to manually insert spaces at the end of each line to separate SQL words):

function TProfilePage.InsertSQL: String;
begin
  Result := 'INSERT INTO tblStudents ([StudentID],'#13 +
            '[StudentName], [StudentSurname],'#13 +
            '[CurrentGrade], [CellNumber], [EmailAddress])'#13 +
            'VALUES (:Username, :StudentName,'#13 +
            ':StudentSurname, :CurrentGrade,'#13 +
            ':CellNumber, :EMailAddress)';
end;

You can then use it with ParamByName, without jumping through all of the hoops with QuotedStr and concatenation:

procedure TfrmProfilePage.AddUser;
begin
  with dmTextbookSales do
  begin
    qryProfilePage.SQL.Text := InsertSQL;
    qryProfilePage.Parameters.ParamByName('Username').Value := frmLogin.sCode;
    qryProfilePage.Parameters.ParamByName('StudentName').Value := frmLogin.sUserName;
    // Repeat for other parameters and values - you have to set every
    // single parameter. To skip, set them to a null variant.

    // Execute the INSERT statement          
    qryProfilePage.ExecSQL;    

    // See if row was inserted, and do whatever.
    If qryProfilePage.RowsAffected > 0 then
      ShowMessage('User added successfully');
   // Perform a SELECT to populate the grid contents with the new
   // rows after the update
  end;
end;

I'd highly suggest you rethink this code. It's extremely convoluted, and it requires too much to accomplish a simple task (adding a new user).

If it were me, I'd use a separate query that was dedicated only to performing the INSERT operation, where you can set the SQL at design-time, and set the proper types for the parameters in the Object Inspector. Then at runtime, you simply set the parameter values and call ExecSQL on that insert query, and refresh your SELECT query to reflect the new row. It avoids all of the noise and clutter (as well as some unnecessary function calls and convoluted SQL building, opening and closing your SELECT query, etc.).

(It would also allow you to remove that horrific with statement, which leads to hard-to-find bugs and difficult to maintain code.)

You also have some bad linkages between forms, where you're referencing frmLogin (a specific form instance) from a second form. This means that you can never have more than one instance of either form in use at the same time, because you've hard-coded in that reference. I'd rethink that to use either parameters passed in when you create the form, or as properties that are set by the login form when creating the profile page form (or whatever TProfilePage is - your post doesn't say).

The best solution would be to move all of the non-UI related code into a separate unit (such as a TDataModule, which is designed to be used to work with non-visual components such as ADO queries) and remove it from the user-interface related forms anyway, which

Eliminates the coupling between forms, allowing the code to be reused.
Removes the non-visual data related components from cluttering the form and form code.
Separates the business logic (the part not related to interacting with the user) in a separate, distinct location, making it (and the code that uses it) easier to maintain.

SQL Query Parameters

Parameters automatically put the quotes around strings for you. Therefore, you cannot include such quotes in the SQL query. Because of that, you also cannot pass the % syntax with the query either.

Instead, keep your statement as the original (replacing = with LIKE), and pass the % around the actual value instead.

Query = 'SELECT * from table_1 where code LIKE :value';
Param := ADOQuery1.Parameters.ParamByName('value');
Param.DataType := ftString;
Param.Value := '%bob%';

On a side note, this code's a bit more elegant...

ADOQuery1.Parameters.ParamValues['value']:= '%bob%';

No need to tell it it's a string. It will detect that automagically.

Delphi query multi Parameters

You should check if textboxes are empty;

begin
    adoquery1.close;
    adoquery1.sql.clear;
    adoquery1.sql.add(SELECT * FROM Table WHERE 1=1 );
    if edtName.text <> '' then
      Begin
        adoquery1.sql.add(' And name=:name ');
        adoquery1.Parameters.ParamByName('name').Value:= edtName.text;
      End;
    if edtTel.text <> '' then
      Begin
        adoquery1.sql.add(' And tel=:tel ');
        adoquery1.Parameters.ParamByName('tel').Value:= edtTel.text;
      End;
    adoquery1.open;
end;

note: if both of them are empty result will show all records.

Delphi: how to pass a list as a parameter to a SQL query?

AFAIK, it is not possible directly.

You'll have to convert the list into a SQL list in plain text.

For instance:

function ListToText(const Args: array of string): string; overload;
var i: integer;
begin
  result := '(';
  for i := 0 to high(Args) do 
    result := result+QuotedStr(Args[i])+',';
  result[length(result)] := ')';
end;

function ListToText(const Args: array of integer): string; overload;
var i: integer;
begin
  result := '(';
  for i := 0 to high(Args) do 
    result := result+IntToStr(Args[i])+',';
  result[length(result)] := ')';
end;

To be used as such:

SQL.Text := 'select * from myTable where intKey in '+ListToText([1,2,3]);
SQL.Text := 'select * from myTable where stringKey in '+ListToText(['a','b','c']);

Delphi 7 changing parameters from integer to shortint

I can explain the behavior you're seeing, and it's not a bug. (Your question is misleading, BTW. Your actual question, based on the edits and comments, is "Delphi 7 changing Parameter.Value.Type from integer to shortint", which is not the same thing at all.)

You're inspecting the Value.Type of the Param.Value, which is not stored in the .dfm file. Value.Type is set either when the form is streamed in from the dfm and the Value is read, or when Value is set at runtime.

I can demonstrate this by following your steps exactly:

Drop a TQuery on a new blank form. Add some SQL to the TQuery.SQL. I used SELECT Id FROM SomeTable WHERE Id = :id.

Click on the ... for the TQuery.Params, and then the id parameter when the Parameter Editor dialog appears. Click the Value in the Object Inspector, and change the Value.Type to Integer.

Save the project and form, and close them. Reopen the project, right-click on the form, and choose View as Text from the context menu. You'll see the following for the Query1 component:

object Query1: TQuery
  SQL.Strings = (
    'select id from sometable where id = :id')
  Left = 152
  Top = 80
  ParamData = <
    item
      DataType = ftInteger
      Name = 'id'
      ParamType = ptInput
      Value = 0
    end>
end

Note that in the ParamData, there is no Value.Type; that's because it's set when the Value is read, not stored when the form is saved.

Now right-click again, and choose View as Form. Go back to the Query1.Parameters, edit the Value, and set it to a high number (I used 123456789).

Save the form, and then View as Text again. Note the Query1 information:

object Query1: TQuery
  SQL.Strings = (
    'select id from sometable where id = :id')
  Left = 152
  Top = 80
  ParamData = <
    item
      DataType = ftInteger
      Name = 'id'
      ParamType = ptInput
      Value = '123456789'
    end>
end

Note that there is still no stored Value.Type. Check the Object Inspector for the ValueType, and it now says String, even though the Parameter.DataType is still ftInteger. This is caused by the IDE's streaming mechanism seeing the quotes, and setting the Value.Type accordingly.

Again, it has no impact on your running application. The Value.Type in the IDE doesn't matter.

Interesting side note: After seeing the Value.Type as String as mentioned above, if you view the form as text again, remove the ' characters around the Value property, and View as Form, then check the Value.Type of the parameter again, it becomes Integer. This is a further indication that it's set during the streaming process, and at design-time has no meaning.

TADOStoredProc and SQL Server stored procedure with parameter as default value

TL;DR - To use the default SP parameter value set:

ADOStoredProc1.Parameters.ParamByName('@p_name').ParameterObject.Value := Unassigned

In the Delphi code, I am not passing second parameter and I am
expecting that SQL Server should take it's default value.

Yes you are. You are using an already existing parameter in design time, so this parameter is explicitly sent as NULL to the SQL Server. You can verify this by inspecting the SQL profiler.

If you must use design time parameters, you can delete it in run time whenever a default parameter is meant to be used or create/assign it otherwise. e.g (you could make a general method for this):

var
  Param: TParameter;
  ParamName: WideString;
  ParamValue: Variant;
  UseDefaultParameter: Boolean;
begin
  ParamName := '@p_name';
  Param := ADOStoredProc1.Parameters.FindParam(ParamName);
  UseDefaultParameter := True; // or False to assign a value

  if UseDefaultParameter then
  begin
    if Assigned(Param) then
      ADOStoredProc1.Parameters.Delete(Param.Index);
  end
  else
  begin
    ParamValue := 'boo!';
    if Assigned(Param) then
      Param.Value := ParamValue
    else
      ADOStoredProc1.Parameters.CreateParameter(ParamName, ftString, pdInput, 10, ParamValue);
  end;
end;

Otherwise, don't use design-time parameters and create the parameters at run-time as needed. An even better approach IMO is to use a local TADOStoredProc, create it at run-time, assign the parameters, execute it, and destroy it.

Personally, I create my TADOStoredProc at run-time and call Parameters.Refresh() method, which will query (and create) the parameters from the SQL Server. then I simply assign the values to the parameters I need. even though there is an extra trip to the SQL Server, it is very convenient to maintain when adding new parameters to the SP.

If a parameter value is not set, the ADO will initiate an exec command setting the parameter with the default keyword. i.e

exec p_dummy_proc @p_id=1, @p_name=default

After further digging, I noticed that the problem is actually in the TParameter.SetValue setter. there is no way to "clear" the parameter value:

procedure TParameter.SetValue(const Value: Variant);
begin
  if VarIsEmpty(Value) or VarIsNull(Value) then <--
    NewValue := Null
  else
  begin
    ...
  end;  
  ParameterObject.Value := NewValue;
end;

You can see that in case you set the Value to NULL or Unassigned, ParameterObject.Value will be set to NULL (but never to the default).

So if you need to clear a parameter, and use the default value, you need to directly set the TParameter.ParameterObject.Value to Unassigned:

if UseDefaultParameter then 
  SP.Parameters.ParamByName('@p_name').ParameterObject.Value := Unassigned
else
  SP.Parameters.ParamByName('@p_name').Value := 'boo!'

How to use parameters in TpFIBDataSet SQL query?

I don't have a copy of the TpFIBDataSet handy. but usually you set a dataset parameter by code like this:

MyDataSet.Parameters.ParamByName('BROJ').AsString := 'some value';

Note : With some TDataSet descendants, the parameters collection is named Params rather than Parameters.

I gather from your suggested edit that with the dataset type you are using, you can write this instead:

MyDataSet.ParamByName('BROJ').AsString := 'some value';

Parameters in SQL - Delphi 7