no value given for one or more required parameters on execute statement in vba

[m_vishal_c]

Hi,

i have "Run time error no value given for one or more required parameters" on execute statement in vba

I am trying to connect Excel as database. i am going to execute update query from one excel workbook.

Public con_daily As Connection
Public con_ele As Connection
Public com_daily As Command
Public com_ele As Command
Public rs_daily As Recordset
Public rs_ele As Recordset




Private Sub cmdUpdate_Click()
Dim lastRow As Long
Dim rng, c As Range
Dim i, iValue As Integer


Set con_daily = New ADODB.Connection
Set con_ele = New ADODB.Connection


    With con_daily
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=C:\Users\abc\Documents\Test for abc auto upload\temp\Template_BOMBOQ_MDU.xlsx;Extended Properties=""Excel 12.0 Xml;ReadOnly=0;HDR=YES"";"
        .Open
    End With
    
With Worksheets("Sheet1")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rng = .Range("A2:A" & lastRow)


End With
    




i = 2
 
Set com_daily = New Command


Do While Sheet1.Cells(i, 1).Value <> ""


iValue = Sheet1.Cells(i, 4)
    With com_daily
        .ActiveConnection = con_daily
        .CommandText = "update [Sheet11$] set MDU1='" & iValue & "' where Item_ID=" & Sheet1.Cells(i, 3) & ""
        .Execute
    End With
    i = i + 1
 Loop
End Sub

Please guide me. it gives me error while execute this above statement

heaps thanks

 

[bobsan42]

Please provide the complete error message. The problem is probably in the .commandtext string.

Check what the complete string is to see for obvious SQL errors. (use debug.print .CommandText immediately after assigning its value to confirm that it at least looks OK)

And also be very careful with the difference between Sheet1 and Worksheets("Sheet1") - these two are not necessarily the same thing ( i would stick to using Worksheets("Sheet1") because this is easy to check and confirm).
Sheet1 is a worksheet object which can have a completely different name.
And preferably refer to sheets including also workbook reference.

 

[m_vishal_c]

Please provide the complete error message. The problem is probably in the .commandtext string.

Check what the complete string is to see for obvious SQL errors. (use debug.print .CommandText immediately after assigning its value to confirm that it at least looks OK)

And also be very careful with the difference between Sheet1 and Worksheets("Sheet1") - these two are not necessarily the same thing ( i would stick to using Worksheets("Sheet1") because this is easy to check and confirm).
Sheet1 is a worksheet object which can have a completely different name.
And preferably refer to sheets including also workbook reference.

HI, Thanks for replying,

I got this below error message

Run time error '-214721790(80040e10)':
No Value given for one or more required parameters

As you said to change to Worksheets("Sheet1"), i changed but it shows below error

Run-time error '13'
Type mismatch

Please guide me which line of code need to be changed

thanks

 

[m_vishal_c]

sorry i forgot to add code
I changed below code but it gives me above error
.CommandText = "update [Sheet1$] set MDU1='" & iValue & "' where Item_ID='" & Worksheets(Sheet1.Cells(i, 3)) & "'"

 

[bobsan42]

I didn't really say to change it to Worksheets("Sheet1")- I said to be careful, because there is a difference between the two.
I notice that your commandtext before was about [Sheet11$], now it is about [Sheet1$].

try to debug your code and submit the value of .CommandText after it is assigned ( use debug.print .CommandText to do it, or in break mode in Immediate window type ?.CommandText)

 

[m_vishal_c]

I didn't really say to change it to Worksheets("Sheet1")- I said to be careful, because there is a difference between the two.
I notice that your commandtext before was about [Sheet11$], now it is about [Sheet1$].

try to debug your code and submit the value of .CommandText after it is assigned ( use debug.print .CommandText to do it, or in break mode in Immediate window type ?.CommandText)

sorry for that. i just changed sheet name so. now its Sheet1 instead of Sheet11. and sorry i am new to vba.
can you please add debug.pring command in my code and send back to me.
it will be very help for me
thanks

 

[m_vishal_c]

hi

.CommandText = "update [Sheet1$] set MDU1='" & iValue & "' where Item_ID='" & Sheet1.Cells(i, 3) & "'"
.Execute

after commantext statement, execution is stopped on ".Execute". when i mouseover to .commandtext, it shows same "Update [Sheet1$] set MDU1='150' where Item_ID='dddfd'"

thanks

 

[bobsan42]

About 99% sure that there is a spelling mistake in your field names: MDU1 or Item_ID.
One or both of them are not correct if they exist at all.
in your connection string you have HDR=Yes: this means that your columns have headers - these are data column labels in the FIRST row of the sheet.

So in row 1 you must have these two labels/headers: MDU1 or Item_ID
My guess is that there is at least one row above the labels (and this row is not blank), or there are no labels at all.
So easiest way to solve is - open C:\Users\abc\Documents\Test for abc auto upload\temp\Template_BOMBOQ_MDU.xlsx and fix the headers.

The other solution is (if you really need rows above the data table) to use a reference to the range rather than the sheet e.g.:

[/COLOR].CommandText = "update [Sheet1$A10:D1000] set MDU1='" & iValue & "' where Item_ID='" & Worksheets(Sheet1.Cells(i, 3)) & "'"[COLOR=#333333]

 

[bobsan42]

BTW, you MUST NOT forget to close the connection at the end of your code.

 

[m_vishal_c]

thank you so so much. you remove my problem. much appreciate. Yes you are right about
"
About 99% sure that there is a spelling mistake in your field names: MDU1 or Item_ID. and My guess is that there is at least one row above the labels (and this row is not blank), or there are no labels at all. "

but in this case in excel A1:- Item_ID was having space like " Item_ID " in Cell A1. i did not know. but when you inform me above then i realized

thanks so much. Now it got sorted

thanks