Run-time error ‘-2147217900(80040e14)’

[Bile]

Hi,everyone I am trying to develop a simple program that will register Users with the following requirements as reflected with the fields on the interface:
imgPassport.Picture  ‘This is the Image for accepting Passport Size provided by the user
lblPicture_Name.Caption = "User's Passport Size" ‘This is the Label to display the Passport Size Name when loaded in the Image Box Control (imgPassport)
txtUser_Name.Text  ‘The Name which User will be using to Login the System
cmbPrefix.Text  ‘This is the User’s Prifix for Mr. , Mrs. , Miss or Something Like that
cmbPrivilege.Text  ‘User’s access to the System ie, Administrator and User
txtPassword.Text  ‘The desired Passsword
txtRetype_Password.Text  ‘The Confirmatory Password
-->>
-->> I have a common dialog with the name : dlogPicture
-->> I have a Command Button (named cmdInsert_Picture ) which Uploads the Picture  with the following codes:
Private Sub cmdInsert_Picture_Click()
Dim Picture_Path As String
On Error GoTo Extention_Error
With dlogPicture
        .DialogTitle = "Select a Picture to Upload."
        .Filter = "(*.bmp;*.jpg;*.gif;*.pcx)| *.bmp;*.jpg;*.gif;*.pcx|"
        .ShowOpen
        If .FileName <> "" Then
        Picture_Path = .FileName ‘Nothing to do with this by now
        imgPassport.Picture = LoadPicture(.FileTitle)
        lblPicture_Name.Caption = .FileTitle
        Picture_Name = .FileTitle ‘This is the Variable Declared in my module as public to hold Name of the Picture
        Exit Sub
        ElseIf .FileName = "" Then
        imgPassport.Picture = LoadPicture(GetAppPath() & "\Pictures\Parand.JPG")’Default Picture
        Exit Sub
        End If
End With
Extention_Error:
MsgBox "Sorry the File has Unrecognised Extension,Extension must be .bmp; .jpg; .gif; and .pcx"
End Sub
-->> Now here comes an Error when I press my save Button with the foolowing code(named cmdSave):
Private Sub cmdSave_Click()
Call dbConnect ‘Declared in Module Level
‘The Error is on the Insert Line
Conn.Execute "INSERT INTO Ministers_AccountTB(User_Name,Password,Prefix,Privilege,Passport) VALUES ('" & txtUser_Name.Text & "','" & txtRetype_Password.Text & "','" & cmbPrefix.Text & "','" & cmbPrivilege.Text & "','" & Picture_Name & "')"
   Conn.Close
   Set Conn = Nothing
End Sub
-->> The Error Code I get is as Follows:
Run-time error ‘-2147217900(80040e14)’:
Syntax error in INSERT INTO statement.
-->>I don’t have a clue what Am I missing here,Help pliz.
Regards.

[Jx_Man]

I believe that problem is Picture_Name.
You put Picture_Name in your insert statement but it never been declared in CmdSave.
Why you don't use lblPicture_Name? it hold picture name too.

Conn.Execute "INSERT INTO Ministers_AccountTB(User_Name,Password,Prefix,Privilege,Passport) VALUES ('" & txtUser_Name.Text & "','" & txtRetype_Password.Text & "','" & cmbPrefix.Text & "','" & cmbPrivilege.Text & "','" & lblPicture_Name.Caption & "')"

[AndreRet]

Dim Picture_Path As String
On Error GoTo Extention_Error
With dlogPicture
        .DialogTitle = "Select a Picture to Upload."
        .Filter = "(*.bmp;*.jpg;*.gif;*.pcx)| *.bmp;*.jpg;*.gif;*.pcx|"
        .ShowOpen
        If .FileName <> "" Then
        Picture_Path = .FileName ‘Nothing to do with this by now
        imgPassport.Picture = LoadPicture(.FileTitle)
        lblPicture_Name.Caption = .FileTitle
        Picture_Name = .FileTitle ‘This is the Variable Declared in my module as public to hold Name of the Picture

        As Jx pointed out, you attach a value to Picture_Name where it has not been declared... Do the following...

        Dim Picture_Path As String, Picture_Name as string
On Error GoTo Extention_Error
With dlogPicture
        .DialogTitle = "Select a Picture to Upload."
        .Filter = "(*.bmp;*.jpg;*.gif;*.pcx)| *.bmp;*.jpg;*.gif;*.pcx|"
        .ShowOpen
        If .FileName <> "" Then
        Picture_Path = .FileName ‘Nothing to do with this by now
        imgPassport.Picture = LoadPicture(.FileTitle)
        lblPicture_Name.Caption = .FileTitle
        Picture_Name = .FileTitle ‘This is the Variable Declared in my module as public to hold Name of the Picture

The value of Picture_Name will now be returned to your Insert statement. I do however agree with JX, use the labels caption, it's less error proned.

[Bile]

-->>Well,I hope my tip on line 23 was NOT so clear BUT the variable is there as see line 23 I refered to "Picture_Name" on my post Or Andre's line 25.
-->>And about the Label Jx I used it at first place then I got the same error thats why I decided to Use the Variables (Picture_Name-To hold Name of the Picture and Picture_Path-To hold the value of the Picture Path) Still working on it and still having the Prob,any thing else?

[AndreRet]

Picture_Name as string

After you have declared the string, did you check if there is an actual file name returned? Use a simple messagebox to check...

msgbox Picture_Name

If a value is returned, check all other values. Also check to see that your field in your database table accepts strings and not maybe integers etc.

[Bile]

-->>I did it as well,I tried to display the name on a Label and the name was there but still trapped with the error...
-->>About my database it was fine untill I got some suggestion of others projects that I should try Memo data type in my database I tried it but didnt work...
-->>Now may be I could use your eyes guys with my attachment to clear things out.
-->>Also Please try to go on Update Account seardh for Angelo and Update it, functions error
Tanx

[AndreRet]

Had a look at your app, major problems there. let me correct most tomorrow morning (my time in hours), will post solution.:)

[Bile]

-->>Tanx AndreRet I'll be glad to see what I'm missing there...
-->>I'll be intouch tommorrow morning (Your time in hours)

[AndreRet]

1) ALWAYS use Option Explicit at the top of EVERY form, module etc. This will force your app to see that everything is declared. That is one of the reasons why you could not pick up on Picture_Name not being declared....

''At the top of your fmCreate_Account code window above Private Sub cmbPrivilege_Change(), paste the following...

Option Explicit

Dim Picture_Path As String, Picture_Name As String

2)DO NOT use the SendKeys command, it is restricted in windows Vista and above. Your app will not run on Vista, Win 7, Win 8.... I have included code to achieve the same result i.e. when user presses ENTER, it will capture the keystroke.

''Under Projects/References select Microsoft Scripting Runtime as a reference...

''Wherever you have used SendKeys "{End}" - replace with the following code...

Dim WshShell As Object

Set WshShell = CreateObject("WScript.Shell")

If KeyAscii = 35 Then ''Keyascii for End
    WshShell.SendKeys "{END}"
End If

''The above code MUST go into the objects KeyPress Event...

3)I have changed most of your code. Your database does not open though so I can not check the field and table names compared to your names used in code. Please give me the table name and field names for your Ministers table AS WELL AS THE text length allowed for each field. As soon as I get these I can finish testing your app. Try and reload your database here again.

[Bile]

-->>Well I'v attached a Text file having the Documentation of my Database if it wont open then it will be help full this time...
-->>But I was wondering..is it necessary to Re-declare again the Variables Picture_Path and Picture_Name on the Form where I need to use them? B'coz I'v declared them already in my Module Level,check the Module:modlUserDefined they are declared Public Picture_Name As String and Public Picture_Path As String and as well The Option Explicit is there...thanks for a Tip of Declaring it on Every Form and Modules as I used only to declare them in Modules...
-->>I'm worried again that I'm using Windows XP,Professional,Version 20002,Service Pack 3 so my Application may fail to work if most of the Function I'm using are NOT compatible wiht New Version of Windows...may I know how to know which functions are compatible with what Windows? May be my Books are a little bit Old if I should get some more advanced one I may do that if I'll have the Titles...

[AndreRet]

But I was wondering..is it necessary to Re-declare again the Variables Picture_Path and Picture_Name

Sorry, my bad. Did not open the module, will check. If it was declared in your module, all fine then.

I'm worried again that I'm using Windows XP,Professional,Version 20002,Service Pack 3 so my Application may fail to work

Sendkeys are the worst culprit here, have a look at the code I posted to replace it. Also have a look at my tutorial HERE wich gives you all the options to run on win vista onwards.

I'll have a look at the textfile tomorrow and will then correct your code. :)

[dspnhn]

please intiate transaction and then commit it to database

Conn.begintrans
conn.execute (<your code>)
conn.committrans

[AndreRet]

Firstly, your table names according to the text file attached differs from the ones you have used in your code - User_Name, Password, Prefixe, Privilegee, Passporte were the 5 fields you gave me... :)

No problem however, I have removed the field names from your insert into statement because you were writing to ALL fields, you did not have to show them all. ONLY when you are writing to some fields do you have to include it. That solved the problem and the data saved 100%.

''Use the following...

Conn.Execute "INSERT INTO Ministers VALUES ('" & txtUser_Name.Text & "', '" & txtRetype_Password.Text & "', '" & cmbPrefix.Text & "', '" & cmbPrivilege.Text & "','" & Picture_Name & "')"

[Bile]

-->>thanks dspnhn I tried it but the arror persisted...
-->>And Andre U did it!!! My data are 100% Saved...though on Update still a mess.
-->>So is it true what I'm thinking that the use of Showing off all the fields was the Problem here? and if so I'm wondering how can I escape it to do the Update!?
-->>And as you said the Sendkeys "{End}" rised against me on Windows 7 Profesional and the New one you posted (Code doesnt do what Im especting,a Hint is I want when the user type in any text box the first letter to be in upper case is that what the Code was doying?),I'v changed my OS now and the stated one is the Current used.
-->>I went through your Tutorial and succeeded with lot of problems but at last Installed my VB 6.0 on my Computer may be bcoz most of the terms used not familiar with me as I'm just a self trained student in VB 6.0
-->>Now I have to go through a hell of the job debuging the codes and whenever I got stuck I hope you guyz 'll be alerted with my alarm.
-->>My all regards Andre,dspnhn and JX Man

[AndreRet]

It was only a pleasure Bile. :)

And as you said the Sendkeys "{End}" rised against me on Windows 7 Profesional and the New one you posted (Code doesnt do what Im especting

Open a new thread and I'll walk you through it. :)

I went through your Tutorial and succeeded with lot of problems but at last Installed my VB 6.0 on my Computer

Again, just ask and we will help where we can...

though on Update still a mess.
Open a new thread here too, I'll post your solution, already done. ;)

[Bile]

-->>Now the Sendkeys"{End}" thing is done,but thanx in advance as its the same code you posted to me I'v used just a little modifications as follows:
-->>I declared Public WshShell As Object on the Module level
-->>Then every where I used the Sendkeys"{End}" code I'v replaced it with WshShell.SendKeys "{END}" but before using the code I just set the Object first with Your code Set WshShell = CreateObject("WScript.Shell") and sure it worked out,Tanx alot and hope my new thread seen alredy and just waiting to see the solution you got.

[AndreRet]

Only a pleasure, will look at your new thread now. :)