SQL Server Field Problem

[MazY]

A question for JH (or any SQL users.) if he pops in.

JH

Code Extract

'// Check tbl_loggedin to see if the user's UID is already entered. (1 = Logged in, 0 = Logged Out.)

objRS.Open "Select * FROM tbl_loggedin WHERE UID = " & lUserID & " AND fld_loggedin = 1", objConn, adOpenKeyset, adLockReadOnly, adCmdText

If Not objRS.BOF Then
MsgBox "Your account is already in use on the following network computer." & vbCrLf & vbCrLf & _
"Machine Name: " & objRS!fld_machinename & vbCrLf & _
"IP Number: " & objRS!fld_ipaddr & vbCrLf & vbCrLf & _
"You cannot login until the above login is terminated. Please consult your system administrator.", vbCritical, AppName
Exit Sub
End If

Syntax error in the SQL Statement? I don't think so! But according to VB there is!

fld_loggedin and lUserID are both long integers.

[JuniorHarris]

Sorry Maz, been pretty busy as of late...

I don't see anything obviously wrong with your query, other then it's not using a stored procedure!~;)

If you haven't already corrected the problem, you might try to build/dump the SQL statement, and then try to execute that statement in SQL Server.

strSQL = "Select * FROM tbl_loggedin WHERE UID = " & lUserID & " AND fld_loggedin = 1"
Response.Write "strSQL - [" & strSQL & "]"
objRS.Open strSQL, objConn, adOpenKeyset, adLockReadOnly, adCmdText

If you did fix the problem, I am still curious what the issue was...

[JuniorHarris]

I was curious what version of SQL you are using?

I did look to see if UID was a reserved word (which could cause problems), but it does not show up on the list of reserved words. Also, the omission of spaces when concatenating the string could cause problems...but the posted code looks good in that regard.

[MazY]

Thanks, JH.

Though you didn't solve the issue. The issue in actual fact was down to a statement that predeeded the actual SQL statement by some 6 lines! In that my recordset was as empty as empty could be! Though I still fail to see why it would report a syntax error. (If you use VB at all, you will know that its error handling is not the best in the world. VB.Net solves this, however.)

LOL at stored procedures. It will be, it will be. (For the record, I physically sweated when trying to learn stored procs some time ago. Then finally it hit me after quite a few days of banging my head on a hard desk!)

The software is being tested in SQL Server and Access as it develops. Touch wood, so far so good. While it is a custom product for a client of ours, we also writing it in generic format as we have a company who is willing to act as our "sales machine" for us, for a cut of the profits.

UID is not a reserved word and I hope to God it never becomes one as it is my standard name for creating a unique identifying field in tables!

It's SQL Server 7 by the way and damned mighty fine it is too!

For the record, here's the start of the user guide for the product. It's way behind the product development but then who likes documentation?

http://www.vbmedia.co.uk/leadmaster/index.htm

Thanks for taking a look at the code anyway. Now get back to whatever mysterious job it is that you do... :)

[JuniorHarris]

LOL!~ :) Yes, VB can be persnickety at times! Seems as if I've had misreported error line numbers before as well...thank goodness for the poor-man's debug...Response.Write!

I always try to guide programmers towards the use of stored procedures, party because of the performance gains stored procs offer with precompiled query plans. But mostly from a maintenance point of view.

I know one chap who had to change hundreds of ASP pages because they had made a design change at the database level, and all their ASP pages utilized in-line SQL.

We actually made a design change at the database level ourselves, demoralizing a single table into two separate tables. However, all I had to do was modify the stored proc to pull the same information from the two tables...still returning the data in the original format. I didn't have to edit any ASP pages to implement the change!~;)

SQL Server 7 is indeed a great tool, it has really matured since version 4.21! SQL Server 2000 has pretty much the same engine components, but also includes active directory (AD) and XML features.

I took a quick peek at your site, and it's looking good! It reflects the typical clean-cut style and format that I've come accustomed to seeing you create!

Got to go, that damn red phone is ringing again...

[MazY]

[i]Originally posted by JuniorHarris LOL!~ :) Yes, VB can be persnickety at times! Seems as if I've had misreported error line numbers before as well...thank goodness for the poor-man's debug...Response.Write!

You leave VB alone, meladdio! I remember the good old days of Borland Turbo Basic. Oh, the magic of creating a "window" in DOS...

I always try to guide programmers towards the use of stored procedures, party because of the performance gains stored procs offer with precompiled query plans. But mostly from a maintenance point of view.

Hey, I don't need any convincing. It was drummed into me, over several courses. The trouble is that it was one of the last things I ever learned about it. Ergo, it's one of the last things I ever think about! I know, not good practise...

I know one chap who had to change hundreds of ASP pages because they had made a design change at the database level, and all their ASP pages utilized in-line SQL.

Yes, I get the point. Leave me alone! lol

We actually made a design change at the database level ourselves, demoralizing a single table into two separate tables. However, all I had to do was modify the stored proc to pull the same information from the two tables...still returning the data in the original format. I didn't have to edit any ASP pages to implement the change!~;)

No, really. I get the point! lol

SQL Server 7 is indeed a great tool, it has really matured since version 4.21! SQL Server 2000 has pretty much the same engine components, but also includes active directory (AD) and XML features.

Everyone I speak to about 2000 states that unless you specifically need those features, then the upgrade isn't worthwhile. I don't really need them, yet.

I took a quick peek at your site, and it's looking good! It reflects the typical clean-cut style and format that I've come accustomed to seeing you create!

Thanks. Cheque is in the post. :D

Got to go, that damn red phone is ringing again... [/B]

lol! So that's what you do!

[JuniorHarris]

I too remember the Turbo Basic days, I even remember before they had turbo!~;)

But nothing wrong with VB either, granted there might be a few quirks, but that's the way software is developed these days. ;)

Apologies for preaching from the stored proc pulpit. But I felt compelled to share the data separation and maintenance advantages, not so much for us, but for other members who may read upon the thread. ;)

There is no doubt in my mind about your skills, and no question you've probably surrounded yourself with equal talent!~ Looks like a promising system! (cheque cashed)

You are spot on regarding SQL 2000, other then the added features mentioned above, the core engines are basically the same.

One can't imagine how sticky these tights get in humid weather...

[MazY]

Originally posted by JuniorHarris
I too remember the Turbo Basic days, I even remember before they had turbo!~;)

Hell, now you are going back. My teeth cutting languages were Turbo Basic and ASM. To my knowledge there is still an old estate agent program out there running my first ASM code. Poor swines!

I tried Borland C then C+ but couldn't really get on with them. I could see the power but couldn't really balance the learning curve vs. the productivity. I also saw a bigger future in VB, RAD being as it is and all that.

Apologies for preaching from the stored proc pulpit. But I felt compelled to share the data separation and maintenance advantages, not so much for us, but for other members who may read upon the thread. ;)

None needed. Sometimes I can use a little preaching.

One can't imagine how sticky these tights get in humid weather...

That would come under the heading of "too much information". :D

Anyway, thanks for the chat, must get some work done!