evaluating values in variables

[jmichael2468]

I am working on a table driven app. I have placed all of my Select and insert statements in an sql 7 DB table. Things seemed to be going great until I needed to do an insert using an insert statement stored in the table. I query the DB to get the insert statement, place the statement in a variable. The problem is that the insert statement refers to the .field(x) property of a recordset and when the insert statement is stored in the variable it stores ".Recordset.Field(x)" instead of the value of .recordset.field(x). How do I evaluate the insert statement or the contents of the variable the insert statement is stored in to reflect the value of the .field(x) property?

[kleinma]

Originally posted by jmichael2468
I am working on a table driven app. I have placed all of my Select and insert statements in an sql 7 DB table. Things seemed to be going great until I needed to do an insert using an insert statement stored in the table. I query the DB to get the insert statement, place the statement in a variable. The problem is that the insert statement refers to the .field(x) property of a recordset and when the insert statement is stored in the variable it stores ".Recordset.Field(x)" instead of the value of .recordset.field(x). How do I evaluate the insert statement or the contents of the variable the insert statement is stored in to reflect the value of the .field(x) property?

your asking for a world of trouble doing it this way.. you would be better off storing the sql statements in a text file than you would do a DB.. because you are going to get very frustrated with all your ' and " that you are going to have to use to get this to work right

[techgnome]

Better still, go with Stored Procedures.

[kinjalgp]

So whatz the problem in that? Just build your query by putting together the string from database and the values from your variables and fire it to the SQL engine!

[techgnome]

The problem seems to be that he's getting


INSERT INTO myTable
(Field1, Field2, Field3)
VALUES
(.Recordset.Field(x), .Recordset.Field(x), .Recordset.Field(x))

Instead of

INSERT INTO myTable
(Field1, Field2, Field3)
VALUES
('1', '2', '3')

[RobIII]

It think your query is wrong....

It think this is what you have:

Connection.Execute "Insert into MyTable (FieldA, FieldB, FieldC) Values (.RecordSet.Field(0),.RecordSet.Field(1),.RecordSet.Field(2))"

But I think it should be:

Connection.Execute "Insert into MyTable (FieldA, FieldB, FieldC) Values (" & .RecordSet.Field(0) & "," & .RecordSet.Field(1) & "," & .RecordSet.Field(2) & ")"

Or, if you're inserting strings:

Connection.Execute "Insert into MyTable (FieldA, FieldB, FieldC) Values ('" & .RecordSet.Field(0) & "','" & .RecordSet.Field(1) & "','" & .RecordSet.Field(2) & "')"

Make sure though that the .recordset.field(0) etc. don't contain a ' character. That'll "crash" the query.
If it does, use this (alway safe) solution:

Sub FixSQL(strVal as string)
  FixSQL = Replace(strVal,"'","''")
End Sub
 
Connection.Execute "Insert into MyTable (FieldA, FieldB, FieldC) Values ('" & FixSQL(.RecordSet.Field(0)) & "','" & FixSQL(.RecordSet.Field(1)) & "','" & FixSQL(.RecordSet.Field(2)) & "')"

TaDaaaaa.... This should help....

[jmichael2468]

Techgnome is correct! I was getting


INSERT INTO myTable
(Field1, Field2, Field3)
VALUES
(.Recordset.Field(x), .Recordset.Field(x), .Recordset.Field(x))

Instead of

INSERT INTO myTable
(Field1, Field2, Field3)
VALUES
('1', '2', '3')

and the reason I was getting this is because the sql statement coming from the table wasn't being evaluated with the field values before being stored in the variable. I did find a fix for the problem though. If anyone is interested here is the code.

it is ugly but it does work.


Dim x1 As Integer, x2 As Integer
Dim MidStr As String
Dim OffSet As Integer
OffSet = 19


Do While x2 < Len(sqlStatement)
x1 = InStr(sqlStatement, ".sRecordset.Fields(")
x2 = InStr(x1, sqlStatement, ")")
MidStr = Mid$(sqlStatement, x1 + OffSet, (x2 - (x1 + OffSet)))
sqlStatement = Replace(sqlStatement, Mid$(sqlStatement, x1, x2 - x1 + 1), .sRecordset.Fields((CInt(MidStr))))
Loop

Thanks guys

[RobIII]

Did you even try the option I gave ya? That should work. And it's not as AWFUL as your current solution.

Want another AWFUL solution? Try this:

Dim strSQL as string
 
strSQL = "INSERT INTO myTable (Field1, Field2, Field3) VALUES .Recordset.Field(1), .Recordset.Field(2), .Recordset.Field(3))"
 
strSQL = Replace(strSQL, ".Recordset.Field(1)", 1001)
strSQL = Replace(strSQL, ".Recordset.Field(2)", 2001)
strSQL = Replace(strSQL, ".Recordset.Field(3)", 3001)
oConn.Execute strSQL

That will result in putting values 1001, 2001 and 3001 in your fields. But as mentioned: AWFUL SOLUTION.

Just use the first suggestion I gave you before....

<grmbl grmbl>

[jmichael2468]

The problem was not in the query. The problem was that the queries were constructed and stored in a SQL table. When I pulled these queries into a variable using VB the values in the .field properties were not evaluated by VB.

Example:

I stored the following in a sql table called Qs in a column called Colx.

"INSERT INTO myTable
(Field1, Field2, Field3)
VALUES
('" & .Recordset.Field(1) & "','" & .Recordset.Field (2) & "','" & .Recordset.Field(3) & "')"

After I open the the recordset I put the query in a variable like so

sqlstatement = .recordset.field(colx)

once that is done the value in
sqlstatement is

"INSERT INTO myTable
(Field1, Field2, Field3)
VALUES
('" & .Recordset.Field(1) & "','" & .Recordset.Field (2) & "','" & .Recordset.Field(3) & "')"

when it should be

"INSERT INTO myTable
(Field1, Field2, Field3)
VALUES
('1','2','.3')"

Give it a try and you'll see the problem I was having. It wasn't a problem with the query or the quotes in the query. It was a dynamic evaluation problem that has now been resolved. Thank you for taking the time to reply.

Michael

[RobIII]

DID YOU EVEN TRY MY SOLUTION? Sjees....

Just try it. What you were doing was just storing the "text". You don't HAVE to evaluate the values.

What you are doing is this

Dim A as integer
Dim strQuery as text
 
A = 12
strQuery = "Select * from MyTable where MyID = A"

This wil result in the query: "Select * from MyTable where MyID = A". Now, I'm sughesting this:

Dim A as integer
Dim strQuery as text
 
A = 12
strQuery = "Select * from MyTable where MyID = " & A

Wich will result in "Select * from MyTable where MyID = 12".

That's all... Just give it a go. It will work!!!

Sjeess... Where's my valium?

[jmichael2468]

Number one, this is not what I was doing.

your code:

visual basic code:-------------------------------------------------------------
Dim A as integer
Dim strQuery as text

A = 12
strQuery = "Select * from MyTable where MyID = A"
------------------------------------------------------------------------------------



But to give you an understanding of what I am doing look at this.

Again your code:

visual basic code:-------------------------------------------------------------
Dim A as integer
Dim strQuery as text

A = 12
strQuery = "Select * from MyTable where MyID = " & A
--------------------------------------------------------------------------------


If you take the query "Select * from MyTable where MyID = " & A and place that in a field of a table you have the same thing. Now, store the value from that field in a string variable like so


visual basic code:-------------------------------------------------------------
dim A as integer
dim StrQuery as string

A = 12

StrQuery = .recordset.field(1) 'the field holding the query
------------------------------------------------------------------------------------

now the variable StrQuery has the following string:

""Select *from MyTable Where MyID = " & A "

A is not evaluated to 12 because VB does not see A as a variable, but as part of the above string.

That is why your suggestion will not work! I know it won't work because that was my very first attempt and the outcome was what I have been stating from the start.

[RobIII]

Why didn't you say so?

But still, the loop sucks. Try using the Replace function (look it up in the helpfiles. You might want to consider the vbTextCompare option to make it case-insensitive)...