Visual Basic 6 and SQL problem

EirePlane

EirePlane

EirePlane

Associate
Joined
3 Aug 2006
Posts
610
Location
London, UK
I've got a program i'm coding in Visual Basic 6 and it's linked to an Access 2003 database. I can pull records from a table ok, but I can't seem to find a way to insert them. No matter what I do I get an error. Is there something wrong with my SQL command or is my code just wrong?

Code: 
Public Workbase As Database
Public WorkRS1 As Recordset
Public SQL As String

Private Sub cmdAddCustomer_Click()
    Set Workbase = OpenDatabase("database.MDB")
    
    SQL = "SELECT SchoolID FROM School WHERE SchoolName='" & cmbSchool.Text & "'"
    Set WorkRS1 = Workbase.OpenRecordset(SQL)
    
    SchoolID = WorkRS1.Fields.Item("SchoolID")
    
    CustomerAddress = txtHouseNo.Text & " " & txtStreet.Text & " " & txtTown.Text & " " & txtPostcode.Text
    
    txtDebug.Text = CustomerAddress
    
    SQL = "INSERT INTO Customer(CustomerTitle, CustomerName, CustomerSurname, CustomerAddress, CustomerTel, SchoolID)VALUES('" & cmbTitle.Text & "','" & txtFirstName.Text & "','" & txtSurname.Text & "','" & CustomerAddress & "','" & txtTelephone.Text & "','" & SchoolID & "','"
    Set WorkRS1 = Workbase.OpenRecordset(SQL)
    
    CloseDB
End Sub

Everything is running ok until I get to the second SQL Line. Basically I am trying to enter the text from several text boxes into the fields listed but when I click the command button, I get the message
Run-time error '3346':

Number of query values and destination fields are not the same.
Click to expand...

Any Ideas?

Cheers,
Mitchel
 
I assume OpenDatabase is a function you've written that returns an ADO connection?

If so you can do this like the below. It's similar to what you're doing where you run the INSERT statement through ADO, except you need to do it through the connection object, not the recordset..

Code: 
Public Workbase As Database
Public WorkRS1 As Recordset
Public SQL As String

Private Sub cmdAddCustomer_Click()
    Set Workbase = OpenDatabase("database.MDB")
    
    SQL = "SELECT SchoolID FROM School WHERE SchoolName='" & cmbSchool.Text & "'"
    Set WorkRS1 = Workbase.OpenRecordset(SQL)
    
    SchoolID = WorkRS1.Fields.Item("SchoolID")
    
    CustomerAddress = txtHouseNo.Text & " " & txtStreet.Text & " " & txtTown.Text & " " & txtPostcode.Text
    
    txtDebug.Text = CustomerAddress
    
    SQL = "INSERT INTO Customer(CustomerTitle, CustomerName, CustomerSurname, CustomerAddress, CustomerTel, SchoolID)VALUES('" & cmbTitle.Text & "','" & txtFirstName.Text & "','" & txtSurname.Text & "','" & CustomerAddress & "','" & txtTelephone.Text & "','" & SchoolID & "','"
    Workbase.Execute SQL
    
    CloseDB
End Sub
 
The error message explains where you are going wrong:
Number of query values and destination fields are not the same
Click to expand...

If you look at your INSERT statement;

SQL = "INSERT INTO Customer(CustomerTitle, CustomerName, CustomerSurname, CustomerAddress, CustomerTel, SchoolID)VALUES('" & cmbTitle.Text & "','" & txtFirstName.Text & "','" & txtSurname.Text & "','" & CustomerAddress & "','" & txtTelephone.Text & "','" & SchoolID & "','"
Click to expand...

Your last bit is "','" - I think this should read ')"

Your comma is stating that you have another parameter to send [which you don't], and you need to close up the INSERT statement [with a )] and close the SQL string [with "].
 
Bah! Missed the extra comma, I think I need stronger glasses. cjm's fix is what you need, although my post about directly executing SQL statements that don't return a recordset through the connection object is still good practice :)
 
cjmUK said:
The error message explains where you are going wrong:

If you look at your INSERT statement;



Your last bit is "','" - I think this should read ')"

Your comma is stating that you have another parameter to send [which you don't], and you need to close up the INSERT statement [with a )] and close the SQL string [with "].
Click to expand...

I missed that, Thank you, although I still got an error because I was trying to add values using OpenRecordset()

Spunkey said:
I assume OpenDatabase is a function you've written that returns an ADO connection?

If so you can do this like the below. It's similar to what you're doing where you run the INSERT statement through ADO, except you need to do it through the connection object, not the recordset..

Code: 
Public Workbase As Database
Public WorkRS1 As Recordset
Public SQL As String

Private Sub cmdAddCustomer_Click()
    Set Workbase = OpenDatabase("database.MDB")
    
    SQL = "SELECT SchoolID FROM School WHERE SchoolName='" & cmbSchool.Text & "'"
    Set WorkRS1 = Workbase.OpenRecordset(SQL)
    
    SchoolID = WorkRS1.Fields.Item("SchoolID")
    
    CustomerAddress = txtHouseNo.Text & " " & txtStreet.Text & " " & txtTown.Text & " " & txtPostcode.Text
    
    txtDebug.Text = CustomerAddress
    
    SQL = "INSERT INTO Customer(CustomerTitle, CustomerName, CustomerSurname, CustomerAddress, CustomerTel, SchoolID)VALUES('" & cmbTitle.Text & "','" & txtFirstName.Text & "','" & txtSurname.Text & "','" & CustomerAddress & "','" & txtTelephone.Text & "','" & SchoolID & "','"
    Workbase.Execute SQL
    
    CloseDB
End Sub
Click to expand...

This worked like a charm, thanks to both of you.
Opendatabase() is just a standard procedure already in VB, however the CloseDB line at the bottom is my own procedure

Deanje said:
I was going to suggest it's a malformed Insert statement, too. ^
Click to expand...

Would you mind explaining how it's malformed? Any way I can clean it up the better
 
EirePlane said:
Would you mind explaining how it's malformed? Any way I can clean it up the better
Click to expand...

Just one comment.
Taking the user input straight out of the text box probably isn't a great idea.
Not that it should be a problem if this app is going to be used by you but what if someone happened to type the following into the txtFirstName box:

Code: 
'','','','',''); DELETE CUSTOMER;--

Simon
 
Parameterized queries will remove that vulnerability, but I'm guessing the OP's application isn't exactly 'front-line' and he's probably got more pressing issues.
 
SimonCHere said:
Just one comment.
Taking the user input straight out of the text box probably isn't a great idea.
Not that it should be a problem if this app is going to be used by you but what if someone happened to type the following into the txtFirstName box:

Code: 
'','','','',''); DELETE CUSTOMER;--

Simon
Click to expand...

True, but it's only an A Level project. They barely expect me to use Visual Basic, let alone compensate for SQL Injections
 
You must log in or register to reply here.
Back
Top Bottom