im trying to write a select command that gets info from 1 table and counts 11 differnt things in it im not sure if this is even posiable but if it is could someone help this is what i got for counting all of them
SELECT owner, COUNT(*) AS TotalPots
FROM Items
WHERE (Name = 'Holy Potion') OR
(Name = N'Arcane Potion') OR
(Name = N'Shadow Potion') OR
(Name = N'Fire Potion') OR
(Name = N'Kinetic Potion') OR
(Name = N'Potion of Holy Resistance') OR
(Name = N'Potion of Arcane Resistance') OR
(Name = N'Potion of Shodow Resistance') OR
(Name = N'Potion of Fire Resistance') OR
(Name = N'Potion of Kinetic Resistance')
GROUP BY owner
ORDER BY COUNT(*) DESC
the 11 coloums i want are Holy, Arcane, Shadow, Fire, Kinetic, Holy Resist, Arcane Resist, Shadow resist, Fire Resist, Kinetic Resist, And Total Pots
Also would like it on my Asp.net page at the bottom of the grid view to have a total row that counts all the colums up
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
I'm using the following Select Command:SELECT MAX(Document) AS DOC FROM dbo.Communicator WHERE (ReleaseDate <= { fn NOW() })It shows the most current date in the ReleaseDate column - even if the date is in the future. I don't want it to show future dates. If I change the command to WHERE (ReleaseDate >= { fn NOW() }) it doesn't work at all. I only want it to return one row - the latest releases date that is equal to or less than now.Any ideas?
I have a web form that has textbox1, textbox2 and DropDownList1. Let’s say textbox one is first name, textbox2 is last name and dropdownlist1 is age. How do I write a query that will select from database table dbo.emoployee where last name = dbo.employee.lastname and all other fields are blank, I want it to return all employees with that last name. If someone types in the last name and selects the age from the drop down list then I want to return all employees where last name = dbo.employee.lastname and age = dbo.employee.age. If someone types in just the first name then I want to return all employees where first name = pub.employees.firstname? I have been trying to do this using the SQLDatasource but cannot seem to figure it out.
I have a web form that has textbox1, textbox2 and DropDownList1. Let’s say textbox one is first name, textbox2 is last name and dropdownlist1 is age. How do I write a query that will select from database table dbo.emoployee where last name = dbo.employee.lastname and all other fields are blank, I want it to return all employees with that last name. If someone types in the last name and selects the age from the drop down list then I want to return all employees where last name = dbo.employee.lastname and age = dbo.employee.age. If someone types in just the first name then I want to return all employees where first name = pub.employees.firstname? I have been trying to do this using the SQLDatasource but cannot seem to figure it out.
I currently have a webpage that allows visitors to post links to their own website. As a spam filter I want to create a scheduled task that selects repeat entries in the database under the column name domain. I already made a filter to take everything out of the link the provide and leave it with just the domain name. I tested it and it works. Now I need a SQL command to select all the rows with repeats of the domain. Look at the following table example to better understand what I mean uid x y domain1 100 110 www.spam.com2 100 120 www.spam.com3 110 130 www.homepage.com4 210 220 www.myaspspam.com5 510 560 www.myaspspam.com in this example I would like 1 and 2 to be selected as well as 4 and 5 into a dataadapter so that I can delete them accordingly.
I am trying to run a select command on an OLE DB Command transform such as:
Select * from table where id = ?
I have the mapping to the parameter working and the command is working but how to map the select output to columns when you can't create output columns on the OLE DB Command?
Thanks for this help. My problem is a little different. I have a stored procedure with an output parameter that I want to use in an OLE DB transform. I can see how to add output columns but can't figure out how to set them from the stored procedure return. Any ideas?
I am using <asp:SqlDataSource ID and for the Select Command, the following, where the WHERE clause ... for an exact match (=) works correctly: SelectCommand="SELECT [PatientID], [MedRecord] , [Accession], [FirstName], [LastName], [Address1] FROM [ClinicalPatient] WHERE (LastName = @LastName) ORDER BY [LastName]DESC"> I would like to do a "LIKE" search where the LastName Parameter is matched using "LIKE". In this situation how would the syntax be written.... I tried: LastName LIKE '%" & LastName & "%'" But I get an error???? Any suggestions, please... Thanks !!
I have a SqlDataSource object that is bound to a GridView control. I have configured the SqlDataSource with a default select command. Under certain values of query strings on the URL for this page (Default.aspx), I want to change the select command. So I put the statements in the Page_Load method for Default.aspx to define SqlDataSource1.SelectCommand. The changed SelectCommand works fine for the first page of GridView data and shows 5 GridView pages, but if I switch to one of the other pages, it seems to revert to the default SelectCommand (which generates 19 GridView pages). I assume I should put my code to change the SelectCommand somewhere else. Can someone help me with where to put it? Thanks!
Hi, I'm trying to use a CheckBoxList to display certain records in a Grid View. I have a Grid View, a CheckBoxList with four items and a SqlDataSource and when the user check or uncheck one or more items in the CheckBoxList the Grid View should show records accordingly. I’ve tried to make it work but it’s only the first checked item in the Grid View that has any effect. I use VB. Thanks
Hi Guys, I'm fairly new to .Net and have reached a point in the coding I can't seem to get past. I've created a table with Visual Studio 2005 and MySQL Server 2008 (Beta) and am trying to make the "Select" button bind a result from that table (in this case, image_id) then open up that image ID in a Image details page, I've created the table in Visual Studio 2005. Here's what I've got so far; Page 1Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)Dim row As GridViewRow = GridView1.SelectedRowSession("session_current") = row.Cells(1).TextServer.Transfer("page2.aspx")End SubI think this page isn't at fault as it reads the parameter into a text box on page2 Page2</asp:GridView><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>"ProviderName="<%$ ConnectionStrings:ConnectionString3.ProviderName %>" SelectCommand="SELECT * FROM [profiles] WHERE ([profile_id] LIKE '%' + ? + '%')"><SelectParameters><asp:SessionParameter Name="profile_id" Type="String"/></SelectParameters> I'd sincerly appreciate any help, any one may be able to offer, Cheers, Craig
select * from TABLE where user='jacky' ,it can working,but if like this: dim name as string="jacky" select * from TABLE where user=name it won't doing,Can a variable used in SQL select command,if can,how to make it working.
i have to field in sql table for example : field1 : Father Field2 : David,Sarah , Niki,John . when i want to find for example sarah i can not becase near sarah is "," how can i solve this problem ? i use this command : "select * from table where field2 like '" & textbox2.text & '"" but show me another records like sarah kimm or sarah dave but i want exact Sarah
I'm trying to populate a DropDownList from my SQL database. I'm using C# 2005 and when I compile my code I get an error. Compiler Error Message: CS0103: The name 'myConnection' does not exist in the current context using System; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class Default3 : System.Web.UI.Page { private string connectionString = WebConfigurationManager.ConnectionStrings["mewconsultingConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e) { SqlCommand myCommand = new SqlCommand(); myCommand.Connection = myConnection; myCommand.CommandText = "SELECT * FROM tblPau"; myConnection.Open(); SqlDataReader myReader; myReader = myCommand.ExecuteReader(); myReader.Read(); // The first row in the result set is now available. lstPau.Items.Add(myReader["PauSiteName"]); myReader.Close(); myConnection.Close(); } }
I need to move various records from a production database to an archive database. The fields in the two databases are identical but the archive database has an additional "Transfer Date" field. Can anyone recommend an easy way to create an SQL statement to move the record from production to archive? I've tried
INSERT INTO ARCHIVE (SELECT * FROM PRODUCTION WHERE ACCOUNTNO='XYZ')
but I get an error saying the columns among the two tables do not match (obviously because of the "Transfer Date" column). Is there a way to use a similar SQL statement that will populate the "Transfer Date" column w/ today's date?
I have a table with a column called SortOrder. It's an integer. The table also has a name and a city.I want to do an Insert and fill in SortOrder with the next higher integer, of all rows with city='NY'
Insert (name,city,SortOrder) values ('Dave','NY', select max(SortOrder)+1 from myTable where city='NY')
Does anyone know if you can add columns to a local pulled table and if so can you use a select command to push the table back and exclude the added columns.
Basically I need to know if a record has been added on the PDA so I can get an ID from the server before pushing it back. I cannot alter the sql server database because it is part another application.
Hello everybody, I have following datasource <asp:SqlDataSource ID="sqlIncidentTemplates" runat="server" ConnectionString="<%$ ConnectionStrings:Standard %>" SelectCommand="SELECT [IncidentText] FROM [IncidentTemplates] WHERE [ConstrAreaID] = @ConstrAreaID AND [ConstrDeviceID] = @ConstrDeviceID"> <SelectParameters> <asp:Parameter DefaultValue="" Name="ConstrAreaID" Type="Int32" ConvertEmptyStringToNull="true" /> <asp:Parameter DefaultValue="" Name="ConstrDeviceID" Type="int32" ConvertEmptyStringToNull="true" /> </SelectParameters> As far as I understand default select command should be "SELECT IncidentText FROM IncidentTemplates WHERE ConstrAreaID IS NULL AND ConstrDeviceID IS NULL". Unfortunately this is not the case. I assume that the "[ConstrAreaID] = @ConstrAreaID " is translated into "ConstrAreaID = NULL". Could this be the reason and how can I solve this problem? Thanks in advance
My compiler says that the line in bold below is illegal. The error msg I'm getting is: No overload for method 'select' takes '0' arguments. How can I correct this error and execute a SELECT? protected void Button1_Click(object sender, EventArgs e) { SqlDataSource2.Select (); } protected void SqlDataSource2_Selected(object sender, SqlDataSourceStatusEventArgs e) {string strReadyFirstName = e.Command.Parameters["@FirstName"].Value.ToString();string strReadyLastName = e.Command.Parameters["@LastName"].Value.ToString(); } <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [User_ID], [User_Name], [FirstName], [LastName], [Company_Name], [Department_Name] FROM [CompanyDepartment] WHERE ([User_Name] = @User_Name)" OnSelected="SqlDataSource2_Selected"> <selectparameters> <asp:sessionparameter DefaultValue="TheirUserName" Name="User_Name" SessionField="TheirUserName" Type="String" /> </selectparameters> </asp:SqlDataSource>
I have a GridView (that uses SqlDataSource1) and a Dropdownlist. Depending upon the value selected on the DropDownList I need to select different stored procedures for the gridview. The problem is that I can do it without taking SqlDataSource1 by using DataSet or DataTable. But, I need to Use SQLDataSource1 for easy way of Header SORTING. So, is there any way to change the SQLDatasource1.SELECT Command dynamically. So that, I can use different queries for the Single DataGrid. I have attached the sample code of the SqlDataSource1 I'm using. I need to change the Command i.e. SelectCommand="usp_reports_shortages" to "usp_reports_shortagesbyID" and "usp_reports_shortagesbyDate" depending on the value selected in the dropdownlist. So, is there any way to do this????<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TESTDrivercommunication %>"
I did a successful Insert to a SQL Server today in .NET VS2005. But before I insert I figure I better make sure the part does not allready exist in the table. Can a check the command to determine if it has a boolan value of False and if so to INSERT as before? Dim myCommand As New SqlClient.SqlCommand
myCommand.CommandText = "Select PartNumber From Pricing Where PartNumber = '" & var0 & "'"
Hi, I have a Command that I can not get to work. I am trying to take an AVERAGE of 1 Columb and display it in a label. ALSO what statement do I add when the Lable that is supposed to display that output is empty. Right now It gives me an Error.comm = New SqlCommand("SELECT * AVG Rating FROM Reviews WHERE CID = " & Request.QueryString("CID"), conn) Dim SqlDataAdapter1 As New SqlDataAdapter("SELECT AVG Rating FROM Company WHERE CID = " & Request.QueryString("CID"), conn)Dim objReader As SqlDataReader conn.Open() objReader = comm.ExecuteReader() objReader.Read()lblRanking.Text = objReader("Rating")
I am trying to implement an "advanced search" feature on my ASP.NET 2.0 web form. I have a GridView control and a SqlDataSource. The SqlDataSource control successfully retrieves data when the SelectCommand attribute is set in the aspx page. I need to make it so when a user clicks on a button, it can take a value from a text box and use it in the WHERE clause. I have tried setting the SelectCommand programmatically and then DataBinding but it never accepts the new SelectCommand. What can I do to fix this?
The gridview of all the records is displayed on the page by default. There is a lookup of SSN and Name and putting values in those fields should filter the records and display only the row containing entered Name.I have trouble with the SelectCommand. No gridview is displayed with the above command even if value is entered in the Lookup textBox (SrchRefName).But when I change the selectcommand to- (using OR) SelectCommand="SELECT [id], [ref_id], [ref_name] FROM [ref] where [delete_flag] = 'N' AND [flag_transmit] = 'N' OR [ref_name] = @ref_name "> <asp:ControlParameter Name="ref_name" ControlId="SrchRefName" PropertyName="Text" ConvertEmptyStringToNull="false"/> the default gridview is displayed with all the records even though a value is entered in the lookup text box. Please help.
Can someone tell me why the syntax checker is choking on the following code? It doesn't like the SQLDataSource1 reference. Protected Sub cmdSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click SqlDataSource1.SelectCommand = "SELECT * FROM users WHERE username='" + Trim(txtUsername.Text) + "' AND password='" + Trim(txtPassword.Text) + "'" End Sub
I've got an access table and I want to query a table that shows no duplicates postcode. Therefore, I want to use the SELECT DISTINCT command in access. SELECT DISTINCT postcode FROM HOUSE But it don't work.
How is it done in access?Any help is welcome!
Table:House column1 : name " : DOB " : postcode " : address
My question : How Do I bring all rows from TblB and matching single row from TblA (I have more than one sessionID in TblB and only one Unique SessionID in TblA).
The Select query I was using is
SELECT PageViews.ID AS ID, PageDetailView.VisitID, PageViews.PageAccessed, PageDetailView.PageAccessed AS Expr1, PageViews.QueryString, PageDetailView.QueryString AS Expr2, PageViews.Referer, PageViews.SessionID, PageDetailView.SessionID AS Expr3, PageDetailView.PdtID, PageDetailView.Pcode, PageDetailView.CustID, PageDetailView.OrdTot, PageViews.[Date] FROM PageViews RIGHT OUTER JOIN PageDetailView ON PageViews.SessionID = PageDetailView.SessionID ORDER BY PageViews.ID DESC
Hi! I want to get some fields from more than one table. How can I use select command to do this? Please help me! The results should be in one table only! Thanks in advance!
Hi,I have two tables: Code and Color.The create command for them is :create table Color(Partnum varchar(10),Eng_Color char(10),Span_Color char(20),Frch_Color char(20),CONSTRAINT pkPartnum PRIMARY KEY(Partnum))create table Code(Partnum varchar(10),Barcode varchar(11),I2of5s varchar(13),I2of5m varchar(13),UPC varchar(11),BigboxBCode varchar(11),DrumBCode varchar(11),TrayBCode varchar(11),QtyBCode varchar(11),CONSTRAINT fkPartnum FOREIGN KEY(Partnum) references Color(Partnum))Now my question is,how can i give a select statement such that I can get all the fields asoutput.Also plz note that the above is a sample. I have another 9 tables and Ineed a solutionsuch that on being refered by Partnum, I can get all the attributes.Thanks
I want to calculate the Total_Cost and get the result of the formula from SELECT Query. Following is the formula of Total_Cost. I have declared variables for formula. How I can create SELECT/RUN query successfully for mathematical calculation to calculate the Total_Cost.
--Formula of Total_Cost = [min (price, Pay1) x Interest1 + max (min (price - Pay1, Pay2), 0) x Interest2 + Max ((price - Pay2), 0) x Interest3, LMT]
declare @price numeric(18,7)=255550 declare @Pay1 numeric(18,7)=645500 declare @Pay2 numeric(18,7)=235000 declare @Interest1 numeric(18,7)=0.05500 declare @Interest2 numeric(18,7)=0.03533 declare @Interest3 numeric(18,7)=1.00000 declare @LMT numeric(18,7)=10000.00 Select [min (@price, @Pay1) x @Interest1 + max (min (@price - @Pay1, @Pay2), 0) x @Interest2 + Max ((@price - @Pay2), 0) x @Interest3, LMT] as Total_Cost
Ihave a table With A Culomn That Is NOT Unique And Repeat Many Tims BUT I want To Have a Select Command That Filter Repeated Value And Show Only One of rows Whith Same Data
