Is my code wrong?
lazarus / 16-Databases

----------------------------------------------------------------------
workinghard · 2012-03-24 04:19

Hi everyone! I'm using lazarus 0.9.30 with FPC 2.4.2, and I'm trying
to make an insert query from the editboxes

My line so far:
Quoteprocedure TForm3.InsertClick(Sender: TObject);
begin
  SQLQuery1.close;
  sqlquery1.fieldbyname('NamaPemilik').Text:= Nama.Text;
  sqlquery1.fieldbyname('AlamatJalan').Text:= Alamat.Text;
  sqlquery1.fieldbyname('UkuranTanah').Text:= Luas_Tanah.Text;
  sqlquery1.fieldbyname('UkuranRumah').Text:= Luas_Bangunan.Text;
  sqlquery1.fieldbyname('PemakaianAirTerakhir').Text:=
Meteran_Air_Terakhir.Text;
  sqlquery1.open;
end;
And when i ran the program and filing the data, it raises an error
saying SQLQuery1: Field not found: "NamaPemilik"

Is my code wrong? or is there something I forgot?

----------------------------------------------------------------------
bambamns · 2012-03-24 04:46

You need to do an insert and post to insert values to database -
something like :
Code: [Select]  strS := LokacijaEdit.Text;
  strS2 := OpisEdit.Text;        
  with LokacijeUpisQ do
   begin
    Insert;
     FieldByName('ID').Value := 0;
     FieldByName('FIRMA_ID').Value :=
FirmeQ.FieldByName('ID').AsInteger;
     FieldByName('DC_ID').Value := DCIQ.FieldByName('ID').AsInteger;
     FieldByName('LOKACIJA').Value := strS;
     FieldByName('SEKTOR_ID').Value :=
SektoriQ.FieldByName('ID').AsInteger;
     FieldByName('OPIS').Value := strS2;
     FieldByName('SAP_ID').Value := SAPQ.FieldByName('ID').AsInteger;
    try
     Post;
     ApplyUpdates;
     DM.SQLTransaction.CommitRetaining;
    except
     on e: exception do
      begin
       DM.ShowMsg('Error', 'Error while writing to LOKACIJE
:'+#13+#13+
                   E.ClassName+#13+E.Message+#13+#13+
                   'Changes will not be saved.',mtWarning,[mbOK]);
       CancelUpdates;
      end;
    end;
    Refresh;
   end;
  LokacijeQ.Refresh;                         

In this example , LokacijeQ is already open.

----------------------------------------------------------------------
workinghard · 2012-03-24 06:18

Is this also works if i use varchar in my table?
and also, what is strS and strS2?


Edit:
I finally managed to get the data to update on the dbgrid
but the problem is, it replaces any data that i've wrote earlier
Code: [Select]procedure TForm3.InsertClick(Sender: TObject);
begin
  SQLQuery1.edit;
  sqlquery1.fieldbyname('NamaPemilik').Text:= Nama.Text;
  sqlquery1.fieldbyname('AlamatJalan').Text:= Alamat.Text;
  sqlquery1.fieldbyname('UkuranTanah').Text:= Luas_Tanah.Text;
  sqlquery1.fieldbyname('UkuranRumah').Text:= Luas_Bangunan.Text;
  sqlquery1.fieldbyname('PemakaianAirTerakhir').Text:=
Meteran_Air_Terakhir.Text;
  sqlquery1.post;
  sqlquery1.applyupdates;
end;
Plus, is there any way that I can make the strings in the combobox to
use it to insert to mysql?
thanks in advance

----------------------------------------------------------------------
eny · 2012-03-24 10:32

The main thing is that you do SQLQuery1.Edit which means you are going
the change the current record.
Bambams does an Insert, which prepares a new empty record.

What exactly do you mean with: "Plus, is there any way that I can make
the strings in the combobox to use it to insert to mysql?"

----------------------------------------------------------------------
workinghard · 2012-03-24 13:08

Riight, for that part, i want to say that i have a combobox filled
with some data, and i want it to be able to be inserted on to the SQL
database

anyways, thanks bambamns & eny
it became very clear now...

sorry for being such a noob

----------------------------------------------------------------------
workinghard · 2012-03-25 08:14

Guys, recently I made another program for login and this is the code:
Code: [Select]procedure TForm1.MasukClick(Sender: TObject);
var
  Nama, Password: string;
  Begin

      begin
        with sqlquery1 do
        begin
         close;
         sql.clear;
         sql.add('select ID, katasandi from iduser where ID =
'+quotedstr(ID.text));
         open;
         end;
         if sqlquery1.recordcount = 0 then
         showmessage('Nama ID dan/atau kata sandi yang anda masukkan
salah')


         else begin
           if sqlquery1.fieldbyname ('katasandi').asstring <>
edit1.text then
               begin
               Form2 := TForm2.Create(nil);
               Form2.ShowModal;
               Form2.Release;
               end;
              end;
      end;
            
It works fine, but when i enter a username, coupled with a password,
it doesn't accept numbers, plus it also accepts if the ID is from one
user while the password is from anothers
what's wrong with it?

----------------------------------------------------------------------
BigChimp · 2012-03-25 08:40

Workinghard,

Sorry, but some requests:
1. If this is not the same problem as you earlier reported, please
start a new thread. Otherwise people may get confused.
2. I don't speak your language (Malaysian?)... makes it a bit more
difficult to understand what's going on. You could comment your
variable names with English names for clarity.

As for your question: I'd really look for using parametrised queries
(e.g. something like - not tested, or compiled):
Code: [Select]sql.add('select ID, katasandi from iduser where ID =
:UserSpecifiedID');
sql.parambyname('UserSpecifiedID').AsString:=ID.Text;
This saves you dealing with different ways of quoting char/varchar and
datetime fields... which can be different in different databases.
Also, less chance of SQL injection.
Finally, if you use parametrised queries, you may be able to improve
performance.
See the wiki: http://wiki.lazarus.freepascal.org/Working_With_TSQLQuer
y#Parameters_in_TSQLQuery.SQL


As far as I can make out your first query checks if the user id
exists. If not it says Nama ID dan/atau kata sandi yang anda masukkan
salah.
Hint: please give the same error message when a user id is not found
and when a password is incorrect. (I think your code won't warn if
password is false, just not show Form2) Otherwise, people can start
guessing user names.

Then: indenting/code flow: are you sure your else begin is properly
indented? You can use the menu Source/Jedi code format to format your
code... and you can set options if you want that say how you want it
formatted.

Finally: why not check for username and password at the same time as
that is the only thing you're interested in anyway - something like:
Code: [Select]sql.add('select ID, katasandi from iduser where ID =
:UserSpecifiedID and katasandi = :UserSpecifiedPassword');
sql.parambyname('UserSpecifiedID').AsString:=ID.Text;
sql.parambyname('UserSpecifiedPassword').AsString:=Edit1.Text;
open;
end;
if sqlquery1.recordcount = 0 then
begin
  showmessage('Username or password incorrect');
end
else
  // We determined username must exist and password must be correct
  Form2 := TForm2.Create(nil);
  Form2.ShowModal;
  Form2.Release;  
begin
end;
sqlquery1.close;

...note: untested code, don't even know if it compiles, but hope you
see what I mean.

----------------------------------------------------------------------
workinghard · 2012-03-25 09:48

I tried the following method, but it returns an error saying
Identifier idents no member "parambyname"
What does that mean?

----------------------------------------------------------------------
BigChimp · 2012-03-25 10:06

Sorry, typo/misspelling on my part. That's why I mentioned it was
untested code

The error message means that the identifier in question (SQL in this
case, if I remember correctlY) does not have a
property/function/method called parambyname.
In other words, the compiler warns me I'm at the least mistyping
something...

Have a look here:
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Parameters_i
n_TSQLQuery.SQL

and see if you can figure out where I went wrong

----------------------------------------------------------------------
workinghard · 2012-03-25 10:13

Thanks bigchimp

----------------------------------------------------------------------
ludob · 2012-03-25 10:27

I can only agree with BigChimps comments with one small reservation:
to my understanding you call form2 when the password is wrong. Perhaps
this is an explanation for
QuoteIt works fine, but when i enter a username, coupled with a
password, it doesn't accept numbers, plus it also accepts if the ID is
from one user while the password is from anothers
what's wrong with it?A few additional comments:
- don't use 'quotedstr'. It doesn't escape a quote as it should be for
mysql. It replaces a quote in the string with a double quote while
MySQL expects a backslash to escape a quote. Use parameters in your
queries. This will take care of quoting. Not all db's have the same
quoting rules and using parameters will abstract this for you.
- don't know what happens in Form2 but hopefully this is only a short
lived form. Modal forms block your main app and make it unresponsive.
A login procedure is typically the other way around. Your main app
brings up a modal dialog prompting for user and password. The user
enters its credentials, the form closes, the main app checks the
credentials and continues if all OK or brings up the login form again
(after a variable delay if you want to avoid "hammering").

----------------------------------------------------------------------
workinghard · 2012-03-25 12:33

Quote from: ludob on March 25, 2012, 12:27:15 pm- don't know what
happens in Form2 but hopefully this is only a short lived form. Modal
forms block your main app and make it unresponsive. A login procedure
is typically the other way around. Your main app brings up a modal
dialog prompting for user and password. The user enters its
credentials, the form closes, the main app checks the credentials and
continues if all OK or brings up the login form again (after a
variable delay if you want to avoid "hammering").

what should i do if i'm going to do it that way?

----------------------------------------------------------------------
workinghard · 2012-03-25 12:49

EDIT: Finally I managed to get the program going
Code: [Select]begin
  with sqlquery1 do
  begin
    Close;
    sql.Clear;
    sql.Text := 'select ID, katasandi from iduser where ID = : ID and
password = :Edit1';
    params.ParamByName('ID').AsString := ID.Text;
    params.parambyname('password').AsString := Edit1.Text;
    execsql;
  end;
But it raised an exception class 'EDatabaseError' with message:
SQLQuery1: Parameter ID not foung

----------------------------------------------------------------------
ludob · 2012-03-25 13:14

QuoteCode: [Select]sql.Text := 'select ID, katasandi from iduser where
ID = : ID and password = :Edit1';Remove the space between :  and ID
Code: [Select]sql.Text := 'select ID, katasandi from iduser where ID =
:ID and password = :Edit1';

----------------------------------------------------------------------
ludob · 2012-03-25 13:22

Quote from: workinghard on March 25, 2012, 02:33:52 pmQuote from:
ludob on March 25, 2012, 12:27:15 pm- don't know what happens in Form2
but hopefully this is only a short lived form. Modal forms block your
main app and make it unresponsive. A login procedure is typically the
other way around. Your main app brings up a modal dialog prompting for
user and password. The user enters its credentials, the form closes,
the main app checks the credentials and continues if all OK or brings
up the login form again (after a variable delay if you want to avoid
"hammering").

what should i do if i'm going to do it that way?
Simple example made in 5 minutes  

Code: [Select]unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics,
Dialogs,unit2;

type

  { TForm1 }

  TForm1 = class(TForm)
    procedure FormCreate(Sender: TObject);
  private
    { private declarations }
    function CheckCredentials(id,pwd:string):boolean;
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.FormCreate(Sender: TObject);
begin
  Form2:=TForm2.Create(self);
  try
  repeat
    case Form2.ShowModal of
       mrOK: if CheckCredentials(Form2.EdtID.Text,Form2.EdtPwd.Text)
then
               break
             else
               showmessage('Username or password incorrect');
       mrCancel:begin
             Close;
             Application.Terminate;
             break;
             end;
    end;
  until false;
  finally
    Form2.Free;
  end;
end;

function TForm1.CheckCredentials(id, pwd: string): boolean;
begin
//Here comes your function testing id and password
end;

end
.

Form2 is just a form containing 2 TEdits EdtID and EdtPwd and 2
buttons, a login button with ModalResult set to mrOK and a cancel
button with ModalResult set mrCancel. No single line of code. Set
PasswordChar to '*' for EdtPwd if you want to mask the password
entered by the user.
Form1 is your main application. The form won't show unless the
credentials are checked.

----------------------------------------------------------------------
workinghard · 2012-03-25 14:07

I see, so the form 2 should be created using those methods up there as
well?

----------------------------------------------------------------------
BigChimp · 2012-03-25 14:24

Quote from: workinghard on March 25, 2012, 02:49:09 pmCode: [Select] 
 sql.Text := 'select ID, katasandi from iduser where ID = : ID and
password = :Edit1';
    params.ParamByName('ID').AsString := ID.Text;
    params.parambyname('password').AsString := Edit1.Text;
But it raised an exception class 'EDatabaseError' with message:
SQLQuery1: Parameter ID not foung
You'll probably also notice that you your second parameter is Edit1.
However, you're trying to assign a value to a parameter called
password.... so you'd need to change either the SQL or the second
params.parambyname line.

QuoteI see, so the form 2 should be created using those methods up
there as well?
Don't quite understand what you mean. You should create Form2 like
ludob said:
QuoteForm2 is just a form containing 2 TEdits EdtID and EdtPwd and 2
buttons, a login button with ModalResult set to mrOK and a cancel
button with ModalResult set mrCancel. No single line of code. Set
PasswordChar to '*' for EdtPwd if you want to mask the password
entered by the user.

In this code:
Code: [Select]
function TForm1.CheckCredentials(id, pwd: string): boolean;
begin
//Here comes your function testing id and password
end;
you will run your query with parameters as mentioned above.
This function checks for username/password. If they are correct, the
function should return true.
Because the checking code is called in the OnCreate event of form 1,
it will immediately show a login form.
If you specify the wrong username and/or password, the login form will
be shown again.
If you press Cancel instead of OK on the login form, the application
will close.
Only if the password check was done correctly will you break out of
the loop and then you will be able to see/use the main form (Form1)

----------------------------------------------------------------------
workinghard · 2012-03-25 14:50

I think i'm starting to get the idea
Thanks Ludob & BigChimp!