Adding New Record..........I'm having a problem

[dpeithman]

I am trying to add a NEW record. I am using VB6 and SQL Server 7 (ADO connectivity).

I KNOW my code is wrong. I'm a beginner and just can't figure this out. I think If I can understand this it will help me overall a great deal. Here's what my code looks like now: (don't laugh)

Private Sub cmdAddNew_Click()

rstAction.AddNew

'Clear the boxes
cboAssignee.Text = ""
cboMeetingName.Text = ""
cboTaskName.Text = ""
txtTaskDescription.Text = ""
txtTargetDate.Text = ""
txtCompleteDate.Text = ""
txtPerformanceRating.Text = ""
txtMeetingDate.Text = Date
txtModDate.Text = ""
txtAssignedBy.Text = ""


' Establish a connection
Set cnSQLConnection = New ADODB.Connection
With cnSQLConnection
.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=ompsv17;User ID=sa;Initial Catalog=MeetingReg"
.Open
End With

' Open a recordset
SQLstr = "SELECT * FROM Action"
Set rstAction = New ADODB.Recordset
rstAction.Open "Select Assignee, MeetingName, TargetDate, TaskDescription, MeetingDate, CompleteDate, PerformanceRating, ModDate, AssignedBy," & _
"TaskName from Action", cnSQLConnection, _
adOpenKeyset, adLockOptimistic

' Bind the text boxes to the recordset object
Set cboAssignee.DataSource = rstAction
cboAssignee.DataField = "Assignee"
Set cboMeetingName.DataSource = rstAction
cboMeetingName.DataField = "MeetingName"
Set cboTaskName.DataSource = rstAction
cboTaskName.DataField = "TaskName"
Set txtTaskDescription.DataSource = rstAction
txtTaskDescription.Text = "TaskDescription"
Set txtTargetDate.DataSource = rstAction
txtTargetDate.Text = "TargetDate"
Set txtCompleteDate.DataSource = rstAction
txtCompleteDate.Text = "CompleteDate"
Set txtPerformanceRating.DataSource = rstAction
txtPerformanceRating.Text = "PerformanceRating"
Set txtMeetingDate.DataSource = rstAction
txtMeetingDate.Text = "MeetingDate"
Set txtModDate.DataSource = rstAction
txtModDate.Text = "ModDate"
Set txtAssignedBy.DataSource = rstAction
txtAssignedBy.Text = "AssignedBy"

End Sub

[sebs]

You don't add anything :
rstAction.AddNew ->need some more

Not sure(i rarely use ADO) but try:
rstAction!someField = "fdasf"
or
set rstAction!someField = "fdasf"

OR just do it in a INSERT statement
set rstAction = cnSQLConnection.execute("INSERT INTO youTable(field,field...) values(1,'df',...)")



[Edited by sebs on 11-22-2000 at 12:33 PM]

[Corby]

I am not sure about your binding code (without testing it)
but your add New is before you open the recordset

Change it to
rstAction.Open "Select Assignee, MeetingName, TargetDate,
rstAction.AddNew

And see if that works

Corby Nichols

[dpeithman]

Sebs,

I think your first suggestion is close. Here's what I have now, but it still doesn't work. I'm not getting an error, but it's not opening a new record:


Private Sub cmdAddNew_Click()

' Establish a connection
Set cnSQLConnection = New ADODB.Connection
With cnSQLConnection
.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=ompsv17;User ID=sa;Initial Catalog=MeetingReg"
.Open

'Open a recordset
SQLstr = "SELECT * FROM Action"
Set rstAction = New ADODB.Recordset
rstAction.Open "Select Assignee, MeetingName, TargetDate, TaskDescription, MeetingDate, CompleteDate, PerformanceRating, ModDate, AssignedBy," & _
"TaskName from Action", cnSQLConnection, _
adOpenKeyset, adLockOptimistic

rstAction.AddNew
rstAction!Assignee = cboAssignee
rstAction!MeetingName = cboMeetingName
rstAction!TaskName = cboTaskName
rstAction!TaskDescription = txtTaskDescription
rstAction!TargetDate = Date
rstAction!CompleteDate = Date
rstAction!PerformanceRating = ""
rstAction!MeetingDate = Date
rstAction!ModDate = Date
rstAction!AssignedBy = txtAssignedBy


End With



End Sub

[sebs]

Try :

Private Sub cmdAddNew_Click() 

' Establish a connection 
Set cnSQLConnection = New ADODB.Connection 
With cnSQLConnection 
.Provider = "SQLOLEDB" 
.ConnectionString = "Data Source=ompsv17;User ID=sa;Initial Catalog=MeetingReg" 
.Open 

'Open a recordset 
Set rstAction = New ADODB.Recordset 
rstAction.AddNew 
rstAction!Assignee = cboAssignee 
rstAction!MeetingName = cboMeetingName 
rstAction!TaskName = cboTaskName 
rstAction!TaskDescription = txtTaskDescription 
rstAction!TargetDate = Date 
rstAction!CompleteDate = Date 
rstAction!PerformanceRating = "" 
rstAction!MeetingDate = Date 
rstAction!ModDate = Date 
rstAction!AssignedBy = txtAssignedBy 
rstAction.Update
rstAction.movelast
End With 



End Sub

[Edited by sebs on 11-22-2000 at 02:19 PM]

[dpeithman]

This gives me an error:

Run-time error '3704': Operation is not allowed when the object is closed.

Then if I Debug, it's pointing to the rstAction.AddNew

I tried taking that out, and I get the same error, but pointing to the first item: rstAction!Assignee = cboAssignee

I sure do appreciate you trying to help me - please don't give up....

[sebs]

Instead of Set rstAction = New ADODB.Recordset

Try

Dim rstAction = New ADODB.Recordset
and put back
rstAction.Open "Select Assignee, MeetingName, TargetDate, TaskDescription, MeetingDate, CompleteDate, PerformanceRating, ModDate, AssignedBy," & _
"TaskName from Action", cnSQLConnection, _
adOpenKeyset, adLockOptimistic ,adCmdTable

but with adCmdTable


[Edited by sebs on 11-22-2000 at 02:55 PM]

[dpeithman]

Private Sub cmdAddNew_Click()

Dim rstAction As ADODB.Recordset

' Establish a connection
Set cnSQLConnection = New ADODB.Connection
With cnSQLConnection
.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=ompsv17;User ID=sa;Initial Catalog=MeetingReg"
.Open

' Open a recordset
SQLstr = "SELECT * FROM Action"
Set rstAction = New ADODB.Recordset
rstAction.Open "Select Assignee, MeetingName, TargetDate, TaskDescription, CompleteDate, PerformanceRating, MeetingDate, ModDate, AssignedBy," & _
"TaskName from Action", cnSQLConnection, _
adOpenKeyset, adLockOptimistic, adCmdTable

rstAction.AddNew
rstAction!Assignee = cboAssignee
rstAction!MeetingName = cboMeetingName
rstAction!TaskName = cboTaskName
rstAction!TaskDescription = TaskDescription
rstAction!TargetDate = Date
rstAction!CompleteDate = Date
rstAction!PerformanceRating = ""
rstAction!MeetingDate = Date
rstAction!ModDate = Date
rstAction!AssignedBy = AssignedBy
rstAction.Update
rstAction.MoveLast
End With

End Sub


*****If I run this 'as is' I get Run-time error'-2147217900(80040e14)' Incorrect Syntax near the keyword 'Select'.

*****If I take off the 'adCmdTable' property, nothing happens. I can run my program and see all of my data, but when I click on 'ADD' - nothing happens(no error, no new record, nothing).

??????????

[dpeithman]

Sebs!!!

I got it!! With your help and a co-workers, this now works.
Here's the code:

Private Sub cmdAddNew_Click()

Dim rstAction As ADODB.Recordset

' Establish a connection
Set cnSQLConnection = New ADODB.Connection
With cnSQLConnection
.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=ompsv17;User ID=sa;Initial Catalog=MeetingReg"
.Open

' Open a recordset
SQLstr = "SELECT * FROM Action"
Set rstAction = New ADODB.Recordset
rstAction.Open "Select ID, Assignee, MeetingName, TargetDate, TaskDescription, CompleteDate, PerformanceRating, MeetingDate, ModDate, AssignedBy," & _
"TaskName from Action", cnSQLConnection, _
adOpenKeyset, adLockOptimistic

rstAction.AddNew

rstAction!Assignee = Assignee
rstAction!MeetingName = MeetingName
rstAction!TaskName = TaskName
rstAction!TaskDescription = TaskDescription
rstAction!TargetDate = adDate
rstAction!CompleteDate = adDate
rstAction!PerformanceRating = PerformanceRating
rstAction!MeetingDate = Date
rstAction!ModDate = adDate
rstAction!AssignedBy = AssignedBy
rstAction!ID = adAddNew
rstAction.Update
rstAction.MoveLast
cmdLast_Click
End With


', adCmdTable


End Sub



Thanks a million for your help!

Always Learning,