I have ViewA that sums up 4 fields from one table. I then have ViewB that uses ViewA to calculate the results. Now I do this with 5 different tables and then link them all to get my final results. Each View has a date range (begin / end) that I need to pass from a Web form.
How should I go about doing this? Should I create a temporary table to hold the begin / end dates to use in the sub Views? Or something else? I am at a lost on how to go about this and need some direction and syntax?
I have a whole bunch of forms that have an unbound StartDate and anEndDate field that I have used in MSAccess MDB databases as parametersin queries (ie tblEvent.StartDate > Forms!myFormName.StartDate.)So, now I'm migrating this beast over to and ADP/SQL Server projectusing Views and Procedures.How do I pass the value in Forms!myFormName.StartDate to a Procedureand get something that looks like:If tblEvent.StartDate > Forms!myFormName.StartDate then ...Any help is GREATLY appreciated. This is a major problem before I canmove ahead with this beast!lq
How do I write an sql statement to link tables in two databases and query them? For example: SELECT db1.table1.field1, db2.table1.field1 FROM Table1 INNER JOIN db1.table1.field1 ON db2.table1.field1 = db1.table1.field1
Is it possible to make views which takes Data from Multiple Servers? NOTE: The view is very complex because it uses FOUR Diff. statements joined together with UNION clause.
One more Q. If my Query Analizer Displays following on execution of query. " Internal Query Processor Error: The query processor could not produce a query plan." What should be my approach to rectify the problem.
I have a large list of table names and want to create a database, and a view for each of the tables with openrowset. There are no constrains no keys nothing. Is there a fast way to achieve that?
I am new to SQL, so I appologize if I am doing something wrong which is really basic. I am receiving a "Incorrect syntax near ';' " when trying to create a view and then perform action based upon the results Here is my basic code...the error is on the ; following the create view statement: create view temp2 as select * from history where crc='XL' and processed<1; insert into history2 (dialid, calldatetime, origcalldate, crc, revenue, projectid, agentid, phonenum)select temp2.dialid, temp2.calldatetime, temp2.calldatetime, 'XL', 0, history2.projectid, temp2.agentid, temp2.phonenum from temp2, history2where temp2.dialid=history2.dialid and temp2.crc='XL' and temp2.processed<1 and temp2.projectid=110; What am I doing wrong??? Thanks for your help.
Hello,I am relatively new to doing non-trivial SQL queries.I have to get data out of 8 diff views based on a parameter Name.There is a view having name-ssn pairs. All other views have SSN field.For a person there MAY NOT be data in all the views.I have to populate data into diff tables in a Report from differentviews.I would like to know what is the best way to approach it.So far I was trying an Inner join from the Name-ssn vies to all otherviews based on the SSN and test for the name field with the inputparameter.I am thinking there will be problem of Cross join if I dont have datain all views about a person.Or the best way is to write query for each view and have all of them ina stored procedure ?Any help will be appreciatedThanksBofo
I wonder if anyone has any hard fact based pro or contra, especially onperformance, about having views as opposed to tables when the object isbeing accessed for read only by multiple sessions/users/spids. I amparticularly concerned about the multiple instantiations of the view.Relevant thoughts on this are much appreciated.Thanks,Caveman
My database's design is set out here. In summary, I'm trying to model a Stock Exchange for a Technical Analysis application written using Visual C++. In order to create the hierachy I'm using a Nested Set Model. I'm now trying to write code to add and delete equities (or, more generically, nodes) to the database using a form presented to the user in my application. I have example SQL code to create the necessary add and delete procedures that calculate the changes to the values in the lft and rgt columns, but these examples focus around a single table, where as my design aggregates rows from multiple tables using UNION ALL:
Code Snippet CREATE VIEW vw_NSM_DBHierarchy -- Nested Set Model Database Hierarchy AS SELECT clmStockExchange, clmLeft, clmRight FROM tblStockExchange_ UNION ALL SELECT clmMarkets, clmLeft, clmRight FROM tblMarkets_ UNION ALL SELECT clmSectors, clmLeft, clmRight FROM tblSectors_ UNION ALL SELECT clmEPIC, clmLeft, clmRight FROM tblEquities_
Essentially, I'm trying to create an updateable view but I receive the error "UNION ALL View is not updatable because a partitioning column was not found". I suspect that my design in wrong or lacks and this problem is highlighting the design flaws so any suggestions would be greatly appreciated.
Hello, My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type. The dropdown options on the form will be as follows: ComplimentsComplaintsGeneral CommentsSuggestions Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item. For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter. However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated. If you need more information, I will provide whatever is needed. As always, thanks for everyone's assistance. Chris
Hi, I got a problem. I installed Microsoft SQL Server Management Studio Express 2005 version. And I created a Compact database. I created an connection in SSMSE to connect the database and opened a query form. then, i run the following sql:
Select * from Table1
It returned 3 records to me. After that, I used program to insert record into this table. Then i ran this sql again, it still show me 3 records. I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right? Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
Hi everyone, What is the main difference between first form normalizations and second form normalization ? In my opinion, they are both generated for the same operation which is to prevent redundancy(in other words; duplication of data in several records). So would you please explain it to me ?
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
HiI have a Platinum database which stores dates in integer form e.g the dateis formatted as below:Column_name Type Length Precision------------------------------ ------------------------------from_date int 4 10Some of the dates in the Platinum database are as follows:729115729359730059730241730302730455How can I bring them into SQL 2000 as valid dates.Thanks for your assistanceSam CJoin Bytes!
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
I am trying to add records to SQL database, thru <form> </form> on click of button, The system is working fine, Databse table is getting updated correctly. But screen displays the same form again while i want it to open another asp.net page i.e. buy.aspx and also generate an e-mail automatically. I m stuck. Please help. pcg
Relevent code of mine is as below. <script runat="server"> void addtosalelist(Object sender, EventArgs e) { .... .... dbConnection.Open(); .... .... dbConnection.Close(); }
SELECT @topicList that produces a list of all the topic_names in the database seperated by commas. That's fine.
I couldn't follow all the code in this thread (http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=18363) but I was able to extract and modify this code:
DECLARE @topicList varchar(200)
SELECT MAB_topics.topic_name INTO #tmp FROM MAB_topics, MAB_articleIndex WHERE MAB_articleIndex.article_ID = 76 AND MAB_topics.topic_ID = MAB_articleIndex.topic_ID
SELECT * FROM #tmp
drop table #tmp which returns the two topic_names that I'd expect. (Note: this is my very first day declaring variables and using temp tables.) So I keep thinking that the following code would combine the two above working functions. But, when I run it in Query Analyzer, it just returns "(2 row(s) affected)" instead of a recordset.
DECLARE @topicList varchar(200)
SELECT MAB_topics.topic_name INTO #tmp FROM MAB_topics, MAB_articleIndex WHERE MAB_articleIndex.article_ID = 76 AND MAB_topics.topic_ID = MAB_articleIndex.topic_ID
drop table #tmp What am I doing wrong? I'm sure it's something simple but I've been reading and trying and reading and trying for a few hours and haven't gotten it yet. The ideal response would figure out what I'm trying to do as well as what my mistake is and explain how I'm misthinking. A close second would be just fixing my code. :)
HiI am using Visual Web Developer 2005 Express and SQL Server Express 2005.From Northwind I can display data to Gridview.Could someone point me in the right direction on two points.How do I start a new database (.mdf) - I cannot find this option.I can change Northwind around - but I want to start my own database. Once I have that sorted - how can I "insert" data into database using a dropdown box?A "dropdown" box such as you would find online. Thanks in advance.Stephen
I need to make a query to a SQLserver db passing values from a dropdownlist and two text boxes as my criteria. Now if nothing is selected and the button is pressed, I would like to return everything that is part of my Select statement. I am having trouble making my sql statement work.
Im sending you my code as an attachment. I've put * around the area i really need help with.
Even if you could just point me in the right direction as to where to look for a good web forms book using VB.NET and ASP.NET I would really appreciate it.
Thanks,
Jose AIM- RangerBud249 Yahoo- RangerBud249
Imports System.Data.OLEDB Imports System.Data Imports System.Data.SqlClient Public Class Test Inherits System.Web.UI.Page Public dbConn As SqlConnection Public da As SqlDataAdapter Protected WithEvents txtStart As System.Web.UI.WebControls.TextBox Protected WithEvents txtEnd As System.Web.UI.WebControls.TextBox Protected WithEvents Calendar1 As System.Web.UI.WebControls.Calendar Protected WithEvents Label1 As System.Web.UI.WebControls.Label Protected WithEvents img_cal1 As System.Web.UI.WebControls.ImageButton Protected WithEvents img_cal2 As System.Web.UI.WebControls.ImageButton Public ds As DataSet
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'the TailNumber is the first thing that will load If Not Page.IsPostBack Then lstAircraft.DataSource = GetAircraft() 'this function will load the Tail Numbers lstAircraft.DataValueField = "TailNumber" lstAircraft.DataTextField = "TailNumber" lstAircraft.DataBind() End If End Sub
Function GetAircraft() As System.Data.DataSet 'this function will load the Tail Numbers Dim connectionString As String = "connection info" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Aircraft].[TailNumber] FROM [Aircraft]" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
Return dataSet End Function
****************************************************************************************************************** this is the area I really need help with
Sub btm_Submit_Click(ByVal sender As Object, ByVal e As EventArgs) Dim connectionString As String = "connection info" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) dbConnection.Open() dbConnection.Close() Dim selection As String Dim startTime As String Dim landingTime As String Dim flights As String
Dim selString As String = "SSELECT AircraftID, fromLocation, startTime, toLocation, landingTime, ID, Filename, VSU, local_1 FROM Flights, tbl_FileNames, tbl_VSU"
Dim commandString As String = "SELECT [Flights].AircraftID, [Flights].fromLocation, [Flights].startTime, [Flights].toLocation, [Flights].landingTime, [tbl_FileNames].ID, [tbl_FileNames].Filename, [tbl_FileNames].VSU, [tbl_FileNames].local_1 FROM Flights, tbl_FileNames, tbl_VSU"
selection = "where " & Me.lstAircraft.SelectedValue.ToString & " " & startTime = Me.txtStart.Text & " " & Me.txtEnd.Text = landingTime & " " & [Flights].[ID] = [tbl_VSU].[flight_id]) AND [tbl_VSU].[filename_id] = [tbl_FileNames].[ID]" If Me.txtStart.Text <> "" Then selString = selString & selection End If Session("SelStr") = selString
Dim dbCommand As New OleDbCommand(commandString, dbConnection)
DataGrid1.DataSource = GetFlights(lstAircraft.SelectedValue) DataGrid1.DataBind() End Sub *********************************************************************************************************************
Function GetFlights(ByVal ID As Integer) As System.Data.DataSet Dim connectionString As String = "connection info" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Flights].[toLocation], [Flights].[startTime], [tbl_FileNames].[ID], [Flig" & _ "hts].[fromLocation], [tbl_FileNames].[filename], [tbl_FileNames].[Local_1], [Fli" & _ "ghts].[AircraftID], [Flights].[landingTime], [tbl_FileNames].[VSU] FROM [Flights" & _ "], [tbl_FileNames], [tbl_VSU] WHERE (([Flights].[ID] = [tbl_VSU].[flight_id]) AN" & _ "D ([tbl_VSU].[filename_id] = [tbl_FileNames].[ID]))" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
Return dataSet End Function
Private Sub img_cal1_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles img_cal1.Click 'this will make the calendar for start visible when clicked and not visible if clicked again Session("Cal1") = True Session("Cal2") = False Me.Calendar1.Visible = True End Sub
Private Sub img_cal2_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles img_cal2.Click 'this will make the calendar for end visible when clicked and not visible if clicked again Session("Cal2") = True Session("Cal1") = False Me.Calendar1.Visible = True
End Sub
Private Sub Calendar1_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged 'this will put the date selected in my text box as a short date If Session("Cal1") = True Then txtStart.Text = Calendar1.SelectedDate.ToShortDateString Session("Cal1") = False ElseIf Session("Cal2") = True Then txtEnd.Text = Calendar1.SelectedDate.ToShortDateString Session("Cal2") = False End If Me.Calendar1.Visible = False
Can anyone help me with this problem? I have a form with three drop down lists. From these lists, the user selects an accountID, the territory that ID is from, and the territory that ID is transfering to. Upon submission, that account number is inserted into the table, along with the TerritoryID, RegionID, DivisionID, and EmpID associated with both of the territories selected. However, upon running this, the accountID and ToTerritory info are inserted once, but the FromTerritory data is inserted tens of thousands of times. Although they are not entered into the same record, which i eventually want to happen, i do want to figure out why so many duplicate entries for the FromTerritory are entered. Here is my code and the stored procedure to enter the FromTerritory. I would appreciate any help. -------------------------
Sub InsertData()
Dim sql as string
dim sql1 = "insertaccounttransfermike 'AccountID''" & AccNumber.SelectedItem.Value & "'" dim sql2 = "insertaccounttransfermikefrom 'FromTerritory''" & FromName.SelectedItem.Value & "'" dim sql3 = "insertaccounttransfermiketo 'ToTerritory''" & ToName.SelectedItem.Value & "'"
' Create Connection Object, Command Object, Connect to the database Dim conn as New SQLConnection(connstr)
Dim cmd1 as New SQLCommand(sql1,conn) Dim cmd2 as New SQLCommand(sql2,conn) Dim cmd3 as New SQLCommand(sql3,conn)
SELECT Territories.TerritoryID, Regions.RegionID, Divisions.DivisionID, Employees.EmployeeID FROM EndoscopySqlUser.Territories INNER JOIN EndoscopySqlUser.Regions ON EndoscopySqlUser.Territories.RegionName = EndoscopySqlUser.Regions.Region INNER JOIN EndoscopySqlUser.Employees ON EndoscopySqlUser.Territories.TerritoryID = EndoscopySqlUser.Employees.TerritoryID INNER JOIN EndoscopySqlUser.Divisions ON EndoscopySqlUser.Regions.Division = EndoscopySqlUser.Divisions.DivisionID, accounts WHERE ( (EndoscopySqlUser.Employees.DateLeft IS NULL) AND (EndoscopySqlUser.Territories.TerritoryName=@FromT erritory)) GO
I'm running SQL Server 2000. I have an interesting form I am creating for a client which has on it literally 20,500 fields that need to be stored in the system which I then have to create reports off of for statistics and trends.
I'm not sure how I should go about storing that large amount of information in SQL Server with the limitations of the size of a table. Would it be best to create 20 some tables to store it, or is there a better fashion to store it. 90% of the fields are numbers ranging from 0 to 100.
Table name 1 : Income Income RentMonth 1500 Jan 1500 Feb 1500 Apr
Table name 2 : Expense Expense ExpMonth 200 Jan 300 Mar 400 Apr
The result table becomes (Profit=Income-Expense) Profit Month 1300 Jan 1500 Feb -300 Mar 1100 Apr
But I form the query by join the both Income and Expense tables to subtract the Income and Expense month wise.
But one moth is in one table the same month is not in another table.
For Example Feb month is in Income table, but not in Expense table.And Mar month is in Expense table and not in Income table. So how will I form the query to achieve my result table as i indicated above. Kindly help me.
I'm using SQL server on godaddy.com, and for the life of me, tried all freakin day yesterday to get a form to send data to my table, and can't get it to work for the life of me
I think the asp form sends the information to the asp page that is to add the information to the tables correctly, but am not entirely sure, so here is the code for the form:
Now for the page that is giving me the problem (I think). I'm pretty sure I'm fudging up the connectivity somewhere because all I get is an http 500 error every time I try to upload the information.
Why won't it bloody connect?!?!
Also, is the DIM function on this page used to hide important information? If you guys can help me fix this, I'd be incredibly, incredibly greatful, I'm frustrated out of my mind!
<%@LANGUAGE="JAVASCRIPT" CODEPAGE="CP_ACP"%> <html> <head> <title>piece of shit page</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> <% Dim oConn, oRs Dim qry, connectstr Dim db_name, db_username, db_userpassword Dim db_server
I have a Visual Basic application that connects to a SQL server database. In my application, I have a login screen that is supposed to pause the application (it's coded as modal) and ask the user for a login and password. I have my connection string and user set up to use Windows NT authentication.
However, what is happening with my application is that as soon as I start the program, it never stops at the login screen, but instead goes right into the application.
Is this "normal" when using Windows NT authentication? What causes this behavior? How can I make my program stop at the login screen, and more interestingly, would I want to stop at a login screen?
Thanks in advance to all who reply. This is driving my crazy!
This forum is best viewed with a computer. Questions asked freely. Confusion happily shared.
What I want i, if there is any value in any fields, other null values in the field should be replaced by 0, if all the rows of the filed are null, simply leave as it is....
Like the select statement of this SIM_TempCustomer should give a result like