Problem Dynamicly Populating A Checkboxlist From A Query
Jul 16, 2007
I have the follow, i get the right amout of checkboxes but they all have the same value(System.Data.Common.DbDataRecord)
Dim objconn As New SqlConnection(connstring_MPR)Dim objcmd As SqlCommand = New SqlCommand("SELECT [Parts Master Table].COMMD_CODE as comcode FROM [Parts Master Table] INNER JOIN [Warehouse balance table] ON [Parts Master Table].PART_NUMBER = [Warehouse balance table].PART INNER JOIN POREPORT ON [Parts Master Table].PART_NUMBER = POREPORT.[Part Number] INNER JOIN [DEMAND TABLE] ON [Parts Master Table].PART_NUMBER = [DEMAND TABLE].PART WHERE POREPORT.[PO Bal] > 0 OR [DEMAND TABLE].QTY > 0 or [Warehouse balance table].ONHAND > 0 and [Parts Master Table].M_B = 1 AND [Warehouse balance table].WHSE = 'sgr' AND ([Parts Master Table].FAMILY NOT LIKE 'lam%' or [Parts Master Table].FAMILY NOT IN ('ULTCH', 'REMOT', 'MKSIN')) GROUP BY [Parts Master Table].COMMD_CODE ORDER BY [Parts Master Table].COMMD_CODE", objconn)
For example, Training OR Production – should include everyone with an Training OR everyone with a Production checked OR everyone with both Training and Production checked. If service AND technical support – just those two options will show – the customer can only have those 2 options selected in their account and nothing else.
Is there an easy way to build the SQL query for this scenario? Any suggestions or tips?
I have a page that is part of a 5 page wizard. The wizard gathers data for a claim filter. The second page is laid out with four labels at the top Filter Name: <filterName> -- filterName_LFilter Description: <filterDescription> -- filterDescription_LFilter Data Source: <filterDataSource> -- filterDataSource_LFilter Purpose: <filterPurpose> -- filterPurpose_L --I have written an sp to populate these four label.Text values. I want to populate them on page load. Two questions. How do I get four output values out of the sp? Is there a better way to do this than an sp? SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
-- =============================================-- Author: <Author,,Name>-- ALTER date: <ALTER Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[a_spNewFilter_Step1_Summary] -- Add the parameters for the stored procedure here @filterID int , @filtername varchar(100) OUTPUT, @filterdescription varchar(250) OUTPUT, @filterOwnerID int OUTPUT, @filterDataSourceID INT OUTPUT, @filterPurposeID int OUTPUT ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON; --DECLARE @FilterID int --SET @filterID = 14 SELECT filterIsComplete, filterCreateDate, filterStep1Complete, filterName, filterDescription, das.AuditorFirstName + ' ' + das.AuditorLastName as FilterOwner, dfds.filterDataSource, dfp.filterPurposeFROM a_factSamplingFilter_2 fsfINNER JOIN dbo.a_dimFilterDataSource dfdsON dfds.filterDataSourceID = fsf.filterDataSourceIDINNER JOIN dbo.a_dimAuditStaff dasON das.AuditorID = fsf.filterOwnerIDINNER JOIN dbo.a_dimfilterPurpose dfpON dfp.filterPurposeID = fsf.filterPurposeIDWHERE filterID = @filterIDGROUP BY filterIsComplete, filterCreateDate, filterStep1Complete, filterName, filterDescription, das.AuditorFirstName, das.AuditorLastName, dfds.filterDataSource, dfp.filterPurpose
END
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO ---- protected void Page_Load(object sender, EventArgs e) { int filterID = int.Parse(Request.QueryString["filterID"]); /// grab the filterID from the URL query string string connectionString = WebConfigurationManager.ConnectionStrings["DDT"].ConnectionString; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("a_spNewFilter_Step1_Summary", con); // get the values from filter setup step1 cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@FilterID", SqlDbType.Int)); // using the filterID created in step1 cmd.Parameters["@FilterID"].Value = filterID; cmd.Parameters.Add(new SqlParameter("@FilterName", SqlDbType.VarChar, 100)); cmd.Parameters["@FilterName"].Direction = FilterName_TB.Text; cmd.Parameters.Add(new SqlParameter("@FilterDescription", SqlDbType.VarChar, 255)); cmd.Parameters["@FilterDescription"].Direction = ParameterDirection.Output; cmd.Parameters.Add(new SqlParameter("@FilterOwner", SqlDbType.VarChar,50)); cmd.Parameters["@FilterOwner"].Direction = ParameterDirection.Output; cmd.Parameters.Add(new SqlParameter("@FilterDataSource", SqlDbType.VarChar,50)); cmd.Parameters["@FilterDataSource"].Direction = ParameterDirection.Output; cmd.Parameters.Add(new SqlParameter("@FilterPurpose", SqlDbType.VarChar,100)); cmd.Parameters["@FilterPurpose"].Direction = ParameterDirection.Output; con.Open(); try { cmd.ExecuteNonQuery(); } finally { con.Close(); } ///get name, description, owner, datasource, purpose
Is there any way i could take the first 50 results of a sql query and store them into 1 table in access and take the next 50 and store them into a second table in access? Is there any SQL statement that will direct where the output gets directed to?
OR is there a way to have the sql reults paird up with a autonumbered ID?
I have the following code and I'm trying to set an event dynamicly in the foreach statement... SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["BvtQueueConnectString2"]); SqlCommand select = new SqlCommand("select top 100 b.jobid as [Job ID], bq.timesubmitted as [Time Submitted], b.timereceived as [Time Received], bq.requestid as [Request ID], bq.timecompleted as [Time Completed], bq.buginfoid as [Bug Info ID], e.eventid as [Event ID], bq.closingeventid as [Closing Event ID], et.eventtype as [Event Type], e.eventtime as [State Start Time], l.twolettercode as [Language], p.projectname as [Project Name], p.packagedesignation as [Package Designation] from bvtjobs b inner join bvtrequestsnew bq on b.jobid = bq.jobid inner join eventlog e on bq.requestid = e.bvtrequestid inner join eventtypes et on e.eventtype = et.eventtypeid inner join languages l on bq.targetlanguage = l.langid inner join projects p on bq.targetplatform = p.projectid order by b.jobid asc", conn); SqlDataAdapter da = new SqlDataAdapter(select); DataSet ds = new DataSet(); conn.Open(); da.Fill(ds); conn.Close(); Table table = new Table();
foreach (DataRow dr in ds.Tables[0].Rows) { TableRow tr = new TableRow(); for (int i = 0; i < dr.ItemArray.Length; i++) { TableCell tc = new TableCell(); tc.Text = dr.ItemArray.GetValue(i).ToString(); tc.BackColor = Color.White; if (dr.ItemArray.GetValue(8).ToString() == "COMPLETE") { tc.BackColor = Color.BlueViolet; } tr.Cells.Add(tc); } table.Rows.Add(tr); } foreach (TableRow tablerow in table.Rows) { TableCell newtc = new TableCell(); Button mybutton = new Button(); newtc.Controls.Add(mybutton); mybutton.Text = "details"; //set the event here tablerow.Cells.Add(newtc); } Panel1.Controls.Add(table); DataBind();
I want to get which column that are sorted dynamicly when the report is shown. I want to use this informastion in some formulas (Like for example: Iif(<sortcolumn>(3);True;False)).
I dont't want to use parameters indicate which column to sort.
I have a stored proc that I am trying to use for sorting and paging below is a portion of it. When I hard code "Stan" into the Where clause I get all the records back that where posted by "Stan" but when I try to pass in "Stan" as a variable it tells me something about an invalid column name.
Here is the working version. When I hard code "Stan" into the Where Clause it works. I am using 2 single quotes on each side. See Where clause.
SET @sql ='SELECT [ThreadName],[PostID],[PostTypeID],[LanguageID],[PostAccessID],[UserID],[ThreadID],[PostParentID],[VoteSummaryID],
The names of the tables that sould be transfered from the production system to the DWH are stored in a table in the production system. Yet I haven't found a proper way to dynamicly define these tables as DataSources and DataDestinations within an Integration Services project.
I have a form with text boxes and checkboxlists that a user will fill out and click submit. When the user clicks submit, it will update the sql database. In my sql database I have checkbox fields. My question is how can I use the selected items in a checkboxlist to update the sql database individual check boxes. Below is the code I have so far that works for a text box:Partial Class windrockform Inherits System.Web.UI.PageProtected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click Dim dashdatasource As New SqlDataSourcedashdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("WindrockIssuesConnectionString").ToString dashdatasource.InsertCommandType = SqlDataSourceCommandType.Text dashdatasource.InsertCommand = "INSERT INTO IssueLog (initiator) VALUES (@initiator)"dashdatasource.InsertParameters.Add("initiator", initiatorTextBox.Text) Dim rowsAffected As Integer = 0 Try rowsAffected = dashdatasource.Insert()Catch ex As Exception Server.Transfer("problem.aspx") End Try If rowsAffected <> 1 ThenServer.Transfer("problem.aspx") ElseServer.Transfer("confirm.aspx") End IfEnd Sub End Class
I am new to asp.net and would appreciate the help. Thanks,
Hi, I'm trying to use a CheckBoxList to display certain records in a Grid View. I have a Grid View, a CheckBoxList with four items and a SqlDataSource and when the user check or uncheck one or more items in the CheckBoxList the Grid View should show records accordingly. I’ve tried to make it work but it’s only the first checked item in the Grid View that has any effect. I use VB. Thanks
Column1 in table 1 has a range from 1 to 5 (int) A CheckboxList displays these 5 items. When checked (for example 1 and 4) I want a gridview with sqldatasource=sqlProducts to display the records where column1 values 1 or 4. When I use the code below I only get the records where column1 values 1.... <asp:SQLDataSource id="sqlProducts" Runat="Server" SelectCommand="Select * From Table1 where Column1 IN (@CBLchecked_items)" ConnectionString="<%$ ConnectionStrings:pubs %>"> <SelectParameters> <asp:ControlParameter Name="CBLchecked_items" ControlID="CBL" propertyname="SelectedValue" type="String" /> </SelectParameters></asp:SQLDataSource>
Hello,Intended scenario:I have a grid view that displays a particular column using a repeater to display data stored in a separate lookup table. In the EditTemplate I drop the repeater and utilize a checkboxlist to allow for updates to be made. After the sqlUpdate command is processed on the main table I call a sub to loop through the checkboxlist and update the lookup table accordingly. Problem: I can't seem to access the checkboxlist in the edit template. I have a 'updateServices' procedure that is called by the main sql updated event. I call findcontrol on the gridview but I keep getting this error: "Object reference not set to an instance of an object". Here is my code: Protected Sub updateServices(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sql_respParty.Updated 'Filing type was modified, update lm_Responsible_Party_Filing_Type If FilingTypeChanged = True Then Dim command As System.Data.Common.DbCommand = e.Command 'Insert service_filing_type Dim li As ListItem Dim checkBoxServices As CheckBoxList = grid_parties.FindControl("checkBoxServices") sql_allFilingTypes.InsertParameters("Party_id").DefaultValue = command.Parameters("@Party_id").Value.ToString For Each li In checkBoxServices.Items If li.Selected = True Then sql_allFilingTypes.InsertParameters("Filing_Type_id").DefaultValue = li.Value sql_allFilingTypes.Insert() Response.Write(" check: " + li.Value.ToString) End If Next End If End Sub Thank you.
Hi, I'm trying to insert multiple checkboxlist values from a databound checkboxlist into a SQL Server Express DB. I need to insert the values JobID and CategoryID into an intermediate table which is made of two columns, JobID and CategoryID, which form the primary key. I'm using the following code:Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim insertCommand As SqlCommand Dim strConnection As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"Dim objConnection As New SqlConnection(strConnection) Try objConnection.Open() Dim ctr As Integer Dim str As StringFor ctr = 0 To CheckBoxList1.Items.Count - 1 If CheckBoxList1.Items(ctr).Selected Then str = "INSERT INTO CategoryVacancies (JobID, CategoryID) values (@JobID, @CategoryID)"insertCommand = New SqlCommand(str, objConnection) insertCommand.Parameters.AddWithValue("@CategoryID", CheckBoxList1.SelectedValue)insertCommand.Parameters.AddWithValue("@JobID", JobIDLabel.Text) insertCommand.ExecuteNonQuery() End If Next objConnection.Close()Catch ex As Exception errorLabel.Text = "Failed because:" & ex.Message End Try End Sub I get this error: Violation of PRIMARY KEY constraint 'PK_CategoryVacancies'. Cannot insert duplicate key in object 'dbo.CategoryVacancies'. The statement has been terminated. It always inserts only the first value of the checkboxlist, instead of looping through and inserting all the rows. Anyone know what's going wrong? Many Thanks
I am using the varchar data type in sql to store a comma-delimited string of multiple selections from a checkboxlist.
The string only has about 28-30 characters in it, but it maxes out the sql row length and I get the 8060 error message every time.
Here is some of the code:
"Dim industry1list As String Dim li As ListItem industry1list = "" For Each li In industry1.Items If li.Selected = True Then industry1list = industry1list & li.Value & "," End If Next .... MyCommand.Parameters.Add(New SqlParameter("@industry1", SqlDbType.VarChar, 60)) MyCommand.Parameters("@industry1").Value = (industry1list)"
I would appreciate any coaching you have for me to get back on track. Thanks, Bob
I'm trying to populate the variable "STATUS" with the BEFORE value from TABLE1 to insert into TABLE2, but not sure how to do that. Attached is a stripped down code I'm working on. Sorry, I'm new at this...// some variable stuff protected ErrorText ErrorText1;protected System.Web.UI.WebControls.DropDownList DISP_CD;public System.Web.UI.HtmlControls.HtmlInputText DISP_DOC;protected System.Web.UI.HtmlControls.HtmlInputText DISP_DATE;protected System.Web.UI.WebControls.DataList DataTagList;protected System.Web.UI.WebControls.Button BtnUpd;public string STATUS = string.Empty; <---- Help me.// some update stuffprivate void UpdateTable(){ using(DatabaseConnection conn = new DatabaseConnection()) { try { conn.OpenConnection(devSettings.junk); for (int i = 0; i < DataTagList.Items.Count; i++) { HtmlInputText textTag = (HtmlInputText)DataTagList.Items[i].FindControl("TagList"); if (textTag.Value.Trim() != string.Empty) { GetOldStatus(conn, textTag.Value.Trim()); <---- Help me. UpdateTable1(conn, textTag.Value.Trim()); UpdateTable2(conn, textTag.Value.Trim()); } } } catch ( Exception ex ) { conn.Rollback(); throw ex; } }}// some sql table stuffprivate string GetOldStatus(DatabaseConnection conn, string tag){ StringBuilder sqlStr = new StringBuilder(); sqlStr.Append(" SELECT "); sqlStr.Append(" STATUS AS STATUS"); <---- Help me. sqlStr.Append(" FROM "); sqlStr.Append(" TABLE1 "); sqlStr.AppendFormat(" WHERE TAG in '{0}'", Functions.DBFormatUpper(tag)); conn.Update(sqlStr.ToString()); return sqlStr.ToString();}private string UpdateTable1(DatabaseConnection conn, string tag){ StringBuilder sqlStr = new StringBuilder(); sqlStr.Append(" UPDATE TABLE1 "); sqlStr.AppendFormat(" SET DISP_DOC = '{0}',",Functions.DBFormatUpper(this.DISP_DOC.Value)); sqlStr.AppendFormat(" DISP_DATE = to_date('{0}', 'mm/dd/yyyy'),", DISP_DATE.Value); sqlStr.AppendFormat(" STATUS = '{0}',", Functions.DBFormatUpper(DISP_CD.SelectedValue)); sqlStr.Append(" UPDT_DATE = sysdate "); sqlStr.AppendFormat(" WHERE TAG in '{0}'", Functions.DBFormatUpper(tag)); sqlStr.Append(" AND STATUS in ('1','2','3')"); conn.Update(sqlStr.ToString()); return sqlStr.ToString();}private string UpdateTable2(DatabaseConnection conn, string tag){ StringBuilder sqlStr = new StringBuilder(); sqlStr.Append(" INSERT INTO TABLE2 ("); sqlStr.Append(" TAG"); sqlStr.Append(" ,DATE"); sqlStr.Append(" ,FIELD1"); sqlStr.Append(" ,FIELD2"); sqlStr.Append(" ,BEFORE"); <---- Help me. sqlStr.Append(" ,AFTER"); sqlStr.Append(" ,USER"); sqlStr.Append(" )"); sqlStr.Append(" VALUES ("); sqlStr.AppendFormat(" '{0}'", Functions.DBFormatUpper(tag)); sqlStr.Append(" ,sysdate "); sqlStr.Append(" ,'JUNK1'"); sqlStr.Append(" ,'JUNK2' "); sqlStr.AppendFormat(" ,'{0}'", Functions.DBFormatUpper(this.STATUS)); <---- Help me. sqlStr.AppendFormat(" ,'{0}'", Functions.DBFormatUpper(DISP_CD.SelectedValue)); sqlStr.AppendFormat(" ,'{0}'", Functions.DBFormatUpper(devState.UserId)); sqlStr.Append(" )"); conn.Update(sqlStr.ToString()); return sqlStr.ToString();}
I've got the following code and it's not really what I want. With the below code I can select in a dropdownlist a value and in the other dropdownlist the correspondending value will be selected. But when I select a value the second dropdownlist won't be filled with all the data in the database. It is filled only with the correspondending value and not with the rest of the value. When someone changes his mind and want to select a value in the dropdownlist it can't be done. Any ideas??Default.aspx:<body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Text="Botanische Naam: "> </asp:Label> <asp:DropDownList ID="DDL1" AutoPostBack="True" runat="server" OnSelectedIndexChanged="ChangeBotanicName" DataSourceID="SqlDataSource1" DataTextField="Botanische_Naam" DataValueField="Botanische_Naam"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BonsaiDataBaseConnectionString %>" SelectCommand="SELECT [Botanische Naam] AS Botanische_Naam FROM [BonsaiSoorten]"> </asp:SqlDataSource> <asp:sqldatasource id="SqlDataSource2" runat="server" connectionstring="<%$ ConnectionStrings:BonsaiDataBaseConnectionString%>" selectcommand="SELECT [Nederlandse Naam] AS Nederlandse_Naam FROM [BonsaiSoorten]WHERE [Botanische Naam] = @Title1"> <selectparameters> <asp:controlparameter name="Title1" controlid="DDL1" propertyname="SelectedValue" /> </selectparameters> </asp:sqldatasource> <asp:Label ID="Label2" runat="server">Nederlandse Naam:</asp:Label> <asp:DropDownList ID="DDL2" AutoPostBack="True" runat="server" OnSelectedIndexChanged="ChangeDutchName" DataSourceID="SqlDataSource3" DataTextField="Nederlandse_Naam" DataValueField="Nederlandse_Naam"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:BonsaiDataBaseConnectionString %>" SelectCommand="SELECT [Nederlandse Naam] AS Nederlandse_Naam FROM [BonsaiSoorten]"> </asp:SqlDataSource> <asp:sqldatasource id="SqlDataSource4" runat="server" connectionstring="<%$ ConnectionStrings:BonsaiDataBaseConnectionString%>" selectcommand="SELECT [Botanische Naam] AS Botanische_Naam FROM [BonsaiSoorten]WHERE [Nederlandse Naam] = @Title2"> <selectparameters> <asp:controlparameter name="Title2" controlid="DDL2" propertyname="SelectedValue" /> </selectparameters> </asp:sqldatasource> </div> </form></body>Default.aspx.vb:Partial Class _Default Inherits System.Web.UI.Page Sub ChangeBotanicName(ByVal Sender As Object, ByVal e As System.EventArgs) DDL2.DataSourceID = "SqlDataSource2" End Sub Sub ChangeDutchName(ByVal Sender As Object, ByVal e As System.EventArgs) DDL1.DataSourceID = "SqlDataSource4" End SubEnd Class P.S. I posted this before but can't find it anymore so here it is again
I have a table with a list of products,once I enter the data into the table and start using it on my web site as a drop down list,the list is sorted as an alphabetical list,is there are way to have a single drop down list but still be able to group the those products,in order words force them not to get sorted aphabetically.
I have a DataSet (which requires 2 parameters), and for some reason even though it works in Query Analyzer and on the Data Tab of VS2003 it will not work when I choose to Preview it. Can anybody shed some light on this behaviour?
If you need to see the dataSet I can post it up! Thanks!
Hi All, I have a startdate (01/11/2007) and a enddate (01/11/2008). I need to add dates into a table for everyday between these dates. Can anyone help?
I am trying to populate an array from a sqlreader. I am getting the error "Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index". Can anyone help? I have my code below. Try sql = "...." cmd = New SqlCommand(sql, conn) SqlReader = cmd.ExecuteReader 'Get the data from SQL Server Dim Counter As Integer = 0 Do While SqlReader.Read() Counter += 1 PageArray(Counter) = SqlReader("WebPageID") Loop Catch ex As Exception lblMessage.Text = ex.Message End Try
Following is an example of a query which I use in a SP. <BR><BR> What I would really like to do is set both variables with one query. Can I get both data elements with one query, or do I have to run 2 queries to set 2 variables? This query is going to run over 2 servers and would like to save the extra trip. <BR><BR> set @int_MCID = (select top 1 iPID from customers inner join tblPersonnel on MtgConsultant = iPid where phone1 = @str_Phone and Position = 'Consultant') <BR><BR> set @int_LocID = (select top 1 iLocID from customers inner join tblPersonnel on MtgConsultant = iPid where phone1 = @str_Phone and Position = 'Consultant') <BR><BR> Select @int_LocID, @int_MCID <BR><BR>
I am quite new to the complexities of MS SQL and have a problem, I would like to resolve. I have 2 tables with a unique identifier in both and want to populate a new table with information from both, but the second table I would like to populate just some fields that have a DOB eg
Table 1: uniqueId Name Address
Table2: uniqueId Type Setting
example of content for Table 2: uniqueId Type Setting 123 DOB 03/04/74 234 TFN 12345678 567 POA Mr Smith
So the new table needs to be populated with a ll of info in table 1 and has a new field called DOB so only the clients with a DOB should populate this field, if the client in Table 1 has a TFN reference, this record should be added to the new table but no value needs to be entered eg
123 Chris Smith 1 high street 03/04/74 234 Jon brown 2 high terrace <Null>
I have a customer who is running a script that generates a custom report. IT is not populating as it should and is returning zeros for everything.
I have tested the the script in the office and it is populating as intended. I have run a debug on the script and it is executing the correct SQL commands. The debug results for the customer and for the one tested in the office are identical. For some reason, the script is not writing to file but is looking at the correct data. I suspect that it is an environment issue most likely on the SQL level.
Could this be an issue with character set? How can I check their character set and language preferences? I understand they are set during installation.
They are using the same collation as us.
What else can I check as I am running low on ideas.
I advised the client to create a new DB and restore over the top. The script was then tested and it was found to be working fine. As it was given an inappropriate name (ie test), I advised to create a new DB with a production name and restore over the top again. We have since returned back to where we started as the report is generating only zeros.
There are two separate SQL Server databases involved that reside on two different servers. One of the depts within our building wants to have building permit data imported from Permit Database on Server "A" to their own database on Server "B".
I dont think this will be an overly complicated process. There are only a few fields they want populated (5 or 6 tops). This will have to be ran every weeknight via some sort of scheduled task in Windows or SQL.
I was just interested in seeing if anyone has had prior experience working on data transfer like this. I would like to know what would be the best and most efficient way to approach this.
county fname sport ------ ----- ----- surrey tara squash surrey tara hockey surrey tara tennis kent tom tennis kent tom football kent tom rugby
I want to read through the sport table and create a distinct list of sports which can be used to create a new table that would look like: -
County fname squash hockey tennis football rugby ------ ----- ------ ------ ------ -------- ----- surrey tara YES YES YES kent tom YES YES YES
I am using the following code: - DECLARE @sql NVARCHAR(MAX) SELECT @sql = 'create table ey_report_temp (county nvarchar(100),fname nvarchar(100), ' + STUFF((SELECT DISTINCT ',[' + sport + '] nvarchar(100) ' FROM ey_report FOR XML PATH('')), 1, 1, '') + ')'
DECLARE merge_cursor CURSOR FAST_FORWARD FOR SELECT county, fname, sport from ey_report
OPEN merge_cursor FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport WHILE @@FETCH_STATUS = 0 BEGIN
select @sql = N' update ey_report_temp set ' + @sport + ' = ''YES'' where county = ''' + @county + ''' and fname = ''' + @fname + '''' print @sql exec sp_executesql @sql if @@ROWCOUNT = 0
begin select @sql = N' insert into ey_report_temp ( county, fname, ' + @sport + ' ) values ( ' + @county + ', ' + @fname + ', ' + @sport + ')'
exec sp_executesql @sql
end FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport END CLOSE merge_cursor DEALLOCATE merge_cursor select * from ey_report_temp
drop table ey_report_temp
This creates the new table fine however, when it trys to poulate I get the following EM, can anybody help? thanks in anticipation
(1 row(s) affected)
(0 row(s) affected)
update ey_report_temp set squash = 'YES' where county = 'surrey' and fname = 'tara'
(0 row(s) affected) Msg 128, Level 15, State 1, Line 4 The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp set hockey = 'YES' where county = 'surrey' and fname = 'tara'
(0 row(s) affected) Msg 128, Level 15, State 1, Line 4 The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp set tennis = 'YES' where county = 'surrey' and fname = 'tara'
(0 row(s) affected) Msg 128, Level 15, State 1, Line 4 The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp set tennis = 'YES' where county = 'kent' and fname = 'tom'
(0 row(s) affected) Msg 128, Level 15, State 1, Line 4 The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp set football = 'YES' where county = 'kent' and fname = 'tom'
(0 row(s) affected) Msg 128, Level 15, State 1, Line 4 The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp set rugby = 'YES' where county = 'kent' and fname = 'tom'
(0 row(s) affected) Msg 128, Level 15, State 1, Line 4 The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
