SQL Efficiency

[kovan]

what does everyone use when adding a new record to a table?
editing a new record?
just wondering if SELECT is BAD for adding new records
like i have done in the following..
i tend to use SELECT * when adding new records to my tables.. dont know how bad this is
if anyone has a better idea
please modify the following code with new way and reasons why.. thank you


SQL = "SELECT * " & _
"FROM Clientcode " & _
"WHERE clientcode= " & Format(txtClientCode.Text, "000")
With NewClientCodeRecords
Call CloseIfOpen(NewClientCodeRecords)
.Open SQL, MeterInfo, adOpenKeyset, adLockOptimistic
If Not .EOF Then
MsgBox "This client code exists,please enter new client code"
txtClientCode.SetFocus
.Close
Exit Sub
End If


'addes the information to the client code table
.AddNew
If Not Trim(txtClientCode.Text) = "" Then !ClientCode = txtClientCode.Text
If Not Trim(txtClientName.Text) = "" Then !ClientName = txtClientName.Text
If Not Trim(txtContact.Text) = "" Then !ContactName = txtContact.Text
If Not Trim(txtAddress.Text) = "" Then !Address = txtAddress.Text
If Not Trim(txtPhone.Text) = "" Then !Phone = txtPhone.Text
If Not Trim(txtCity.Text) = "" Then !City = txtCity.Text
If Not Trim(txtProvince.Text) = "" Then !Province = txtProvince.Text
If Not Trim(txtPostalCode.Text) = "" Then !PostalCode = txtPostalCode.Text
If Not Trim(txtContractorNum.Text) = "" Then !ContractorNum = txtContractorNum.Text
.Update
.Close
End With

[Aldea]

hello!
let me say what i do(not always):
suppose i have only two textboxes:text1 and text2
First i create a function to verify if are null values in textboxes:
Public Function Verify() as Boolean
verify=true
if trim(text1)="" or trim(text2)="" then
verify=false
exit function
end if
End Function
Let's say i make insert through button's event Command1_click
Private Sub Command1_Click()
if verify then
strSQL = "insert into tablename values('" + txtUser.Text + "','" + txtPassw.Text + "')"
rsUser.Open strSQL, conn, adOpenKeyset, adLockOptimistic
end if
end sub
Anyway i passed through the code for connection to database....
The point is:i use Insert for a new record and Update for Edit a record...are faster then select....

[kovan]

yap i agree insert into is faster
but with insert into you have to know the order of your fields...

since i have all my project written with SELECT for adding new fields..
is it worth changing it into INSERT into
or should i just slap myself and stick with select and next time do with INSERT into?

[Aldea]

Originally posted by kovan
yap i agree insert into is faster
but with insert into you have to know the order of your fields...

since i have all my project written with SELECT for adding new fields..
is it worth changing it into INSERT into
or should i just slap myself and stick with select and next time do with INSERT into?


Perhaps next time is better for a new beginning...
I think the order is not such a problem.U have to know it anyway...even if u use select...
Let's say u have a boolean named STATUS,and two constants INSERT=0 and UPDATE=1.
const STATUS as Boolean
const INSERT as Integer=0
const INSERT as Integer=1
When u make that select to see if there are Clients(probably) with that Code(txtClientCode) u think so:

If (NewClientCodeRecords.EOF and _ NewClientCodeRecords.BOF) then
'STATUS=INSERT
'call INSERT Statement
else
'STATUS=UPDATE
'call UPDATE Statement
end if
In fact this constant STATUS is better to be set through general buttons:ADD and UPDATE.
But if select works...that's fine!