I am getting a crash on line 09 of the following code:
01 dim sql as string=“select * from ref”
02 dim db as new MSSQLServerDatabase
03 db.Host=“MACPRO-W7\SQLEXPRESS”
04 db.DatabaseName=“doctracs”
05 db.UserName=“c1”
06
07 if db.Connect=False then Return
08
09 dim rs as RecordSet=db.SQLSelect(sql)
10
11 Listbox1.AddRow rs.Field(“refcreated”).StringValue
12
13 exception err
14 Listbox1.AddRow “Exception”
I am able to connect to the same database using MSExcel using the same log-in details.
I have two questions:
The obvious, why is the Standalone app crashing and quitting at line 09 and
You might try this code out. It should show if there is an error after db.SQLSelect.
// Try To Connect To Server
if db.Connect = True then
// Query Database
rs = db.SQLSelect( sql )
// Check For Error In Query
if db.ErrorCode = 0 then
if rs <> Nil then
While rs.EOF = False
ListBox1.AddRow( rs.Field( "refcreated" ).StringValue )
rs.MoveNext
wend
rs.Close()
end if
// Close Connection If That's What Your Doing
db.Close()
else
// Display Query Error
MsgBox db.ErrorMessage
end if
else
MsgBox db.ErrorMessage
end if
Thanks very much for your time replying Johnny. I should have explained more clearly. I was stepping through the code in debug and connection was happening without errors.
I still replaced with you code (thanks for that), but it made no difference.
Do you know if there are any restrictions using SQLEXPRESS? If there were I would have thought it would have calved when trying to connect…
I hoped that code would help show what was causing your problem. I am working right on a project that uses CubeSQL. I had a customer request that it work with SQL Server. I have ran into a couple of issues with data types in the process.
Hi Johnny, thanks again for your reply and time. I have no experience in CubeSQL - That’s single user, right?
Your question about refcreated’s type prompted me to change the sql variable to specific fields rather than the ‘*’ wildcard and I got it going, so thanks very much. I might do a bit of investigation into which type in the table is causing the problem - if you have an interest in knowing the result - I’ll post it?
[quote=38841:@Chris O’Brien]Hi Johnny, thanks again for your reply and time. I have no experience in CubeSQL - That’s single user, right?
[/quote]
No CubeSQL is a multi-user database. http://www.sqlabs.net
Glad you got it worked out. Please post the result if you have time. I may run into the same problem working on my app with SQL Server.
I have INT,VARCHAR(40), DATETIME and TEXT - all of the other fields can be selected without an issue - I also added VARCHAR(MAX) to see how it reacted and that too caused a crash. I added additional VARCHAR(50)s, INTs without a problem and crashed again with a new TEXT.
Hope this helps you in the future and thanks again for your help
I actually had the same problem several days ago. My app wasn’t crashing but the characters from the TEXT field displayed as “gibberish”. I’m sure it was an encoding issue but didn’t have time to look into it. I set the field to VARCHAR(nnn) and it worked just fine.
I would also recommend to avoid text, because this datatype is likely to be removed beyond MS SQL Server 2012.
From the MS online documentation : ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.