Adding a New Record to a Database

[Catrina]

I am just learning to use databases with VB. I am converting a DOS program that uses an older ISAM database to VB

I am working on adding a new employ to the database and I was wondering the best way to go about it. In an example I have seen, the programmer had 3 textboxes to collect the data, these values were held in variables (ex: Name$ = text1.text) then the INSERT INTO statement was used to insert the data into the database.

In the original program I am converting, the data was put into the database field buffer (MF.LName) and then inserted at the end (LSet PayRec = MF: INSERT 5, PayRec), basically like you do with a file and the PUT statement. The orginal progammer made the comment that using variables to hold the data seemed like a waste. I have 29 textboxes so I would be using 29 variables. Is there another way to save the data to be inserted?

Just some added info, the textboxes are an array and are not bound, and on some I use various verification routines. I am using an Access database
Here is a sample of the insert string (Just a small portion of what it would be)

stSQL$ = "INSERT INTO Master"
stSQL$ = stSQL$ & "("
stSQL$ = stSQL$ & "PAYEMP,DIVA,DEPTA,ACCTA,RATEA,"
stSQL$ = stSQL$ & "DIVB,DEPTB,ACCTB,RATEB,"
stSQL$ = stSQL$ & "DIVC,DEPTC,ACCTC,RATEC"
stSQL$ = stSQL$ & &quot"
stSQL$ = stSQL$ & " VALUES"
stSQL$ = stSQL$ & "("
stSQL$ = stSQL$ & IsNVLString(FEmp$) & ","
stSQL$ = stSQL$ & PRRADI & ","
stSQL$ = stSQL$ & PRRADE & ","
stSQL$ = stSQL$ & PRRAA & ","
stSQL$ = stSQL$ & PRRTA & ","
stSQL$ = stSQL$ & PRRBDI & ","
stSQL$ = stSQL$ & PRRBDE & ","
stSQL$ = stSQL$ & PRRBA & ","
stSQL$ = stSQL$ & PRRTB & ","
stSQL$ = stSQL$ & PRRCDI & ","
stSQL$ = stSQL$ & PRRCDE & ","
stSQL$ = stSQL$ & PRRCA & ","
stSQL$ = stSQL$ & PRRTC & &quot"

Access.Execute stSQL$

Basically I am wanting to know if saving all the data into separate variables (29) then using the INSERT INTO method is the best way to add a new record.

Thanks for any advice

Catrina [sig][/sig]

 

[33216CLC]

Hi Catrina

I have inserted a few lines of Code that I used to add a new record to an access database table named Bank:

Private Sub chkBank()
Dim sql as string
Dim rsBank as recordset

sql = "select * from Bank where company_id='5'"
Set rsBank = dbPayroll.OpenRecordset(sql, dbOpenDynaset, dbExecDirect, dbOptimistic)

With rsBank
.AddNew
![employee_id] = Text(0)
![bank_id] = Trim(Combo1.Text)
![bank_account] = Text(1).Text
![amount] = (Text(2).Text)
![percentage] = (Text(3).Text)
.Update
End With

The text boxes the contain the info for employee_id, bank account, amount and percentage are all named text, but they are each recognignized differently, because their indexes (0 - 3).

I hope this helps

[sig][/sig]

 

[tjfox]

When you create textboxes, make sure you specify the
datasource and the data field for each.
e.g.
name : txtidno
datasource : data1
datafield : idno

to add :

with data1.recordset
.addnew
.idno = txtidno.text

' insert the other fields if there are other fields

.update
end with



[sig][/sig]

 

[TNN]

Hello Catrina,
Sounds like you may be working on payroll or payroll related software. If this is the case can you please E-mail me some details of what your working on.

Myself, I am trying to develop payroll processing software using VB 6.0, access data base, and SQL. For a pattern I am using an old piece of DOS based payroll software.

Maybe we can be of some help to each other with an exchange of ideas or processes.

Please E-mail me at

http://WWW.TNPAYROLL@AOL.COM

Tom, TNN [sig]<p>TOM<br><a href=mailto:WWW.TNPAYROLL@AOL.COM>WWW.TNPAYROLL@AOL.COM</a><br>[/sig]

 

[Guest_imported]

I think you can do this, I am not sure as I am away from development since last 06 months.

Dim intFieldCount as Integer
With rsBank
.AddNew
For intFieldCount = 0 to .Field.count-1
!Fields(intFieldCount).VALUE = text(intFieldCount).text
next intfieldcount
.update
end with

Note that u need to take care of conversiton values and combo boxes by checking the condition or tag properties/caption of your controls. Except Text box you need to change the code accordingly or add lines with OR conditons