DataSource Controls :: Appending Some Variable To SQL Query?
Jan 21, 2010
I want query like this
declare @stateQuery as nvarchar(200)
if @stateid is null
set @stateQuery=''
if @stateid is not null
set @stateQuery='and tbl_city.stateid ='+cast(@stateid as nvarchar(10))
set @length=(select top 1 len(dbo.Tbl_city.cityName) FROM dbo.tbl_city INNER JOIN tbl_cityinfonew ON dbo.tbl_city.cityId = tbl_cityinfonew.cityId where (tbl_cityinfonew.language =@language )+cast( @stateQuery as nvarchar(100)) order by tbl_cityinfonew.createdate desc)
I am using a data list to display some value retrieved from a SQL query. My problem is that I would like to insert a variable for the field of ISDS_454.dbo.booksusers.userID. The variable has already been declared and stored upon page load. I just need some way to get it within the SQL query for the datalist.
<script runat="server"> Sub Page_Load() Dim ID As String = "D07AD7E0-8E34-4C18-9B4D-10576B0CC6BF" End Sub </script> <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate> fic: <asp:Label ID="ficLabel" runat="server" Text='<%# Eval("fic") %>' /> <br /> <br /> </ItemTemplate> </asp:DataList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ISDS_454ConnectionString %>" SelectCommand="Select COUNT(*) as fic From ISDS_454.dbo.Books, ISDS_454.dbo.booksusers, ISDS_454.dbo.Genre where ISDS_454.dbo.Books.ISBN = ISDS_454.dbo.booksusers.ISBN And ISDS_454.dbo.Genre.Genre_ID = '1' And ISDS_454.dbo.booksusers.userID = 'D07AD7E0-8E34-4C18-9B4D-10576B0CC6BF'"> </asp:SqlDataSource>
I am working on an asp webform that pulls from an ODBC datasource then puts the data in an sql server. Once the data is in the sql server several queries pull data from it to provide information for a report.
This is an example of what I am talking about
private void ODBCpdEmployeeAb() { DataTable dt = new DataTable();
[Code]....
The first part gets the data from the ODBC connection and the next part stores it in an sql server table. The problem is I have these methods in the page load event of the webform so everytime it loads it just puts the exact same data in the sql server table which would then provide incorrect information on the report.
Is there a way to append the most recent data from the ODBC table to the sql server ?
1. I have a GridView on my page and it uses sqldatasource with parameterized query. What I want to do is, on page load (where nothing has been selected so no parameter supplied), I want it to query everything (something like SELECT * FROM [this_table]) but since my SelectCommand is something like
SELECT * FROM [this_table] WHERE [this_column] = @someParameters AND [that_column] = @someParameters.
Can I play around with default value to achieve something like that but how ? Now, when the page loads, it doesn't show anything (No Gridview).
2. On my page, I made something like (username, gender, address, and more) and one single search button. That means, no single control enable auto postback. What I am trying to accomplish is building dynamic query
(if username specifed -> SELECT * FROM [this_table] WHERE [username] LIKE @username).
If both username and gender are specified (SELECT * FROM [this_table] WHERE [username] LIKE @username AND [gender] = @gender) and you know the rest. How can I do this using GridView and SqlDataSource ? To my knowledge, I can only specify one SELECT statement in a sqldatasource.
By right clicking on my database i created a query in server explorer. But where are this query stored can't find them back. I should aspect that their is a folder query's like there is a folder tables but this isn't the case.
I am trying to use a variable from the code-behind page in a sqlDataSource WHERE statement. I am using Membership.GetUser().ProviderUserKey to obtain the UserId of the logged in user and I would like to only show record that have this UserId as a foreign key. How can I write the SQL for this?
Here is what I have so far:
SQL Query:
SelectCommand="SELECT [UserID], [CarID], [Make], [Model], [Year] FROM [Vehicle]"
Dim MediaQuery = From m In dB.DOWNLOADS _Where m.ID = id _Select
which returns a record from the database. One of the fields in the record is a FK to another table. I need to read this value to be able to set a dropdown based on the ID but I can't work out how to access it. For a standard record I can just do the following txtTitle.Text = MediaQuery.FirstOrDefault().TITLE
However with the foreign key it doesn't work like that. I've tried drpGroup.SelectedIndex = MediaQuery.FirstOrDefault().DOWNLOAD_GROUPS.ID where DOWNLOAD_GROUPS is the FK field but it returns Object reference not set to an instance of an object. If you're simply wanting to read some values from a single db record in the entitiy framework and one is a foreign key how should I go about getting the value?
As you can see, the connectionstring parameter is defined to a specific connection string name and I need to be able to set such a parameter to a different value, for example, to a session variable content.
I am storing a custom "Organisation" object as a session variable. One of the properties of the Organisation object is "OrganisationID" (integer). I have a DataSource that requires a parameter value to run, and I want to use a SessionParameter to populate this. In a previous version, I stored the OrganisationID directly as a session variable. In that case, I could easily access it like this:
[Code]....
However, how do I now access the OrganisationID property of an "Organisation" type session variable (called "Organisation")? I have tried this, which does not seem to work: <asp:SessionParameter Name="OrganisationID" SessionField="Organisation.OrganisationID" Type="Int32" />
I have developed the below web page which has an asp.net 3.5 listview control as it's frontend and an sql server db as it's backend.
[URL]
The first row on the web page is the inserting row. What I am hoping to achieve is that when the user enters their appropriate data they click the "Next" button to assign this dataset with it's own identity ID (customer_id) value within my database (this works perfectly), at the same time on the fly I want to use this customer_id as a query string variable to populate a child page (customerTrades.aspx) in order to pin further data to this identity. Currently I am attempting to use the below code within the iteminserted event of my listview control but I receive the error: "object reference not set to the instance of an object"??
Is this because the child page cannot find the customer_id variable being passed through the query string? Because it's not actually created yet? Am I using the wrong listview event? How can I achieve what I am looking for?
I must also mention that I'm using a linqdatasource to insert my data on runtime into my database. However, I am using the listview iteminserting event to insert my frontend dropdownlist selected values, code below:
Is it possible to use a database name as a variable in a SP?I use a separate database for each custromer.I also use a master database that keeps database name, ID, and Password.I then want to generate an SQL select against the appropriate Database based on the userID and password (for an XML REsponse)is this possible?Set @DBName as varchar(50)Select * from @DBName.dbo.tablename where bla bla blaNote: that the actual select is about 150 lines with multiple sub selects
Would anyone know how to pass a variable to SqlDataAdapter. Here is my code.
TextBox1.Text = 1 Dim textboxval As String textboxval = TextBox1.Text connection.Open()Using connection As New SqlConnection("Data Source=CHRIS-PCSQLEXPRESS;Initial Catalog=TorGHL;Integrated Security=True") Dim dapubcb As New SqlDataAdapter("SELECT [BarName], [MenuSubId], [Address], [PhoneNo], [BarURL], [Email] [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] = texboxval ", connection)
I have an objectDataSource which uses my stored procedure GetNotifications
And my GridView is bound to that objectDataSource
the only problem is that my stored procedure requires the variable @UserName and then returns the values for that username, but I do not know how to pass the variable to the objectDataSource, does anyone know the code for this?
I can get userName from HttpContext.Current.User.Identity.Name but how do I pass that to the objectDataSource?
I am trying to pass a variable within a selectcommand statment in ASP.NET and I have researched online how to do it but I can't seem to find a way to do it...What I am trying to do is write a code for a web page that will display a page with useful links on it where the links are separated into different topics and where everything is dynamically grabbed from a database. This is what I have so far:
I've done some SSIS package work in the past, but am by no means an expert.
I want to use an SSIS package to do the following:
1. Get the file name of the newest file in a windows folder (using a filename template like his: filename_ *.txt where the * part is always a date in the format of yyyy-mm-dd.
2. Use that file name in later portions of the SSIS package by reading the file and transferring data from it to a SQL table using a Data Flow task.
and use the result (which should be the name of the newest file) to open that file and pull data out of it and into a 2nd SQL table. I admit, I'm not sure how to use the dynamic result of this SELECT query as the file name in a flat file connection manager.
I am rather confused as how or where to declare this variable in asp.net.
[Code]....
Whenever i put this in my sqldatasource for it is asking me to Define Parameters:
The wizard has detected one or more parameters in your SELECT statement. For each parameter in the Select Statement, choose a source for the parameter's value.
i want to ask how to i set variable in asp:sqldatasource in sqlcommand in asp.net c#.... This is my code
asp.net <dx:ASPxListBox ID="lsAssignToko" runat="server" DataSourceID="SqlDataSource6" TextField="NAMA" ValueField="ID" AutoPostBack="true" EnableCallbackMode="True" SelectionMode="CheckColumn" OnSelectedIndexChanged="lsAssignToko_SelectedIndexChanged"> <Columns> <dx:ListBoxColumn FieldName="ID"/> <dx:ListBoxColumn FieldName="NAMA" /> </Columns> </dx:ASPxListBox> <asp:SqlDataSource ID="SqlDataSource6" runat="server" ConnectionString="<%$ ConnectionStrings:Ora2010 %>" ProviderName="<%$ ConnectionStrings:Ora2010.ProviderName %>" SelectCommand="SELECT ID, NAMA FROM REF_TOKO WHERE ID IN ( :inTOKO ) ORDER BY NAMA"> <SelectParameters> <asp:SessionParameter Name="inTOKO" SessionField="inTOKO" Size="200" Type="String" /> </SelectParameters> </asp:SqlDataSource> c# string dummy = ""; string data = ""; string inTK = ""; string inTOKO = ""; //string inTOKO1 = ""; int pot; for (int i = lsToko.SelectedItems.Count - 1; i >= 0; i--) { string tok = lsToko.SelectedItem.Text.ToString(); data = lsToko.SelectedItem.Value.ToString(); dummy = dummy + "" + data + ","; int index = lsToko.SelectedItem.Index; lsToko.Items.RemoveAt(index); } if (lsToko.SelectedItems.Count <= 1) { inTK = dummy.Length.ToString(); pot = int.Parse(dummy.Length.ToString()) - 1; inTOKO = dummy.Substring(0, pot); } Session["inTOKO"] = inTOKO;
the problem is where in my variable inTOKO consist of couple of value... Example while variable inTOKO = one value success but while value inTOKO = two value error inTOKO = 1101111 => success inTOKO = 1101111,1211321 => not success
I create a variable in code behind to save today's date.
[Code]....
And I want to save it as a parameter for an accessDataSource but I'm not finding anywhere that tells me how to do this properly. My attempts thus far have been
I'm making a page with a DropDownList (called ddlSupp) that contains several suppliers, and I populate it with an SqlDataSource, all in front code.
In another part of the page I make a CheckBoxList, and populate it again with an SqlDataSource. The select command of the SqlDataSource is a procedure call to my MySQL database. However, what the CheckBoxList gets populated with depends on who is selected in ddlSupp. How do I make a variable in front code that will take the value from ddlSupp and populate my checkbox accordingly?
Here is my code:
[Code]....
I get a MySQL error at (<%$ddlSupp.SelectedValue %>) so I know the wrong value is being passed into the query. How can I fix this?
I'm using the DetailView control to display employee details from a specfic company, but it seems when I try to create the query in the data source wizard I'm unable to select the variable for the WHERE clause, only the web pages control components.
The employees are selected through the company name which is entered at the beginning of the page and in turn is used to select the company id.
