2 Replies - 1607 Views - Last Post: 21 November 2012 - 04:53 AM Rate Topic: -----

#1 GuardianAlien  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 20-November 12

ADDING NEW RECORDS IN VB 6.0 USING ADODB

Posted 20 November 2012 - 03:21 PM

Hello!
I'm trying to put an add function in my program. Here's how I did it:
Private Sub cmdreg_Click()
If txtsn.Text = "" Or txtpw.Text = "" Then
MsgBox "All fields are required!", vbOKOnly + vbInformation, "ERROR"
Else
Dbase
Set rs = db.Execute("INSERT INTO TBL_STUDREG (StudentNumber, SPassword) VALUES (txtsn.text, txtpw.text)")
rs.Update
MsgBox "Record Saved!", vbOKOnly + vbInformation, "SUCCESS"
db.Close
End If
End Sub


I don't understand why it won't work. I always get "No value given for one or more required parameters."
For this program I use a module. These are the codes in it:
Public db As New ADODB.Connection
Public rs As New ADODB.Recordset

Public Sub Dbase()
db.CursorLocation = adUseClient
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=LIBRARY.mdb"
End Sub


This post has been edited by BobRodes: 20 November 2012 - 08:50 PM


Is This A Good Question/Topic? 0
  • +

Replies To: ADDING NEW RECORDS IN VB 6.0 USING ADODB

#2 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: ADDING NEW RECORDS IN VB 6.0 USING ADODB

Posted 20 November 2012 - 08:52 PM

Don't use recordsets unless you are using a SELECT query. Use the Command object. Suppose you have a go at doing that and see if you get your record inserted. Then if you still have trouble, post back, but this time PLEASE tell us the line of code that is causing your error. It's important.
Was This Post Helpful? 0
  • +
  • -

#3 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: ADDING NEW RECORDS IN VB 6.0 USING ADODB

Posted 21 November 2012 - 04:53 AM

Generally, this error means that you try to run a sql statement and you don't supply values to one of the columns specified in that statement.
In your case
 db.Execute("INSERT INTO TBL_STUDREG (StudentNumber, SPassword) VALUES (txtsn.text, txtpw.text)")



you try to insert values in StudentNumber and SPassword fields. But, the result of what you wrote is something else than you expect or db engine expects:
INSERT INTO TBL_STUDREG (StudentNumber, SPassword) VALUES (txtsn.text, txtpw.text)


On db engine the above statement will run. So, best case scenario, you will get txtsn.text and txtpw.text in your fields. Now, I said best case scenarion, which would be the case when string values doesn't require quotes. It's not the case, SQL standard specifies that every string value should be enclosed in quotes. Like
 db.Execute("INSERT INTO TBL_STUDREG (StudentNumber, SPassword) VALUES ('txtsn.text', 'txtpw.text')"


Now, you will not get any error. But, also it is wrong because you don't insert the values in those textboxes. You have to options here: an ugly one and a pretty one.
The ugly one is to compose your statement by concatenation.
 db.Execute("INSERT INTO TBL_STUDREG (StudentNumber, SPassword) VALUES ('" & txtsn.text & "', ' txtpw.text &"')"


The pretty one is to use parameters. But this requires a Command object. Have look on the internet, there are many examples.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1