can't display data from an SQL server viewtable

P

Patrick Sullivan

I have a view of 3 tables joined with IDs named "appsvw" I can't get it to
display in my datagrid1. code is below. TIA.

Protected Const CONNECTION_STRING As String = _
"Server=localhost;" & _
"DataBase=Jsearch;" & _
"Integrated Security=SSPI;Connect Timeout=5"

Private ConnectionString As String = CONNECTION_STRING
Private tdsdata As DataSet1

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

Dim con As New SqlConnection(ConnectionString)
tdsdata = New DataSet1
Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
appsvw", con)
con.Open()
adapter.Fill(tdsdata)
con.Close()
DataGrid1.DataSource = tdsdata
DataGrid1.DataMember = "appsvw"
End Sub


viewtable creation
CREATE VIEW dbo.appsvw AS
SELECT TOP 1000 dbo.Positions.posName AS [Position], dbo.Companies.compName
AS Company, dbo.Applications.appDate AS [Date],
dbo.Applications.appNotes AS Notes,
dbo.Applications.appID AS ID
FROM
dbo.Companies INNER JOIN
dbo.Applications ON
dbo.Companies.compID = dbo.Applications.appCompany INNER JOIN
dbo.Positions ON
dbo.Applications.appPosition = dbo.Positions.posID
ORDER BY dbo.Applications.appID

TIA
 
M

Mark Ashton

try this
adapter.Fill(tdsdata, "appsvw")
or
DataGrid1.DataMember = "Table"

When an adapter.Fill(dataset) is called, which table should it map to or
create. Against sql server, by default the client doesn't have any metadata
information other than column name and type. If we did have metadata
information it would have the basetable information for where the column
originated - not the views. With your three tables in the view, which
table would should be picked. By default adapter.Fill(DataSet) will fill
DbDataAdapter.DefaultSourceTableName which is "Table".
 
P

Patrick Sullivan

Hey Mark, thanks a lot! This is what finally worked! BTW, There is no table
in the query, it's just three tables joined in an SQL server 2000 view, so
the view IS the table, from what I understand. It's the only way I can see
to display data from 3 tables in a single datagrid row with no child table
controls.

adapter.Fill(tdsdata, "appsvw")

con.Close()

DataGrid1.DataSource = tdsdata

DataGrid1.DataMember = "appsvw"


--

Patrick Sullivan, AA-BA, BA-IT

Mark Ashton said:
try this
adapter.Fill(tdsdata, "appsvw")
or
DataGrid1.DataMember = "Table"

When an adapter.Fill(dataset) is called, which table should it map to or
create. Against sql server, by default the client doesn't have any metadata
information other than column name and type. If we did have metadata
information it would have the basetable information for where the column
originated - not the views. With your three tables in the view, which
table would should be picked. By default adapter.Fill(DataSet) will fill
DbDataAdapter.DefaultSourceTableName which is "Table".

--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Patrick Sullivan said:
I have a view of 3 tables joined with IDs named "appsvw" I can't get it
to
display in my datagrid1. code is below. TIA.

Protected Const CONNECTION_STRING As String = _
"Server=localhost;" & _
"DataBase=Jsearch;" & _
"Integrated Security=SSPI;Connect Timeout=5"

Private ConnectionString As String = CONNECTION_STRING
Private tdsdata As DataSet1

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

Dim con As New SqlConnection(ConnectionString)
tdsdata = New DataSet1
Dim adapter As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM
appsvw", con)
con.Open()
adapter.Fill(tdsdata)
con.Close()
DataGrid1.DataSource = tdsdata
DataGrid1.DataMember = "appsvw"
End Sub


viewtable creation
CREATE VIEW dbo.appsvw AS
SELECT TOP 1000 dbo.Positions.posName AS [Position],
dbo.Companies.compName
AS Company, dbo.Applications.appDate AS [Date],
dbo.Applications.appNotes AS Notes,
dbo.Applications.appID AS ID
FROM
dbo.Companies INNER JOIN
dbo.Applications ON
dbo.Companies.compID = dbo.Applications.appCompany INNER JOIN
dbo.Positions ON
dbo.Applications.appPosition = dbo.Positions.posID
ORDER BY dbo.Applications.appID

TIA
Click to expand...
Click to expand...
 
Top