• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

Using SQL's Insert command with VB6 and an Access 97 database

Hey all, first time user. OK, the question. :)

Private Sub add_customer_Click()
Dim ItemCode As String
        ItemCode = item_code_box.Text
        Dim CustName As String
        CustName = item_code_box.Text
        Dim CustPhone As String
        CustPhone = item_code_box.Text
        Data1.RecordSource = "INSERT INTO PhoneList (PhoneNo, ProdItemCode, CustomerName) VALUES ('" & ItemCode & "', '" & CustName & "', '" & CustPhone & "')"
        MsgBox "Customer added to database."
End Sub

It isn't adding the information in the text boxes to the database (the table = PhoneList). Where am I going wrong?

---Overlord
0
Overlord44
Asked:
Overlord44
  • 6
  • 6
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
You need to execute the command (setting the RecordSource is not enough). To execute it, you must Refresh (data1.refresh).

You should consider not to use the data control to do action queries (insert, update, delete). In ADO, you can use Connection.Execute
0
 
Overlord44Author Commented:
I added data1.refresh in straight after the Data1.RecordSource = blah blah blah line. Now it's given a runtime error 3129-invalid operation, and highlighted the data1.refresh in yellow...
0
 
Éric MoreauSenior .Net ConsultantCommented:
In this case create a command to run it. Are you using DAO or ADO?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Overlord44Author Commented:
"Create a command"? How? I don't know which of those I'm using... does "Microsoft Jet Engine" help?

Heh, I'm a newbie at this... :)
0
 
Éric MoreauSenior .Net ConsultantCommented:
why don't you use the AddNew method:

with data1
   .recorsource="select * from phonelist"
   .refresh
   .addnew
   .fields("PhoneNo").value = CustPhone
   .fields("ProdItemCode").value = ItemCode
   .fields("CustomerName").value = CustName
   .update
end with
0
 
Overlord44Author Commented:
Where do I put that in? And do I put it in exactly as shown, or with data1 before all the dots?
0
 
Éric MoreauSenior .Net ConsultantCommented:
>>Where do I put that in?

in your add_customer_Click event.


>>And do I put it in exactly as shown, or with data1 before all the dots?

Exactly as is. When you use the With statement, you don't need to repeat data1 on each line.
0
 
i014354Commented:
Use ADO instead:

Dim db As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL as string

strSQL = "INSERT INTO PhoneList (PhoneNo, ProdItemCode, CustomerName) VALUES ('" & ItemCode & "', '" & CustName & "', '" & CustPhone & "')"


    Set db = New adodb.Connection
    Set cmd = New adodb.Command

'Open connection using ODBC Datasource called AccessDB
  db.Open "PROVIDER=MSDASQL;dsn=AccessDB;uid=;pwd=;"

 db.BeginTrans
    cmd.ActiveConnection = db
    cmd.CommandType = adCmdText
    cmd.CommandText = strSQL

    cmd.Execute
   
 db.CommitTrans
0
 
Éric MoreauSenior .Net ConsultantCommented:
Why would you ask someone to add ADO in reference (7.5 meg) to someone that is using DAO? Also, you will have problem refreshing. Updates will not appear as quick if you mic DAO and ADO.
0
 
Overlord44Author Commented:
Method or Data not recognised for ".AddNew"...
0
 
Éric MoreauSenior .Net ConsultantCommented:
I forgot the Recordset object.

with data1
  .recorsource="select * from phonelist"
  .refresh
  with .recordset
    .addnew
    .fields("PhoneNo").value = CustPhone
    .fields("ProdItemCode").value = ItemCode
    .fields("CustomerName").value = CustName
    .update
  end with
end with
0
 
Overlord44Author Commented:
YES! It works! Thankyou!
0
 
Overlord44Author Commented:
Thank you very much!!! I'll be using this place again. :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now