ADO Field Name problem

[markphsd]

Hello,

I have a field name of "1s" on a SQL Server Database. When using ADO to edit the field i get a 3265 error, that the field can't be found. I try to use an Alias, but it doesn't work.

What can i do without changing the Fieldname?

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[dpaulson]

Try a [] around the field name. This works with an access DB.

David Paulson

 

[markphsd]

That doesn't work, actually in SQL server itself it puts the brackets around. I also found that i may need to put a back quote, but that didn't solve the problem either.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[Andrzejek]

What does it show when you do:

"Select * from MyProblemTable"

in any tools to see data in your DB?

Also, with the previous SQL, try:

For i = 0 To rst.Fields.Count - 1
    Debug.Print rst.Fields(i).Name
Next i

Have fun.

---- Andy

 

[BobRodes]

Can you publish the line of code that gives the error?

Alternatively, you can just use field offsets rather than field names.

 

[markphsd]

Aha, I knew this would trip some people up.

Notice second sentence line one of my first comment. Only editing calls this error. I can read the value of the field, using:

SELECT 1s FROM myTable
rst.open sSQl, mycon, ....

debug.print rst(0).value 'this works
debug.print rst(0) ' this works
debug.print rst!1s this works

but i can't edit the value, i.e.:

rst!1s = 1
rst.update

does not work

and i don't use SELECT * FROM as general rule. But I believe it would cause the same problem.

Conclusion:

SELECT 1s 'does not work
SELECT [1s] ' does not work
SELECT `1s` 'does not work
SELECT `[1s]` 'does not work


Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[SBerthold]

Try using the recordset's absolute field position for updating:

rst.Fields(0).value = 1
rst.Update

Or, set a reference to the field:

Dim MyField as ADODB.Field

'Open rst

Set MyField = rst.Fields(0)

MyField.Value = 1
rst.Update

 

[gmmastros]

Ah... there's a lot of goofiness here.

I assume you are using Microsoft SQL Server. If not, feel free to ignore the rest of this.

There are some quirks with the T-SQL engine. For example, you can 'manufacture' a field that doesn't really exist. Like this...

Select 'Hello World' As Greetings
From   MyFavoriteTable

You will return a recordset with 1 column (named Greetings). There will be as many rows in the returned recordset as there are rows in the table. Every record will have the same value.

With T-SQL, there are some things that are optional. For example, the AS keyword is optional. So, the previous query could also be written as...

Select 'Hello World' Greetings
From   MyFavoriteTable

By now, you probably think I'm nuts, but bear with me.

Again, with T-SQL, some spaces are not necessary, so, again the above query could be written as...

Select 'Hello World'Greetings
From   MyFavoriteTable

Now, suppose you changed the column alias to S, the query would look like...

Select 'Hello World's
From   MyFavoriteTable

Now, change the data to an integer value, and for kicks, let's set it's value to 1. You end up with...

Select 1s
From   MyFavoriteTable

So, you see... you returned a derived column named S with a value of 1. This is why you could retrieve its value but not set its value. Of course, I would never have a column named 1s in a table, but that's my choice, not yours. And, I certainly respect that there are circumstances when you have no choice. With that being said, here's your solution.

Change your query to:

SELECT [1s] FROM myTable

Now, the 1s column will be returned and you will be able to update it. My favorite syntax for this would be...

RS("1s").Value = 30

you could also use...

RS![1s] = 30

Make sense?


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[markphsd]

Well.. ` vs. '

I am agreeing that there is goofieness. however,

You'll notice i didn't use a single quote, I used a back quote. From what i read and understand since coming across this problem is: The backquote is the alternate method to use []'s however, in the enterprise manager the column 1s is surrounded by brackets to begin with, so I pressumed that i wouldn't need back quotes



I know you can create a fake column.

I would like to know if you did attempt to create a table with a field named: 1s. If not, please do so. Create an ado recordset with vb and let me know if you can update it.

I continuously get an error howver. The field type is smallint. It's not the identity column, I'm sure you can name the table whatever you'd like.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[markphsd]

SBerthold

For good measure, i will try setting the field as a vb object, but i don't think we could reduce it much further than:

SELECT * From myTable

rst(0).value = 1

'where 1s is always the first column, however that's bad design. (Even though 1s as a column name appears to be bad design)

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[gmmastros]

OK. Sure.

[COLOR=blue]Create[/color] [COLOR=blue]Table[/color] MyFavoriteTable ([1s] [COLOR=blue]smallint[/color])

Private Sub Command1_Click()
    
    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset
    
    Set DB = CreateObject("ADODB.Connection")
    DB.CursorLocation = adUseClient
    DB.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=tektips;Data Source=(local);Integrated Security=SSPI;"
    Call DB.Open
    
    Set RS = CreateObject("ADODB.Recordset")
    RS.CursorLocation = adUseClient
    Call RS.Open("Select [1s] from MyFavoriteTable", DB, adOpenDynamic, adLockBatchOptimistic)
    RS.AddNew
    RS![1s] = 50
    
    RS.UpdateBatch
    RS.Close
    Set RS = Nothing
    DB.Close
    Set DB = Nothing
    
End Sub

Select * from MyFavoriteTable

Results
[tt][blue]
1s
------
50

(1 row(s) affected)
[/blue][/tt]

Private Sub Command2_Click()

    Dim DB As ADODB.Connection
    Dim RS As ADODB.Recordset
    
    Set DB = CreateObject("ADODB.Connection")
    DB.CursorLocation = adUseClient
    DB.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=tektips;Data Source=(local);Integrated Security=SSPI;"
    Call DB.Open
    
    Set RS = CreateObject("ADODB.Recordset")
    RS.CursorLocation = adUseClient
    Call RS.Open("Select [1s] from MyFavoriteTable", DB, adOpenDynamic, adLockBatchOptimistic)
    
    While Not RS.EOF
        RS![1s] = -50
        
        RS.MoveNext
    Wend
    
    RS.UpdateBatch
    RS.Close
    Set RS = Nothing
    DB.Close
    Set DB = Nothing

End Sub

Select * from MyFavoriteTable

Results
[tt][blue]
1s
------
-50

(1 row(s) affected)
[/blue][/tt]

As you can see, I successfully added a record and then also updated it. Does this help?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[markphsd]

Yes,

Damnit.. must be something else.

seriously... i do this all day long. I wonder what else it would be.

Thanks a lot.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[gmmastros]

>> From what i read and understand since coming across this problem is: The backquote is the alternate method to use []'s however, in the enterprise manager the column 1s is surrounded by brackets to begin with, so I pressumed that i wouldn't need back quotes

I have, uhh... considerable experience with sql server. No. really. A lot. Seriously. With Microsoft SQL Server, the back-quote is [!]NOT[/!] an acceptable replacement for the square brackets. I suspect that you were reading some material regarding MySQL. Unfortunately, with various database engines floating around, it's easy to mistake advice for one and the solution for another.

In any case, I wish you luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[markphsd]

Thanks, gmmastros

Thanks a lot.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[gmmastros]

Alright. It's late and I'm tired. So 1 more quick post.

I assume you didn't make this table, but are now stuck with it. Sorry about that, by the way.

I can think of a couple reasons why you might not be able to update a column.

1. The column could be a computed column. Run sp_help 'TableName' If it's a computed column, the previous query will let you know.

2. The column could have constraints. Most commonly, these are implemented as unique indexes, but they don't have to be either.

3. The column could be a foreign key (also a constraint) on another table.

4. There could be a trigger on this table that is actually failing. This can be a bugger because it's difficult to track down. sp_helptrigger 'MyFavoriteTable'

If I've convered something here that you already know about, then my apologies. I don't know what you know, so some of this can seem obvious to you.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[markphsd]

Thanks for those thoughts. I I'll send you payment via paypal plus overtime...

I was at least was correct with the information i supplied. The field is very simple. Not 1,2,3 or 4 applied. I'll just have to review your post tomorrow; since i don't try out things this late. Too difficult to focus.



Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[BobRodes]

<I have, uhh... considerable experience with sql server. No. really. A lot. Seriously.