vb6 to connect to acess database and insert records into the table

[ponnu2012]

GIVEN below is vbscript to connect to acess database and insert records into the table using forms

Private Sub Command1_Click()

Set rs = cn.Execute("insert into employee values(txtempname.text,txtempid.text,txtssn.text) ")
rs.Close
cn.Close
End Sub

Private Sub Form_Load()
datafile = "C:\mas.accdb"
With cn
.Provider = "microsoft.ACE.OLEDB.12.0"
.ConnectionString = datafile
.Open

End With
End Sub

GLOBAL SECTION

Global cn As New ADODB.Connection
Global rs As New ADODB.Recordset
Global datafile As String





I am getting error "no value given for one or more parameters ".please help me to fix this error?

 

[Golom]

An "INSERT INTO" statement is an action query that doesn't return any records so there's no point in trying to load it into a recordset.

Now ... your INSERT statement ...

You have enclosed the entire statement in quotes which means that the items in the VALUES clause (i.e. txtempname.text,txtempid.text,txtssn.text) will not be resolved. They are simply text strings and not references to variables in your code. As a first step you may need something like

"INSERT INTO employee " & _
"VALUES ( " & txtempname.text & ", " & _
              txtempid.text & ", " & _
              txtssn.text & ")"

The second think to note is that this form of an INSERT INTO assumes that you are inserting values for every field in the table employees because you have not provided a list of the target fields in that table. While that is legal it is usually safer to include a list of the fields that are to receive the values that you are providing.

"INSERT INTO employee (Field1, Field2, Field3) " & _
"VALUES ( " & txtempname.text & ", " & _
              txtempid.text & ", " & _
              txtssn.text & ")"

You may also need quotes around the arguments in the VALUES clause if they resolve to text strings.

 

[ponnu2012]

I did the modification but got error at this line as runtimeerror 424 .object required
Set rs = cn.Execute("insert into employee (empname,empid,ssn) values('" & txtempname.Text & "'," & txtemplid.Text & "," & txtessn.Text & ") ")

 

[MakeItSo]

Perhaps because of this:

txtemp[b][COLOR=#EF2929]l[/color][/b]id.Text

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[Golom]

Try it this way

cn.Execute "INSERT INTO employee (empname, empid, ssn) " & _
            "VALUES ( '" & Replace(txtempname.text,"'","''") & "', '" & _
                           Replace(txtempid.text,"'","''")   & "', '" & _
                           Replace(txtssn.text,"'","''")     & "')"

I have assumed that all three of your values are text and have enclosed them in single quotes. If they are not then remove the quotes around any that are numeric. The 'Replace' function just doubles up single quotes in the strings to avoid conflicts with the enclosing single quotes.

As I said, this is an action query so you can just execute it. You do not need to assign it to a recordset.

What exactly are txtempname.text, txtempid.text and txtssn.text? They look like references to class properties but they may be some other construct.

 

[ponnu2012]

txtempame.text,txtempid.text ad txtss.text are the ames of the texboxes in the form through which user enters the data.