-1

I write the following piece of codes :

rst.Open(strSQL & Request.QueryString("C-PLACE")), conn, 0, 1

But got the following error. However, if the querystring is in English or just number, no error will pop out. Any guru can help please ?

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

/deliverable/GetMemberTest.asp, line 19

braX
  • 11,506
  • 5
  • 20
  • 33
Sum
  • 1
  • 2

1 Answers1

-1

It's going to either be passing an encoding variable to the server, or in the case of your error, its saying "too few parameters". In this case, the parameter is "C-PLACE" and its suppose to be passed to your asp script from the previous page's link, something like:

/deliverable/GetMemberTest.asp?C-PLACE=THECPLACE

https://www.w3schools.com/asp/coll_querystring.asp (citation about query strings)

or something like that .. obviously its not actually "THECPLACE", but just saying a QueryString("VARIABLENAME") looks to the URL of the previous page to pass the parameter to the script, so that error message should of done something to add a ? mark = C-PLACE= to something, and we aren't seeing that. So something on the previous page that was suppose to add this when they click a submit button didn't do it's job, or the script is just getting run on its own without the proper previous page's work being done to prepare it to execute properly on the following page.

It will also be of note that these types of things are easily hacked through sql script injection, so if you aren't validating your url first, someone could use some code to escape out of your sql and add their own code, such as one to drop your tables ..., so make sure you validate the variable FIRST instead of dumping it straight into your code. I can give some guidance into that later, but first lets figure out your problem.

(side note - can i request strSQL from you? Can you put this line in before that line:

<%
response.write("strSQL is " & StrSQL & "<BR>")
%>

All this code does is display what is stored in the StrSQL variable, see if we can figure out what is going on here. Also take note that your error message indicated that it expected 2 parameters, so we are missing 2 for this script to run properly.

EDIT - try this encoding:

<%
  Response.CodePage=65001
  Response.Charset="UTF-8"
  Response.ContentType = "text/html"
%>

Try this strSQL, you didn't need the Response.Write and on C-PLACE you want to use '' instead of "" because the "" will exit you out of the SQL statement. Try this, and let me know how it works, but I still think we are going to need another parameter supplied to it, unless its getting one from the string and then it isn't actually counting the one supplied from the url perhaps.

<%
strSQL="SELECT * FROM DetailMemberInfo 
WHERE C-PLACE=" & strSQL & Request.QueryString('C-PLACE'))" 
%>
easleyfixed
  • 219
  • 1
  • 13
  • Hi Sir, here is the querystring when the value is in Chinese - SELECT * FROM DetailMemberInfo WHERE C-PLACE=長康明愛中心B. Here is the URL resulted - http://localhost/deliverable/GetMemberTest.asp?C-PLACE=%E9%95%B7%E5%BA%B7%E6%98%8E%E6%84%9B%E4%B8%AD%E5%BF%83B.........this is suppose in Chinese :( Noted that if the querystring is a number, say MemberID, then everything is fine – Sum May 19 '21 at 07:31
  • strSQL="SELECT * FROM DetailMemberInfo WHERE C-PLACE=" response.write(strSQL & Request.QueryString("C-PLACE")) rst.Open(strSQL & Request.QueryString("C-PLACE")), conn, 0, 1 Here is the piece of code which I used. The sql string is output from the line response.write(strSQL & Request.QueryString("C-PLACE")) which is SELECT * FROM DetailMemberInfo WHERE C-PLACE=長康明愛中心B But the URL is http://localhost/deliverable/GetMemberTest.asp?C-PLACE=%E9%95%B7%E5%BA%B7%E6%98%8E%E6%84%9B%E4%B8%AD%E5%BF%83B when I paste here, which is normal Chinese on my browser – Sum May 19 '21 at 07:42
  • Ah okay, so it appears that it is trying to pass the data to the next page, but the data is in Chinese as you originally suspected to be the problem. I'm not sure exactly, but I think this has something to do with the database encoding type that you would need to select but will have to think a bit on this one first. – easleyfixed May 19 '21 at 12:16
  • I have to head to work right now, but take a look at this link and see if this helps. I know its for php but maybe you can work something out from this, and if not, I will be back later to check back in and see if this helped at all. http://www.herongyang.com/PHP-Chinese/Input-Chinese-Text-to-MySQL-UTF-8.html – easleyfixed May 19 '21 at 12:31
  • Many thanks Sir. The link given looks useful but it is in C and MSSQL and I am using ASP with VBA. However, the HTML header he mentioned seems useful – Sum May 19 '21 at 16:05
  • I am back now and yeah that was the problem its in php, but that was what I wanted you to look at the header encoding. I actually only had one minute this morning to respond and find that, but now I will see if i can find something more direct for you. However, let me ask this as well, are you trying to return the results in Chinese characters as well? Side note .. translating those characters into English is "Chang Kang Caritas Center B", i wonder, can you try doing this too, replace the chinese characters with the words Chang Kang Caritas Center B on the link and see what happens? – easleyfixed May 19 '21 at 21:40
  • although now that i am thinking of it .. that string has spaces in it, which won't translate well into a link .. but I will have some encoding information soon hopefully as well. – easleyfixed May 19 '21 at 21:46
  • I updated the answer to include a basic HTML encoding, its possible you already have this on your page, but if now, try adding those 3 lines to the top of your page. Then run the script again and see if there are any different results and report back. If not we can play with the encoding types, but curious if you have any set at all yet. – easleyfixed May 19 '21 at 21:57
  • I just re-read everything again .. and remembered this part: Too few parameters. Expected 2. We are only providing the script ONE variable, which is C-PLACE, the script wants ANOTHER parameter as well, and I would need to see much more of the script to figure out what variable it is needing to pull the data, or if its even actually needed or can be re-written to be excluded, but I am suspecting you need it to combine with the location variable to be combined for the specific query you are doing. – easleyfixed May 19 '21 at 22:01
  • ONE MORE THING - Don't forget to add the new code to BOTH pages before you do your tests to make sure the encoding is set on both them. But even after the encoding, we still must figure out what the other parameter it is needing and you should be good to go. – easleyfixed May 19 '21 at 22:05
  • Hi Sir, Yes, I had all those codes you recommended in my program. The returned value is in perfect chinese character. The problem only shows up when it goes into the SQL statement – Sum May 20 '21 at 01:37
  • By the way, I had tried returning English and numeric value and it was perfectly working. – Sum May 20 '21 at 01:38
  • <%Language="Vbscript" Response.ContentType = "text/html" Response.AddHeader "Content-Type", "text/html;charset=UTF-8" Response.CodePage = 65001 Response.CharSet = "UTF-16"%> <% Set conn = Server.CreateObject("ADODB.Connection") conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "F:\CYTU 130103.mdb" Set rst = Server.CreateObject("ADODB.Recordset") strSQL="SELECT * FROM DetailMemberInfo WHERE C-PLACE=" response.write(strSQL & Request.QueryString("C-PLACE")) rst.Open(strSQL & Request.QueryString("C-PLACE")), conn, 0, 1 – Sum May 20 '21 at 01:41
  • Is the data in the database in Chinese and it going to pull Chinese characters as a result? And did you see what I said about you need TWO parameters not just C-PLACE, I need to see more of your code to see what it is missing. But your report back tells me yes it is `MySQL` encoding and not `IIS` so I will look into this real fast while you address the missing 2nd parameter issue. – easleyfixed May 20 '21 at 01:42
  • WAIT a second .. see the `Response.CharSet = "UTF-16"` , try setting that to `Response.CharSet = "UTF-8"` just for giggles, that is the default, but it might be you need `UTF-16` for Chinese characters, but I will find out shortly. Not only is it the default it matches the other code types we already set to UTF-8 so something to consider. – easleyfixed May 20 '21 at 01:43
  • if you have access to the mysql console you could try feeding it this line to set the encoding to People's Republic of China standard. `SET NAMES 'gbk ';` If that works, you can edit your my.ini config to make it stick by adding this: [mysqld] character-set-server=gbk [client] default-character-set=gbk citation - https://dev.mysql.com/doc/refman/5.7/en/faqs-cjk.html – easleyfixed May 20 '21 at 01:49
  • (Also after we sort this, I did see you had this on a `localhost` so its probably not going on the web?) My question is, are you the ONLY one that will use this script? Because if you are letting it have web access or user access without validating that Request.QueryString you leave yourself open to SQL Injection attacks which could corrupt and/or destroy your data left to the wrong hands. – easleyfixed May 20 '21 at 01:54
  • Also here is some info on converting data types in Access, nothing direct but at least some information to consider: https://www.techonthenet.com/access/functions/string/strconv.php – easleyfixed May 20 '21 at 02:08
  • Hi Sir, everything works fine before the Chinese Characters comes into the SQL statement. I deliberately insert a line to print out the querystring to confirm that. Setting up the gbk is not an option as we are using traditional Chinese instead of the simplified Chinese. By the way, the SQL injection is not an issue right now, I am just testing how to pull the data out from an Access database to the internet for web access. Once I get there, I will come back to consider web security which is my number one concern as the database contain personal details of 8000+ users. – Sum May 20 '21 at 08:39
  • Ok good deal, here is something I found where someone was dealing with both traditional and simplified, I will keep looking though. https://stackoverflow.com/questions/12472795/mysql-inserting-traditional-simplified-chinese-in-the-same-cell And I see, so you took the other line out just so we could see it, so you are in fact passing more than just C-PLACE to the script but took out the 2nd parameter? Because the original error is complaining about wanting a 2nd one passing to it, and perhaps it is just the encoding and C-PLACE isn't actually making it there in tact. – easleyfixed May 20 '21 at 14:11
  • One disturbing element I am starting to see is that ... I am seeing a bug report about this that is a duplicate .. so I hope it is indeed possible to do this. https://bugs.mysql.com/bug.php?id=13183 – easleyfixed May 20 '21 at 14:12
  • Hi Sir, ""And I see, so you took the other line out just so we could see it, so you are in fact passing more than just C-PLACE to the script but took out the 2nd parameter?"" --this is definitely not the case as I had past another field which is numeric and everything works fine. Therefore the syntax is not an issue for sure – Sum May 21 '21 at 01:41
  • OK good, just saying you MUST include 2 variables or the script won't work at least how it is coded now. But if you are passing 2, and it doesn't work, it is not passing the chinese characters over to database in those instances. That being said, take a look at this post and see if it helps a little bit? https://stackoverflow.com/questions/923876/why-are-my-chinese-characters-not-displayed-correctly-in-c-sharp-string – easleyfixed May 21 '21 at 02:43
  • Do you have access to a mysql server you can import the data into it, and then you would be able to play with the encoding commands I mentioned earlier? If anything, you can install Mysql CE for free on your current system, and we could maybe import the data and see if doing that allows it to be accessed easier, if you aren't able to change settings on the Access file its self, then if you can get it into actual MySQL you would then be able to have more control over the data to pull this off i believe. – easleyfixed May 21 '21 at 02:46
  • Everything works fine once I change the querystring to MemberID with is a number If Len(Request.QueryString("MemberID")) <> 0 Then strSQL="SELECT * FROM DetailMemberInfo WHERE MemberID=" response.write(strSQL & Request.QueryString("MemberID")) rst.Open(strSQL & Request.QueryString("MemberID")), conn, 0, 1 I am trying to figure out how to use the MySQL server as you recommended. Hope to find out what is wrong. Many thanks – Sum May 22 '21 at 03:58
  • One bit of advice I can offer is that you have to be very aware of datatypes. You might assume something is a string but you need to write code to make sure it is what you think is, or you will get errors. Such as if you are trying to do a math type thing but insert a string variable into the equation, it defies the logic and throws and error. One other thing is work with what you know is known good, such as, well this works, and its a number, so why isn't the string working type situations. – easleyfixed May 22 '21 at 04:13
  • A bit of advice about "" and '' with MySQL and ASP, in ASP most of the time you want to use the "" and with MySQL you want to use the '' to quote things. So something like this is used SQL = "SELECT * FROM TABLE WHERE NAME = 'bob'" . its hard to see on the bob, but that is 'bob' " so a single and double quote. – easleyfixed May 22 '21 at 04:15
  • And in a case where you want to put an ASP variable into SQL you have to "escape" the string to insert asp code. So if it was: SQL = "SELECT * FROM TABLE WHERE NAME = '" & aspvariable & "'" Now that is hard to see but let me break it down: Name = ' <- first a single quote THEN a " double quote to "escape" the SQL to use ASP then you use & symbol, the variables name, another & symbol a double quote to enter sql again, which then adds a ' single quote to close the Mysql String and then a double double " after to finish up the SQL statement. I hope that helps and makes sense. – easleyfixed May 22 '21 at 04:18
  • Directly adding querystring variables into a SQL string is bad practice and leaves you open to [SQL Injection](https://en.m.wikipedia.org/wiki/SQL_injection) attacks, please do not do this. Use `ADODB.Command` to build a parameterised query. – user692942 May 22 '21 at 07:52
  • If you spent the time to read this post, you will see I have already covered that. Its also an Access database in a dev environment so currently that isn't a problem. But yes, when he gets this sorted, I have mentioned he is going to need to do some validation on his variables to avoid unwanted access. – easleyfixed May 22 '21 at 14:06
  • Hi Sir, the code given is not working and an error pops out : Syntax error /deliverable/GetMemberTest.asp, line 22 strSQL="SELECT * FROM DetailMemberInfo WHERE C-PLACE=" & strSQL & Request.QueryString('C-PLACE'))" – Sum May 23 '21 at 05:53
  • Syntax error means we got a typo somewhere let me see. I see what it is you are missing the single ticks. "SELECT * FROM DetailMemberInfo WHERE C-PLACE='" & strSQL & Request.QueryString('C-PLACE'))"'" When you use a string in MYSQL the format is : SELECT * FROM table WHERE C-PLACE = ' VARIABLE' you missed the ' single quotes .. – easleyfixed May 23 '21 at 14:08
  • Hi Sir, I did more test and found that it may not be a problem with the Chinese character at all. I had create a record with entry in English and I still have the problem. – Sum May 30 '21 at 11:09
  • Is the error "too few parameters" still? If so, then it needs 2 sets of parameters for it to not error out. I think you said you tried doing 2 before, but if the error is that message, it needs more than what its getting, so focus on that. – easleyfixed May 30 '21 at 13:27
  • Hi Sir, Yes, I had tried it again and found that if the data field type is text (short or long) the same error message will pop up. If the querystring is number then everything is alright – Sum Jun 02 '21 at 02:20
  • Interesting, so when you use a number you get a result though? Some of the entries are integers for C-Place as well as names? But if you are getting results and its not the Chinese its probably the formatting of some of the other code. Is it the parameter error you are getting or what is the error message we will work from there. – easleyfixed Jun 03 '21 at 00:47