About Updating a Record in adodb

Question

ryan311 -7 Posting Whiz in Training

15 Years Ago

Private Sub Command1_Click()
If Combo1.Text = "" Then
MsgBox "Please Choose Supplier Code", vbInformation
Combo1.SetFocus
Exit Sub
End If
If Combo2.Text = "" Then
MsgBox "Please Choose Product Code", vbInformation
Combo2.SetFocus
Exit Sub
End If
If Text1.Text = "" Then
MsgBox "Please Input the Quantity", vbInformation
Text1.SetFocus
Exit Sub
End If
If Text3.Text = "" Then
MsgBox "Please Input the Amount", vbInformation
Text3.SetFocus
Exit Sub
End If
rs.Open "Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "','" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "','" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "','" + Text7.Text + "')", cn, adOpenKeyset, adLockPessimistic
Set rs = Nothing
rs1.Open "select * from Product where pcode='" & Combo2.Text & "'", cn, adOpenKeyset, adLockPessimistic
If rs1.RecordCount > 0 Then
rs1.Fields![pstock] = Val(rs1.Fields![pstock]) + Val(Text1.Text)
rs1.Update
rs.Close
MsgBox "The Product has been save successfully", vbInformation
Combo1.Text = ""
Combo2.Text = ""
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Call Form_Load
Else
MsgBox "OHH NO!", vbInformation
End If
End Sub

i have a problem everytime i click my command1 button can anyone help me about my problem? please? badly needed rush! please help me god bless daniweb!

error is on about in INSERT INTO

visual-basic

2 Contributors
25 Replies
170 Views
1 Day Discussion Span
Latest Post 15 Years Ago Latest Post by aktharshaik

aktharshaik 16 Posting Whiz

15 Years Ago

Corrections

rs.Open "Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "','" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "','" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "','" + Text7.Text + "')", cn, adOpenKeyset, adLockPessimistic

must be

cn.Execute "Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "','" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "','" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "','" + Text7.Text + "')"

and

rs1.Open "select * from Product where pcode='" & Combo2.Text & "'", cn, adOpenKeyset, adLockPessimistic

must be

rs1.Open "select * from Product where pcode='" & Combo2.Text & "'", cn, adOpenDynamic, adLockOptimistic

remove the statement

rs.close

Regards
Shaik Akthar

Edited 11 Years Ago by Reverend Jim because: Fixed formatting

aktharshaik 16 Posting Whiz

15 Years Ago

check out the syntax. u have added single quotes to the numeric fields also. remove them.

'" + Combo1.Text + "','" + Combo2.Text + "','" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "','" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "','" + Text7.Text + "'
'must be
'" + Combo1.Text + "','" + Combo2.Text + "'," + Text1.Text + "," + Text2.Text + "," + Text3.Text + "," + Text4.Text + "," + Text5.Text + "," + Text6.Text + "," + Text7.Text + ")"

I am unable to figure out the numeric fields from ur query, but that's the problem.

Put quotes for text fields and remove them for numeric fields.

Regards
Shaik Akthar

aktharshaik 16 Posting Whiz

15 Years Ago

post the code again now,

and which of ur fields are Text and which of them are numeric in the database

and also the error message please.

Regards
Shaik Akthar

aktharshaik 16 Posting Whiz

15 Years Ago

post the code again now,

and which of ur fields are Text and which of them are numeric in the database

and also the error message please.

Regards
Shaik Akthar

aktharshaik 16 Posting Whiz

15 Years Ago

ur code is

"Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "'," + Text1.Text + "," + Text2.Text + "," + Text3.Text + "," + Text4.Text + "," + Text5.Text + "," + Text6.Text + "," + Text7.Text + ")"

I assume scode, pcode, unit, pcarrier, pterms, drnumber, date are Text fields
and quantity, amount are Numeric fields
hence

"Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "'," + Text1.Text + ",'" + Text2.Text + "'," + Text3.Text + ",'" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "','" + Text7.Text + "')"

also remove the
, cn, adOpenKeyset, adLockPessimistic
in the insert statement.

Regards
Shaik Akthar

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ryan311 -7 Posting Whiz in Training · Answer 1 · 2008-12-01T20:19:37+00:00

ryan311 -7 Posting Whiz in Training

15 Years Ago

still have a synthax error :(

ryan311 -7 Posting Whiz in Training · Answer 2 · 2008-12-01T20:21:06+00:00

ryan311 -7 Posting Whiz in Training

15 Years Ago

in the insert into

ryan311 -7 Posting Whiz in Training · Answer 3 · 2008-12-01T20:32:07+00:00

ryan311 -7 Posting Whiz in Training

15 Years Ago

huhuhu sir still an error ocured in insert into

ryan311 -7 Posting Whiz in Training · Answer 4 · 2008-12-01T20:36:01+00:00

ryan311 -7 Posting Whiz in Training

15 Years Ago

wrong number of agruments thats is the eror!

ryan311 -7 Posting Whiz in Training · Answer 5 · 2008-12-01T20:39:07+00:00

If Combo1.Text = "" Then
MsgBox "Please Choose Supplier Code"
Combo1.SetFocus
Exit Sub
End If
If Combo2.Text = "" Then
MsgBox "Please Choose Product Code"
Combo2.SetFocus
Exit Sub
End If
If Text1.Text = "" Then
MsgBox "Please Input the Quantity"
Text1.SetFocus
Exit Sub
End If
If Text3.Text = "" Then
MsgBox "Please Input the Amount"
Text3.SetFocus
Exit Sub
End If
cn.Execute "Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "'," + Text1.Text + "," + Text2.Text + "," + Text3.Text + "," + Text4.Text + "," + Text5.Text + "," + Text6.Text + "," + Text7.Text + ")", cn, adOpenKeyset, adLockPessimistic
rs1.Open "select * from Product where pcode='" & Combo2.Text & "'", cn, adOpenDynamic, adLockOptimistic
If rs1.RecordCount > 0 Then
rs1.Fields![pstock] = Val(rs1.Fields![pstock]) + Val(Text1.Text)
rs1.Update
rs1.Close
MsgBox "The Product has been save successfully", vbInformation
Combo1.Text = ""
Combo2.Text = ""
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Call Form_Load
Else
MsgBox "OHH NO!", vbInformation
End If

Compile Error:

wrong Number of agruments or invalid property assignment

ryan311 -7 Posting Whiz in Training · Answer 6 · 2008-12-01T20:50:18+00:00

ryan311 -7 Posting Whiz in Training

15 Years Ago

syntax error in inert into

aktharshaik 16 Posting Whiz · Answer 7 · 2008-12-01T20:52:32+00:00

what is the datatype of the field 'date' in your database and is it MS-Access Database?

aktharshaik 16 Posting Whiz · Answer 8 · 2008-12-01T20:53:20+00:00

just once give me all the fields with their datatypes with which they exist in the database in the table Deliver

ryan311 -7 Posting Whiz in Training · Answer 9 · 2008-12-02T19:36:02+00:00

ryan311 -7 Posting Whiz in Training

15 Years Ago

the date is Date/time and the rest is text

aktharshaik 16 Posting Whiz · Answer 10 · 2008-12-02T19:41:18+00:00

If the database is MS-Access u have to use a # for date field.
It goes like this. replace the query with this one.

"Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "'," + Text1.Text + ",'" + Text2.Text + "'," + Text3.Text + ",'" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "',#" + Text7.Text + "#)"

ryan311 -7 Posting Whiz in Training · Answer 11 · 2008-12-02T19:57:29+00:00

still having a error

synthax error in insert into, how can i upload my files here? can u fix it?

aktharshaik 16 Posting Whiz · Answer 12 · 2008-12-02T20:02:54+00:00

just zip the access database file and the vbform.

Click on Reply to Thread button. do not post in quick reply.
below the reply box u can find the button for attachments. there u select the zip file and upload it.

aktharshaik 16 Posting Whiz · Answer 13 · 2008-12-02T20:06:11+00:00

aktharshaik 16 Posting Whiz

15 Years Ago

i have enclosed a screenshot of the reply screen

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with x-ms-bmp files.

abc1.bmp (338.04 KB)

ryan311 -7 Posting Whiz in Training · Answer 14 · 2008-12-02T20:11:48+00:00

ryan311 -7 Posting Whiz in Training

15 Years Ago

here it is!

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

Inventory_System.zip (46.42 KB)

aktharshaik 16 Posting Whiz · Answer 15 · 2008-12-02T20:17:05+00:00

All the fields in the Deliver table are Text except for the Date Field.

"Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, date) Values ('" + Combo1.Text + "','" + Combo2.Text + "','" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "','" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "',#" + Text7.Text + "#)"

ryan311 -7 Posting Whiz in Training · Answer 16 · 2008-12-02T20:21:17+00:00

ryan311 -7 Posting Whiz in Training

15 Years Ago

cn.execute "insert into? right?

aktharshaik 16 Posting Whiz · Answer 17 · 2008-12-02T20:26:52+00:00

Hey Got the problem.

Actually the field with the name "date" is actually a reserved keyword.

just put square brackets around that field in the query.

her it is

"Insert Into Deliver (scode, pcode, quantity, unit, amount, pcarrier, pterms, drnumber, [date]) Values ('" + Combo1.Text + "','" + Combo2.Text + "','" + Text1.Text + "','" + Text2.Text + "','" + Text3.Text + "','" + Text4.Text + "','" + Text5.Text + "','" + Text6.Text + "',#" + Text7.Text + "#)"

aktharshaik 16 Posting Whiz · Answer 18 · 2008-12-02T20:28:47+00:00

a better suggestion is change the name of the field in every table with the fieldname "date" as "mdate" or "edate" or "ddate" or anything else , other than the spelling "date"

ryan311 -7 Posting Whiz in Training · Answer 19 · 2008-12-02T21:25:32+00:00

thank you so much shaik akthar it helps a lot of me thanks! god bless u!

aktharshaik 16 Posting Whiz · Answer 20 · 2008-12-03T01:08:08+00:00

If ur problem is solved then Mark the thread as solved, b'cos it may help even the others to search for similar problem and those threads having the notation of solved.