command object insert operation

[santosh.garg]

I am using command object in for loop on first time execution it inserts the record but the next time it gives an error

Multible OLEDB error

following is the code I use

Set rssave = New ADODB.Recordset

Set cmd = New ADODB.Command
'cmd.ActiveConnection = GCon
'cmd.CommandText = "prc_Invoice_Insert"

With mshFlexInvoiceEntry
fnOpenConnection
For i = 1 To .Rows - 1
tempindex = i
fnvalidate
fnSetNull
fntxtDate

' cmd.ActiveConnection = GCon
If .TextMatrix(i, 1) <> "" Or i = .Rows - 1 Then
tmp_Item = IIf(.TextMatrix(i, 1) = "", .Rows - 1, .TextMatrix(i, 1))
Cust_Addr = rchtxtCustAddress.Text

'sqlInsert = "Insert into Invoice (InvoiceNo,item,pono,dcno,invoicedate,podate,dcdate, custaddress,tinno,custservicetaxno,description,quantity,unitprice,amountTotal,kvat1,kvat2,cst1,cst2,servicetax) values ('" & txtInvoiceNo.Text & "','" & tmp_Item & "', '" & txtPONo.Text & "','" & txtDCNo.Text & "' , '" & Invoicedate & "','" & podate & "','" & DCDate & "','" & rchtxtCustAddress.Text & "' , '" & txtTinNo.Text & "','" & txtServiceTaxNo.Text & "','" & .TextMatrix(i, 2) & "', " & tempQTY & ", " & tempUNIT & "," & tempVALUE & "," & tempKVAT1 & "," & tempKVAT2 & "," & tempCST1 & "," & tempCST2 & "," & tempStax & ")"

'sqlInsert = "Insert into Invoice (InvoiceNo,pono,dcno,invoicedate,podate,dcdate, custaddress,tinno,custservicetaxno,description,quantity,unitprice,amountTotal,kvat1,kvat2,cst1,cst2,servicetax,subtotal) values (@invoicenum,@pono,@dcno,@invoicedt,@podt,@dcdt,@custadd,@tinno,@custservicetaxnum,@desc,@qty,@price,@totalamount,@kvat1,@kvat2,@cst1,@cst2,@servicetax,@subtotal)"

cmd.CommandText = "Insert into Invoice (InvoiceNo,pono,dcno,invoicedate,podate,dcdate, custaddress,tinno,custservicetaxno,description,quantity,unitprice,amountTotal,kvat1,kvat2,cst1,cst2,servicetax,subtotal) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

'cmd.CommandText = "prc_insert_invoice"

cmd.CommandType = adCmdTable

cmd.Parameters.Append cmd.CreateParameter("@invoicenum", adVarChar, adParamInput, 30, txtInvoiceNo.Text)
' cmd.Parameters.Append cmd.CreateParameter("@item", adInteger, adParamInput, tmp_Item)
cmd.Parameters.Append cmd.CreateParameter("@pono", adVarChar, adParamInput, 30, txtPONo.Text)
cmd.Parameters.Append cmd.CreateParameter("@dcno", adVarChar, adParamInput, 30, txtDCNo.Text)
cmd.Parameters.Append cmd.CreateParameter("@invoicedt", adDate, adParamInput, 10, Mid(Invoicedate, 1, 4) + "-" + Mid(Invoicedate, 6, 2) + "-" + Mid(Invoicedate, 9, 2))
cmd.Parameters.Append cmd.CreateParameter("@podt", adDate, adParamInput, 10, Mid(podate, 1, 4) + "-" + Mid(podate, 6, 2) + "-" + Mid(podate, 9, 2))
cmd.Parameters.Append cmd.CreateParameter("@dcdt", adDate, adParamInput, 10, Mid(DCDate, 1, 4) + "-" + Mid(DCDate, 6, 2) + "-" + Mid(DCDate, 9, 2))
cmd.Parameters.Append cmd.CreateParameter("@custadd", adVarChar, adParamInput, 250, rchtxtCustAddress.Text)
cmd.Parameters.Append cmd.CreateParameter("@tinno", adVarChar, adParamInput, 30, txtTinNo.Text)
cmd.Parameters.Append cmd.CreateParameter("@custservicetaxnum", adVarChar, adParamInput, 45, txtServiceTaxNo.Text)
cmd.Parameters.Append cmd.CreateParameter("@desc", adVarChar, adParamInput, 200, .TextMatrix(i, 2))
cmd.Parameters.Append cmd.CreateParameter("@qty", adDouble, adParamInput, tempQTY)
cmd.Parameters.Append cmd.CreateParameter("@price", adDouble, adParamInput, tempUNIT)
cmd.Parameters.Append cmd.CreateParameter("@totalamount", adDouble, adParamInput, tempVALUE)
cmd.Parameters.Append cmd.CreateParameter("@kvat1", adDouble, adParamInput, tempKVAT1)
cmd.Parameters.Append cmd.CreateParameter("@kvat2", adDouble, adParamInput, tempKVAT2)
cmd.Parameters.Append cmd.CreateParameter("@cst1", adDouble, adParamInput, tempCST1)
cmd.Parameters.Append cmd.CreateParameter("@cst2", adDouble, adParamInput, tempCST2)
cmd.Parameters.Append cmd.CreateParameter("@servicetax", adDouble, adParamInput, tempStax)
cmd.Parameters.Append cmd.CreateParameter("@subtotal", adDouble, adParamInput, tempSubtotal)

' Else
' sqlInsert = "update invoice set comments = '" & .TextMatrix(i, 2) & "' where invoiceno = " & txtInvoiceNo.Text & " and Item = " & tmp_Item & ""

End If
cmd.CommandType = adCmdText
cmd.Execute
cmd.CommandText = ""

Next
End With
fnCloseConnection

I look forward for suggessions

[AndreRet]

I'm not sure what you are trying to do here. I see that you have a MS Flexgrid to populate data. Are you adding data by using the flexgrid? It also seems that you are using MySql, therefor the insert into statements.

Please elaborate a bit more on what you are planning, I might then be able to pick up the error message and where all is going wrong.

[Vineeth K]

looks like that code is tooooooooooooo small ,
I think it is better to attach a sample file ...
This code makes me mad ..........

[AndreRet]

pOST THE SAMPLE PROJECT AND LET'S HAVE A LOOK AT THAT.

[santosh.garg]

Thanks for your remarks,

Ya I am using flexgrid to populate values and these values are passed as a parameter for insert operation. I have checked the values in immediate value it shows me the right value.
Here when I execute the code to save data from flexgrid and other fields it executes but some of the fields are filled with '0'. but the next time it gives me multiple oledb error the code the for save command is all here.

For i = 1 To .Rows - 1
If .TextMatrix(i, 1) <> "" Or i = .Rows - 1 Then
tmp_Item = IIf(.TextMatrix(i, 1) = "", .Rows - 1, .TextMatrix(i, 1))
Cust_Addr = rchtxtCustAddress.Text

cmd.CommandText = "Insert into Invoice (InvoiceNo,pono,dcno,invoicedate,podate,dcdate, custaddress,tinno,custservicetaxno,description,quantity,unitprice,amountTotal,kvat1,kvat2,cst1,cst2,servicetax,subtotal) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"

'cmd.CommandText = "prc_insert_invoice"

cmd.CommandType = adCmdTable

cmd.Parameters.Append cmd.CreateParameter("@invoicenum", adVarChar, adParamInput, 30, txtInvoiceNo.Text)
cmd.Parameters.Append cmd.CreateParameter("@pono", adVarChar, adParamInput, 30, txtPONo.Text)
cmd.Parameters.Append cmd.CreateParameter("@dcno", adVarChar, adParamInput, 30, txtDCNo.Text)
cmd.Parameters.Append cmd.CreateParameter("@invoicedt", adDate, adParamInput, 10, Mid(Invoicedate, 1, 4) + "-" + Mid(Invoicedate, 6, 2) + "-" + Mid(Invoicedate, 9, 2))
cmd.Parameters.Append cmd.CreateParameter("@podt", adDate, adParamInput, 10, Mid(podate, 1, 4) + "-" + Mid(podate, 6, 2) + "-" + Mid(podate, 9, 2))
cmd.Parameters.Append cmd.CreateParameter("@dcdt", adDate, adParamInput, 10, Mid(DCDate, 1, 4) + "-" + Mid(DCDate, 6, 2) + "-" + Mid(DCDate, 9, 2))
cmd.Parameters.Append cmd.CreateParameter("@custadd", adVarChar, adParamInput, 250, rchtxtCustAddress.Text)
cmd.Parameters.Append cmd.CreateParameter("@tinno", adVarChar, adParamInput, 30, txtTinNo.Text)
cmd.Parameters.Append cmd.CreateParameter("@custservicetaxnum", adVarChar, adParamInput, 45, txtServiceTaxNo.Text)
cmd.Parameters.Append cmd.CreateParameter("@desc", adVarChar, adParamInput, 200, .TextMatrix(i, 2))
cmd.Parameters.Append cmd.CreateParameter("@qty", adDouble, adParamInput, tempQTY)
cmd.Parameters.Append cmd.CreateParameter("@price", adDouble, adParamInput, tempUNIT)
cmd.Parameters.Append cmd.CreateParameter("@totalamount", adDouble, adParamInput, tempVALUE)
cmd.Parameters.Append cmd.CreateParameter("@kvat1", adDouble, adParamInput, tempKVAT1)
cmd.Parameters.Append cmd.CreateParameter("@kvat2", adDouble, adParamInput, tempKVAT2)
cmd.Parameters.Append cmd.CreateParameter("@cst1", adDouble, adParamInput, tempCST1)
cmd.Parameters.Append cmd.CreateParameter("@cst2", adDouble, adParamInput, tempCST2)
cmd.Parameters.Append cmd.CreateParameter("@servicetax", adDouble, adParamInput, tempStax)
cmd.Parameters.Append cmd.CreateParameter("@subtotal", adDouble, adParamInput, tempSubtotal)
End If
cmd.CommandType = adCmdText
cmd.Execute

Next
End With

The problem here is I cannot insert the values into database and some fields populates to zero for the first time also. Hope I could assert the problem now better.

Thank you
Santosh

[AndreRet]

Try the following. Use MS Datagrid control and not the MS flexgrid control. Right click on the Datagrid control, select 'Properties'. On the General tab, select the 'Add New AND Update' check boxes. This will cut out all the coding for updating a new record for it is done automatically. Just make sure that the grid is properly connected to your database. You can add the FULL connection string in the Datasource property box in the Properties window.

Also ensure that the data you add corresponds with the field setup of your table, for instance, if you had the property of a field set to 'Date' and some other data than a date is added, errors will be generated or the field will be updated with a "0" zero factor.

I hope this helps.

[santosh.garg]

Thanks for all your advice, Actually I changed my insert statement. Now I am executing it with recordset.
But I have one question here "How to know the parameter passed object" as in following statement

cmd.Parameters.Append cmd.CreateParameter("@price", adDouble, adParamInput, tempUNIT)

and the parameter @price is passed to insert statement. How to what is the value passed in insert statement.
Thank you
Santosh

[santosh.garg]

Hi there,
what is the easiest way to hide close button in vb6 Form.

Thanking You
Santosh Kumar

[vb5prgrmr]

If you are talking about the title bar buttons (minimize, maximize, close) then in the forms property you can set the controlbox=false and this will remove all three. If you are talking about a command button then its .visible=false.

Good Luck