Problem with Select * from <tablename>

[devilman0]

I have a problem with the below code in vb, i am using a dsn less connection to the db and everythign else works except:

select * from JobInfo

do until recordset.eof
i=i+1
recordset.movefirst
recordset.move i-1
tempstr = tempstr & recordset(0) & "~"
loop

this doesn't raise an error in vb, also if I do:

select job_id, Job_Number from JobInfo

that also doen't work. any thoughts?
this only doesn't work in mysql, works in using a .mdb








Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[snowboardr]

try

do while not recordset.eof

' looping data

recordset.movenext
loop

Im not sure on that one been a while since I have done any vb

www.vzio.com
ASP WEB DEVELOPMENT

 

[devilman0]

Thanks, that worked, now i have another problem (with updating)

Public Function UpdateInfo(ByVal TableName As String, InfoToWrite As String, ColName As String, RowId As String, ArrayColNames As String) As String
Dim RecordSet As New ADODB.RecordSet
Dim i As Integer
Dim StrSql As String
Dim TempArry() As String
Dim TempArryB() As String
Dim tempInt As Integer
On Error GoTo ErrOccured:
If DBConnection.State <> adStateOpen Then
Path = ReadIni(&quot;Main&quot;, &quot;Path&quot;, App.Path & &quot;\Settings.ini&quot;)
Call Main(App.Path & &quot;\JobIformation.mdb&quot;)
End If
StrSql = Z&quot;Select * From &quot; & TableName & &quot; Where &quot; & ColName & &quot;=&quot; & RowId
RecordSet.ActiveConnection = DBConnection
RecordSet.CursorLocation = adUseServer
RecordSet.CursorType = adOpenDynamic
RecordSet.LockType = adLockPessimistic
RecordSet.Open StrSql
TempArry = Split(InfoToWrite, Chr(9), -1, vbBinaryCompare)
TempArryB = Split(ArrayColNames, Chr(9), -1, vbBinaryCompare)
For i = 0 To UBound(TempArryB) - 1
tempInt = Int(TempArryB(i))
RecordSet.Fields(tempInt) = (TempArry(i))
Next i
RecordSet.Update
RecordSet.Close
Set RecordSet = Nothing
Exit Function
ErrOccured:
Call ErrorTracking(&quot;UpdateInfo&quot;, CStr(Err.Number), Err.Description, 3, &quot;TableName: &quot; & TableName & &quot;  InfoToWrite: &quot; & InfoToWrite & &quot;  ColName: &quot; & ColName & &quot;  RowId: &quot; & RowId & &quot;  ArrayColNames: &quot; & ArrayColNames)
End Function

this works for integers, but fails on strings (namely blob fields) error i get is something about mulit step process generated errors, please check each setp (i am at work, if you need exact err number and description i can get that)

What really has me confused is that i can write an integer, but when i try to write a string to a blob field i get an error.

Sql statement with fields inserted looks like this:
"Select * From Designer Where Designer_Id=1"
string i am trying to write looks like this:
'326:321:689:566:'
with or with out the ' still causes the same error.

any thougths?

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[devilman0]

also i have tried RecordSet.CursorLocation = adUseClient with the same result.


Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[snowboardr]

StrSql = Z"Select * From " & TableName & " Where " & ColName & "=" & RowId


is that z a miss type?

www.vzio.com
ASP WEB DEVELOPMENT

 

[snowboardr]

If that z is just a miss type, I will tell you I havn't really worked with blobs...I was thinking about using them in my current project, but I am unsure if it will meet my needs.

If that isn't the problem let me know and i'll take a look at the code, and a error message always helps when posting a problem.

- Jason

 

[devilman0]

Yeah, "z" is a misstype here, here is my code:

Public Function UpdateInfo(ByVal TableName As String, InfoToWrite As String, ColName As String, RowId As String, ArrayColNames As String) As String
Dim RecordSet As New ADODB.RecordSet
Dim i As Integer
Dim StrSql As String
Dim TempArry() As String
Dim TempArryB() As String
Dim tempInt As Integer
On Error GoTo ErrOccured:
If DBConnection.State <> adStateOpen Then
Path = ReadIni(&quot;Main&quot;, &quot;Path&quot;, App.Path & &quot;\Settings.ini&quot;)
Call Main(App.Path & &quot;\JobIformation.mdb&quot;)
End If
StrSql = &quot;Select * From &quot; & TableName & &quot; Where &quot; & ColName & &quot;=&quot; & RowId
RecordSet.ActiveConnection = DBConnection
RecordSet.CursorLocation = adUseClient
RecordSet.CursorType = adOpenStatic
RecordSet.LockType = adLockPessimistic
RecordSet.Open StrSql
TempArry = Split(InfoToWrite, Chr(9), -1, vbBinaryCompare)
TempArryB = Split(ArrayColNames, Chr(9), -1, vbBinaryCompare)
For i = 0 To UBound(TempArryB) - 1
tempInt = Int(TempArryB(i))
'RecordSet.Fields(tempInt).Value = (TempArry(i))
Debug.Print TempArry(i)
RecordSet.Update tempInt, TempArry(i)
Next i

RecordSet.Close
Set RecordSet = Nothing
Exit Function
ErrOccured:
Call ErrorTracking(&quot;UpdateInfo&quot;, CStr(Err.Number), Err.Description, 3, &quot;TableName: &quot; & TableName & &quot;  InfoToWrite: &quot; & InfoToWrite & &quot;  ColName: &quot; & ColName & &quot;  RowId: &quot; & RowId & &quot;  ArrayColNames: &quot; & ArrayColNames)
End Function

from what i've been reading, blob isn't the best solution, so i changed the fields to text and that doesn't work either, same error. Same data being passed to the function,
table name, string: "600:563:" type, colindex 10

thanks for your help so far..

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[devilman0]

Error Number: 2147217887
Error Description: Multiple-step operation generated errors. Check each status value.

odd thing is it will write integer fields, but not text fields.

Thanks in advance

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[snowboardr]

I see. Im not sure I can provide you a solution on this one, the only thing I can think of is calling the db in a function I have had trouble doing that in ASP before, I am not sure why, it was a while ago.

Wish I could help you solve it.

Jason

 

[devilman0]

snowboardr, thanks for your time and help.

I will try to change from a function to a sub and see if that'll help.

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker

 

[devilman0]

function to sub didn't help, i actually found the problem a " " in the name, didn't see it : james_ jobs instead of James_Jobs thanks to all who helped, code was right, db was wrong.

Thanks,
James

"Make it idiot-proof and someone will make a better idiot." ~bumper sticker