I have literally spent the last 2 1/2 hours troubleshooting an issue where I pass a parameter to the page and based on the parameter it runs a SQL statement and fills in the fields on the form. It is giving me the error message below but I know its not a NULL value and I know those controls are named correctly.
System.NullReferenceException: Object reference not set to an instance of an object.
All of my textboxes and labels that are being populated are working fine when I comment out the dropdowns, I have this working on other pages but the only thing that is different here is I am using some AJAX (Autocomplete, Collapsable Panel and Calendar Control).
If I add a label or a textbox field on the form and take the same value that is going to populate the dropdownlist and populate the new control it works, so i know I'm getting the data and its not null/nothing. I also verified that the parameter is working correctly.
Any help will be a blessing, its probably something write under my nose!
Thanks in advance!
Tim
Here is the codeDim conn As SqlConnection
Dim comm As SqlCommand
Dim reader As SqlDataReader
Dim connectionstring As String = ConfigurationManager.ConnectionStrings("MagicDEV").ConnectionString
Dim SeqIncident As Integer = Convert.ToInt16(Request.QueryString("NAME"))
If SeqIncident > 0 Thenconn = New SqlConnection(connectionstring)
comm = New SqlCommand("Select isnull(t.[Sequence],'') as SeqIncident, " & _
"isnull(t.Client,'') as SeqClient, " & _
"isnull(c.Client,'') as Client, " & _
"isnull(t.[Open By],'') as SeqOpenBy, " & _
"isnull(p.[Code],'') as OpenedBy, " & _
"isnull(t.[Sent To],'') as SeqAssignedTo, " & _
"isnull(p1.code,'') as AssignedTo, " & _
"isnull(t.[Date Open],'') as DateOpen, " & _
"isnull(t.[Closed On],''), " & _
"isnull(t.[Closed By],'') as SeqClosedBy, " & _
"isnull(p2.[Code],'') as ClosedBy, " & _
"isnull(t.[Seq_Severity:],'') as SeqUrgency, " & _
"isnull(s.[Name],'') as Urgency, " & _
"isnull(t.[Seq_UdStatus:],'') as SeqStatus, " & _
"isnull(u.[id],'') as Status, " & _
"isnull(t.Contact_Type,'') as ContactType, " & _
"isnull(t.Ctrpart,'') as SeqAsset, " & _
"isnull(i.[Asset#],'') as Asset, " & _
"isnull(t.seq_rpt_party,'') as SeqRptParty, " & _
"isnull(c1.client,'') as RptParty, " & _
"isnull(t.Whiteboard_ID,'') as SeqWhiteboard, " & _
"isnull(w.Whiteboard_ID,'') as Whiteboard, " & _
"isnull(t.Subject,'') as SeqSubject, " & _
"isnull(s1.[Description],'') as Subject, " & _
"isnull(t.[Description],'') as IncDescription, " & _
"isnull(t.Resolution,'') as IncResolution, " & _
"isnull(t.[FCR:],'') as FCC, " & _
"isnull(t.Sent_Ack,'') as SentAck, " & _
"isnull(t.Inc_Closed,'') as IncClosed, " & _
"isnull(t.RC,'') as RC " & _
"From _Smdba_._Telmaste_ t " & _"inner join _Smdba_._Customer_ c on t.client = c.[Sequence] " & _
"left outer join _Smdba_._Personnel_ p on t.[open by] = p.[Sequence] " & _"left outer join _Smdba_._Personnel_ p1 on t.[Sent To] = p1.[Sequence] " & _
"left outer join _Smdba_._Personnel_ p2 on t.[Closed By] = p2.[Sequence] " & _"left outer join _Smdba_._Severity_ s on t.[Seq_Severity:] = s.[Sequence] " & _
"left outer join _Smdba_._UdStatus_ u on t.[Seq_UdStatus:] = u.[Sequence] " & _"left outer join _Smdba_._Inventor_ i on t.[Ctrpart] = i.[Sequence] " & _
"left outer join _Smdba_._Customer_ c1 on t.seq_rpt_party = c1.[Sequence] " & _"left outer join _Smdba_._Whiteboard_ w on t.Whiteboard_ID = w.[Sequence] " & _
I'm a bit stumped at this. I have done this before a bunch of times. The difference is that this time I have two rectangles in the footer. For the each, the visibility is set to an expression which checks whether the page is page 1 or 2. If it is 1, the first rectangle is visible, if 2, the second. When I reference the hidden fields on the main area of the report via ReportItems, they come through fine on the first rectangle, but are invisible on the second. I've tried the same exact fields and every time they are visible in rectangle1, and invisible in rectangle2. What is going on here???
I am trying to filter data on a report by using drop downs. I have been able to create the drop downs, however I can only get one to actually filter content. I need to allow users to select the group from drop down one, and then a different group from drop down 2 so the end result will only display informaiton that matches both drop downs.
Any suggestions will be appreciated. Below is the code if needed
<SqlVariable name="V_Queue" display="Queue :" type="dbselect" displaycolumn="display" datacolumn="group_name" default="%"> <SelectQuery mssql="true" oracle="false" db2="false"> <![CDATA[ SELECT group_name, group_name AS display FROM table_1 WHERE group_name like '%cat1%' UNION SELECT '%' AS group_name, 'All' AS display ]]> </SelectQuery> </SqlVariable> <SqlVariable name="Group" display="Group :" type="dbselect" displaycolumn="display" datacolumn="group_name" default="%"> <SelectQuery mssql="true" oracle="false" db2="false"> <![CDATA[ SELECT group_name, group_name AS display FROM table_1 WHERE group_name like '%group%' UNION SELECT '%' AS group_name, 'All' AS display ]]> </SelectQuery> </SqlVariable>
Hi: I have two drop downs bound to the same data source.. These dropdowns are automatically populated from a database. When I click the button I get some sort of strange query error. Not sure what I'm doing wrong here.
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SQLClient" %> <script language="VB" runat="server"> Dim sOrderby as String Dim sDirection as String Dim MySQL As String Dim MySQL1 As String Dim sSubject As String Dim sCategory As String
Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs)
If Not Page.IsPostBack Then Dim strConn As String = "server=GAALP-DT-UHABB2CFW;uid=sa;pwd=removed;database=NetG" Dim MySQL As String = "Select DISTINCT [Subject] from dbo_v_netG_courses" Dim MySQL1 As String = "Select DISTINCT [Category] from dbo_v_netG_courses" Dim MyConn As New SqlConnection(strConn) Dim objDR As SqlDataReader Dim Cmd As New SqlCommand(MySQL, MyConn) Dim Cmd1 As New SqlCommand(MySQL1, MyConn) MyConn.Open() objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection) ddl.DataSource = objDR ddl.DataValueField = "Subject" ddl.DataTextField = "Subject" ddl.DataBind() MyConn.Close() MyConn.Open()
MyConn.Close() ddl.Items.Insert(0, "-- Choose --") ddlDir.Items.Insert(0, "-- Choose --") End If 'ddl.Items.Insert(0, "-- Choose --") End Sub
' Sub Page_Change(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs) ' MyDataGrid.CurrentPageIndex = e.NewPageIndex ' BindData()
'Sub GridOne(ByVal Source As Object, ByVal E As EventArgs) ' MyDataGrid.CurrentPageIndex = 0 'End Sub
'Sub GetData(ByVal Source As Object, ByVal E As EventArgs) ' BindData() ' End Sub Sub BindData(ByVal Source As Object, ByVal E As EventArgs)
sSubject = ddlDir.SelectedItem.Text sCategory = ddlDir.SelectedItem.Value Dim strConn As String = "server=GAALP-DT-UHABB2CFW;uid=sa;pwd=removed;database=NetG" If sSubject = "" And sCategory = "" Then MySQL = "Select * from dbo_v_netG_courses"
Else ( THIS LINE IS GIVING ME THE ERROR) MySQL = "Select * from dbo_v_netG_courses where [Subject] = & sSubject" End If Dim MyConn As New SqlConnection(strConn) Dim ds As DataSet = New DataSet() Dim Cmd As New SqlDataAdapter(MySQL, MyConn) Cmd.Fill(ds, "dbo_v_netG_courses") MyDataGrid.DataSource = ds.Tables("dbo_v_netG_courses").DefaultView MyDataGrid.DataBind() End Sub
just a simple question here: i have some input parameters on my report that are datetimes (i.e. the user gets the date picker to select the date), if i want to use that parameter in a MDX statement what will it look like? IOW would today's date look like the string "13/02/07", or would i be expecting a string like this: "2007/02/13 00:00"?
I am looking to convert some dropdowns that contain dates extracted from a cube heirarchy with the datepickers so i need to know what i have to change in the MDX to accomodate this.
I also filter the dates that appear in the current dropdowns, is there a way to do this with the datepickers (maybe by pointing them to a dataset of dates extracted from the cube)?
I’m using a SQLDataSource to populate a dropdown. The SQL table I use to populate the drop down has two columns. I only want one of them to be displayed in the drop down but I need to make decisions later in the code based on both columns. How do I access that second column in the datasource?
I built a very simple report which uses a query to define the options in the parameter€™s dropdown. I used that same dataset to define the default for that parameter (meaning that it will just pick the first row from the dataset and use that as the default). When I run the report watching a Profiler trace on the SQL database, it runs that query twice. (Presumably, that€™s once to fill the dropdown list and once to figure out the default.) That seems silly to me since it is the same query that is the same dataset in Reporting Services. Is there any way around this? My parameter bar is rendering twice as slowly as it should be.
I've tested against SSRS 2005 SP1 and the CTP of SP2.
I have one page, one connection, and three dropdowns. The connection looks like (as an example):<asp:SqlDataSource ID="DropDownConn" runat="server" ConnectionString="<%$ ConnectionStrings:aousConnectionString %>" SelectCommand="SELECT [Value], [Text] FROM [DropDown] WHERE (([Group] = @Group) AND ([Viewable] = @Viewable))"> <SelectParameters> <asp:Parameter Name="Group" Type="String" /> <asp:Parameter DefaultValue="True" Name="Viewable" Type="Boolean" /> </SelectParameters></asp:SqlDataSource> The DropDowns Look like this: <asp:DropDownList ID="DropDown1" runat="server"></asp:DropDownList><asp:DropDownList ID="DropDown2" runat="server"></asp:DropDownList><asp:DropDownList ID="DropDown3" runat="server"></asp:DropDownList>The C# Code I am trying is like this:DropDownConn.SelectParameters["Group"].Equals("DropDown1");DropDownConn.SelectParameters["Viewable"].Equals(true);DropDown1.DataSourceID = "DropDownConn";DropDown1.DataTextField = "Text";DropDown1.DataValueField = "Value";DropDown1.DataBind(); As an example. I can not get it done so that I don't have to create 3 dataconnections. Any help, PLEASE?
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 ?
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!
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 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
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.
Hi. I have a report which has several datasources which require a table to be populated before they read from it. i.e. The first thing that needs to happen whenever the report is run, is a call to a stored procedure which populates the table the report datasources are based off of. The SP takes several minutes to complete and MUST complete before any of the datasources fetch their data.
How can this be achieved?
I can not find anything in the Visual Studio Report Designer which allows to me to instruct Datasource B to not execute before Datasource A has completed (or any other way to call a data population SP, before the data reader SP's execute).
Just when I thought I was starting to understand SSRS just a little and then I encounter a strange (maybe not too strange) of an issue.
I have a DataSet which runs a stored procedure and it requires 10 parameters. When I run it in the dataset portion itself it runs fine however, it will not work when I choose to Preview it. I refreshed my dataset and even rebooted my machine. Can anybody shed some light on this?
