Save record in Database

[smugcool]

HI,

I have created a form with various text boxes around.... i want as soon as sombody will click on the save button the data will be saved in a database called mydb.mdb......i m a very newbie to this vb....kindly help.....i have written code as below.....but geting an error of "c:\incidentreport\incidentreportmydb.mdb not found"

Whereas i am keeping the database in the same path as the error is coming.

Private Sub cmdAdd_Click()
Dim conec As New ADODB.Connection
Dim rs As New ADODB.Recordset
conec.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "MyDB.mdb'"
conec.CursorLocation = adUseClient
conec.Open
rs.Open "select * from clients where Client_Id='" & txtUserName.Text & "'", conec, adOpenKeyset, adLockOptimistic
If txtUserName.Text = "" Then
MsgBox "Please fill the Client Id Number", vbCritical
Else
With rs
' Search for identical/matching STUDENTCODE

If rs.RecordCount = 0 Then '---> If there's no identical/matching Clientid, then save as new data
.AddNew
End If
!clientid = txtUserName.Text
!clientname = txtName_First.Text

!clientADDRESS = txtName_Last.Text
.Update
End With
End If

End Sub


kindly help me in geting this minor problem resolved.

when i debug error gets highlighted in cn.open

[smugcool]

Private Sub cmdAdd_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strMyDB & ";"
cn.CursorLocation = adUseClient
cn.Open
strSQL = "SELECT * FROM clients" & " WHERE client_id=" & FixApostrophies(txtClient_Id.Text)
rs.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic
If txtName_First.Text = "" Then
MsgBox "Please fill the Student Code Number", vbCritical
Else
With rs
' Search for identical/matching Clientid

If rs.RecordCount = 0 Then '---> If there's no identical/matching STUDENTCODE, then save as new data
.AddNew
End If
!clientCODE = txtUsername.Text
!clientNAME = txtName_First.Text

!clientADDRESS = txtName_Last.Text
.Update
End With
End If

End Sub

i have resolved the earilier problem now i got stuck with some other problem..

and i am geting syntex error missing some query expression in 'client_Id='

I am not geting any idea of how to deal with it....please somebody help me.

below is the code what i did

Private Sub cmdAdd_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strMyDB & ";"
cn.CursorLocation = adUseClient
cn.Open
strSQL = "SELECT * FROM clients" & " WHERE client_id=" & FixApostrophies(txtClient_Id.Text)
rs.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic
If txtName_First.Text = "" Then
MsgBox "Please fill the Student Code Number", vbCritical
Else
With rs
' Search for identical/matching Clientid

If rs.RecordCount = 0 Then '---> If there's no identical/matching STUDENTCODE, then save as new data
.AddNew
End If
!clientCODE = txtUsername.Text
!clientNAME = txtName_First.Text

!clientADDRESS = txtName_Last.Text
.Update
End With
End If

End Sub

[hariharanmca]

conec.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "MyDB.mdb'"

You have to check here
App.Path & "MyDB.mdb'"
that should be
App.Path & "\MyDB.mdb'"

like

conec.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "\MyDB.mdb'"

[smugcool]

Thanx hariharan

i got rid of this problem..

Now the problem is somthing different.

Now i am geting syntex error missing some query expression in 'client_Id='

I am not geting any idea of how to deal with it....please somebody help me.
See here the code.

Private Sub cmdAdd_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strMyDB & ";"
cn.CursorLocation = adUseClient
cn.Open
strSQL = "SELECT * FROM clients" & "WHERE client_id="" "
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
If txtName_First.Text = "" Then
MsgBox "Please fill the Student Code Number", vbCritical
Else
With rs
' Search for identical/matching Clientid

If rs.RecordCount = 0 Then '---> If there's no identical/matching STUDENTCODE, then save as new data
.AddNew
End If
!clientCODE = txtUsername.Text
!clientNAME = txtName_First.Text

!clientADDRESS = txtName_Last.Text
.Update
End With
End If

End Sub

[hariharanmca]

strSQL = "SELECT * FROM clients" & "WHERE client_id="" "

that also simple.
just check this line, you gave 3 double quotes there is no value to select in where clause.

strSQL = "SELECT * FROM clients WHERE client_id=" & valNumericToSelect & ""

this will better

[QVeen72]

Thanx hariharan

i got rid of this problem..

Now the problem is somthing different.

Now i am geting syntex error missing some query expression in 'client_Id='

I am not geting any idea of how to deal with it....please somebody help me.
See here the code.

Private Sub cmdAdd_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strMyDB & ";"
cn.CursorLocation = adUseClient
cn.Open
strSQL = "SELECT * FROM clients" & "WHERE client_id="" "
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
If txtName_First.Text = "" Then
MsgBox "Please fill the Student Code Number", vbCritical
Else
With rs
' Search for identical/matching Clientid

If rs.RecordCount = 0 Then '---> If there's no identical/matching STUDENTCODE, then save as new data
.AddNew
End If
!clientCODE = txtUsername.Text
!clientNAME = txtName_First.Text

!clientADDRESS = txtName_Last.Text
.Update
End With
End If

End Sub

Hi,

Not very Sure, What is the field name of ID? coz, in SQL Statement u search for "client_id" and in ur RS AddNew, u refer to as "ClientCode".
may be the table does not have client_id as field name...?

REgards
Veena