Stored Procedure Using UNION Joins Is Not Displaying Correctly... Can Someone Help Me With My Logic?
May 16, 2007
I have a stored procedure using UNION joins on three SQL queries.
Sadly, I'm only now learning how to use Stored Procedures, so if this is a really dumb question, please forgive me. I'm not used to big UNION statements like this either... usually I'm just programming websites to serve information out pretty simply :)
I need to return one result set, sorted by date... one complete result per day.
eg:
5/15/2007 | XX | XX | XX | XX | XX | XX |
5/16/2007 | XX | XX | XX | XX | XX | XX |
5/17/2007 | XX | XX | XX | XX | XX | XX |
Currently, when I run the query, I'm getting three separate date values for each date...
eg:
5/15/2007 | XX | XX | 00 | 00 | 00 | 00 |
5/15/2007 | 00 | 00 | XX | XX | 00 | 00 |
5/15/2007 | 00 | 00 | 00 | 00 | XX | XX |
5/16/2007 | XX | XX | 00 | 00 | 00 | 00 |
5/16/2007 | 00 | 00 | XX | XX | 00 | 00 |
5/16/2007 | 00 | 00 | 00 | 00 | XX | XX |
etc
How do I fix this? I've looked through my query ad naseum and don't see anything that sets me off as "wrong".
Here is the stored procedure if you can help. I'd really really love the help!
C R E A T E P R O C E D U R E sp_ApptActivityDate
(@strWHERE as varchar(500),
@strWHERECANCELED as varchar(500)
)
as
exec ('SELECT [date] AS Date, SUM(length) AS TotalSlots, COUNT(cast(substring(appointUniqueKey, 1, 1) AS decimal)) AS TotalAppts, SUM(length * 5) / 60 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHours
FROM dbo.vw_ALL_ApptActivity ' + @strWHERE + '
UNION
SELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, SUM(length) AS TotalActiveSlots, COUNT(cast(substring(appointuniquekey, 1, 1) AS decimal)) AS TotalActiveAppts, SUM(length * 5) / 60 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHours
FROM dbo.vw_Active_ApptActivity' + @strWHERE + '
UNION
SELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, SUM(length) AS totalCancelSlots, COUNT(cast(substring(AppointUniqueKey, 1, 1) AS decimal)) AS TotalCancelAppts, SUM(length * 5) / 60 AS TotalCancelSlotHours
FROM dbo.vw_CANCELED_ApptActivity ' + @strWHERECANCELED + '
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
Hi. I have a stored procedure that I'm interacting with through vb.net... the stored procedure logic, by itself, runs in query analyzer, but when I run it from the code side using the stored procedure, it dies. Now, that said, I'm not a pro at writing a stored procedure, so you might look at this and gasp in horror. I'm executing a three-staged query, all of which are using temp tables, the final of which is what I'm using for my datagrid import. The temp table name I'm using doesn't work... it's not accepted in the SP and stops the execution of the query stating "invalid table name." Not being a guru at this, I dont know why it runs in Query Analyzer but not w/i the SP. Can someone look at this and help? (If its grossly written and there's a better way, I wouldn't be offended to be told that either. I"m looking for ways to write cleaner, faster code) Here is the SP, any suggestions are very very appreciated. Thanks so much!
CREATE PROCEDURE sp_PCPPanel_Summary (@strWHEREMale as varchar(500), @strWHEREFemale as varchar(500)) AS EXEC(' SELECT Gender, RealAge, WeightedAge, FWeight, Mweight, AccountNum, PatLastName, PatFirstName, PatAddress, Patcity, PatState , PatZip, Tertiary, PatientStatusKey, InsClass, InsClassDesc, PCPDr, PCPClass, InsurancePlan, CarrierKey, CarrierName, PlanName, Age INTO #tblTempPCP FROM( SELECT tblPCP.AccountNum, tblPCP.PatLastName, tblPCP.PatFirstName, tblPCP.PatAddress, tblPCP.PatCity, tblPCP.PatState, tblPCP.PatZip, tblPCP.PatPhone, tblPCP.DOB, tblPCP.Age, tblPCP.Gender, tblPCP.InsurancePlan, tblPCP.PCPClass, tblPCP.CarrierName, tblPCP.CarrierKey, tblPCP.PCPDr, tblPCP.PlanName, tblPCP.InsAddress, tblPCP.InsCity, tblPCP.InsState, tblPCP.InsZip, tblPCP.Tertiary, tblPCP.PatientStatusKey, dbo.tblPanelWeights.WeightedAge, dbo.tblPanelWeights.Category, 0 as MWeight, dbo.tblPanelWeights.Female as FWeight, dbo.tblPanelWeights.RealAge, tblPCP.InsClass, tblPCP.InsClassDesc, tblPCP.ApptDate FROM dbo.tblPanelWeights RIGHT OUTER JOIN dbo.[vwPCP+Continuity] tblPCP ON dbo.tblPanelWeights.RealAge = tblPCP.Age ' + @strWHEREFEMALE + ' UNION SELECT tblPCP.AccountNum, tblPCP.PatLastName, tblPCP.PatFirstName, tblPCP.PatAddress, tblPCP.PatCity, tblPCP.PatState, tblPCP.PatZip, tblPCP.PatPhone, tblPCP.DOB, tblPCP.Age, tblPCP.Gender, tblPCP.InsurancePlan, tblPCP.PCPClass, tblPCP.CarrierName, tblPCP.CarrierKey, tblPCP.PCPDr, tblPCP.PlanName, tblPCP.InsAddress, tblPCP.InsCity, tblPCP.InsState, tblPCP.InsZip, tblPCP.Tertiary, tblPCP.PatientStatusKey, dbo.tblPanelWeights.WeightedAge, dbo.tblPanelWeights.Category, dbo.tblPanelWeights.Male as MWeight, 0 as FWeight, dbo.tblPanelWeights.RealAge, tblPCP.InsClass, tblPCP.InsClassDesc, tblPCP.ApptDate FROM dbo.tblPanelWeights RIGHT OUTER JOIN dbo.[vwPCP+Continuity] tblPCP ON dbo.tblPanelWeights.RealAge = tblPCP.Age ' + @strWHEREMALE + ' ) unionWEIGHTS GROUP BY Gender, RealAge, WeightedAge, FWeight, Mweight, AccountNum, PatLastName, PatFirstName, PatAddress, patcity, patState , patZip, Tertiary, PatientStatusKey, InsClass, InsClassDesc, PCPDr, PCPClass, InsurancePlan, CarrierKey, CarrierName, PlanName, Age ORDER BY realage ')
SELECT isnull(cast(RealAge as varchar), 'Unknown') as AgeRange, RealAge as AgeSort, gender, mweight, fweight, [Male] = sum(case when gender = 'M' then 1 else 0 end), [Female] = sum(case when gender = 'F' then 1 else 0 end), count(*) as Totalinto #tblTempPCP2 FROM #tblTempPCPgroup by realage, gender, mweight, fweight
Select AgeRange, AgeSort, [MALE], [FEMALE], [Male Weighted] = case when gender = 'M' then Sum(MWeight * [MALE]) end, [Female Weighted] = case when gender = 'F' then Sum(FWeight * [FEMALE]) endfrom #tblTempPCP2group by AgeRange, AgeSort, [MALE], [FEMALE], gender, totalorder by AgeSortGO
I am new to trying to do IF/THEN logic in a stored procedure. I want to return one query if @property=1 and another if @property=0. Can anyone give me a hint in how to do this correctly. Here's the basic idea of what I am doing:
Create Procedure "get_orders_by_date_and_card" @property int =0 as
if @property=0 begin SELECT stores FROM shops_master_Table end else begin SELECT restaurants FROM restaurant_master_table end return
is there a way of checking if the component is already in the data base if it is then just get the CompID and attach that CompId in the AppsComponents link table and put that in the AppComponents table. the snapdate field is to be the current datetime when the data is collected. Is there a current date function in SQL server? Is it possible to do this in a stored procedure?
I have a report in SQL Reporting Services that isn't displaying correctly. The report comprises a header and a simble table, typically consisting of 20 to 30 columns and 1-20 rows.
When I run the report IE, the table is truncated and only the first five columns are displayed. I can get the table to display correctly by doing one of the following:
Changing the report zoom - once the zoom has been changed, the report displays correctly even back at the default 100%
Moving to the next report page, then back to the first page
Refreshing the report by right-clicking and selecting 'Refresh' (not using the refresh button in the tool bar) What seems strange is that if I copy the truncated table from IE to Excel, the full table is copied so it appears that what I'm seeing is some kind of display issue. I've trawled Google and the forums but to no avail - can anyone help?
Hello.Looking for a smarter way to code the following. I have a storedprocedure I will be passing several variables to. Some times, some ofthe fields used in a WHERE clause will not be passed, and I would liketo avoid having to code a bunch of if statements to set the executingcode. For example, below I would only like to execute the LIKEconditions only when the variable in question is not NULL. I did atest and if the variable is set to null, obviously the select does notreturn what I'm expecting.if @switch = "B"SELECT * from ikb whereikbtitle like @ins1 andikbtitle like @ins2 andikbtitle not like @ins3 andikbbody like @ins1 andikbbody like @ins2 andikbbody not like @ins3endThanks for any help or information with this.
I am working with stored procedures to provide conditional business logic for a customer based on triggers. Is it possible for a stored procedure to execute an external program ie opening or distributing a crystal report?
STR(YEAR(DATEENTERED)) + '/' + STR(MONTH(DATEENTERED)) + '/' + STR(DAY(DATEENTERED)) AS DATETIME ) AS Date,
[From], [To], [Company], [Catagory], [Client], [Description], [TotalHours] FROM [JcpowersJobs]"
It will show correctly in the query builder test but when you load it up in the gridview it will show the date AND 12:00:00AM for every one of the dates
I tried replacing DATETIME with VARCHAR but it would insert spaces into the date where I didn't want them: EX: (12/04/1994 => 12/ 04/ 1994)
I have managed to get reporting services running on Vista, but when I open report manager, I don't see the "Contents" or "Properties" tabs. I get the "SQL Server Reporting Services - Home" header and the links on the top right, but no tabs along the yellow or blue lines. I also have created and deployed a report but it does not show up in Report Manager.
I have managed to get reporting services running on Vista, but when I open report manager, I don't see the "Contents" or "Properties" tabs. I get the "SQL Server Reporting Services - Home" header and the links on the top right, but no tabs along the yellow or blue lines. I also have created and deployed a report but it does not show up in Report Manager.
I have an Excel spreadsheet with three columns of data, one of which is a "Score". The score will range between 1.0 and 0, going out to two decimal places. I am able to get this data into a global temporary table. I have a script in which a message box pops up, displaying the MIN value of the "Score" field in the temp table. The MIN value is .2. When I try to get the data from the temp table to a staging table, the Scores are all rounded to the nearest whole number. I think I've tried using every numeric data type for the staging table, and I always get the same results. In the temp table, Score is defined as such:
[Score] [decimal](18, 0) NULL
Does anybody know what I need to do to get the score to display acccurately?
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
Iam working with a query where iam joining different fields from two tables and this is working well SELECT Ind.Industry_Name,Com.Company_Name,Com.Company_Address FROM Industry AS Ind INNER JOIN Company AS Com INNER JOIN ON Ind.Ind_ID_PK = Com.Ind_ID_FK Here iam getting the values depending on the Ids of both the tables. But now i want to join three different tables..can i use this Query SELECT Ind.Industry_Name,Com.Company_Name,Com.Company_Address,Pla.Plant_Name,Pla.Created_On FROM Industry AS Ind INNER JOIN Company AS Com INNER JOIN Plant AS Pla ON Ind.Ind_ID_PK = Com.Ind_ID_FK and Ind.Ind_ID_PK = Pla.Ind_ID_FKIam getting an error like : Incorrect syntax near 'Ind_ID_FK'. i.e here Com.Ind_ID_FK . I checked by removing and Ind.Ind_ID_PK = Pla.Ind_ID_FK but iam getting same error.
This example is working: Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY LastActivityDate DESC) AS Row, UserId, UserName FROM aspnet_Users) SELECT UserId, UserName FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND and this one doesn't work:USE [xx]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[battle_Paging]@PagerIndex INT,@ShowMembers INT ASBEGINDeclare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY aspnet_Users.LastActivityDate DESC) AS Row, aspnet_Users.UserId, aspnet_Users.UserName, mb_personal.Avatar FROM aspnet_Users LEFT JOIN mb_personal ON (mb_personal.UserID=aspnet_Users.UserId) SELECT UserId, UserName, Avatar FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND Error:Msg 156, Level 15, State 1, Procedure battle_Paging, Line 18Incorrect syntax near the keyword 'SELECT'. I try to join in the table mb_personal here. Some help would be very appreciated! Thanks.
I have a database of news articles and i have a stored procedure that basically pulls one from the database based on an ID number. The author (Press Contact) and publication are stored as just ID numbers and pulled in via JOINs.
SELECT Articles.date_published, Articles.headline, Publication.press_contact, Publication.pub_name, Articles.body FROM Articles LEFT OUTER JOIN PressContact ON PressContact.press_id = Articles.press_id LEFT OUTER JOIN Publication ON Publication.publication_id = Articles.publication_id WHERE (Articles.id = @ID)
Everything works great in this setup. However, we've recently added a press_id2 field to the articles table to be able to store a 2nd press contact. So now I need my stored procedure to pull out both press contact names and I'm not sure the best way to do that. I tried to JOIN the PressContact table a 2nd time on PressContact.press_id = Articles.press_id2 but that didn't seem to work. Can anyone give me any suggestions? Thanks in advance.
How 'NULL' value is displaying after encrypting a stored procedure? links to know the procedure behind this how the encrypted procedure is storing and updating with NULL value under the same column after the encryption.
I am having problems displaying time values in my SSRS report. below is info. Tried expressions still does not work. I want the values to show what in the SQL Server table 00:00:00.82. I tried stored proc still does not work.
SQL Server table time value shown in milliseconds: 00:00:00.82
I am having problems producing the following report.
The function of this report is to list people who have not paid their account in 30,60, and 90 days.
In the BillingRun table, there is a dateprocessed field. this is the date to check against the paydate field in the payment table. i.e. if their account is in debit, check to see when a payment was last made.
Using the following columns: Sitename , Child Name, Debt Age, Amount Outstanding,
Debt Age would be 30 days, 60 days or 90 days+ I am using a datediff(day,billingrun.dateprocessed,payment.paydate) to get the age of the debt.
Rpt_ageDebtorsForSite (@cmb1 as varchar(100)) with encryption as Begin Would suggest a union. i.e. create a select statement for the 30 days to 59 days UNION select statement for 60 to 89 days UNION select statement for 90 + days
i.e. SELECT S.SITENAME,C.FORENAME+€™ €˜+C.SURNAME,€™30 DAYS€™,TOTAL WHERE DEBT BETWEEN 30 AND 59 DAYS OLD UNION SELECT S.SITENAME,C.FORENAME+€™ €˜+C.SURNAME,€™30 DAYS€™,TOTAL WHERE DEBT BETWEEN 60 AND 89 DAYS OLD UNION SELECT S.SITENAME,C.FORENAME+€™ €˜+C.SURNAME,€™30 DAYS€™,TOTAL WHERE DEBT 90 DAYS OR GREATER OLD End
This is what i have so far,
CREATE procedure rpt_ageDebtorsForSite (@cmb1 as varchar(100)) WITH ENCRYPTION AS BEGIN
SELECT DISTINCT
Site.siteName, Child.surname + ' ' + Child.forename AS Child_Name, datediff(day,billingrun.dateprocessed,payment.paydate) AS DebtAge --
FROM Site INNER JOIN BillingRun ON Site.siteID = BillingRun.siteID INNER JOIN payment ON Site.siteID = payment.siteID INNER JOIN Child ON Site.siteID = Child.siteID
select amount from payment AS Amount_Outstanding
--WHERE --site.sitename=@cmb1 END
The output should look like
Sitename: childname: debtage: amount_outstanding HappySite mary 10 100
I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,
Table A Contains a Main Image, this image is displayed in the results Table B Contains an Icon, this image is displayed in the results Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.
Select title, description, image from tableA UNION ALL Select title, description, icon as image from tableB UNION ALL title, description, ( inner Join SELECT top(1) from imageTableC where imagetableC.FK = tableC.PK) as image from tableC
Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.
i have a search page having four text boxes like name,accountnumber,ssn..etc we have to search the database by these values. but even if we give value in one text box keeping the others null the stored procedure have to serach and get the values. and we display it using gridview control.
here is the stored procedure i wrote.but its not working.its not giving any erros...but its not showing any values.
AS BEGIN if(@name!=null AND @ssn!=null AND @accountnumber!=null) select * from Userinfo where name=@name AND ssn=@ssn AND accountnumber=@accountnumber else if(@name=null AND @ssn!=null AND @accountnumber!=null) select * from Userinfo where ssn=@ssn AND accountnumber=@accountnumber else if(@name=null AND @ssn=null AND @accountnumber!=null) select * from Userinfo where accountnumber=@accountnumber else if(@name!=null AND @ssn=null AND @accountnumber!=null) select * from Userinfo where accountnumber=@accountnumber AND name=@name else if(@name!=null AND @ssn=null AND @accountnumber=null) select * from Userinfo where name=@name else if(@name!=null AND @ssn!=null AND @accountnumber=null) select * from Userinfo where name=@name AND ssn=@ssn else if(@name=null AND @ssn!=null AND @accountnumber=null) select * from Userinfo where ssn=@ssn
end
table name is userinfo
please help me with this. its very urgent. thanx for your help in advance.
I have the following stored procedure in SQL 2000 and would like todiplay the database name where data is drawn from. I'm using 4databases db1, db2, db3, db4 which all have the same table (Table1)with identical column names (Surname, GivenNames).CREATE PROCEDURE [dbo].[x_searchwildcard] @varSurname VARChar(25)ASSelect a.Surname, a.GivenNamesFrom [db1]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db2]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db3]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db4]..Table1 As aWhere a.Surname LIKE @varSurname + '%'Order By a.Surname,a.GivenNamesGOI tried the followingSelect a.Surname, a.GivenNames, db_name()However it only gave me the name of the database where the storedprocedure is kept (in my case 'Common')I was hoping it would display results something like the followingSurname GivenNames Database------- ---------- --------Fred Smith db1Freddy Smith db2Fred Smith db3Fred Smithe db3Fred Smith db4Fred Smithye db4Instead I receiveSurname GivenNames Database------- ---------- --------Fred Smith commonFreddy Smith commonFred Smith commonFred Smithe commonFred Smith commonFred Smithye commonAny ideas?ThanksRick
Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.
I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.
I've got a union query (below)and it returns rows that have duplivate itemno's, descrip's, imsrp3's, and imsrp4's, while the remaining columns are not duplicate for the same row. An Excel report uses this query to populate itself and for a more visually appealing look, I'd like to skip the duplicated columns in the display. I'm not sure how to use the Distinct or Group by in this case, since technically I'm dealing with two separate queries, neither one separately returning any duplicate rows. thanks for any suggestions...
~ select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nowo where nowo.wasrst <='40' union select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nopo where nopo.wasrst <='499'
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
Insert Into Table2 Select * From Table1 Where Column1Val = '4'
As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.
Why is the above query failing with
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
Hello all .. I have a form that views the date automatically in a textbox... the date of this text box must be stored in the database after the user press the submit button ... However, I am getting a wrong date which is 01/01/1900 .. although I see it in the form as 15/01/2008 .. this is the code ... please give me a solution ASAP ..
Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click 'Declaring the variablesDim objConnection As SqlConnection Dim objDataCommand As SqlCommand Dim courseId, traineeName, traineeId, department, comment1, comment2 As String Dim formDate As Date Dim RB1, RB2, RB3, RB4, RB5, RB6, RB7, RB8, RB9, RB10, RB11, RB12, RB13, RB14 As String Dim ConnectionString, evaluationSQL, TraSQL, iTotal As String 'Save form values in variables formDate = Convert.ToDateTime(DateTxt.Text) courseId = CourseIDTxt.Text traineeId = EmailTxt.Text traineeName = TraineeNTxt.Text department = DeptDropDownList.SelectedValue RB1 = RBL1.SelectedValue RB2 = RBL2.SelectedValue RB3 = RBL3.SelectedValue RB4 = RBL4.SelectedValue RB5 = RBL5.SelectedValue RB6 = RBL6.SelectedValue RB7 = RBL7.SelectedValue RB8 = RBL8.SelectedValue RB9 = RBL9.SelectedValue RB10 = RBL10.SelectedValue RB11 = RBL11.SelectedValue RB12 = RBL12.SelectedValue RB13 = RBL13.SelectedValue RB14 = RBL14.SelectedValue comment1 = CommentTxt1.Text comment2 = CommentTxt2.TextConnectionString = WebConfigurationManager.ConnectionStrings("GRPConnectionString").ConnectionString 'Create and open the connection objConnection = New SqlConnection(ConnectionString) objConnection.Open()evaluationSQL = "Insert into Evaluation (Eva_Date, CourseD_Id, Tra_Id , Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13, Q14, Comment1, Comment2 ) " & _ "values(" & formDate & ",'" & courseId & "','" & traineeId & "','" & RB1 & "','" & RB2 & "','" & RB3 & "','" & RB4 & "','" & RB5 & "','" & RB6 & "','" & RB7 & "','" & RB8 & "','" & RB9 & "','" & RB10 & "','" & RB11 & "','" & RB12 & "','" & RB13 & "','" & RB14 & "','" & comment1 & "','" & comment2 & "')"objDataCommand = New SqlCommand(evaluationSQL, objConnection) objDataCommand.ExecuteNonQuery()
Hey guys,I have to import a csv file to my database and then add some values to other fields based on these values. At present I use a store procedure that does a bulk insert and then I use an update and fetch inside the sql to perform these updates. I am not sure if this is the best way. Is this bad, since some amount of busniess logic is in the store procedure?If it is, how can I do this better?Thanks for the answer.
Hello,I stuck in a delimma.Where to put the business logic that involves only one updatebut N number of selects from N tables........with N where conditions
