Insert error 1

[tekthis]

hello...
im trying to insert information from a datagrid into a TempTbl but, i keep getting an error involving "Number of query values and destination fields aren't the same"...here is the code im using for that...
--------------------------------------------------
Private Sub cmdSave_Click()

Dim vall As String
Dim iSQL As String

vall = DataGrid1.Columns(DataGrid1.Col).Text

Set objConnTemp = New ADODB.Connection
With objConnTemp
.CursorLocation = adUseClient
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data Source=c:\test\test.mdb"
.Open
End With

iSQL = "Insert Into TempTable (Field1, Field2, Field3, Field4, Field5,)" _
& "Values('" & vall & "')"

Set objRecTemp = New ADODB.Recordset
With objRecTemp
Set .ActiveConnection = objConnTemp
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = iSQL
.CursorType = adOpenDynamic
.Open
End With

MsgBox "thank you"

objRecTemp.Close
Set objRecTemp = Nothing
objConnTemp.Close
Set objConnTemp = Nothing

End Sub
------------------------------------------------------

i checked the table and the code and im inserting the correct amount of fields...any ideas where the problem is???do you think it has to do with my ID field in my table that im using as my primary key(autonumber)???thanks for your time...

 

[fredericofonseca]

vall = DataGrid1.Columns(DataGrid1.Col).Text
is inserting just one field.

To see exactly how your SQL looks add
Dim s As String
s = MsgBox(iSQL)

before "Set objRecTemp"

It should look like
Insert Into TempTable (Field1, Field2, Field3, Field4, Field5,) values('value1', 'value2', 'value3', 'value4')

where value1-4 are the values of columns 1 to 4 of the datagrid1.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[tekthis]

thanks frederico fonseca...i went ahead and restructured my code and i got it to insert in the database but, it just inserts the first row, which makes sense since i only told it to...what if i wanted to insert the entire column of each field in the temptable??? is that possible??? thanks again...

 

[fredericofonseca]

For that I would normally use a command object. You should be able to find more info on these on these forums/FAQ's, and on

http://www.microsoft.com

so a very high level of how to work with it.

1- dim objects (connection and command)
2- create sql statement (insert into tbl (field1, field2), values(?,?) one ? for each field)
3- define parameters ('cmdobject.Parameters.Append cmdobject.CreateParameter("parameter_name", adInteger, adParamInput, size_of_parameter_field_as_defined_on_database)
One for each parameter

The above is only done once for each SQL statement.

Now you need to proceed with the insert itself.
So create a loop

for i = 1 to last_grid_row (you get this from one of the grid properties)
cmdobject.parameters("parameter_name1").value = grid(row_i,col_1).value

cmdobject.parameters("parameter_name2").value = grid(row_i,col_2).value
cmdobject.execute
next i


The above is one way to do. Regardless of how you do it, you either use a BOUND grid or you loop through it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[tekthis]

thanks for the reply, here's a star fredericofonseca...i think im going to go with this and try to figure it out...the only thing is that i don't know what you mean by "last_grid_row"...i don't see that anywhere in the grid properties...here is my code with the parameter...
-------------------------------------
Private Sub cmdSave_Click()

Dim value1 As String
Dim value2 As String

Dim iSQL As String
Dim i As Column

value1 = DataGrid1.Columns(0).Text
value2 = DataGrid1.Columns(1).Text

Set objConnTemp = New ADODB.Connection
With objConnTemp
.CursorLocation = adUseClient
.ConnectionString= "Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data Source=c:\test\test.mdb"
.Open
End With

Set objCommTemp = New ADODB.Command
With objCommTemp
.Parameters.Append .CreateParameter("ParamName", adInteger, adParamInput, 25, 0)
End With

For i = 1 To last_grid_row
objCommTemp.Parameters("ParamName1").Value = DataGrid1(row_1, col_1).Value
objCommTemp.Parameters("ParamName2").Value = DataGrid1(row_i, col_2).Value
objCommTemp.Execute
Next i

iSQL = "Insert Into TempTable (Field1, Field2)" _
& "Values('" & value1 & "', '" & value2 & "')"

Dim s As String
s = MsgBox(iSQL)

Set objRecTemp = New ADODB.Recordset
With objRecTemp
Set .ActiveConnection = objConnTemp
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = iSQL
.CursorType = adOpenDynamic
.Open
End With

End Sub
------------------------------------


i see FirstRow in the properties, but no LastRow...was that a misprint??? thanks again...

 

[fredericofonseca]

No it was not a misprint.

The code I place is not real code, just an example of how to do.
the correct properties will depend on the control used, and not all will have that property.

in the casd of the datagrid that is supplied with VB this one is missing, so you store it whenever you add a row to the grid.

The same goes for DataGrid1(row_i, col_1).Value
row_i is just an index of the current row, and col_1 is whatever column you are processing.
The correct properties aren't even like this for the default datagrid.

I would not even use the datagrid, but would use instead the flexgrid, or any other of the grid controls available on the net. (I personally use Spread from

http://www.fpoint.com).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com