data retival problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

data retival problem

Author

Topic

donafams
Starting Member

11 Posts

Posted - 2007-01-16 : 12:40:48

have the following vbscript select statement.. problem im having is
if those record fields shown in the script below arnt retrievd in the same order as the columns in the table, i dont get data for them.

this just started happening and we mave have hundreds of pages of code where the order doesnt match because it didnt matter with prior versions. any ideas?

strSQL1="SELECT * FROM Employees WHERE UserName = '" & UserName & "'"
set rs = conn.execute (strSQL1)
If Not rs.EOF Then
rs.Movefirst
EmployeeIDNo = rs.fields ("EmployeeIDNo")
BranchID = rs.fields ("BranchID")
BranchChief = rs.fields ("BranchChief")
DepartmentCode = rs.fields ("DepartmentCode")

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-16 : 12:44:41

You can just type the names of the colums.

Obviously Substitute the actual Column Name for a.etc.

strSQL1="SELECT a.UserName, a.etc, a.etc FROM Employees a WHERE UserName = '" & UserName & "'"

donafams
Starting Member

11 Posts

Posted - 2007-01-16 : 12:47:08

the problem is, is there a fix to the out of order issue by a patch or anything? i have no idea how long it would take to go thru all the code and do it manually

donafams
Starting Member

11 Posts

Posted - 2007-01-16 : 12:50:08

the problem all of a sudden is the order of this part below.. not the select statement itself

EmployeeIDNo = rs.fields ("EmployeeIDNo")
BranchID = rs.fields ("BranchID")
BranchChief = rs.fields ("BranchChief")
DepartmentCode = rs.fields ("DepartmentCode")

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-16 : 12:57:55

I'm sorry I'm not following you, according to the code in your last post the column order would
not make a difference since you are referencing the column by name.

What are you ultimatly trying to do with the columns, I have tons of code on this that might help.

donafams
Starting Member

11 Posts

Posted - 2007-01-16 : 13:06:39

my problem is with the retrival below

EmployeeIDNo = rs.fields ("EmployeeIDNo")
BranchID = rs.fields ("BranchID")
BranchChief = rs.fields ("BranchChief")
DepartmentCode = rs.fields ("DepartmentCode")

if these dont match the column order i dont get data.. this problem just happened after many years of not being a problem.. if i did it as you stated it might take me about 6 months to recode everything. im wondering what caused this problem and maybe if there is a patch solution as opposed to recoding

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-01-16 : 14:07:05

How do you know that the issue is the column order? Did you actually do a test re-aranging your code?

This is very strange if that's the case, I would first make sure that you have applied all service packs. It sounds to me like the dataconnection library has gotten corrupted. if possible try installing your program on another machine to see if you have a similiar issue. If it works on the other machine, do a full rebuild and re-install your windows current service pack.

also as a workaround, when I return data and I am not returning to a custom class, I always return it to a datatable rather than process the actual result set

Try
dim dt as datatable
dim cn as connection = "server=1234;user=etc...."
Dim sqlCmd As New SqlCommand

sqlcmd.cmdtype = commandtype.text
sqlcmd.cmdtext = "SELECT * FROM Employees WHERE UserName = '" & UserName & "'"

cn.Open()
sqlCmd.Connection = cn
da.SelectCommand = sqlCmd
'
da.Fill(dts)
cn.Dispose()
dt = dts.Tables(0)
Finally
dts.Dispose()
cn.Dispose()
End try

'Then do your processing
dim dr as datarow
for each dr in dt.rows
EmployeeIDNo = dr("EmployeeIDNo")
BranchID = dr("BranchID")
BranchChief = dr("BranchChief")
DepartmentCode = dr("DepartmentCode")
next

' or even better create a custom class and use the data, also using your query in
'that method is 'not very secure, I would also redo your sql connection to
' pass the parameters rather then the string itself.

donafams
Starting Member

11 Posts

Posted - 2007-01-16 : 14:41:25

How do you know that the issue is the column order? Did you actually do a test re-aranging your code?

yea i did just like below and it worked

FROM
EmployeeIDNo = rs.fields ("EmployeeIDNo")
BranchID = rs.fields ("BranchID")
BranchChief = rs.fields ("BranchChief")
DepartmentCode = rs.fields ("DepartmentCode")

TO
EmployeeIDNo = rs.fields ("EmployeeIDNo")
DepartmentCode = rs.fields ("DepartmentCode")
BranchID = rs.fields ("BranchID")
BranchChief = rs.fields ("BranchChief")

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources