在Select语句中找到但未在Insert语句中找到的Delphi 7 SQL参数 [英] Delphi 7 SQL Parameter found in Select Statement, but not Insert Statement

问题描述

我目前正在编写一个使用SQL和Access 2003数据库的Delphi 7程序.

I am currently coding a Delphi 7 program that utilises SQL and an Access 2003 database.

该单元通过公共变量(这是frmLogin.sCode)从上一个单元接收5位代码.激活表单后，程序将执行SQL查询以显示来自与sCode匹配的tblStudents的记录.该语句使用了ParamByName行，并且运行良好.

The unit receives a 5 digit code from a previous unit, via a public variable (this is frmLogin.sCode). On form activation, the program will execute an SQL query to display the record from tblStudents that matches sCode. This statement uses a ParamByName line and works perfectly.

如果找不到匹配项，则会显示一条消息，并且用户别无选择，只能单击添加用户按钮.然后，提示用户将其所有详细信息输入到程序中，然后将这些详细信息传递给设置SQL插入语句的类.但是，现在出现问题，因为显示了一条消息，指出未找到参数用户名.我不明白为什么，因为它是在运行Select语句时发现的.请有人帮忙吗?

If no match is found, a message is displayed and the user is left with no option, but to click on the add user button. The user is then prompted to enter all of his details into the program, which are then passed to a class which sets out the SQL Insert Statement. A problem occurs now, however, as a message is displayed stating that Parameter Username is not found. I cannot understand why, as it is found when the Select statement is run. Please could someone help with this?

procedure TfrmProfilePage.FormActivate(Sender: TObject);
begin
  //Instantiates the object.
  objProfilePage := TProfilePage.Create;
  sSQL := objProfilePage.SelectSQL;
  ExecuteSQL(sSQl);
end;

procedure TfrmProfilePage.ExecuteSQL(sSQL : String);
begin
  With dmTextbookSales do
    Begin
      dbgrdDisplay.DataSource := dsProfilePage;
      qryProfilePage.SQL.Clear;
      qryProfilePage.SQL.Add(sSQL);
      qryProfilePage.Parameters.ParamByName('Username').Value := frmLogin.sCode;
      qryProfilePage.Open;
      If qryProfilePage.RecordCount = 0
        Then
          Begin
            ShowMessage('Please click on the "Add Details" button to get started.');
            btnChange.Enabled := False;
            btnSelling.Enabled := False;
            btnBuying.Enabled := False;
          End;
    End;
end;

procedure TfrmProfilePage.GetValues(VAR sStudentName, sStudentSurname, sCellNumber, sEmailAddress : String; VAR iCurrentGrade : Integer);
begin
  ShowMessage('Fields may be left blank, but users wishing to sell textbooks should enter at least one contact field.');
  sStudentName := InputBox('Name','Please enter your first name:','');
  sStudentSurname := InputBox('Surame','Please enter your surname:','');
  iCurrentGrade := StrToInt(InputBox('Current Grade','Please enter your current grade:',''));
  sCellNumber := InputBox('Cellphone Number','Please enter your cellphone number:','');
  sEmailAddress := InputBox('Email Address','Please enter your email address:','@dainferncollege.co.za');
end;

procedure TfrmProfilePage.btnAddClick(Sender: TObject);
begin
  GetValues(sStudentName, sStudentSurname, sCellNumber, sEmailAddress, iCurrentGrade);
  sSQL := objProfilePage.InsertSQL;
  ExecuteSQL(sSQL);
  btnChange.Enabled := True;
  btnSelling.Enabled := True;
  btnBuying.Enabled := True;
end;

以下代码是从链接的类clsProfilePage获得的:

The following code is obtained from the linked class, clsProfilePage:

function TProfilePage.InsertSQL: String;
begin
  Result := 'INSERT INTO tblStudents (' + '[StudentID]' + ',' + '[StudentName]' + ',' + '[StudentSurname]' + ',' + '[CurrentGrade]' + ',' + '[CellNumber]' + ',' + '[EmailAddress]' + ') VALUES (' + 'Username' + ',' + QuotedStr(fStudentName) + ',' + QuotedStr(fStudentSurname) + ',' + IntToStr(fCurrentGrade) + ',' + QuotedStr(fCellNumber) + ',' + QuotedStr(fEmailAddress) + ')';
end;

function TProfilePage.SelectSQL: String;
begin
  Result := 'SELECT * FROM tblStudents Where StudentID = Username';
end;

推荐答案

您的 INSERT 语句错误.您需要先添加参数，然后才能设置参数值.在Delphi中，您可以在SQL语句中的参数名称之前使用:进行此操作.

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

此外，仅在执行 SELECT 时使用 Open . INSERT ， UPDATE 和 Delete 不返回行集，因此必须使用 ExecSQL (数据集方法，而不是名称冲突的函数).

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.

(在此情况下，切勿在SQL数据集上使用 RecordCount -它要求检索所有行以获得计数，并且除执行a以外的其他操作均无关紧要仍然是 SELECT .由 ExecSQL 执行的操作应改用 RowsAffected ，它告诉您受该操作影响的行数.

(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.

(如果需要对行数进行计数，请改为从YourTable WHERE<某些条件> 中执行 SELECT COUNT(*)AS NumRecs，然后访问 NumRecs FieldByName 的code>字段.)

(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.)

将返回 INSERT 语句的函数更改为类似的内容( Result 中的#13 是回车符，这可以防止手动在每行的末尾插入空格以分隔SQL字):

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;

然后，您可以将其与 ParamByName 一起使用，而无需通过 QuotedStr 和串联操作跳过所有箍圈:

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).

如果是我，我将使用一个单独的查询，该查询专用于执行 INSERT 操作，您可以在设计时设置SQL，并为对象检查器中的参数.然后，在运行时，您只需设置参数值并对该插入查询调用ExecSQL，然后刷新SELECT查询以反映新行.它避免了所有杂音和混乱(以及一些不必要的函数调用和复杂的SQL构建，打开和关闭SELECT查询等).

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.).

((这还使您可以删除带有 with 的那条可怕的语句，这会导致难以发现的错误并且难以维护代码.)

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

您在表单之间还存在一些不好的联系，您要在第二个表单中引用 frmLogin (特定的表单实例).这意味着您永远不能同时使用任何一种形式的实例，因为您已经在该引用中进行了硬编码.我会重新考虑使用创建表单时传入的参数，还是使用创建个人资料页面表单时登录表单设置的属性(或任何 TProfilePage 是-您的帖子没有)说).

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).

最好的解决方案是将所有与UI无关的代码移到一个单独的单元中(例如 TDataModule ，该单元旨在与非可视组件一起使用，例如ADO查询)并将其从与用户界面相关的表单中删除，

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

• 消除了表单之间的耦合，使代码得以重用.
• 从表单和表单代码的混乱中删除与非可视数据相关的组件.
• 将业务逻辑(与用户交互无关的部分)分离在一个单独的位置，使它(以及使用它的代码)更易于维护.

这篇关于在Select语句中找到但未在Insert语句中找到的Delphi 7 SQL参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！