nested joins, from mdb. Possible?

[Sul]

Hi. I am working on a particular sql statemtent that looks like this, but I simplified it greatly.

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

[AstroNox]

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:

INSERT INTO [MyView] ([col1], [col2], [col3]) VALUES ('val1', 'val2', 'val3');

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:

[desiredfieldname]: [actualfieldname]

This will give the effect of the 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 somerequired 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.

[AstroNox]

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!

[Comatose]

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:

X = 5
msgbox "x"

Does NOT produce a msgbox of 5..... it performs a msgbox of the letter x. I'm thinking the problem is the same.

[AstroNox]

I agree with the frequency of this mistake. Normally what I would do in Java if I'm not using a 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));

I would just replace all instances of a single quote ( ' ) 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 DataAdapter s and so on in VB/ADO.NET:

Dim sql As String = "INSERT INTO [MyTable] ([VChar1], [Dbl2], [Int3]) " & _
    "VALUES ('" & myString1.Replace("'", "''") & "', " & _
    myDouble2 & ", " & myInt3 & ");"

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.

[Comatose]

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:

' 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.

[AstroNox]

Comatose, I'm sorry but I believe you got me wrong. I said:
Databases read two single quotes (not one double quote) as the escape sequence for a single quote character.
I did not mean anything about escaping double quotes in VB. I know that I have to use 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! :)

[Comatose]

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?

[AstroNox]

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:
Bob's shades
And 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 developmentso 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.

[Comatose]

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:

INSERT INTO [ItemTable] ([ItemName]) VALUES ('Bob\'s shades');

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.

[AstroNox]

I concur with what you've said. I would imagine that he tested this out in Access at the Query panel, where it is known for creating SQL statements that sometimes work internally only. Been there, done that. If he could get it to work at the internal VBA level perhaps there won't be this port problem. In any case, I admit that I've never tried creating an INSERT SQL statement at the Query panel. The method I described in my first post to this thread always worked for me so I've always used that, and thus recommended that. The other posts in this thread were just informative filler? :)

[Comatose]

Ah yes, filler. Honestly, I'd like to be able to mark this thread solved, because I know that I have a lot of questions in this forum regarding vb and databases. In fact, the sticky thread at the top of this forum is about vb and access.... if you decide to write a tutorial or something along those lines, we can link to it, so that other people get resolutions too.

[AstroNox]

Sure. I'd be looking forward to that when I have the time to write a full tutorial out.

I just recalled this regarding the use of \' in an SQL statement. I would not recommend that, one reason is what you've already mentioned, Comatose. Another is because, for Access, the last time I tried, somehow along the ODBC/Jet/Access connection it converts \' 's into ‘ or ’ and not ' . It's as if the same algorithm Microsoft Word uses to handle single quotes parses and converts the single quotes into the left or right version. Can be a problem, especially if you are storing data where the original single quotes matter—such as a programming forum like DaniWeb—compilers do not read those lefties or righties.

[Comatose]

Right, and to take it a step further most SQL servers don't support it (again, my note on it being server specific) and if I remember correctly, the SQL92 Rules obsolete the use of it.... however, for people who do a lot of programming, the slash is a tradition, and so long as the server supports will use it (since we are giving off a lot of information in this thread, figured it's good knowledge).

[AstroNox]

Just rating this thread. Wasn't able to edit my previous posts to do this. Apparently there is a 30 minutes edit limit.

[Comatose]

Yeah there is.