Editing data in a database 1

[sirchris]

(with VB5)

I am trying to make an application that can add, edit and delete data to, in and from a database, but I am having problems with the my adding and editing subroutines

here is my adding code:

Sub subAdd()
strWhere = ""
strSubject = "SELECT Subject.SubjectID FROM Subject WHERE Subject.SubjectName = '" & Me.dbcSubject & "';"
Set Recordset = Database.OpenRecordset(strSubject)
strSubjectID = Recordset!SubjectID
strTeacher = "SELECT Teacher.TeacherID FROM Teacher WHERE Teacher.TeacherName = '" & Me.dbcTeacher & "';"
Set Recordset = Database.OpenRecordset(strTeacher)
strTeacherID = Recordset!TeacherID

strAdd = "INSERT INTO Work(Dateset, Datedue, SubjectID, TeacherID, YearGroup, Details, Additionalinfo)"
If Not (txtDateSet.Text = "&quot Then
If Len(strWhere) > 0 Then
strWhere = strWhere & ", "
Else
strWhere = strWhere & " VALUES ("
End If

strWhere = strWhere & txtDateSet.Text
End If

If Not (txtDateDue.Text = "&quot Then
If Len(strWhere) > 0 Then
strWhere = strWhere & ", "
Else
strWhere = strWhere & " VALUES ("
End If

strWhere = strWhere & txtDateDue.Text
End If

If Not (Me.dbcSubject = vbNullString) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & ", "
Else
strWhere = strWhere & " VALUES ("
End If
strWhere = strWhere & strSubjectID
End If

If Not (Me.dbcTeacher = vbNullString) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & ", "
Else
strWhere = strWhere & " VALUES ("
End If

strWhere = strWhere & strTeacherID
End If

If Not (Me.cmbYear = vbNullString) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & ", "
Else
strWhere = strWhere & " VALUES ("
End If

strWhere = strWhere & Me.cmbYear
End If

If Not (txtHomework.Text = "&quot Then
If Len(strWhere) > 0 Then
strWhere = strWhere & ", "
Else
strWhere = strWhere & " VALUES ("
End If

strWhere = strWhere & txtHomework.Text
End If

If Not (txtAdditional.Text = "&quot Then
If Len(strWhere) > 0 Then
strWhere = strWhere & ", "
Else
strWhere = strWhere & " VALUES ("
End If

strWhere = strWhere & txtAdditional.Text
End If

strAdd = strAdd & strWhere & ""


Set Recordset = Database.OpenRecordset("SELECT * FROM Work&quot
Recordset.Execute strAdd

End Sub


Private Sub Form_Load()
standrews = "E:\Chris\College\Computing\standrews.mdb"
Set Workspace = DBEngine.Workspaces(0)
Set Database = Workspace.OpenDatabase(standrews)
End Sub


There seems to be a problem with the "Recordset.Execute strAdd" line

 

[CasperTFG]

First off where is it dying?

Second, you can't execute against a recordset, you execute against a database connection. It looks like ADOX that you are using?

I would switch to ADODB. The following code should be changed.

Dim cn = ADODB.Connection
Dim rs = ADODB.Recordset

then later on in your code.
cn.Execute "INSERT...."

Craig, mailto:sander@cogeco.ca

In the computer industry, there are three kinds of lies:
lies, damn lies, and benchmarks.

 

[CCLINT]

Change this:
Set Recordset = Database.OpenRecordset("SELECT * FROM Work&quot
Recordset.Execute strAdd

To:

Database.Execute strAdd

 

[sirchris]

Thanks, I tried the line "Database.Execute strAdd", but an error message came up saying it had too few parameters and that it had expected 4...

However I have solved this problem through a different method now, thanks for your help

 

[CCLINT]

>too few parameters and that it had expected 4...

This simply means you have wrong field names (they do not exist or you mis-spellt them)

As this is a share forum so please post your solution here so others can take advantage of the solution.

 

[sirchris]

(using DAO by the way)


Private Sub Form_Load()
standrews = "E:\Chris\College\Computing\standrews.mdb"
Set Workspace = DBEngine.Workspaces(0)
Set Database = Workspace.OpenDatabase(standrews)
End Sub

Sub subAdd()

Set Recordset = Database.OpenRecordset("SELECT * FROM Work&quot
Recordset.MoveLast
Recordset.AddNew
Recordset!Dateset = txtDateSet.Text
Recordset!Datedue = txtDateDue.Text
Recordset!YearGroup = Me.cmbYear
Recordset!Details = txtHomework.Text
Recordset!AdditonalInfo = txtAdditonal.Text
Recordset!SubjectID = strSubjectID
Recordset!TeacherID = strTeacherID
Recordset.Update

End Sub