Datagrid not filling up [RESOLVED]

[drpcken]

Usually I create my dataset, dataadapter manually in Visual Studio, but in this case, I need the dataadapter.SelectCommand to give me only rows that one column matches the value of a textbox on the webform. But it wouldn't let me do that, so I tried manualy coding the dataadapter, and dataset.

But when the page loads, there is no data in the datagrid. Am I misssing something??

Thanks again, here's the code. No errors on load, just no data in datagrid1. It shows the column headings. Pay special attention to the selectcommand of the dataadapter.


Private dsItems As New DataSet()
Private daItems As New SqlDataAdapter()


Private Sub LoadLineItems()

If SqlConnection1.State = ConnectionState.Closed Then SqlConnection1.Open()

daItems.SelectCommand = New SqlCommand("SELECT ItemNumber, Price, Quantity from dbLineItems where RecID = '" + txtRecId.Text + "'", SqlConnection1)
daItems.Fill(dsItems)
DataGrid1.DataSource = dsItems
DataGrid1.DataBind()
SqlConnection1.Close()

End Sub

[mendhak]

The legendary CyberHawke once told me:

'you could do it this way
 
Dim dt As DataTable
dt = ds.Tables(0)
DataGrid1.DataSource = dt
 
 
 
'or this way
 
DataGrid1.DataSource = ds
DataGrid1.DataMember = "Table"

And then DataBind() it.

HTH.

[drpcken]

Originally posted by mendhak
The legendary CyberHawke once told me:

'you could do it this way
 
Dim dt As DataTable
dt = ds.Tables(0)
DataGrid1.DataSource = dt

Ok kew, here's the code I made from that:

Private Sub LoadLineItems()
Dim dt As DataTable

If SqlConnection1.State = ConnectionState.Closed Then SqlConnection1.Open()

daItems.SelectCommand = New SqlCommand("SELECT ItemNumber, Price, Quantity from dbLineItems where RecID = '" + RecId + "'", SqlConnection1)
daItems.Fill(dsItems)
dt = dsItems.Tables(0)
DataGrid1.DataSource = dt
DataGrid1.DataBind()
SqlConnection1.Close()


End Sub



But still nothing in my datagrid. For some reason, I think it has something to do with the SelectCommand.

[drpcken]

hmmmm
ok I just changed the select command to just a normal query, and it STILL isn't filling my datagrid. So its not the selectCommand

[mendhak]

Private Sub LoadLineItems()
        Dim dt As DataTable
 
        If SqlConnection1.State = ConnectionState.Closed Then SqlConnection1.Open()
 
        daItems.SelectCommand = New SqlCommand("SELECT ItemNumber, Price, Quantity from dbLineItems where RecID = '" + RecId + "'", SqlConnection1)
        daItems.Fill(dsItems)
        dt = dsItems.Tables(0)
        DataGrid1.DataSource = dt
 
        SqlConnection1.Close()
 
 
    End Sub

Now? I removed the DataBind() line...

[mendhak]

I hope you have the daItems, dsItems, connections etc. all declared.

[mendhak]

Let me also show you an example I quickly wrote write now to see if I was going wrong anywhere:

Dim dt As DataTable
        Dim ds As New DataSet
 
 
        Dim strConn2 As String
 
        strConn2 = "Data Source=(local);" & _
                            "Initial Catalog=NorthWind;" & _
                            "User ID=sa;Password=;"
        Dim sqlconnection1 As New SqlConnection(strConn2)
 
 
        sqlconnection1.Open()
 
        Dim da As New SqlDataAdapter
        da.SelectCommand = New SqlCommand("SELECT * FROM Products", sqlconnection1)
 
        da.Fill(ds)
        dt = ds.Tables(0)
        DataGrid1.DataSource = dt
 
 
 
 
        'DataGrid1.DataBind()  
        'Had to comment this out... 
        SqlConnection1.Close()

[drpcken]

hmm still no go and yes i declared all my variables. Here's more code, I just can't seem to get this...

Private VendorID, RecId, Desc, DateNeeded, Reason, Clinic As String
Private Const CONN_STRING = "server=(local);uid=***;pwd=****;initial catalog=****"
Private sqlconn As SqlConnection
Public dsItems As New DataSet()
Public daItems As New SqlDataAdapter()



Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not IsPostBack Then
VendorID = Session("VendorId")
RecId = Session("RecId")
Desc = Session("Desc")
DateNeeded = Session("DateNeeded")
Reason = Session("reason")
Clinic = Session("Clinic")

txtRecId.Text = RecId
txtDescription.Text = Desc
txtDateNeeded.Text = DateNeeded
txtReason.Text = Reason
ddlUserClinic.SelectedItem.Text = Clinic
LoadLineItems()
End If
If sqlconn.State <> ConnectionState.Open Then sqlconn.Open()
End Sub

Private Sub LoadLineItems()
Dim dt As DataTable

SqlConnection1.Open()

daItems.SelectCommand = New SqlCommand("SELECT ItemNumber, Price, Quantity from dbLineItems", SqlConnection1)
daItems.Fill(dsItems)
dt = dsItems.Tables(0)
DataGrid1.DataSource = dt
'DataGrid1.DataBind()
SqlConnection1.Close()


End Sub


Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim oRec As New Recquisition()

Try
With oRec
.VendorId = VendorID
.Description = txtDescription.Text
.DateNeeded = txtDateNeeded.Text.ToString
.Reason = txtReason.Text
.RecqusitionId = txtRecId.Text
.Clinic = ddlUserClinic.SelectedItem.Text
.Save()
End With
Finally
oRec = Nothing
End Try
End Sub

[drpcken]

and i keep getting an Object reference not set to an instance of an object error pointing back to this line


da.SelectCommand = New SqlCommand("Select * from dbLineItems where RecID = '" + TextBox1.Text + "'", sqlconn)

[mendhak]

Originally posted by drpcken


da.SelectCommand = New SqlCommand("Select * from dbLineItems where RecID = '" + TextBox1.Text + "'", sqlconn)

Shouldn't that be daItems?

Edit: never mind. Still checking.

[mendhak]

Yes, check to see if you have da instead of daItems.

Also, look at this part of your code:

Private Const CONN_STRING = "server=(local);uid=***;pwd=****;initial catalog=****"
Private sqlconn As SqlConnection
 
'and later on:
 
 If sqlconn.State <> ConnectionState.Open Then sqlconn.Open()

Nowhere have you used CONN_STRING when opening up the SQL Connection!

[drpcken]

Ok I think I got it WIth your help of course!! The CONN_STRING was an extra connection i had in there, SqlConnection1 was in the designer. The da was correct, but i was testing another form to see if i could recreate the error, so you were right insaying daItems was the correct adapter. I finally got the data to bind. With this...


Private Sub LoadLineItems()
sqlconn.ConnectionString = "server=(local);uid=sa;pwd=****;initial catalog=t***"
Dim cmdLoad As New SqlCommand("select * from dbLineItems where RecId='" + txtRecId.Text + "'", sqlconn)
Dim daItems As New SqlDataAdapter(cmdLoad)

Try
sqlconn.Open()
daItems.Fill(dsItems, "dbLineItems")
DataGrid1.DataSource = dsItems.Tables(0)
DataGrid1.DataBind()
sqlconn.Close()
Finally
'To do after binding
End Try
End Sub


I think I wasn't specifiying the member in my .Fill

Thanks for your help. Probably won't be the last time i'll need it though

You are the man!

[mendhak]

Yeah, I'm da frogg

[drpcken]

Thanks once again!!

[mendhak]

Now edit your first post and add [Resolved].