Hi everyone..i m new to this field.. can anyone explain me with simple example onhow to insert,update,select data from the sqldatabase? i m using vwd 2005 express edition along with sql express edition. plz explain the simple example with code (C#) including how to pass connection strings etc.thank you.jack.
I'm trying to extract data from our database for the number of phone calls our reps are doing.
In counting the calls I only want to include up to 3 calls to the same customer (field name is CompanyID) per day - anything more than this is ignored.
The query at the moment is something like:
SELECT COUNT(CallID) AS CallCount FROM Sales_Calls WHERE CallDate >= '2014-09-01' AND CallDate <= '2014-09-30' AND RepID = 1 Using MSSQL 2012.
i am facing a problems, hope that anyone who know the answer can help me.
i am using sql server 2000 and vs.net. i wan to retrieve a data from the table which only select 3 top row based on their different contingentID. the table have different contingent and each contingent have 5 row data. i wan to retrieve 3 top row based on the total group by contingentID and sum up the total to order by the total and generate the ranking based on total. what i can do is only retrieve every row of data in that table. so anyone know the answer please help me, or if u dun understand my question , can message me..thank your
Hi there, I have installed Sql server 2005 developer on my machine which already has a Sql server 2000 installed on. Now i am trying to query the Sqlserver 2005 data(Ex: from Person.Address located in AdventureWorks database) in Sqlserver 2000 query analyzer: When i try as Select * from Address it said invalid object. But when i explored AdventureWorks database in the Management studio i see it as Person.Address!!! i don't understand what is a Person in Person.Address??? any clues on this? So as a test when i ran select * from Person.Address it did work and i saw the results in query analyzer. Can any one help me understand about this confusion? Thanks-L
Dear list,I am trying to get the names of the tables and the column names from thecode below for a database but it is not working. When I run the querybelow the column titles are delivered but there is no data. I think thismight be a premissions issue. Has anyone run into this before?Thanks in advance.Use Test_db/* Provides Table Name, Column Name, Extened Description */Select a.name as tbl_name, b.name as column_name, d.name as data_type,d.length as length, d.xprec as prec, d.scale as scale, b.usertype,b.scale, c.valuefrom sysobjects as a inner join syscolumns as b on a.id=b.id inner joinsysproperties as c on b.colid=c.smallid and a.id=c.idinner join systypes as d on b.xtype=d.xtype
Here is the setup. Text Box: User enters in customer transaction number Button: User clicks button to display information about the customer
Now the database has a lot of unique customer numbers. What I am trying to do is take what the user enters so it can search the database and pull out that customers information. I am having a hard time getting that information from the textbox. Any suggestions! Here is what I have so far.
Private Sub btnViewFlow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewFlow.Click
Try
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "DTR_Document_Summary", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("DocumentId", "DocumentId"), New System.Data.Common.DataColumnMapping("PartnerId", "PartnerId"), New System.Data.Common.DataColumnMapping("PartnerName", "PartnerName"), New System.Data.Common.DataColumnMapping("Direction", "Direction"), New System.Data.Common.DataColumnMapping("TranSet", "TranSet")})})
I would like to create a table which can store a VLOB or BLOB.. which are pictures and images.. How do I insert them into the database and how do I select them into the data base for playing in a media player
I really do not have a background on this.. much.. I just know how to connect to a database and insert texts.. and updating and stuffs...
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish Even after this it is not working, So provided select on schema also. used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
HiI have a query that is performing very strangely.I f I put a top statement in it returns rows,soSelect top 10 * from .......returns 10 rowsbut without it then no data is returnedSelect * from ..........returns 0 rows.
An error has occurred during report processing. (rsProcessingAborted) Query execution failed for data set 'TestID'. (rsErrorExecutingCommand) For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created. All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection) For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed. Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name) Dim selectSQL As StringDim con As SqlConnection Dim cmd As SqlCommand Dim reader As SqlDataReader Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;") Dim username As String username = Request.QueryString("username") selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con) con.Open() reader = cmd.ExecuteReader() lbl.Text = selectSQLCatch ex As Exception lbl.Text = ex.Message Finally If (Not con Is Nothing) Then con.Close() con.Dispose() End If End Try End Sub '------------------------------------------------------------------------------------------------------------------------------------------------------------------------- May i know that how do i retrieve data from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password
Hi If i use this code i cant get the data showed, it show nothing."SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages WHERE DogImageDate NOT LIKE (SELECT TOP 1 DogImageDate FROM EnggaardImages ORDER BY DogImageDate DESC;) GROUP BY DogImageDate ORDER BY DogImageDate DESC;" But if i use this code i get data showed"SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages GROUP BY DogImageDate ORDER BY DogImageDate DESC;" Then i get Images(6)Images(1)Images(1) But i dont want the first to be showed, therefor i use the Select TOP 1, so i get a look like this Images(1)Images(1) But i cant get it to work.
I have created a sql login account called "webuser" and has given public role in my database. In my asp.net application i build connection string using above account and its password . We give permission on store procedure for for the above account to execute .We dont give table level permission for the above account . When we run the application with the above settings it runs fine on test server . However Now i have transfered the databse object to live server with its permissions . Now while I executing the aspx page , I am getting above error . I have checked that the store procedure has execute permission for webuser account and dbo(i.e SA) has all the permissions for all database objects . Still why i am getting error ? (Please note , the thing is working fine in test server)
Not totally sure if this is the best place for this question, but regarding maintainability / good practice issues vs speed I have a question regarding what is the best way to SELECT some data from a database.I have the item_id of an item bought. I want to get a customer's billing information based on the item he bought. Should I do this with two queries or one using joins? Here is my dbase schema: customer_info--------------customer_idnamecc_numexpdate item_bought------------item_idcustomer_id And here is a basic pseudocode algorithm:function getCustomerInfo(customerID){ SELECT customer_info.name, customer_info.cc_num, customer_info.expdate FROM customer_info WHERE customer_id=customerID; return results;} function getCustomerInfoWithItemId(itemID){ SELECT item_bought.customer_id FROM item_bought WHERE item_bought.item_id=itemID; results = getCustomerInfo(customer_Id); return results;} results = getCustomerInfoWithItemId(400);OR function getCustomerInfoWithItemId(itemID){ SELECT customer_info.name, customer_info.cc_num, customer_info.expdate FROM customer_info, item_bought WHERE item_bought.item_id=itemIDAND customer_info.customer_id = item_bought.customer_id; return results;} results = getCustomerInfoWithItemId(400);
I've been having some trouble finding the correct syntax for the following query, where I am simply trying to move selected records from table A in database A to table A in database B.
Here is what I have:
Code:
SELECT pd.phoneID, phonebookID, timezoneID, phoneNumber, lastName, firstName, address, city, state, country, zip, custom, custom2, custom3, memo, fax, email INTO history.set_appointment1 FROM pro_PhoneData pd LEFT JOIN pro_Campaign_1_Results cr ON pd.phoneID = cr.phoneID WHERE ((phonebookID='3') OR (phonebookID='1')) AND descriptionID = '203' AND callTime < GETDATE()-365 ORDER BY callTime DESC
When I execute this query, I get the following message: "The specified schema name "history" either does not exist or you do not have permission to use it."
I've looked up examples for select into queries and have played around with it a bit, but no such luck.
-------------------------- Teams -------------------------- ID | Name | City | State --------------------------
I want to output the Teams in this format:
Name City, State
However, some teams don't have a name, so I want to output them like this:
City, State Team City, State
And some teams don't have a name or a city:
State Team State
And then there are teams who have a name, but not a city:
Name State
I'd like to do this all in one select statement. Is there a way to do something like: SELECT Name IF EXISTS, City IF EXISTS, State IF EXISTS FROM TEAMS
my table ha follow structure..SalaryTable:Value Salary------------Jan 1000Feb 2000Mar 3000Apr 4000i want the o/p as..Jan Feb Mar Apr1000 2000 3000 4000Pls give me a query for thisRegards,Satheesh
I am trying to select info from a database (MS-SQL) and show that whit a label. And don’t really get every thing to work. So I am glad for that help I can get.
I have a single form that the users can do lookups for items. We have two locations each with its own SQL Server and database. Trying to use an IF...THEN statement as they select the DB they want to query it then creates the connection string for that particular database. I haven't a clue on how to do this. If someone could point me to the documentation to do this I would really appreciate it. <%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDB" %> <%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server"> Sub get_Dies(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim dbConnection As New SqlConnection
If ddlDatabase.SelectedValue = "db2" Then ??? dbConnection = "server=server2;Database=db2;UID=user;PWD=pass" dbConnection.Open() Else ??? dbConnection = "server=server1;Database=db1;UID=user;PWD=pass" dbConnection.Open() End If Dim sqlString As String = " sql statement" Dim dbCommand As New SqlCommand(sqlString, dbConnection)
Dim dbDataReader As SqlDataReader dbDataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
I have a problem during my etl process. my customer do not want that everytime the ssis package running, the dw table been cleen up/delete all. so i just have to process only with the new data. For example is like this:
Source Database: db1 Table DataEmp id name address ---------------------------- 1 Mike California 2 David New York 3 Bruce Ohio
Destination Database: db2 Table DimDataEmp DataEmpKey DataEmpName DataEmpAddress ---------------------------------------------------------------------- 1 Mike California 2 David New York
My goal now is, if i run the ssis package the only data transfer is (3, Bruce, Ohio). how am i do it? i already try the merge, merge join, or lookup control, but still don't work. basically i want to "select not exist" the destination table but in different database. please help me, cos i still new in ssis. Thanks
I have a table "tbNews" in my database with N_ID, N_Content... eg: N_ID N_Content 1 Hello 3 How are you? 14 Are you there? 23 Can you help me? 32 ABC 33 CDE 36 EFG If I have my N_ID = n (with n=1,3...) , can I select my records with next 1,2 or pre 1,2 eg: I have my N_ID=23, -->can I have got N_ID=3, 14 or N_ID=32, 33 ? N_ID N_Content 3 How are you? 14 Are you there? or 32 ABC 33 CDE Please help me have a Select clause! Thanhs all.
select * from tbl where nmonth between datepart(mm,DATEADD(month, -2, getdate())) and datepart(mm,getdate()) this query is returning my data between 1(jan) and 3 (mar) if i change my query to get all datas between nov to mar select * from tbl where nmonth between datepart(mm,DATEADD(month, -4, getdate())) and datepart(mm,getdate()) this query is not returning data since the month between 11 to 3 Guys,Help me out to correct this syntax.
Hi! Anyone knows how to select only certain data from one data field?
Lets say i have this field which captures Member Name and ID. The ID is in parantheses "()". I only want the Member name. How do I select this from the table?
E.G.: Field: Data MName: John Doe (123)
I need to select only "John Doe". Any help is deeply appreciated. Thank you!!
I'm really in need of help with this one!! I am writing a script which will provide me with financial info. This will then be used to upload to a management system. I have tried using the following code; select year.mem_desc as year, line.mem_name as line, unit.mem_name as unit, ver.mem_name, version = Case when ver.mem_name like 'Actual' then 'Actual Input' when ver.mem_name like 'Forecast' then 'Actual Input' when ver.mem_name not like 'Actual' then ver.mem_name end, cust1.mem_name as product, cust2.mem_name as costcentre, cust3.mem_name geographic_market, cust4.mem_name as segment, Jan = case when Jan is null and ver.mem_name like 'Actual' then select Jan from finloc where year.mem_desc like year and line.mem_name like line and unit.mem_name like unit and ver.mem_name like 'Forecast' when ver.mem_name like 'Forecast' then select Jan from finloc where year.mem_desc like year and line.mem_name like line and unit.mem_name like unit and ver.mem_name like 'Actual' when Jan is not null then convert(varchar(50),Jan) end,
This doesnt work!! I need to be able to say that if the columns are blank or null for Actual then take Forecast, a kind of merge statement is needed but I dont know how to write one, can someone please please help!!
