Here is the deal.... I have a table in my database that has these fields. (sql server 2005 standard)
intBlog Id = int
intBlogCategoryId= int
vcBlogTitle = varchar(8000)
vcBlogShortDescription = ntext
vcBlogContent ntext
vcUsername = varchar(200)
intBlogViewed = int
dtBlogPosted = datatime
Okay here is the thing, I want to figure out how to write an sql statement that (as you see on other sites, a blog archive that goes through the DB and gets the unique month and year and then count how many blogs are in each distinct month/year.
Hope that makes sense so it looks like this
January 2007 (24)
December 2006 (2)
November 2006 (98)
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are:
Field02
----------------------
22810 8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760 22810
Is there any good suggestion? Thanks in advance for any help, Aldo.
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E
can any one help me in this? please give me a sample query.
I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code. Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be? Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message. Compilation Error Description: An error occurred during the compilation of a resource required to service this request.Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error: Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;"); 1 <asp:sqldatasource runat="server"></asp:sqldatasource> 2 <%@ Page Language="C#"%> 3 using System; 4 using System.Data; 5 using System.Collections; 6 using System.Data.SqlClient; 7 8 <script runat=server> 9 10 protected void Page_Load(object o, EventArgs e) 11 { 12 ObjectDataSource dsa; // This works no problems from the compiler here 13 SqlDataSource ds; // This works no problems from the compiler 14 15 if (IsPostBack) 16 { 17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text)) 18 { 19 instructions.Text = "Congratulations, your authenticated!"; 20 instructions.ForeColor = System.Drawing.Color.Red; 21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;"); 22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'"; 23 } 24 else 25 { 26 instructions.Text = "Please try again!"; 27 instructions.ForeColor = System.Drawing.Color.Red; 28 } 29 } 30 31 } 32 33 bool AuthenticateUser(string username, string password) 34 { 35 // Authentication code goes here 36 37 }
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
material ========= material_id project_type project_id qty 1 AB Corporate 1 3 2 Other Project 2 7
i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project
sample query i write :--
select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id, ab.custname ,op.other_proj_id,op.other_custname,op. po case if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_id else if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_id end from material m,AB_Corporate_project ab,Other_project op
but this query not work,also it gives errors
i want sql query to show data as follows
material_id project_type project_id custname other_custname qty 1 AB Corporate 1 abc -- 3 2 Other Project 2 -- dsd 7
so plz help me how can i write sql query for to show the output plz send a sql query
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
Suppose I have an MS access database with query1 and query2. Query2 usesquery1 something like this:Select table.* from (table left join query1 on field1=field2)Now I want to make a change in query1 to change the behaviour of query2. Butthis cannot be done in a live environment because I have to shut down theapplication, make the change in the database and start the applicationagain. What I can do is make a query 3 in my application (instead of in thedatabase) that is initially does the same as query2 like this:Select table.* from (table left join -->some SQL code to embed query1<-- onfield1=field2)Once I know how to do this, I can change the "embedded" sql code to myneeds.Question is: how do I "embed" SQL code in another statement?
Can some one point me in the right direction in how to construct my SQL query within my cursor?
I Have got a cursor which i am using to iterate through a table, What i am trying to do is in my statement(used to open the cursor) is compare 2 tables (the one which my cursor is iterating) to see if there is a matching row in the other table (using both tables ID's Like So:
SELECT column_List FROM Table1 WHERE Table1_id = Table2_id
so for each row my cursor checks if there is a corresponding match in table2... but i would like to write to an error log
and do other statements if there is no match
how do i add this condition to my statement either using an if...else statement proceeding to the next row?
here is the statment i attempted to write:
SELECT column_List FROM table1 WHERE Table1_id = Table2.id
now i want to incoporate the statements below into the statement above as a condition when table1.id <> table2.id
IF table1.id <> table2.id
BEGIN SET @DebugMessage = 'data not live.' RAISERROR (@DebugMessage, 16, 1) WITH LOG END
essentially what i am trying to sayin my statement is:
go to the first row
check if it has a match in table 2,
if there is no match execute a number of statements such as error loging e.t.c
go to the next row
repeat the previous statements
...i also looked through some Case...When statements am just not sure how to put in the condition
Hi all, I gotta retrieve fields that is not null from database according to ID.There are a tota of 45 fields to retrieve, but I only needed fields that are not null. How do I retrieve fields that are not null where the id = id?
Hi, I am having trouble retreiving data for and application. What I need in simple terms is the following: I have a table called Orders that contains 2 dates - OrderDate and Received date. However not all the fields have a ReceivedDate where as they all have an OrderDate. What I want to do is to retreive a data set for each record which has the ReceivedDate if it has one, but if not it uses the OrderDate. Can anyone help with the SQL statement required to do this? ThanksPaul
If you can see what I am trying to do here, how can I get this to work? I want to list the dates in the datebase in a particular format and a count for each
Seems simple enough, but maybe not?
select count(*),x from (select right('0' + cast(datepart(yyyy,datetime1) as char(4)),4) + '-' + right('0' + cast(datepart(m,datetime1) as varchar(2)),2) + '-' + right('0' + cast(datepart(d,datetime1) as varchar(2)),2) x from users) group by x
I am trying to figure out what is wrong with my sql statement. It shows up right and all but my last and where clause is not working right. I want to search the database for a parameter of searchtext but only display jobs where the expiration date is greater than todays date. My statement is as follows: SELECT tblJobs.intJobID, tblJobs.vcBusinessName, tblJobs.vcJobTitle, tblJobs.vcJobShortDescription, tblJobs.vcJobExpirationDate, tblJobs.vcJobDescription, tblJobs.IntJobCategory, tblJobs.intJobSchedule, tblJobs.vcPositionExperience, tblJobs.vcJobInquiry, tblJobs.vcApplicationPath, tblJobs.vcFileName, tblJobs.intApplicationSize, tblSalary.vcSalary, tblCity.vcCityName, tblJobCategory.vcJobCategory, tblPositionSchedule.vcJobScheduleType FROM tblJobs INNER JOIN tblSalary ON tblJobs.intSalaryID = tblSalary.intSalaryID INNER JOIN tblPositionSchedule ON tblJobs.intJobSchedule = tblPositionSchedule.intJobScheduleTypeID INNER JOIN tblJobCategory ON tblJobs.intJobCategory = tblJobCategory.intJobCategoryId INNER JOIN tblCity ON tblJobs.intCityID = tblCity.intCityId WHERE tblJobs.vcJobTitle LIKE '%' + @SearchText + '%' OR tblJobs.vcBusinessName LIKE '%' + @SearchText + '%' OR tblJobs.vcJobShortDescription LIKE '%' + @SearchText + '%' OR tblJobs.vcJobDescription LIKE '%' + @SearchText + '%' OR tblJobs.vcPositionExperience LIKE '%' + @SearchText + '%' OR tblJobs.vcJobInquiry LIKE '%' + @SearchText + '%' And tblJobs.vcJobExpirationDate > getdate() AND tblJobs.intJobCategory = @CID AND tblJobs.IntJobSchedule = @SID ORDER BY intjobid DESC END
"SELECT * FROM assyProductionData WHERE lineNumber='"&j&"' AND productNumber='"&myRs1("productNumber")&"' AND date='"&myDate&"' AND shift='"&myShift&"'"
Can anybody tell me what the "&j&" characters is calling in this string?
I would like to build a OR condition in my sql statement. The OR condition is based on the field BookID whereby it is not fixed. How to build a SQL statement whereby the OR condition is not fixed?
For example, first run, BookId= '1', BookId='2' second run BookId='1', BookId='2', BookId='3'
and my table name is 'BookInfo'
select * from BookInfo whereby BookId='1' or BookId='2' .....
Simple question (when you know the answer) What is the syntax for and SQL if statement and how do you end the if statement and what is syntax for else (suppose it is just ELSE) if @ShippingInfoID1 > 1 UPDATE CMRC_ShippingInfo_Shrek SET ShippingID = @ShippingID1, ShippingAmount = CAST((@ShippingAmount1) AS MONEY) WHERE ShippingInfoID = @ShippingInfoID1
I'm sorry that the subject isn't very specific, but I don't know how to explain it shortly.I create an application to monitor my web site whether the sever is still online. I have the monitor application to log it to SQLExpress database.The table which I stored the log is something like thisLogTime (datetime) ServerOnline (bit)2003-03-28 17:14:01.000 True2003-03-28 17:14:24.000 False2003-03-28 17:15:32.000 False2003-03-28 18:15:50.000 True(Please note that the interval betwenn each log is not consistent.)Now, I want to SELECT and show which period the server's online and which period the server's offline. Probably something likeStartOfPeriod EndOfPeriod ServerOnline2003-03-28 17:14:01.000 2003-03-28 17:14:24.000 False2003-03-28 17:14:24.000 2003-03-28 17:15:32.000 False2003-03-28 17:15:32.000 2003-03-28 18:15:50.000 TrueOr, if that's too complicate, I want to calculate the uptime of the server in percent.Using one of free web monitors on the net is impossible because this is an intranet site.Can anybody help? I would be really grateful.
Can anyone take a look at this sql statement for a .net app written in VB? "Select * FROM Servee WHERE Exists(Select ServeeID FROM Orders WHERE Orders.ServeeID=Servee.ServeeID)"I'm trying to get a row from Servee where it matches the currently selected row in Orders. The Orders table has OrdersID as PK and ServeeID as FK to Servee table, which has ServeeID as PK. So, if Orders has a ServeeID of 24, then it should pull row 24 off of the Servee table. If no records match, then I need to to pull nothing. Currently, it always pulls the last entered row in Servee. Thanks in advance!
