Dynamic CommandText for an SqlDataAdapter

  • Thread starter Thread starter Rickard Andersson via .NET 247
  • Start date Start date
R

Rickard Andersson via .NET 247

I'm using a DataGrid to display and edit entries from a databasetable called Contacts. I've completed the MSDN document"Walkthrough: Using a DataGrid Web Control to Read and WriteData" and it's working out fine. My SQL select command lookslike this:

SELECT Id, Name, Phone, Email FROM Contacts

Now I want to restrict that query to only display entries with acertain OrganizationId. The OrganizationId I want to use can befound in Session["OrganizationId"]. I basically want this:

SELECT Id, Name, Phone, Email FROM Contacts WHERE OrganizationId= THE_ID_FROM_THE_SESSION

What I'm asking is how I should do this. I've tried editing thesqlSelectCommand manually in "Web Form Designer generated code"that was generated for me by the SqlDataAdapter wizard, but if Ieven touch that code, VS.NET freaks out and removes theCommandText property completely (and then I am unable to re-addit!). I guess I could add it via the properties for theSelectCommand for the SqlDataAdapter in design mode, but I can'tsee how to add dynamic data such as a session variable in thattextbox. What am I missing/doing wrong?
 
Dear Rickard,
If I'm not wrong, there is a comment in the section "Web Form Designer generated code"-which kindly suggests You not to modify the designer-generated code :
You should modify the SQLCommand in other sections of the code. Modify the SQL command in event that occurs when the command should be executed. This is generally in someBtn.Click event

if txtOrgId.Text <> "" the
mSqlCmd.Text += "WHERE OrganizationId = " & THE_ID_FROM_THE_SESSIO
endif

Good Luck
Andranik Khachatryan,
Armenian Sofware Co
 
Hi Rickard,

As Andranik told you, you shouldn't modify the designer generated code.
However, if you are careful you can (not that I recommended it) - you just
can't add anything dynamic (only static text is good) like your
THE_ID_FROM_THE_SESSION.

I think that a good solution for you would be to use parameter for id
session.
Take a look at
Parameters in Data-Adapter Commands
..net help topic.

--
Miha Markic [MVP C#] - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

I'm using a DataGrid to display and edit entries from a database table
called Contacts. I've completed the MSDN document "Walkthrough: Using a
DataGrid Web Control to Read and Write Data" and it's working out fine. My
SQL select command looks like this:

SELECT Id, Name, Phone, Email FROM Contacts

Now I want to restrict that query to only display entries with a certain
OrganizationId. The OrganizationId I want to use can be found in
Session["OrganizationId"]. I basically want this:

SELECT Id, Name, Phone, Email FROM Contacts WHERE OrganizationId =
THE_ID_FROM_THE_SESSION

What I'm asking is how I should do this. I've tried editing the
sqlSelectCommand manually in "Web Form Designer generated code" that was
generated for me by the SqlDataAdapter wizard, but if I even touch that
code, VS.NET freaks out and removes the CommandText property completely (and
then I am unable to re-add it!). I guess I could add it via the properties
for the SelectCommand for the SqlDataAdapter in design mode, but I can't see
how to add dynamic data such as a session variable in that textbox. What am
I missing/doing wrong?
 
Back
Top