ADO.Net data adapter doesn't allow a table of more than 100 column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In our VB.Net project, we are using VS.Net's data adapter wisard to generate
SQLDataAdapter. What we find out is that if the table we use for this
contains no more than 99 columns, everthing will be fine. But as soon as you
go beyung that, the process will error out with a message indicating that
generating select statement failed.

I wonder is that a limitation of ADO.net? But I can run select query against
the same table without any issue. What is wrong?

Thanks in advance

Feng
 
It is a set limitation.

In most cases, tables with this many columns are improperly normalized (or
denormalized). While this may not be the answer you desire (and you may have
an exception), it is true in the cases I have seen.

You can get around the limitation by creating a vertical split of the table
(pull first 99 columns with one query, the rest with another), but you will
have to put forward the rest of the CRUD, as the Adapter.Update() method will
not automagically save changes if you go this route. In addition, to view all
columns, you will have to set up the binding in code rather than using the
native databinding in .NET.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
Feng - I believe this is only b/c of using the wizard. If you really need
this to happen, you can roll your own code for it and I'm almost positive
that it will work. I know it's a lot of potential work to do for something
that i'm only 'almost positive' of, but I'd be willing to bet $100.00 on it.
The Wizard doesn't let you do it but I think it's just a visual tools
limitation. However, as the others have pointed out, 100+ column table is
probably a design with some flaws in it and very seldom would you really
need all of these columns. However you probably are already aware of this
and I'm guessing have a table structure in place that you can't easily
change --- if that's the case, just wwanted to mention it as a FYI

good luck,

Bill
 
Feng,

I was curious about this, so I created a program to create a database and a
table with 200 columns.

To that I have inserted a row using the dataadapter.

If you want to try it, see the code bellow. It needs a datagrid on a form
and of course an SQLServer and ou need to change the name "TheServerName" in
the name of that

\\\\
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load

Dim Conn As New
SqlConnection("Server=TheServerName;DataBase=;Integrated Security=SSPI")
Try
Conn.Open()

Try
Dim strSQL As String = "CREATE DATABASE THISCRAZYTEST"
Dim cmd As New SqlCommand(strSQL, Conn)
cmd.ExecuteNonQuery()
cmd.CommandText = _
"USE THISCRAZYTEST" & vbCrLf & _
"CREATE TABLE CrazyTest ( "
For i As Integer = 0 To 199
cmd.CommandText += "V" & i.ToString & " int, "
Next
cmd.CommandText += " CONSTRAINT [pk_V0] PRIMARY KEY
CLUSTERED(V0))"
cmd.ExecuteNonQuery()
Dim ds As New DataSet
Dim da As New SqlDataAdapter("Select * from crazyTest",
Conn)
da.Fill(ds)
Dim dr As DataRow = ds.Tables(0).NewRow
For i As Integer = 0 To 199
dr(i) = i
Next
ds.Tables(0).Rows.Add(dr)
da.InsertCommand = New SqlCommand
da.InsertCommand.CommandText = "INSERT INTO CrazyTest( "
For i As Integer = 0 To 198
da.InsertCommand.CommandText += "V" & I.ToString & ","
Next
da.InsertCommand.CommandText += " V199 ) VALUES ("
For i As Integer = 0 To 198
da.InsertCommand.CommandText += "@V" & I.ToString & ","
Next
da.InsertCommand.CommandText += "@V199 ) ; Select * FROM
CrazyTest Where V0 = @V0 "
da.InsertCommand.Connection = Conn
For i As Integer = 0 To 199
da.InsertCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@V" & i.tostring, _
System.Data.SqlDbType.Int, 4, "V" & i.tostring))
Next
da.Update(ds)
da.SelectCommand.CommandText = "Select V195, V199 from
CrazyTest"
ds = new dataset
da.Fill(ds)
datagrid1.datasource = ds.Tables(0)
Catch ex As SqlException
MessageBox.Show(Ex.ToString)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
Conn.Close()
End Try
End Sub
///

I hope this helps,

Cor
 
Back
Top