By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Save record in Database

P: 81
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
Sep 18 '07 #1
Share this Question
Share on Google+
5 Replies


P: 81
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
Sep 18 '07 #2

hariharanmca
100+
P: 1,977
Expand|Select|Wrap|Line Numbers
  1. 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

Expand|Select|Wrap|Line Numbers
  1. conec.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "\MyDB.mdb'"
Sep 18 '07 #3

P: 81
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
Sep 18 '07 #4

hariharanmca
100+
P: 1,977
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM clients WHERE client_id=" & valNumericToSelect & ""
this will better
Sep 18 '07 #5

QVeen72
Expert 100+
P: 1,445
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
Sep 18 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.