C# 3.0 in a Nutshell
C# 3.0 in a Nutshell, Third Edition A Desktop Quick Reference By Joseph Albahari, Ben Albahari
September 2007
Pages: 858

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
Format Exception In Linqpad
d-price
post Nov 6 2009, 05:39 AM
Post #1


New Member
*

Group: Members
Posts: 7
Joined: 6-November 09
Member No.: 20,892
First off - amazing product! I'm trying to query our SQL Server 2008 database using LINQPad. I can select any table within that database and I always get a FormatException error.

from a in Accounts
select a

I've attached a screenshot of the error message. Making sure it was not a connection issue, I switched to the SQL pane and copied the SQL and executed that with no problems. Any ideas why I'm getting this error? It's tough to take the LINQPad Challenge, when I can't use any LINQ!

Thanks,
Derek
Attached thumbnail(s)
Attached Image
 
Go to the top of the page
 
+Quote Post
JoeAlbahari
post Nov 7 2009, 04:45 PM
Post #2


Advanced Member
******

Group: Members
Posts: 215
Joined: 15-February 08
From: Perth, Australia
Member No.: 90
Very odd!

Can you try:
- running LINQPad on another machine
- running L2S queries on that database on the same machine with VS2008

and let me know the outcome!

Joe

This post has been edited by JoeAlbahari: Nov 7 2009, 04:46 PM
Go to the top of the page
 
+Quote Post
d-price
post Nov 9 2009, 07:23 AM
Post #3


New Member
*

Group: Members
Posts: 7
Joined: 6-November 09
Member No.: 20,892
Hi Joe,

I haven't had a chance to do your requests yet, but I do have a little more information. It turns out I *can* read some tables correctly. Roughly 30 out of 110 tables give that "FormatException" error, but the rest query with no issues.

I'll work on those other requests later today and let you know what the results are!

Thanks,
Derek
Go to the top of the page
 
+Quote Post
d-price
post Nov 10 2009, 05:04 AM
Post #4


New Member
*

Group: Members
Posts: 7
Joined: 6-November 09
Member No.: 20,892
Hi Joe,

I've been comparing the similarities of the tables that have the exception and I think the common factor is that all tables have one or more fields defined as varchar(1), not null. Do you think this could be it?

Thanks,
Derek
Go to the top of the page
 
+Quote Post
JoeAlbahari
post Nov 10 2009, 05:44 AM
Post #5


Advanced Member
******

Group: Members
Posts: 215
Joined: 15-February 08
From: Perth, Australia
Member No.: 90
That shouldn't cause a problem. LINQPad maps varchar(1) to System.String (and char(1) to System.Char).

I can query the following table without trouble:

create table Test (ID int not null primary key, Foo varchar(1) not null)
insert Test values (1, '')
insert Test values (2, 'X')

I presume you're running LINQPad 1.35 or later? (It should update itself if you're using an older version).

Joe
Go to the top of the page
 
+Quote Post
d-price
post Nov 10 2009, 10:44 AM
Post #6


New Member
*

Group: Members
Posts: 7
Joined: 6-November 09
Member No.: 20,892
I had a colleague try on his PC and he came back with the same observation:

It appears that Linqpad does not know how to properly handle a column of datatype varchar(1).

I can successfully run

from a in Accounts
select new
{
a.Accounts_pk, a.Employee_fk, a.Company_fk, a.APerson, a.Bill_STD
}

but as soon as I add in

a.Acct_Type

it breaks.

He says varchar(1) should map to String, but in the tree view it appear to map to Char


Any thoughts?
Go to the top of the page
 
+Quote Post
d-price
post Nov 12 2009, 07:15 AM
Post #7


New Member
*

Group: Members
Posts: 7
Joined: 6-November 09
Member No.: 20,892
Hi Joe,

I finally got around to creating a L2S project. Truth be told I had to figure out how to do it! I ran the sqlmetal command line app to create my database wrapper and this code runs with no errors:

CODE
var db = new SYSTOC800(<my connection string>);
var q = from a in db.Accounts
where a.Accounts_pk < 10
select new
{
a.Accounts_pk,
a.I_Form1,
a.Acct_Type
};
var sb = new StringBuilder();
foreach (var a in q)
{
sb.AppendFormat("{0}, {1}, {2}\r\n", a.Accounts_pk,
a.I_Form1,
a.Acct_Type);
}
textBox1.Text = sb.ToString();

I get this output:

0, ,
2, S, C
3, S, C
4, H, C
5, H, C
6, H, C
7, H, C
8, S, C
9, H, C

If I paste the above code into a LINQPad C# Statement query window, I get the same FormatException error. Is there anything else I can do to help debug this?

Thanks,
Derek
Go to the top of the page
 
+Quote Post
JoeAlbahari
post Nov 17 2009, 05:12 PM
Post #8


Advanced Member
******

Group: Members
Posts: 215
Joined: 15-February 08
From: Perth, Australia
Member No.: 90
Can you give me the schema of a table that I can create to reproduce the problem?

When I create the following table:

create table Test (ID int not null primary key, Foo varchar(1) not null)
insert Test values (1, '')
insert Test values (2, 'X')

LINQPad queries it without error.

Thanks
Joe
Go to the top of the page
 
+Quote Post
d-price
post Nov 18 2009, 06:46 AM
Post #9


New Member
*

Group: Members
Posts: 7
Joined: 6-November 09
Member No.: 20,892
I created the Test table and data using your SQL below into our database (SQL Server 2008):

QUOTE (JoeAlbahari @ Nov 17 2009, 05:12 PM) *
create table Test (ID int not null primary key, Foo varchar(1) not null)
insert Test values (1, '')
insert Test values (2, 'X')


When I query it with LINQPad on our database, I still get the error. I then created a new database and ran the same SQL and got the exception. I then went to SQL Server 2005, and created a new database and ran the same SQL and got the same exception!

Here's the script generated by SQL Manager for the database and table:

CODE
USE [master]
GO

/****** Object: Database [TestLinq] Script Date: 11/18/2009 09:30:13 ******/
CREATE DATABASE [TestLinq] ON PRIMARY
( NAME = N'TestLinq', FILENAME = N'C:\SqlData\MSSQL10.DEV2008\MSSQL\DATA\TestLinq.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestLinq_log', FILENAME = N'C:\SqlData\MSSQL10.DEV2008\MSSQL\DATA\TestLinq_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [TestLinq] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestLinq].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [TestLinq] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [TestLinq] SET ANSI_NULLS OFF
GO

ALTER DATABASE [TestLinq] SET ANSI_PADDING OFF
GO

ALTER DATABASE [TestLinq] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [TestLinq] SET ARITHABORT OFF
GO

ALTER DATABASE [TestLinq] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [TestLinq] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [TestLinq] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [TestLinq] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [TestLinq] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [TestLinq] SET CURSOR_DEFAULT GLOBAL
GO

ALTER DATABASE [TestLinq] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [TestLinq] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [TestLinq] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [TestLinq] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [TestLinq] SET DISABLE_BROKER
GO

ALTER DATABASE [TestLinq] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [TestLinq] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [TestLinq] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [TestLinq] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [TestLinq] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [TestLinq] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [TestLinq] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [TestLinq] SET READ_WRITE
GO

ALTER DATABASE [TestLinq] SET RECOVERY FULL
GO

ALTER DATABASE [TestLinq] SET MULTI_USER
GO

ALTER DATABASE [TestLinq] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [TestLinq] SET DB_CHAINING OFF
GO




USE [TestLinq]
GO

/****** Object: Table [dbo].[Test] Script Date: 11/18/2009 09:31:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Test](
[ID] [int] NOT NULL,
[Foo] [varchar](1) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


I've also provided a screenshot of the treeview showing the column showing up as a Char instead of a String (according to your post #5). Is there anything else I can provide? I really want to try "The LINQPad Challenge" and see how it works out!

Thanks again,
Derek
Attached thumbnail(s)
Attached Image
 
Go to the top of the page
 
+Quote Post
JoeAlbahari
post Nov 18 2009, 07:23 AM
Post #10


Advanced Member
******

Group: Members
Posts: 215
Joined: 15-February 08
From: Perth, Australia
Member No.: 90
OK - I think I put you wrong before.

This was fixed not in 1.35.3, but in a later version which has not yet been pushed out as an auto-update.

Try downloading the preview here:

http://www.linqpad.net/preview/LINQPad.exe

Sorry about that!

Joe
Go to the top of the page
 
+Quote Post
d-price
post Nov 19 2009, 05:04 AM
Post #11


New Member
*

Group: Members
Posts: 7
Joined: 6-November 09
Member No.: 20,892
That was it! You just got my $19!

Thanks for all your help!
Derek
Go to the top of the page
 
+Quote Post
nirvana14888
post Today, 03:25 AM
Post #12


New Member
*

Group: Members
Posts: 1
Joined: Today, 03:18 AM
Member No.: 21,121
Thanks


--------------------
Arbitrage Conspiracy Reviews
Colon Cleanse
Go to the top of the page
 
+Quote Post
 

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

RSS Lo-Fi Version Time is now: 21st November 2009 - 08:41 PM
Powered By IP.Board © 2009  IPS, Inc
Powered by Minerva SEO IPB module