Problems with update sql statemnt in vb6

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:
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.

re: Problems with update sql statemnt in vb6

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:

Regards
Veena

re: Problems with update sql statemnt in vb6

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.

Thanks again for your help.

re: Problems with update sql statemnt in vb6

Hi,

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

Regards
Veena

re: Problems with update sql statemnt in vb6

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.

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

re: Problems with update sql statemnt in vb6

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.

re: Problems with update sql statemnt in vb6

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:
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.

re: Problems with update sql statemnt in vb6

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 :

REgards
Veena

re: Problems with update sql statemnt in vb6

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.

re: Problems with update sql statemnt in vb6

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

re: Problems with update sql statemnt in vb6

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

re: Problems with update sql statemnt in vb6

Hi,

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

Some thing Like This :
assuming you connection is Open..
You Can use the Same Code To Loop through FlexGrid Rows..(Just your TextMatrix Row will change)

Regards
Veena

re: Problems with update sql statemnt in vb6

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.

re: Problems with update sql statemnt in vb6

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