Select vVehRegNAddr.RegContChar1 From (((vVeh LEFT OUTER JOIN
vVehBrand ON vVeh.Plate = vVehBrand.Plate) LEFT OUTER JOIN
vVehRegNAddr ON vVeh.Plate = vVehRegNAddr.Plate) LEFT OUTER JOIN
vVehLegal ON vVeh.Plate = vVehLegal.Plate) Where ((vVeh.Active = 1)
AND (vVeh.VUse NOT IN ('C/G','H/D','SNO','SNX')) AND (vVeh.LExpDt =
'20050715'))
ERROR MESSAGE:
"
Error in selection of Aggregates:
If ANY aggregate functions(Count, Sum,...) are chosen,All fields must
be aggregate that are not in Grouping
"
hi i have copied this from my other page where it works fine and i cant understand what is going wrong! maybe one of your guys can point out what i cant see! herei s my code string strOrderID = Request.QueryString["orderID"].ToString();int intOrderID = Convert.ToInt32(strOrderID); int intCustID = Convert.ToInt32(Request.QueryString["qsnOrderCustID"].ToString());lblCustomerID.Text = Request.QueryString["qsnOrderCustID"].ToString();lblOrderID.Text = Request.QueryString["orderID"].ToString();
SqlConnection myConn = new SqlConnection("Data Source=xxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx"); //This is the sql statement.string sql = "SELECT [del_address], [del_post_code], [del_time] From tbl_del WHERE order_ID = " + intOrderID;
//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn); myConn.Open();SqlDataReader dr = sqlCmd.ExecuteReader(); //This reads the first result from the sqlReader dr.Read(); try { lblDelTime.Text = Convert.ToString(dr["del_time"].ToString); lblDelAddy.Text = dr["del_address"].ToString();lblDelPCode.Text = dr["del_post_code"].ToString();if (lblDelAddy.Text != "") { lblDelDate.Visible = true;lblDelTime.Visible = true; Label1.Visible = true;Label2.Visible = true; } }catch (Exception except) {lblerror.Text = Convert.ToString(except); }
Receive Error: when I Run Select Statement Asking to Declare @Country Not sure how to Declare the variable '@Country'. Any help would be appreciated... Thank You. Here is the Code: ____________________________________________________________________________________________________________ Private Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) If Not IsPostBack Then Dim conCommerce As SqlConnection Dim cmdSelect As SqlCommand Dim CategoriesCounter As SqlDataReader Dim StylesSource As SqlDataReader conCommerce = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) conCommerce.Open() cmdSelect = New SqlCommand("Select CountrySubCatCount FROM StylesSource WHere Country = @Country", conCommerce) StylesSource = cmdSelect.ExecuteReader() txtCounter1.DataSource = StylesSource txtCounter1.DataTextField = "Country" txtCounter1.DataBind() StylesSource.Close() conCommerce.Close() End If ______________________________________________________________________________ Error Message: Must declare the variable '@Country'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@Country'. Source Error: Line 220: cmdSelect = New SqlCommand("Select CountrySubCatCount FROM StylesSource WHere Country = @Country", conCommerce)Line 221:Line 222: StylesSource = cmdSelect.ExecuteReader()Line 223:Line 224:
Hi,I try to insert record using select statement. But, I am having a problem with this code:Insert into PaymentVoucherTable (ID, Duration1, min1, Total1, Duration2, min2, Total2)select (ID, Duration1, min1, (Duration1 * min1), (select Duration2,min2,(Duration2 * min2) from Table2)from Table1The MSSQL keep displaying error message : "the number of column in INSERT statement is not the same as the select statement...".Is my subselect statement structure correct ???Thanks...
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/vendorlist.asp, line 68 ----------------------------------------- Here's the statement.. and ill point out line 68 below with a few other details... ----------------------------------------- Dim vendorRS, SQL set vendorRS = Server.CreateObject("ADODB.Recordset") SQL = "SELECT ttVendors.company, ttVendors.website, ttVendors.phone, ttVendors.phone800,ttVendors.vendorID," &_ SQL = SQL & "ttInventory.newprice, ttInventory.refurbprice, ttInventory.oosprice, ttInventory.venddescrip" &_ SQL = SQL & "FROM ttVendors INNER JOIN ttInventory on ttVendors.vendorID = ttInventory.vendorID" &_ SQL = SQL & "WHERE ttInventory.prodID = '" &prodID& "'"
vendorRS.Open SQL ,objCn, 1, 3
DIM company company = vendorRs("company") vendorRs.close set vendorRs = nothing --------------------------------------------- Ok so... line 68 IS vendorRS.Open SQL ,objCn, 1, 3
ive tried it like vendorRS.Open = SQL ,objCn, 1, 3 vendorRS.Open, SQL ,objCn, 1, 3 vendorRS.Open SQL ,objCn, 3, 3
no luck.. Also, prodID is passed from a querystring from previous page.. just used the typical request object to gather.. in the DB, all prodID fields are of INT type.. and im wondering if that makes a difference..
I am receiving error msg on the below query line as "incorrect syntax near MSF" , "Incoorect syntanx near ExtCost", "incorrect syntax near From on line 28"
SELECT xxxcolumns, (rj.RECEIVEDLINEAL * ((r.WIDTH / 12.0)) / 1000.0 MSF, Case when rv.PricePerCode = 'MSF' Then ((rj.RECEIVEDLINEAL * (r.WIDTH / 12.0)) / 1000.0) * rv.price
Have run to a select permission error when attempting insert data to a table. received the following error
Msg 229, Level 14, State 5, Line 11 The SELECT permission was denied on the object 'tableName', database 'DBname', schema 'Schema'.
Few things to note - There are no triggers depending on the table - Permissions are granted at a roll level which is rolled down to the login - The test environments have the same level of permission which works fine.
,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear FROM Channels AS C INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoIDError message:
Msg 107, Level 16, State 3, Line 1 The column prefix 'Channels' does not match with a table name or alias name used in the query.
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.
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 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
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
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
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees, ( SELECT COUNT(gifts.donor_id) AS Count2 FROM gifts WHERE gifts.donor_id = ed.id_number ) subquery2
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.
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
I am a newbie to SQL. I have a table (AenComponent) with three columns (State1, State2, State3). Each column has a set of numeric values. I would like to get a number count from all of the rows that contain the value of 1, no matter which column they are in.
I have tried SELECT COUNT(*) AS Expr1FROM AenComponentWHERE (State1 = 1) OR (State2 = 1) OR (State3 = 1) but it does not give me an accurate count. Any help would be appreciated.
I'm trying to get a list of clients and their sum of total pmts, their pmt level, and pmt level description by date range. Here is what I’ve tried and it will not work. I need to do this without using temp tables. SELECT C.ClientID, SUM(P.AmountPaid) AS SumOfpmts, tblpmtLevels.pmtLevel, tblpmtLevels.DescriptionFROM tblPmts AS PL INNER JOIN tblPmtReceipts AS P ON PL.PmtID = P.PmtID INNER JOIN tblClients AS C ON PL.ClientID = C.ClientID INNER JOIN tblPmtLevels ON SUM(P.AmountPaid) >= tblPmtLevels.PmtLevelLow AND SUM(P.AmountPaid) <= tblPmtLevels.PmtLevelHighWHERE (P.PaymentDate BETWEEN @Start AND @End)GROUP BY C.ClientID Please provide any help you can,
Hello, Is there a SQL SELECT Statement that can remove the time part in my DataTable. see example below This is what I have in the first column 0 - MM/dd/yyyy 12:00:00 AM This is what I want - MM/dd/yyyy I would rather do it in the SELECT statement instead of doing it in my DataTable using the FORMAT function. Thanks Steve
I have a stored procedure that works like this getCountryByIPAddress @ipaddr='1.2.3.4' returns a single 2 char column. I want to use it in a select statement similar to: select customername, @country=(exec getCountry @ipaddr=customer.customeripaddress) from customer I'm intend to receive a list of customer names and the 2 char result of GetCountry based on the customeripaddress field of the customer table. What I get is a syntax error near Exec and then another one near '.' Is there another way to accomplish this that I am not seeing? Ryan
Hi, I am trying to select only the most recent record added to a table per user. Say the table has a userid, date, and a color and may look like this: 2 5/06/07 red 8 5/3/07 grey 2 5/13/07 green 4 4/30/07 orange 8 4/26/07 yellow And I want, in any order: 2 5/13/07 green 8 5/3/07 grey 4 4/30/07 orange
