[fdtoo]

I am using vb6 with sql server 2000:

I have a form with a datagrid that allows user to insert data into the datagrid columns.I have created a InsertRecords function that i called when the user click on the cmdAdd button. I faced an error message that says "Run-time error '3704', operation is not allowed when the object is closed" when i tried to insert records into my sql server 2000 database.Appreciate if i can obtain some advice from some experts, thanks! My code is as follows:

================================================

Sub InsertRecords()

	Dim con As New ADODB.Connection
	Dim rs As New ADODB.Recordset
	Dim cmd As ADODB.Command
	Dim prm1 As New ADODB.Parameter
	Dim prm2 As New ADODB.Parameter
	Dim prm3 As New ADODB.Parameter
	Dim prm4 As New ADODB.Parameter
	Dim strconnect As String
	
	strconnect = "DSN=pubs;uid=sa;pwd=stephanie"
	con.Open strconnect
	con.CursorLocation = adUseClient
	
		
	Set cmd = New ADODB.Command
	
	cmd.ActiveConnection = con
	cmd.CommandType = adCmdStoredProc
	cmd.CommandText = "insert_category"
   
	Set prm1 = cmd.CreateParameter("Category_Name_VC", adVarChar, adParamInput, 15, dgResults.Columns(0))
	Set prm2 = cmd.CreateParameter("Sales_IN", adInteger, adParamInput, , dgResults.Columns(1))
	Set prm3 = cmd.CreateParameter("Cos_IN", adInteger, adParamInput, , dgResults.Columns(2))
	Set prm4 = cmd.CreateParameter("Pdt_Category_VC", adVarChar, adParamInput, 20, dgResults.Columns(3))
	
	With cmd
	  .Parameters.Append prm1
	  .Parameters.Append prm2
	  .Parameters.Append prm3
	  .Parameters.Append prm4
	End With
	
Set rs = cmd.Execute
	
rs.Open


If rs.RecordCount > 0 Then '<--run-time error occurred under this line
  rs.MoveFirst
  Do Until rs.EOF
	With rs
	 .AddNew 'FieldList, Values
	 rs.Fields("Category_Name_VC").Value = dgResults.Columns(0)
	 rs.Fields("Sales_IN").Value = dgResults.Columns(1)
	 rs.Fields("Cos_IN").Value = dgResults.Columns(2)
	 rs.Fields("Pdt_Category_VC").Value = dgResults.Columns(3)
	End With
   Loop
End If
	
	
	
 Set dgResults.DataSource = rs
 rs.Close
 Set cmd.ActiveConnection = Nothing
	   
	
End Sub

================================================

Private Sub cmdAdd_Click()

Call InsertRecords

End Sub

Mod Edit: Please use code tags when posting your code. Code tags are used like so =>

Thanks,
PsychoCoder

[June7]

You attempt to open a recordset with rs.Open but then don't say with what table. I always see the Open method with SQL statement like this:

'most simple form, asterisk means all fields
rs.Open "SELECT * FROM tablename;"
'more complex
rs.Open "SELECT DISTINCT fieldname, fieldname FROM tablename WHERE fieldname = '" & strVariable & "' ORDER BY fieldname DESC;"

I think the rs = cmd.Execute is not valid usage.

This post has been edited by June7: 18 May 2009 - 11:43 PM

[vb5prgrmr]

Quote

Execute Method (ADO Command)


Executes the query, SQL statement, or stored procedure specified in the CommandText property.

Syntax

For a row-returning Command:

Set recordset = command.Execute( RecordsAffected, Parameters, Options )

For a non–row-returning Command:

command.Execute RecordsAffected, Parameters, Options

Return Value

Returns a Recordset object reference.

Parameters

RecordsAffected Optional. A Long variable to which the provider returns the number of records that the operation affected.

Parameters Optional. A Variant array of parameter values passed with an SQL statement. (Output parameters will not return correct values when passed in this argument.)

Options Optional. A Long value that indicates how the provider should evaluate the CommandText property of the Command object. Can be any of the following:

Constant Description
adCmdText Indicates that the provider should evaluate CommandText as a textual definition of a command, such as an SQL statement.
adCmdTable Indicates that ADO should generate an SQL query to return all rows from the table named in CommandText.
adCmdTableDirect Indicates that the provider should return all rows from the table named in CommandText.
adCmdStoredProc Indicates that the provider should evaluate CommandText as a stored procedure.
adCmdUnknown Indicates that the type of command in CommandText is not known.
adExecuteAsync Indicates that the command should execute asynchronously.
adFetchAsync Indicates that the remaining rows after the initial quantity specified in the CacheSize property should be fetched asynchronously.


See the CommandType property for a more detailed explanation of the first four constants in this list.

Remarks

Using the Execute method on a Command object executes the query specified in the CommandText property of the object. If the CommandText property specifies a row-returning query, any results the execution generates are stored in a new Recordset object. If the command is not a row-returning query, the provider returns a closed Recordset object. Some application languages allow you to ignore this return value if no Recordset is desired.

If the query has parameters, the current values for the Command object's parameters are used unless you override these with parameter values passed with the Execute call. You can override a subset of the parameters by omitting new values for some of the parameters when calling the Execute method. The order in which you specify the parameters is the same order in which the method passes them. For example, if there were four (or more) parameters and you wanted to pass new values for only the first and fourth parameters, you would pass Array(var1,,,var4) as the Parameters argument.

Note Output parameters will not return correct values when passed in the Parameters argument.

An ExecuteComplete event will be issued when this operation concludes.

[June7]

So fdtoo code is trying to build a recordset with VB6 commands? Now I really appreciate SQL. My skills are with VBA for Access and I was trained in SQL from the start. Has fdtoo mixed apples and oranges? Is it the recordset Open method that is out of place? Here is example of SQL statements to Update and Insert records of table:

If rs.RecordCount > 0 Then
	rs.MoveFirst
	'strLabNum is retrieved number from an aborted login
	cn.Execute "UPDATE Submit SET DateEnter=#" & Date & "#, EnterWho='" & gstrUser & "' WHERE LabNum='" & rs!LABNUM & "'"
	strLabNum = rs!LABNUM
Else
	strLabNum = GetNewLabNumber
	cn.Execute "INSERT INTO Submit (LabNum, DateEnter, EnterWho) VALUES ('" & strLabNum & "', #" & Date & "#, '" & gstrUser & "')"
End If

This post has been edited by June7: 20 May 2009 - 12:41 AM

[fdtoo]

June7, on 19 May, 2009 - 11:39 PM, said:

So fdtoo code is trying to build a recordset with VB6 commands? Now I really appreciate SQL. My skills are with VBA for Access and I was trained in SQL from the start. Has fdtoo mixed apples and oranges? Is it the recordset Open method that is out of place? Here is example of SQL statements to Update and Insert records of table:

If rs.RecordCount > 0 Then
	rs.MoveFirst
	'strLabNum is retrieved number from an aborted login
	cn.Execute "UPDATE Submit SET DateEnter=#" & Date & "#, EnterWho='" & gstrUser & "' WHERE LabNum='" & rs!LABNUM & "'"
	strLabNum = rs!LABNUM
Else
	strLabNum = GetNewLabNumber
	cn.Execute "INSERT INTO Submit (LabNum, DateEnter, EnterWho) VALUES ('" & strLabNum & "', #" & Date & "#, '" & gstrUser & "')"
End If

i have changed Set rs = cmd.Execute to cmd.Execute, and exclude rs.Open, i'm guessing an insert would not need this. Tried running the code but same error message occured, this time it managed to insert 1 row into the database. However, if i entered more than 1 row of records into the datagrid, it only inserts 1 row into my database. Any idea?

[noorahmad]

in vb.net i am using a loop from 0 to datagridview.rows.count

for i as integer = 0 to dgentry.rows.count
insert into table values from dgentry
next i

[firebolt]

@Noorahmad, there is no set converter. You have to address each syntax and convert it.

[noorahmad]

@Firebolt:

[thava]

ok let me tell you some thing

datagrid is updatable when you show the datas in a table straight away

but when you use a query like group by or distrinct like some else it is not updatable
and
one more thing you don't need to add any code for adding a record to the table when you use the data grid with correct locktypes and cursors

so you need to show the table directly to the datagrid then it will add the data
try this for getting recordset from a command

Private Sub Form_Load()
con.Open (Adodc1.ConnectionString)
com.CommandText = "Select * from itemmaster"
com.ActiveConnection = con
com.CommandType = adCmdText
Set Rs = com.Execute
'Rs.Open
If Not Rs.EOF Then
MsgBox Rs(0)
End If
End Sub

This post has been edited by thava: 21 May 2009 - 07:01 PM

[fdtoo]

Charles William (author) has a good example in his book on inserting multiple records from a datagrid. However, the insert function was executed via built-in sql syntax, instead by executing a sproc to perform the insert function.

In my case, i am trying to perform a similar function, only this time, by calling a sproc to execute the insert task.

Just for an assurance, in order to insert multiple records, i need to run the sproc in proportion to the number of records entered in the datagrid?

Is there a way, a multiple insertion can be performed by executing the sproc at one go? I couldn't figure out how the coding should be.

[June7]

I have not used datagrid but perceive it as an object on a form like a listbox (or is virtual in code, like an array?). Is the subproc behind the form? If not, you could pass the form and datagrid names to the procedure as arguments in the Call. In either case, yes, I would say you have to loop through the datagrid items.

[thava]

there is a method for this with a specific locktype

updatebatch

multiple records inserted or updated in a table

try this link

Disconnected Recordsets

i think it helps you

This post has been edited by thava: 22 May 2009 - 03:21 AM