In this programming tutorial we will learn how to fix this nasty error: No value given for one or more required parameters. Import to excel error. Few days ago one of my friend asked me to provide the code that import excel data to gridview. I gave him the link of the import excel data to gridview tutorial but he suddenly asked me that he is getting the above mentioned error. I checked his code and fixed the error. Let's have a look over how i fixed this error.
No value given for one or more required parameters. Import to excel error
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/test.xlsx") + ";Extended Properties=Excel 12.0;"); oconn.Open(); //Getting records from Sheet1 of excel file. As you know one excel file may have many sheets, in this case we are getting data from Sheet1 string sqlQuery = "select * from [Sheet1$] where [user_name]=Lauren"; OleDbCommand ocmd = new OleDbCommand(sqlQuery, oconn); DataSet ds = new DataSet(); OleDbDataAdapter odapt = new OleDbDataAdapter(ocmd); odapt.Fill(ds);
My friend was showing the above mentioned code that it was giving this error: No value given for one or more required parameters. I checked the code and found below mentioned line as a guilty line, causing the error.
string sqlQuery = "select * from [Sheet1$] where [user_name]=Humaira Shafique";
The column(Parameter) user_name in excel file was containing string data so the value of column in where clause should be enclosed in single quote like given below.
string sqlQuery = "select * from [Sheet1$] where [user_name]='Humaira Shafique'";
This fix my friend's error but there are some more reasons that cause this error, these are
- Parameter name not spell correctly.
- Case-Sensitivity issue in parameter (firstname instead of FirstName)
So that's it. I love your feedback.