Run-Time error when editing Database

**willakawill** · Dec 20 '06, 08:53 AM

Originally posted by nkechifesie

When I run this code to update my database

Code:

Set AdVehDecp = New ADODB.Connection
Set RsVehDecp = New ADODB.Recordset
AdVehDecp.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\VEHICLE_TRACKER.mdb;"
AdVehDecp.Open
AdVehDecp.CursorLocation = adUseClient
RsVehDecp.Open "SELECT * FROM qryVehSearch;", AdVehDecp, adOpenDynamic, adLockPessimistic
VehNo = Text1(4).Text
            StrSql = "UPDATE tblVEHLIC SET dtmVLISS = '" & Text2(0).Text & "', dtmVLEXP = '" & Text2(1).Text & "'" _
                    & "WHERE txtVEHNO = " & VehNo & ";"
            AdVehDecp.Execute StrSql
            StrSql = "UPDATE tblHPERT SET dtmHPISS = '" & Text2(2).Text & "', dtmHPEXP = '" & Text2(3).Text & "'" _
                    & "WHERE txtVEHNO = " & RsVehDecp("txtVEHNO") & ";"
            AdVehDecp.Execute StrSql
            StrSql = "UPDATE tblRDWORTH SET dtmRWISS = '" & Text2(4).Text & "', dtmRWEXP = '" & Text2(5).Text & "'" _
                    & "WHERE txtVEHNO = " & RsVehDecp("txtVEHNO") & ";"
            AdVehDecp.Execute StrSql
            StrSql = "UPDATE tblINSCERT SET dtmICISS = '" & Text2(6).Text & "', dtmICEXP = '" & Text2(7).Text & "'" _
                    & "WHERE txtVEHNO = " & RsVehDecp("txtVEHNO") & ";"
            AdVehDecp.Execute StrSql
            StrSql = "UPDATE tblMBADVT SET dtmMAISS = '" & Text2(8).Text & "', dtmMAEXP = '" & Text2(9).Text & "'" _
                    & "WHERE txtVEHNO = " & RsVehDecp("txtVEHNO") & ";"
            AdVehDecp.Execute StrSql
            StrSql = "UPDATE tblHDUTY SET dtmHDISS = '" & Text2(10).Text & "', dtmHDEXP = '" & Text2(11).Text & "'" _
                    & "WHERE txtVEHNO = " & RsVehDecp("txtVEHNO") & ";"
            AdVehDecp.Execute StrSql
            RsVehDecp.Requery
            CheckDt

it gives me an error

RUN-TIME ERROR '-2147217904(8004 0E10)';
NO VALUE GIVEN FOR 1 OR MORE REQUIRED PARAMETER.

Pls which parameter does it require?

Hi you need to debug this code and let it run line by line until you get the error. Then, at least, you will know which statement is causing the problem.

**nkechifesie** · Dec 20 '06, 09:00 AM

Originally posted by willakawill

Hi you need to debug this code and let it run line by line until you get the error. Then, at least, you will know which statement is causing the problem.

Sorry, I forgot to state that. The error was coming from the first line of
AdVehDecp.Execu te StrSql

**devonknows** · Dec 20 '06, 10:19 PM

Dont quote me on this, as i cant check it as imnot on my main machine. but whereyou are using

AdVehDecp.Execu te StrSql

try this

AdVehDecp.Execu te StrSql, , AdCmdText.

ADO isnt my big thing, but whenever i use the execute command i put for example

set rs = AdVehDecp.Execu te(StrSql, , AdCmdText)

or

AdVehDecp.Execu te StrSql, , AdCmdText

just a suggestion,

Kind Regards
Devon.

**devonknows** · Dec 20 '06, 10:45 PM

Please ignroe the above post, i completely msread the code and i apologise for any misleading i have caused. i will look again when i ave a little more time on my main machine, i have requested removal of the above post as i believe it has no relevance to what you are trying to do.

i will say though i only get that error when i have data missing from one of the fields in the error

ill do a quick debug here

Code:

original
            StrSql = "UPDATE tblVEHLIC SET dtmVLISS = '" & Text2(0).Text & "', dtmVLEXP = '" & Text2(1).Text & "'" _
                    & "WHERE txtVEHNO = " & VehNo & ";"


i believe it is to do with this
                    & "WHERE txtVEHNO = " & VehNo & ";"

you are missing you ' symbols.
                    & "WHERE txtVEHNO = '" & VehNo & "';"

Sorry again bout the previous post

Kind Regards
Devon

**willakawill** · Dec 20 '06, 10:50 PM

Originally posted by nkechifesie

Sorry, I forgot to state that. The error was coming from the first line of
AdVehDecp.Execu te StrSql

What type of variable is VehNo

**devonknows** · Dec 20 '06, 11:07 PM

Originally posted by willakawill

What type of variable is VehNo

Either way, if you are going to use " & VarName & " in an sql statemet
you have to put the ' symbols

strSql = "Blah blahblah WHERE blah='" & Variable & "'"

Kind Regards
Devon.

**devonknows** · Dec 20 '06, 11:27 PM

also im not sure if you need the ; at the end of your statment like you have here

StrSql = "UPDATE tblVEHLIC SET dtmVLISS = '" & Text2(0).Text & "', dtmVLEXP = '" & Text2(1).Text & "'" _
& "WHERE txtVEHNO = " & VehNo & ";"

try replacing you sql statment with this

StrSql = "UPDATE tblVEHLIC SET dtmVLISS = '" & Text2(0).Text & "', dtmVLEXP = '" & Text2(1).Text & "'" _
"WHERE txtVEHNO = "' & VehNo & "'"
let me know how that goes.

Kind Regards
Devon.

**willakawill** · Dec 21 '06, 01:11 AM

Originally posted by devonknows

Either way, if you are going to use " & VarName & " in an sql statemet
you have to put the ' symbols

strSql = "Blah blahblah WHERE blah='" & Variable & "'"

Kind Regards
Devon.

This information is incorrect.
In order to help you we need to know what type this VehNo variable is.
If it is numeric then your assignment is incorrect
VehNo = Text1(4).Text

Should be
VehNo = CInt(Text1(4).T ext)

If it is text then your use is incorrect
& "WHERE txtVEHNO = " & VehNo & ";"

should be
& "WHERE txtVEHNO = '" & VehNo & "';"

The semi-colon at the end of your statement will not cause an error.

**nkechifesie** · Jan 4 '07, 07:47 AM

Originally posted by willakawill

This information is incorrect.
In order to help you we need to know what type this VehNo variable is.
If it is numeric then your assignment is incorrect
VehNo = Text1(4).Text

Should be
VehNo = CInt(Text1(4).T ext)

If it is text then your use is incorrect
& "WHERE txtVEHNO = " & VehNo & ";"

should be
& "WHERE txtVEHNO = '" & VehNo & "';"

The semi-colon at the end of your statement will not cause an error.

What of if it is Date?

**willakawill** · Jan 4 '07, 08:58 AM

Originally posted by nkechifesie

What of if it is Date?

If it is a date you should get your ass kicked for calling it VehNo :)
A data should be like this:

Code:

& "WHERE txtVEHNO = #" & VehNo & "#;"

**nkechifesie** · Jan 4 '07, 09:37 AM

Originally posted by willakawill

If it is a date you should get your ass kicked for calling it VehNo :)
A data should be like this:

Code:

& "WHERE txtVEHNO = #" & VehNo & "#;"

Now that was funny. But well, you wont be able to kick it because I was not asking for that particular code. I have already solved the issue for that one. Sorry I didnt say so, I travelled and lost track. I was asking for date because I was having a new issue with one that had to do with dates. But its still giving the problem after I changed it to what you asked me too. I would post a new topic on that.
Thank you

Run-Time error when editing Database

Run-Time error when editing Database

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment