VB & Database, SQL server varchar data type is not readable in VB

SQL server varchar data type is not readable in VB

Author	Message
anita her #1 / 7	SQL server varchar data type is not readable in VB Hi All, I have a SQL server table that contains a varchar column. The length of the column is 4000. The column name is remark. All rows contain data (without null). My problem is : When VB read one row of the table, the remark content is unreadable. It looks like null. But if change the column length to be 1024 or shorter, VB can read it. Below is my VB codes with description on the right arrows. Dim s as string debug.print myrecord!remark --> output is Null if IsNull(myrecord!remark) then debug.print "yes" else debug.print "no" --> output is no endif s = myrecord!remark --> error invalid use of null Could anyone show me the cause of my problem and how to solve it. Thanks in advance Anita Hery *** Sent via Developersdex http://www.--.com/ ** Don't just participate in USENET...get rewarded for it!
Fri, 12 Aug 2005 23:32:47 GMT

Val Mazu #2 / 7	SQL server varchar data type is not readable in VB Anita, What is your connection string? -- Val Mazur Microsoft MVP Quote: > Hi All, > I have a SQL server table that contains a varchar column. > The length of the column is 4000. The column name > is remark. All rows contain data (without null). > My problem is : When VB read one row of the table, > the remark content is unreadable. It looks like null. > But if change the column length to be 1024 or shorter, > VB can read it. > Below is my VB codes with description on the right arrows. > Dim s as string > debug.print myrecord!remark --> output is Null > if IsNull(myrecord!remark) then > debug.print "yes" > else > debug.print "no" --> output is no > endif > s = myrecord!remark --> error invalid use of null > Could anyone show me the cause of my problem and > how to solve it. > Thanks in advance > Anita Hery > * Sent via Developersdex http://www.developersdex.com * > Don't just participate in USENET...get rewarded for it!
Fri, 12 Aug 2005 23:49:52 GMT

anita her #3 / 7	SQL server varchar data type is not readable in VB In my table, there are several columns. One of them is a varchar column. The function of Sub startcon below is to connect to database. The Sub test is called after connection exists. Private Sub startcon() dim x as string DBEngine.DefaultType = dbUseODBC Set pbws = DBEngine.Workspaces(0) x = "ODBC;DRIVER=SQL server; UID=xxx; PWD=aaa; SERVER=SRV; DATABASE=abc" 'pbcn is public variable Set pbcn = pbws.OpenConnection("", dbDriverNoPrompt,False, x) End Sub Private Sub test() dim x as String, myrecord is Recordset dim s as String x = "select * from mytab" Set myrecord = pbcn.OpenRecordset(x, dbOpenDynaset) If myrecord.RecordCount = 0 Then Exit Sub End If debug.print myrecord!remark If IsNull(myrecord!remark) then debug.print "yes" Else debug.print "no" End If s = myrecord!remark End Sub Anita Hery Quote: >From: Val Mazur >Anita, >What is your connection string? >-- >Val Mazur >Microsoft MVP * Sent via Developersdex http://www.developersdex.com * Don't just participate in USENET...get rewarded for it!
Sat, 13 Aug 2005 02:22:47 GMT

Val Mazu #4 / 7	SQL server varchar data type is not readable in VB Anita, First of all this is DAO, not ADO, and you are using ODBC driver, which may not support long strings. You would need to use ADO with OLEDDB Provider for SQL Server to have full support to datatypes from SQL Server -- Val Mazur Microsoft MVP Quote: > In my table, there are several columns. One of them is a > varchar column. > The function of Sub startcon below is to connect to > database. The Sub test is called after connection exists. > Private Sub startcon() > dim x as string > DBEngine.DefaultType = dbUseODBC > Set pbws = DBEngine.Workspaces(0) > x = "ODBC;DRIVER=SQL server; UID=xxx; PWD=aaa; SERVER=SRV; DATABASE=abc" > 'pbcn is public variable > Set pbcn = pbws.OpenConnection("", dbDriverNoPrompt,False, x) > End Sub > Private Sub test() > dim x as String, myrecord is Recordset > dim s as String > x = "select * from mytab" > Set myrecord = pbcn.OpenRecordset(x, dbOpenDynaset) > If myrecord.RecordCount = 0 Then > Exit Sub > End If > debug.print myrecord!remark > If IsNull(myrecord!remark) then > debug.print "yes" > Else > debug.print "no" > End If > s = myrecord!remark > End Sub > Anita Hery > >From: Val Mazur > >Anita, > >What is your connection string? > >-- > >Val Mazur > >Microsoft MVP > * Sent via Developersdex http://www.developersdex.com * > Don't just participate in USENET...get rewarded for it!
Sat, 13 Aug 2005 03:04:29 GMT

anita her #5 / 7	SQL server varchar data type is not readable in VB Val Mazur, Thank you for your sugestions. Microsoft support several libraries for accessing data. If I need one that - support all sql server data type - support all sql server queries - work quickly / good performance - using small memory Which library is the best. Could you show me the link address that talk about Microsoft library advantages and issues. Anita Hery Quote: >Anita, >First of all this is DAO, not ADO, and you are using ODBC >driver, which may >not support long strings. You would need to use ADO with >OLEDDB Provider for >SQL Server to have full support to datatypes from SQL >Server >-- >Val Mazur >Microsoft MVP * Sent via Developersdex http://www.developersdex.com * Don't just participate in USENET...get rewarded for it!
Sat, 13 Aug 2005 23:04:04 GMT

Val Mazu #6 / 7	SQL server varchar data type is not readable in VB Anita, Data access technology is moving from ODBC to OLEDB. OLEDB is faster and you could expect more functionality from OLEDB providers. Also DAO is not working with OLEDB, but ADO does it. If you will use ADO with OLEDB for SQL Server native provider, then you should get required functionality -- Val Mazur Microsoft MVP Quote: > Val Mazur, > Thank you for your sugestions. Microsoft support several > libraries for accessing data. If I need one that > - support all sql server data type > - support all sql server queries > - work quickly / good performance > - using small memory > Which library is the best. Could you show me the link address that talk > about Microsoft library advantages and issues. > Anita Hery > >Anita, > >First of all this is DAO, not ADO, and you are using ODBC >driver, > which may > >not support long strings. You would need to use ADO with >OLEDDB > Provider for > >SQL Server to have full support to datatypes from SQL >Server > >-- > >Val Mazur > >Microsoft MVP > * Sent via Developersdex http://www.developersdex.com * > Don't just participate in USENET...get rewarded for it!
Sat, 13 Aug 2005 23:14:35 GMT

anita her #7 / 7	SQL server varchar data type is not readable in VB Val Mazur, Thanks again for your information. Anita Hery Quote: >Anita, >Data access technology is moving from ODBC to OLEDB. >OLEDB is faster and you >could expect more functionality from OLEDB providers. >Also DAO is not >working with OLEDB, but ADO does it. If you will use ADO >with OLEDB for SQL >Server native provider, then you should get required >functionality >-- >Val Mazur >Microsoft MVP * Sent via Developersdex http://www.developersdex.com * Don't just participate in USENET...get rewarded for it!
Sun, 14 Aug 2005 02:02:26 GMT

Page 1 of 1

[ 7 post ]