I'm using VS2005 (but the program I was working on was converted from VS2003) and I have a program thats running off of SQL SERVER 2000. I can't get the datagrid/gridview to populate from the variable(parameter: used in a control {textbox})

I have a rather long sqlstatement using the variable (parameter) and it should populate the datagrid/gridview but for some reason the binding isn't working.... Am I missing something?

PrivateSub bindGrid()
'Declarations
Dim strParent AsString = Me.txtParentID.Text
 
'SQL select
Me.SqlSelectCommand1.CommandText = "SELECT DISTINCT CASE WHEN LEN(RTRIM(per.NickName)) > 0 THEN" & _
" RTRIM(per.NickName) W" & _
"HEN LEN(RTRIM(per.NickName)) = 0 THEN RTRIM(per.FirstName) END AS NickName, RTRI" & _
"M(per.FirstName) + ' ' + RTRIM(per.MiddleName) AS FirstName, RTRIM(per.LastName)" & _
" AS LastName, RTRIM(per.Suffix) AS Suffix, RTRIM(comp.Name) AS CompanyName, RTRI" & _
"M(inst.Name) AS InstName, ord.ShipToAddrLine1, per.City, per.State, CASE WHEN pe" & _
"r.Country = 'United States' THEN '' ELSE UPPER(per.Country) END AS Country FROM " & _
"Person per INNER JOIN OrderMaster ord ON ord.ShipToID = per.ID INNER JOIN OrderD" & _
"etail od ON ord.ID = od.OrderID INNER JOIN Product prod ON od.ProductID = prod.I" & _
"D LEFT OUTER JOIN Company comp ON ord.ShipToCompanyID = comp.ID LEFT OUTER JOIN " & _
"CompanyCompany cc ON comp.ID = cc.CompanyID AND cc.CompanyRelationshipTypeID = 1" & _
" LEFT OUTER JOIN Company inst ON cc.RelatedCompanyID = inst.ID WHERE (prod.Paren" & _
"tID = '" & strParent & "') AND (ord.OrderStatusID IN (1, 2)) AND (ord.OrderType <> 'Cancellatio" & _
"n') AND (ord.ID NOT IN (SELECT OriginalOrderID FROM OrderMaster WHERE OrderType " & _
"= 'Cancellation')) ORDER BY per.LastName, per.FirstName"
'Bind and fill Datagrid
Me.DataGrid1.SetDataBinding(DsGrid1, "person")
Me.DsGrid1.Clear()
SqlDataAdapter1.Fill(DsGrid1)
Me.DataGrid1.Refresh()
EndSub
 
PrivateSub btnParentOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParentOK.Click
'call the bindGrid sub
bindGrid()
EndSub

You're taking the long and complicated route. Fill a DataTable ( not DataSet ) and then assign that to the DataGrid1.DataSource; voila. At least that's how it works in WinForms, but I think the WebForms version of the grid is something else, so I'm assuming you're writing Windows code.

Kill the dynamic SQL and use a stored procedure; faster and easier to manage, plus it's better code.

And you don't need any of the Me.xxx; that only comes into play when you actually need to fully qualify an object that's part of the class you're working on, but also another object you have access to. So if you had a line at the top of the code saying "Imports System.Threading" and you had a control on the form called Thread, you would have to use Me.Thread.Text = "xxx" to distinguish between, say, "Thread.Sleep(100)" or "x = new Thread()". This doesn't really have much effect one way or the other, except that it's less keystrokes, and less to go back and read/understand when you need to fix a bug ... like right now. Tip of the day.

Without loading up VS and testing it, I would assume this would work...

Dim myCommand = New SqlDataAdapter("SQL COMMAND HERE", CONNECTION HERE)
Dim ds As Data.DataSet = New Data.DataSet
myCommand.Fill(ds)
GridView1.DataSource = ds
GridView1.DataBind()
myCommand.dispose()

- Post here if/when you solve this so that others in the future are able to find the answer

Originally Posted by ForrestCroce

You're taking the long and complicated route. Fill a DataTable ( not DataSet ) and then assign that to the DataGrid1.DataSource; voila. At least that's how it works in WinForms, but I think the WebForms version of the grid is something else, so I'm assuming you're writing Windows code.

Kill the dynamic SQL and use a stored procedure; faster and easier to manage, plus it's better code.

And you don't need any of the Me.xxx; that only comes into play when you actually need to fully qualify an object that's part of the class you're working on, but also another object you have access to. So if you had a line at the top of the code saying "Imports System.Threading" and you had a control on the form called Thread, you would have to use Me.Thread.Text = "xxx" to distinguish between, say, "Thread.Sleep(100)" or "x = new Thread()". This doesn't really have much effect one way or the other, except that it's less keystrokes, and less to go back and read/understand when you need to fix a bug ... like right now. Tip of the day.

hmmm... let me play with it some more. As I stated prior, this was created in VS2003 and then converted in VS 2005. (this is the first time I've ever fired up 2005....certain phrases and syntax are different.)
I'll take your suggestions and play.
I'll be back with an update and/or errors.
lol

Originally Posted by boomers

Without loading up VS and testing it, I would assume this would work...

Dim myCommand = New SqlDataAdapter("SQL COMMAND HERE", CONNECTION HERE)
Dim ds As Data.DataSet = New Data.DataSet
myCommand.Fill(ds)
GridView1.DataSource = ds
GridView1.DataBind()
myCommand.dispose()

- Post here if/when you solve this so that others in the future are able to find the answer

I'm going to also give this a shot... you'll hear back from me later today.
Thanks, guys. You all rock!

mb

.

You have to call GridView1.DataBind() after setting the DataSource property in ASP.NET but not Windows Forms.NET.

Hey! Sorry it took so long to get back to you! Thanks for your help! It's all good, now! You guys are the best!

We know.