code for DB access in VB6.0

[isha]

I want to know the correct sql statement for insertion of data(name,address....) from textboxes into database(MSACEESS) .

Private Sub cmdinsert_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New Recordset
Set cn = New Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\student.mdb;Persist Security Info=False"
If cn.State = adStateOpen Then
cn.Close
End If
cn.Open
rs.Open "select * from studentdetails", cn, adOpenStatic
While rs.BOF = False
If rs.State = adStateOpen Then
rs.Close
End If
rs.Open "insert into studentdetails values ('&txtname.text','&txtage.text','&txtcity.text','&txtphone.text') ", cn, adOpenStatic
MsgBox "successfully inserted"
rs.MoveNext
Wend
End Sub

thanks

[daninmanchester]

The problem with the statement you have is that you don't specify the fields you are inserting......

"insert into studentdetails
(Name, Age, City, Phone)
values ('&txtname.text','&txtage.text','&txtcity.text','&txtphone.text') "

but also you should execute this using a connection. e.g.
cn.exeucte("SQL Command goes here")

as well as this you should be aware that it is very bad practice to build dynamic SQL in this way and you should use parameterised queries with an ADODB command to avoid SQL injection attacks.

[smeagol]

It's not necessary to specify the fields if the table is structured in the same way as the sql.

At first glance I'd say the problem is with the way the SQL statement is concatenated.

It should be:

rs.Open "insert into studentdetails values ('" & txtname.text & "','" & txtage.text & "','" & txtcity.text & "','" & txtphone.text & "')", cn, adOpenStatic