Getting Started

Hi,

I'm trying to get to grips with MS SQL and ADO at the same time. I've
got a working method of adding a record to a table with unique
constraints and getting back an autonumbered, but it seems decidely
sub-optimal.

I'm pretty sure there'll be a better way than this, can someone point
me in the right direction?

function TMSSQLImporter.AddLocation(const Location: string): Integer;
var
SQLCmd: string;
Qry: TADOQuery;
begin
Qry:=TADOQuery.Create(nil);
try
Qry.Connection:=FConnection;

SQLCmd:=Format('Insert Into Location (Location) VALUES
(''%s'')',[Location]);

try
FConnection.Execute(SQLCmd);
except
//Catch foreign key violations
end;

Qry.SQL.Text:=Format('Select LocnCode from Location where
Location=''%s''',[Location]);
Qry.Active:=True;

if Qry.RecordCount=0 then
Result:= -1
else
begin
Result:=Qry.FieldByName('LocnCode').AsInteger;
end;

finally
Qry.Free;
end;
end;

Cheers,
Rik
.

Relevant Pages

  • Re: Insert date into a datetime column in Visual Basic 2008
    ... Dim cmdString As String ... Dim dte As System.DateTime ... wholly contained within the SQL executed on the server. ... ADO, not ADO.Net, so I can't provide a working example for you). ...
    (microsoft.public.data.ado)
  • Re: Compact SQL Database and ADO
    ... I've since made this change to the code for 'N' in front of the string. ... What happens when you change the working SQL statements to the ones I ... I haven't used SQL CE with ADO myself, I do still write a lot of VB6 apps ...
    (microsoft.public.data.ado)
  • Re: Source size and how to change
    ... It should not truncate sql string. ... It must be happening in the ADO call because the actual ...
    (microsoft.public.data.ado)
  • Executing stored procedures as CommandType=Text
    ... I don't think it is problem of sql query. ... You might need to check the connection ot connection ... >'p3'" that execute on a VB 6 application and ADO. ... >I receive the SQL requests in the string format with all ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Getting Started
    ... >I'm trying to get to grips with MS SQL and ADO at the same time. ... >got a working method of adding a record to a table with unique ... You can have multiple SQL commands in SQL ... which returns the value placed in the Identity field of the last record ...
    (borland.public.delphi.database.ado)