Excel to vba add data to a table

[yogeshtyagi100]

Hi
I have a database. And I want to add data to this database by insert into command but I am getting an error stating that “No value given for one or more required parameters” please help me on this issue. I am not understanding why this is happening.</SPAN>
Below is the code.</SPAN>
Sub newuser1()</SPAN>
Dim cnt As ADODB.Connection</SPAN>
Dim rst1 As ADODB.Recordset, rst2 As ADODB.Recordset</SPAN>
Dim stDB As String, stSQL1 As String, stSQL2 As String</SPAN>
Dim stConn As String</SPAN>
Dim wbBook As Workbook</SPAN>
Dim wsSheet1 As Worksheet</SPAN>
Dim lnField As Long, lnCount As Long</SPAN>
Dim id As String</SPAN>
Dim password As String</SPAN>


'Instantiate the ADO-objects.</SPAN>
Set cnt = New ADODB.Connection</SPAN>
Set rst1 = New ADODB.Recordset</SPAN>


'Path to the database.</SPAN>
stDB = "H:\db1.mdb"</SPAN>

'Create the connectionstring.</SPAN>
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _</SPAN>
& "Data Source=" & stDB & ";"</SPAN>


id = newuser.TextBox1.Value</SPAN>
password = newuser.TextBox2.Value</SPAN>
Dim id1 As String</SPAN>
Dim password1 As String</SPAN>
id1 = CStr(id)</SPAN>
password1 = CStr(password)</SPAN>

stSQL1 = "insert into login99 values (id1 , password1)"</SPAN>

With cnt</SPAN>
.Open (stConn) 'Open the connection.</SPAN>
.CursorLocation = adUseClient 'Necessary to disconnect the recordset.</SPAN>
End With</SPAN>
With rst1</SPAN>
.Open stSQL1, cnt 'Create the recordset.</SPAN>
Set .ActiveConnection = Nothing 'Disconnect the recordset.</SPAN>
End With</SPAN>







End Sub</SPAN>

 

[iliace]

This part:

stSQL1 = "insert into login99 values (id1 , password1)"

probably needs to concatenate the variables, such as:

stSQL1 = "insert into login99 values (" & id1 & " , " & password1 & ")"

I think you need to create a Command object to do the actual insertion.

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnt
cmd.CommandText = stSQL1
cmd.Execute

Untested.

 

[yogeshtyagi100]

Thanks a lot first one suggesion is working i appriciate your help very much