Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums
 |
•
•
Join Date: Mar 2006
Posts: 1
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Hi. I am working on a particular sql statemtent that looks like this, but I simplified it greatly.
That is how Access created the statement. All 3 tables have the Key field. This works in Access, but I am unable to get it to perform in VB6.
Details: Key is unique in Table 1, which is the source for T2 & T3.
Table 1 holds a product list, with an autonumber for Key. The key cannot repeat. Pulling product descriptions from this table.
Table 2 holds purchase orders for those keys. The key can repeat. Pulling 'order qty' from this table.
Table 3 holds prices from 3 tables (inserted) for 3 vendors catalogs.
Table A is the place I am wishing to store every entry from Table 1 (product list), possibly with each Key listed more than once due to Table 2 (orders) having more than one instance of a Key, and on each record, corresponding to the Key, the current price, which comes from Table 3.
The reason for this is to create one table to do many different selects from for reports/viewing inventory & availabilities. I am forced to segregate the actual data from each vendor into seperate tables, which is fine. But mulitple selects into many different tables is proving to be much too slow. Thus the desire to merge the data to one table that is deleted and repopulated on data changes.
I have restructured this select into VB in lot's of different manners, but I still get the same error. Error is -
-2147217904 - No value given for one or more required parameters.
I have not found the answer on the web yet. I think it is in the nesting. But I have not found why.
Any ideas?
Thanks,
Sul
x="INSERT INTO TableA (T1.Key,T1.Field1,T2.Field1,T2.Field2,T3.Field1,T3.Field2) FROM (T2 RIGHT JOIN T1 on T2.Key = T1.Key) LEFT JOIN T3 ON T1.Key = T3.Key"
That is how Access created the statement. All 3 tables have the Key field. This works in Access, but I am unable to get it to perform in VB6.
Details: Key is unique in Table 1, which is the source for T2 & T3.
Table 1 holds a product list, with an autonumber for Key. The key cannot repeat. Pulling product descriptions from this table.
Table 2 holds purchase orders for those keys. The key can repeat. Pulling 'order qty' from this table.
Table 3 holds prices from 3 tables (inserted) for 3 vendors catalogs.
Table A is the place I am wishing to store every entry from Table 1 (product list), possibly with each Key listed more than once due to Table 2 (orders) having more than one instance of a Key, and on each record, corresponding to the Key, the current price, which comes from Table 3.
The reason for this is to create one table to do many different selects from for reports/viewing inventory & availabilities. I am forced to segregate the actual data from each vendor into seperate tables, which is fine. But mulitple selects into many different tables is proving to be much too slow. Thus the desire to merge the data to one table that is deleted and repopulated on data changes.
I have restructured this select into VB in lot's of different manners, but I still get the same error. Error is -
-2147217904 - No value given for one or more required parameters.
I have not found the answer on the web yet. I think it is in the nesting. But I have not found why.
Any ideas?
Thanks,
Sul
•
•
Join Date: Mar 2006
Location: Singapore
Posts: 50
Reputation:
Rep Power: 3
Solved Threads: 2
Junior Poster in Training
Re: nested joins, from mdb. Possible? Dear Sul, I do not know if you know about Queries in Microsoft Access.
Queries in Microsoft Access are more commonly known as "Views" in the database world. They behave exactly like Tables but some DML may be restricted depending on the complexity of its design.
I suggest you create a Query with the SELECT version of that statement (i.e. SELECT those fields that you want to insert into in that Query). If possible, do not write a custom SQL statement but use the Query Design View to choose the relevant tables and attributes in your Query. After you are done with that, switch to the Datasheet View and see if your fields are selected properly. Most importantly, check that there is a "blank" row at the bottom of the Datasheet with an asterisk ("*") on the left. If that asterisk exists, you can now use that Query to INSERT data into the database.
Simply save the Query and use its name in a simple basic INSERT statement:
One thing that might be useful in working with Queries is to rename the columns to something unique. In the Field row of the Design View you can type:
This will give the effect of the
I believe you are getting the "-2147217904 - No value given for one or more required parameters." error because there are some required fields in your tables used in your INSERT statement that are not given values. That's probably why your program is complaining.
Hope this helps.
Queries in Microsoft Access are more commonly known as "Views" in the database world. They behave exactly like Tables but some DML may be restricted depending on the complexity of its design.
I suggest you create a Query with the SELECT version of that statement (i.e. SELECT those fields that you want to insert into in that Query). If possible, do not write a custom SQL statement but use the Query Design View to choose the relevant tables and attributes in your Query. After you are done with that, switch to the Datasheet View and see if your fields are selected properly. Most importantly, check that there is a "blank" row at the bottom of the Datasheet with an asterisk ("*") on the left. If that asterisk exists, you can now use that Query to INSERT data into the database.
Simply save the Query and use its name in a simple basic INSERT statement:
INSERT INTO [MyView] ([col1], [col2], [col3]) VALUES ('val1', 'val2', 'val3');[desiredfieldname]: [actualfieldname]
AS SQL keyword in your SELECT statements.I believe you are getting the "-2147217904 - No value given for one or more required parameters." error because there are some required fields in your tables used in your INSERT statement that are not given values. That's probably why your program is complaining.
Hope this helps.
Best Regards, God Bless,
AstroNox
AstroNox
•
•
Join Date: Mar 2006
Location: Singapore
Posts: 50
Reputation:
Rep Power: 3
Solved Threads: 2
Junior Poster in Training
Oh yes, you might want to post this in the database forum next time... I think this is more of a database question than a VB question. I would not have spotted it if I didn't happen to venture into this forum. Thanks!
Best Regards, God Bless,
AstroNox
AstroNox
•
•
Join Date: Dec 2004
Location: Lincoln Park, Michigan
Posts: 1,858
Reputation:
Rep Power: 8
Solved Threads: 116
In VB, I'm sure the problem has more to do with the fact there are variables in the SQL statement, that are inside of quotes.... making the variables a literal string. For example:
Does NOT produce a msgbox of 5..... it performs a msgbox of the letter x. I'm thinking the problem is the same.
X = 5 msgbox "x"
•
•
Join Date: Mar 2006
Location: Singapore
Posts: 50
Reputation:
Rep Power: 3
Solved Threads: 2
Junior Poster in Training
I agree with the frequency of this mistake. Normally what I would do in Java if I'm not using a
I would just replace all instances of a single quote (
In VB.NET (and VB I believe) you can use the
I work with VB.NET and rarely VB, so I dare not vouch for VB.
However, if it were a quote error as you've mentioned, Comatose, he would get a SQL syntactical error, not the required field problem he mentioned. So I have reason to believe in the case I've presented. Setting a field as required (no nulls) yet without a default value usually results in this.
PreparedStatement is something to this extent:String sql = "INSERT INTO [MyTable] ([VChar1], [Dbl2], [Int3]) " +
"VALUES ('%s', %f, %d);";
ResultSet rs = stmt.executeQuery(
String.format(sql, myString1.replace("'", "''");, myDouble2, myInt3));') with two single quotes (''). Databases read two single quotes (not one double quote) as the escape sequence for a single quote character.In VB.NET (and VB I believe) you can use the
Replace function to do the same, although I would prefer to work with DataAdapters and so on in VB/ADO.NET:Dim sql As String = "INSERT INTO [MyTable] ([VChar1], [Dbl2], [Int3]) " & _
"VALUES ('" & myString1.Replace("'", "''") & "', " & _
myDouble2 & ", " & myInt3 & ");"However, if it were a quote error as you've mentioned, Comatose, he would get a SQL syntactical error, not the required field problem he mentioned. So I have reason to believe in the case I've presented. Setting a field as required (no nulls) yet without a default value usually results in this.
Best Regards, God Bless,
AstroNox
AstroNox
•
•
Join Date: Dec 2004
Location: Lincoln Park, Michigan
Posts: 1,858
Reputation:
Rep Power: 8
Solved Threads: 116
No, VB would flip it's lid. You can't escape characters in VB, it just doesn't work that way, so you have to use the chr function in order to represent those values outside of the string. Since this isn't a java forum, it's not pertinent to the discussion, so escaping is not available. You would need to concantenate the Statement in the variable, like so:
Which would make a field blank, since it's not being passed anything but 1 parameter.... which is the string itself.
' double quote dq = chr(34) x= dq & "hello there" & dq
Which would make a field blank, since it's not being passed anything but 1 parameter.... which is the string itself.
•
•
Join Date: Mar 2006
Location: Singapore
Posts: 50
Reputation:
Rep Power: 3
Solved Threads: 2
Junior Poster in Training
Comatose, I'm sorry but I believe you got me wrong. I said:
I did not mean anything about escaping double quotes in VB. I know that I have to use
What I meant was that if someone would like to feed single quotes into the database, they must escape it by using two single quotes instead. SQL uses single quotes as its string delimiters.
I hope this clarifies what I was saying!
•
•
•
•
Databases read two single quotes (not one double quote) as the escape sequence for a single quote character.
Chr(34) to use the double quote... painful yes, but what choice since Microsoft designed it that way. Even Environment.Newline can get a little irritating from overuse.What I meant was that if someone would like to feed single quotes into the database, they must escape it by using two single quotes instead. SQL uses single quotes as its string delimiters.
I hope this clarifies what I was saying!
Best Regards, God Bless,
AstroNox
AstroNox
•
•
Join Date: Dec 2004
Location: Lincoln Park, Michigan
Posts: 1,858
Reputation:
Rep Power: 8
Solved Threads: 116
Yup that makes sense. environment.newline doesn't work in vb4, 5, and 6 (it does in .net, but that's a language I could do without). Since both of us are approaching this from different sides (Mine from a VB standpoint, yours from the SQL standpoint) Are you seeing a problem with the SQL Statement that would need to have escaped quotes?
•
•
Join Date: Mar 2006
Location: Singapore
Posts: 50
Reputation:
Rep Power: 3
Solved Threads: 2
Junior Poster in Training
It's hard to say because of the kind of data put into the database at a certain insertion.
I do not know if you have heard of this term "MVC", but it you have, I would like to first of all say that this quote problem lies with the Control, not the Model.
Put it this way—let's say I have a intelligent application that indexes the name of items in my house daily at midnight and puts it into a database, given that this application is smart enough to come up with proper names of the various items. One day my friend Bob comes over and forgot to take his shades home when he leaves. So at midnight my application runs and notes the item that Bob left as:
And then it uses this SQL statement to store the item into the database:
Look carefully at the statement now. The SQL interpreter in the database will take the underlined portion as a string:
And the rest of it as trailing characters—error!
This is why it is important to escape the single quotes, and this can only be done at your Controller. As mentioned several times before, the escape for a single quote is two single quotes, one after another. Assuming that I fixed my application, this should be the correct SQL statement:
Now, the database will store the intended string:
Many naïve implementations of database applications overlook this problem. This is especially so for the ignorant who just learnt how to develop a web application together with SQL and are starting out in some e-commerce site. Throw in one single quote in a string field and all hell breaks loose. Worse still, throw in a smart programmer/hacker and look—assuming this is the supposed statement for a search query on the e-commerce site (the underlined "pencil" is a variable term):
And I put this string
There goes your database! You might wonder a hacker does his/her job, well basically, it is through loopholes such as this. This technique is also known as "SQL injection," simple yet very deadly.
That's why Sun provides its SQL framework for Java, and Microsoft its ADO.NET framework. These frameworks (if you know how to use them properly) does the mundane job of ensuring safe input into your database. I especially love Microsoft's ADO.NET—if you know how to use it, it makes database application development so quick, so easy.
So now, in regard to Sul's SQL statement, I do not know the nature of the information being inserted into the database, or I could give a better suggestion (he gave a very general SQL statement). The quotes do matter—but notice that he did not include the
I do not know if you have heard of this term "MVC", but it you have, I would like to first of all say that this quote problem lies with the Control, not the Model.
Put it this way—let's say I have a intelligent application that indexes the name of items in my house daily at midnight and puts it into a database, given that this application is smart enough to come up with proper names of the various items. One day my friend Bob comes over and forgot to take his shades home when he leaves. So at midnight my application runs and notes the item that Bob left as:
Bob's shadesAnd then it uses this SQL statement to store the item into the database:
INSERT INTO [ItemTable] ([ItemName]) VALUES ('Bob's shades');Look carefully at the statement now. The SQL interpreter in the database will take the underlined portion as a string:
INSERT INTO [ItemTable] ([ItemName]) VALUES ('Bob's shades');And the rest of it as trailing characters—error!
This is why it is important to escape the single quotes, and this can only be done at your Controller. As mentioned several times before, the escape for a single quote is two single quotes, one after another. Assuming that I fixed my application, this should be the correct SQL statement:
INSERT INTO [ItemTable] ([ItemName]) VALUES ('Bob''s shades');Now, the database will store the intended string:
INSERT INTO [ItemTable] ([ItemName]) VALUES ('Bob''s shades');Many naïve implementations of database applications overlook this problem. This is especially so for the ignorant who just learnt how to develop a web application together with SQL and are starting out in some e-commerce site. Throw in one single quote in a string field and all hell breaks loose. Worse still, throw in a smart programmer/hacker and look—assuming this is the supposed statement for a search query on the e-commerce site (the underlined "pencil" is a variable term):
SELECT FROM [Products] WHERE LOWER([ProdName]) LIKE '%pencil%';And I put this string
'; DELETE FROM [Products] WHERE [ProdName] LIKE ':SELECT FROM [Products] WHERE LOWER([ProdName]) LIKE '%'; DELETE FROM [Products] WHERE [ProdName] LIKE '%';There goes your database! You might wonder a hacker does his/her job, well basically, it is through loopholes such as this. This technique is also known as "SQL injection," simple yet very deadly.
That's why Sun provides its SQL framework for Java, and Microsoft its ADO.NET framework. These frameworks (if you know how to use them properly) does the mundane job of ensuring safe input into your database. I especially love Microsoft's ADO.NET—if you know how to use it, it makes database application development so quick, so easy.
So now, in regard to Sul's SQL statement, I do not know the nature of the information being inserted into the database, or I could give a better suggestion (he gave a very general SQL statement). The quotes do matter—but notice that he did not include the
VALUES part of the insert statement. I would believe that he would append the VALUES part later on, completing the statement. Otherwise that might be the very reason he's getting the problems—insertion without fulfilling the data integrity rules on NOT NULL and primary key fields. Best Regards, God Bless,
AstroNox
AstroNox
•
•
Join Date: Dec 2004
Location: Lincoln Park, Michigan
Posts: 1,858
Reputation:
Rep Power: 8
Solved Threads: 116
Just a note, on some SQL Server implimentations, you actually can escape the ' character (and any other) just like in C and Perl, so the statement:
would also work, but that is server specific. As for hackers, it's pretty common knowledge that a hacker uses flawed programming to gain access and/or execute remote code.... I certainly appreciate posts with as much knowledge as possible, I just have a hard time seeing how that relates.... I suppose in order to fix the problem that Sul has posted, we need him/her to post the entire sql statement, and the information relevant to it so that we can go from there. I would imagine, however, since the statement works in access and not in VB, that it's a port problem from access to VB and has nothing to do with the SQL statement.
INSERT INTO [ItemTable] ([ItemName]) VALUES ('Bob\'s shades'); |
Other Threads in the Visual Basic 4 / 5 / 6 Forum
- Previous Thread: Total VB noob asking for info.
- Next Thread: Searching for a string in an access database
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
