Problems with update sql statemnt in vb6

[depash]

I have a table called SupplierInfor with tables:
SupplierID - Autonumber
Date - date
SupplierName - Text
SupplierAddress - text
Telephone - Number
email - text
Town - text
country - text
I want to update all records in this table where supplierID is a certain number. The code i have is as below:

dim strSQL as string
strSQL = "UPDATE SupplierInfor SET " & _
    "[Date]='" & txtDate.Text & "', " & _
    "[SupplierName]='" & txtSuppName.Text & "', " & _
    "[SupplierAddress]='" & txtSuppAddress.Text & "', " & _
    "[Telephone]='" & txtTelephone.Text & "', " & _
    "[email]='" & txtEmail.Text & "', " & _
    "[Town]=" & cboTown.Text & ", " & _
    "[Country]=" & cboCountry.Text & " WHERE [SupplierID]='" & Val(txtSuppID.Text) & "'"
 
   Cn.Execute strSQL

When i run the code i get the error message "no value given for one or more required parameters" Am using vb6 and ADO connection to MS Access 2000.
What is the problem with my code? PLease help me and i will appreciate any assistance.

[QVeen72]

Hi,

Dates need to be enclosed with a Hash:
And since you say Supplier ID is Autonumber and Telephone is Numeric, no need to Wrap it with Single quotes:

dim strSQL as string
strSQL = "UPDATE SupplierInfor SET " & _
    "[Date]=#" & txtDate.Text & "#,  " & _
    "[SupplierName]='" & txtSuppName.Text & "', " & _
    "[SupplierAddress]='" & txtSuppAddress.Text & "', " & _
    "[Telephone]=" & Val(txtTelephone.Text) & ", " & _
    "[email]='" & txtEmail.Text & "', " & _
    "[Town]='" & cboTown.Text & "', " & _
    "[Country]='" & cboCountry.Text & "' WHERE [SupplierID]=" & Val(txtSuppID.Text) 
 

Regards
Veena

[depash]

Thanks alot Veena
It worked and now am so grateful for that. But their is another problem, when i try to delete a record in the same table using the code below, it runs without any error but does not delete any record even if i keep on clicking the delete button. Please look at the code and help me with the area of the problem.

Private Sub cmdDelete_Click()
Dim kuiz As String
kuiz = MsgBox("Are you sure you want to DELETE the current record?", vbYesNo + vbQuestion, "Confirm delete...")
If kuiz = vbYes Then
R3.Open "SELECT * FROM SupplierInfor where SupplierName='" & Trim(txtSuppName.Text) & "'", Cn, adOpenDynamic, adLockBatchOptimistic
    If Not R3.EOF Then
        R3.Delete
    End If
R3.Close
Set R3 = Nothing
End If
End Sub

Thanks again for your help.

[QVeen72]

Hi,

No Need TO Open a Record and Delete.. Just Run a Delete Query:

Cn.Execute "Delete FROM SupplierInfor where SupplierName='" & Trim(txtSuppName.Text) & "'"
 

Regards
Veena

[depash]

Hi Veena,
Thanks for this help, just imagine the delete code just works fine, Thanks coz i was stuck in this. I just don't seem to get someting clear about the UPDATE SQL. Am trying to update a table called ITEM_MASTER_TB WITH THE FOLLOWING FIELDS:
ID - Autonumber
Bardcode - text
Description - text
Category - text
Units - text
Priceperunit - Currency
Quantity - Number
Totalcost - Curency
SupplierName - text
DateOfSupply - date
VAtPer - currency

the code for updating the table is as follows, please forgive me for asking too many questions its only that am not good in SQL.

Dim strSQL As String
   strSQL = "UPDATE ITEM_MASTER_TBL SET " & _
    "[DESCRIPTION]='" & txtItemDescription.Text & "', " & _
    "[CATEGORY]='" & cboCategory.Text & "', " & _
    "[UNITS]='" & cboUnits.Text & "', " & _
    "[PRICEPERUNIT]='" & txtPricePerUnit.Text & "', " & _
    "[QUANTITY]=" & txtQuantity.Text & ", " & _
    "[TOTALCOST]='" & txtCost.Text & "', " & _
    "[DATEOFSUPPLY]='" & txtDate.Text & "', " & _
    "[VATPER]='" & txtVATPER.Text & "' WHERE [BARCODE]=" & txtBarcode.Text & ""
   Cn.Execute strSQL

the code returns 'DATA TYPE MISMATCH IN CRITERIA EXPRESSION'. Just maybe it is the single quotes have put in wrong place.

[depash]

Hi Veena,
Sory I just got the problem, its my ignorance the same problem again. I forgot HASH for date column and single quote for txtBarcode.text. I have done the corrections and its OK. Thanks Bro.

[depash]

Hi Veena,
This i know is the logic maybe. In the same project am working on, i want to insert several records in a table using the INSERT INTO SQL. The records i want to insert are in a MSflexgrid that i have populated with data. My coden to do this is as below:

Dim q As Integer
    With Me.MSFlexPOS
    For q = 1 To (.Rows - 1)
 
        Cn.Execute "INSERT INTO POS_TBL(INVNO,INVDATE,BARCODE,DESCRIPTION,CATEGORY,UNITS,COST,QTY,VATPER,STOTAL,VATAMT,LINETOTAL)VALUES(" & _
            Me.TXTINVNUMBER.Caption & ",#" & _
            Me.TXTINVDATE.Caption & "#,'" & _
                .TextMatrix(q, 1) & "','" & _
                .TextMatrix(q, 2) & "','" & _
                .TextMatrix(q, 3) & "','" & _
                .TextMatrix(q, 4) & "'," & _
                .TextMatrix(q, 5) & "," & _
                .TextMatrix(q, 6) & "," & _
                .TextMatrix(q, 7) & "," & _
            Val(.TextMatrix(q, 8)) & "," & _
            Val(.TextMatrix(q, 9)) & "," & _
            Val(.TextMatrix(q, 10)) & ")"
        Next q
    End With

The problem is that it can't loop through the records while inserting them, but if i do away with the for statement, it works but inserts only the first record in the msflexgrid. Do you have an idea how to insert several records while looping in a table. Your help is invaluable.

[QVeen72]

Hi,

Ms.Veena here,
Query Seems to be OKay.. Let me know what is your Database and What is the Provider in Connection String.. Is there any PrimaryKey for the Table..?
If you have a PK and If you are trying to Insert mutiple Records for same PK, then records cannot be Inserted..
If there is no PK, then Try using Begin and Commit Trans,,
Some thing like this :

Cn.BeginTrans
Cn.Execute "<MySQL Statement>"
Cn.CommitTrans
 

REgards
Veena

[depash]

Thanks Ms Veena,
My Database is MS access 2000 called POS_DB and the connection string is:
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Point-Of-Sale\POS_DB.MDB; Persist Security Info=False"
Cn.CursorLocation = adUseClient
The table am inserting multiple records has a primary key i.e. ID - Autonumber
Thanks in advance.

[QVeen72]

Hi,

Did you try using Begin and committrans...?
Also Try to Keep a BreakPoint at Cn.Execute and immediately after executing check if the recs are inserted in the Accessdb.. Check after every insert..
It takes a Few Seconds in Access To Get re-freshed..

But I guess, You can Open a Recset To add data..
Thats what everyone normally follow..
To Delete /Edit Records use "Execute <SQL>"
To Add Records Open a RecordSet....


Regards
Veena

[depash]

Hi,

Sorry, i didn't try coz i have never used Begin or Committrans in vb6. Actually i do not know where to code it maybe you could sort me out on this.Another clarification from you please, When i want to use the Recordset instead of the Execute SQL, is it a must i have a ADO control on the form for it to work? Do you have an example of using the Recordset? Much Gratitude

[QVeen72]

Hi,

Not Necessary to have DATA control to open recordset...
You can use ADO Objects...

Some thing Like This :
assuming you connection is Open..

Dim RST As New ADODB.Recordset
RST.Open "Select * From MyTable",Cn,adOpenDynamic, adLockOptimistic
RST.AddNew
RST("MyCol1") = Text1.Text
RST("MyCol2") = Text2.Text
RST("MyCol3") = Text3.Text
RST.Update
RST.Close
 

You Can use the Same Code To Loop through FlexGrid Rows..(Just your TextMatrix Row will change)

Regards
Veena

[depash]

Let me give it a try and i hope this sorts me out. Thanks alot for your invaluable assistance you've provided. You remain in my memory. Incase am stuck, i'll seek your help. Nice time.

[depash]

Am sorry i was not able to work through your suggestion. I wanted to insert several records into a table while looping throuth the records in a msflexgrid with the code below:


1. Dim q As Integer
2. With Me.MSFlexPOS
3. For q = 1 To (.Rows - 1)
4.
5. Cn.Execute "INSERT INTO POS_TBL(INVNO,INVDATE,BARCODE,DESCRIPTION,CATEGORY ,UNITS,COST,QTY,VATPER,STOTAL,VATAMT,LINETOTAL)VAL UES(" & _
6. Me.TXTINVNUMBER.Caption & ",#" & _
7. Me.TXTINVDATE.Caption & "#,'" & _
8. .TextMatrix(q, 1) & "','" & _
9. .TextMatrix(q, 2) & "','" & _
10. .TextMatrix(q, 3) & "','" & _
11. .TextMatrix(q, 4) & "'," & _
12. .TextMatrix(q, 5) & "," & _
13. .TextMatrix(q, 6) & "," & _
14. .TextMatrix(q, 7) & "," & _
15. Val(.TextMatrix(q, 8)) & "," & _
16. Val(.TextMatrix(q, 9)) & "," & _
17. Val(.TextMatrix(q, 10)) & ")"
18. Next q
19. End With

but the program generates a syntax error in the insert into statement. The table has a primaly key which is autonumber. Please sort me out coz am seriously stuck. Consider an elaborate answer please.
Thanks in advance