i have creat some code to get data from Database like:
Dim strNewsID As Integer = Request.QueryString("ID")Dim Newsconn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("SiteSQLServer").ToString())
Dim Newscmd As New System.Data.SqlClient.SqlCommand("SELECT FROM News (NewsGroupID, NewsTitle, News, NewsPosition) WHERE NewsID=strNewsID", Newsconn)
Now i wanna get Data in Values
Newsconn.Open()
Dim NewsGroupID As Integer = ?????
Dim NewsTitle As String = ?????
Dim News As String = ?????
Dim NewsPosition As Integer = ?????Please let me know how?
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
I have 4 Tablix and 2 of the Tablix get data from Server 1 and other 2 get the data from Server 2.I have set NoRowsMessage "=Data Not Available for the Selected Values" for all the 4 Tablix.Now if data is not available from Server 1 then I must show "Data Not Available for the Selected Values" only once in the outputbut now its appearing twice in the output because of the 2 tablix that had no rows.Similarly if data not available from Server 2 then it should show "Data Not Available for the Selected Values" only once in my output.If Data not avilable from all the Tablix then also i t should show only once as "Data Not Available for the Selected Values" in the report output.
Hello all...I am trying to validate that the new work date that is being imported from the text file does not exist in the table. So in other words I do not want duplicate data. This is what I haveSqlDataReader dr = new SqlDataReader(); SqlParameter sp = new SqlParameter("@WorkDate", Data.SqlDbType.datetime, 8, Data.ParameterDirection.Input); if (dr.HasRows) sp = "@WorkDate"; else *How does that look? Am I in the ball part…
i will tell whats it ------ i have a webpage creating using ASP.NET,C# using SQL server 2000.
i have a table in the database which has attribute named "Priority" which has values like "High" "Low" "Medium". all i wana to make is that i enter any of this value tru a dropdown list into the database and i have used Datalist in Webpage so that i will retrieve back the info from the database. when any request is made to retrieve back this value . i want to see all the priority field in the datalist which has priority as "High" in bold and in order. is this possible ,if it is plz let me know
Ok what i am looking to do i cannot figure out. What i want to do is have a simple script that when a user logs onto the website (via windows auth) to get there username somehow llike with Request.ServerVariables("LOGON_USER") that should display there username either "domain/name" or "username" and then insert that into the UserLogs Table under my database with the date with the GETDATE() command.. But when i do this i cannot get the page to auto submit the values. Actually i cannot get anything to write to the DB unless i am doing it under the query builder. Here is my Query that i was using. INSERT INTO UserLogs([User], Date) VALUES (@UserName, GETDATE()) then in the Code of the page i have <%@ Page Language="VB" AutoEventWireup="false" CodeFile="SubmitForm.aspx.vb" Inherits="Template_SubmitForm" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"><title>Untitled Page</title> </head> <body><% Dim name name = Request.ServerVariables("LOGON_USER")%> <form id="form1" runat="server"> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:REPCOMConnectionString %>" InsertCommand="INSERT INTO UserLogs([User], Date) VALUES (@name, GETDATE())" SelectCommand="SELECT [User], Date FROM UserLogs" CancelSelectOnNullParameter="False"> <InsertParameters> <asp:SessionParameter DefaultValue="test1234" Name="name" SessionField="name" /> </InsertParameters> </asp:SqlDataSource> </form> </body> </html>
Hi,I have a message system, where people insert their message to the database, and others pick up their messages by selecting messages that have their username as the reciever ID.I have just intoduced bulk message sending - sending to multiple users. This requires many usernames being entered into the recievername column, seperated by a comma.Is there a way to select messages if the column name contains their username, rather than is their username? Because there may be more than one name, I would like everyone that has their name in the column to be able to retrieve the message, even if they arent the only reciever..Please ask if more explanation is needed!Thanks,Jon
I'm built a trace in SQL Profiler and I have selected columns "DatabaseName" and "Filename" (among others) to be displayed. When I run the trace, the columns are displayed but the values are blank. The SQLServer 2K version is 8.0.760. Is this a bug or am I missing something?
I have 2 databases( "A" + "B") with identical number of tables and identical number of records for each table. There are also identical number of fields pre record per table. Table A has had the sensitive data within the fields scrambled.
I need to know if there is a way to read down each DB table by table, record by record, field by field and compare the data values. If they are different I need to output the Field name, the data value and the Table name from the Scrambled Table (lets say its "A").
I am querying several tables and piping the output to an Excel spreadsheet. Several (not all) columns contain repeating data that I'd prefer not to include on the output. I only want the first row in the set to have that data. Is there a way in the query to do this under SQL 2005?
As an example, my query results are as follows (soory if it does not show correctly): OWNERBARN ROUTE DESCVEHDIST CASE BARBAR TRACKING #70328VEH 32832869.941393 BARBAR TRACKING #70328VEH 32832869.941393 BARBAR TRACKING #70328VEH 32832869.941393 DAXDAX TRACKING #9398VEH 39839834.942471 DAXDAX TRACKING #9398VEH 39839834.942471 DAXDAX TRACKING #9398VEH 39839834.942471 TAXTAX TRACKING #2407 40754.391002 TAXTAX TRACKING #2407 40754.391002 TAXTAX TRACKING #2407 40754.391002
I only want the output to be: OWNERBARN ROUTE DESCVEHDIST CASE BARBAR TRACKING #70328VEH 32832869.941393
i'm new to this and i am trying to create a statement that would add the values together for Hong Kong and China and then delete the Hong Kong road. (this is a example what i am trying to acheive)
Hi Fellows I am trying to organize these information.the data come form two tables that are not relating, but I did a join and my primary key is the filed polygon. I have a list of points(geomseq) for each polygon but the number of points(geomseq) can change. I have this inofrmation in a data base.
can anyone please help on a sql statement that would sort out three kinds of data. let's say i have a column that contains a status flag. there are only 2 possible values, add and delete. now, i want to sort out three kinds of data: add_only, delete_only, and update. Update is the data that contains duplicate records in the table. Which means, there are two rows having same "student_number" but two different status flags. This happens when there's an update or change in data, such as new address or new phone number. The old row/data is sent with status flag of delete, while the updated data is sent with status flag of add. Example below. An output table with a new column of tag is created.
reports table
STUDENT_NO NAME ADDRESS PHONE STATUS FLAG
1 McKenzie Ohio 9111111 add
2 Hario New Jersey 1234567 add
3 Oda Japan 9876543 delete
4 Davis Chicago 1112222 add
5 Rodriguez Detroit 4445555 add
1 McKenzie Ohio 7778888 delete
4 Davis Detroit 1112222 delete
create, remove, update table
STUDENT_NO NAME ADDRESS PHONE STATUS FLAG TAG
2 Hario New Jersey 1234567 add create
5 Rodriguez Detroit 4445555 add create
3 Oda Japan 9876543 delete remove
1 McKenzie Ohio 9111111 add update
4 Davis Chicago 1112222 add update
Anyone knows what SQL statements to use to have this output? Thanks.
How do I access sqldatasource data values in code behind and bind them to strings or texboxes etc. as oposed to using Eval in the markup? I can create a new database connection, but I would like to use the data values from the autogenerated sqldatasource control Many thanks,
Hello There: I am running a data flow within a ForEach loop wherein I am computing a value called QuotaGap. When it is 0 I do not want any further execution of the loop. I am using a Conditional Transform within this dataflow that writes a record to a table only when the QuotaGap is NOT 0. However, I am unable to terminate the execution of the loop as I am still within the dataflow.
Now, the computation of the gap requires a value from another variable called NetPurchases. I tried using an ExecuteSQL task in the control flow but could not figure out how to pass the value of the variable NetPurchases into the select statement to compute the gap. For example, the select statement would read:
select (QuotaUpperLimit - ?) As QuotaGap from <<tablename>>
I tried setting the parameter as an input as well as an output and it did not work.
Then I tried passing the entire SQL as a string within a variable. This does not work either because in order to compute the math QuotaUpperLimit - NetPurchases, both variables need to be integers but then you cannot concatenate integres together, which is what we need to do to create the SQL.
The other reason I am going through these hoops I guess is that I have not figured out a way to set the value of a variable within a data flow. I compute the value for QuotaGap within the dataflow in a ForEach loop but I have no way to pass this result to a variable called QuotaGap without using an ExecuteSQL task or another ForEach Loop.
I have spent hours on this simple issue and so have given up and looking to the good friends in this forum for help.
If what I have stated is not clear please let me know and I will try to clarify things a bit.
Hi, Ive got a report using a List item that is vertically displaying the columns from a table. The problem I run into, is that some of the fields in this table contain large blocks of text where the users have entered comments and such.
I am using Textboxes to display this data.
So my report will look something like ----- Field label 1 Field value 1 Field label 2 Field value 2 Field label 3
<white space>
<page break>
Field value 3 ---> this is a big block of text Field label 4 Field value 4 etc ------ It appears as though the report attempts to keep the contents of each textbox together even if that means breaking onto an entirely new page to do this. I would prefer for the data to flow more natrually instead where the page breaks in the middle of the data being displayed should it be too large to fit on the page it started on.
----- Field label 1 Field value 1 Field label 2 Field value 2 Field label 3 Field value 3 --- As much as can fit on this page
<page break>
Field value 3 ---> remaining data that broke over the page Field label 4 Field value 4 etc ------
I am trying to find what datatype I can use for variable values like below in a column
E.g. column which we get
10000.10 100 180.34 98203710231.34
From the above example, you can see some of the values contains no decimal and with decimal
Also we cant say whether the decimal comes after the 5th number or 10th number. Any other datatype to capture this values. If not last option is to give varchar2.
I'm trying to query an XML column in SQL. The problem I am having is that when multiple values exist it retuns them all in one field. I'd like to insert a comma between values.
I need to populate some SqlParameter but I temporarily need to populate with a default value for the data type in question. Of course I can work them out, but I was wondering if there is already a way of doing this, from the .Net Classes or from SQL Server but without making a trip to the DB.
I am new in MSSQL server. I need export users IP address from SCCM. I have a CSV file with lot of IP address. I need import those IP address and export same IP address and users samaccountname.
the error message I get is {"Object reference not set to an instance of an object."} and it points to < Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text > this is my code": Protected Sub TickMastBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TickMastBtn.Click REM Collect variablesDim Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text Dim Comp As String = CType(FindControl("CoTextbx"), TextBox).TextDim Exch As String = CType(FindControl("ExchTextbx"), TextBox).Text REM Create connection and command objectsDim cn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataVTRADE.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")Dim cmd As New SqlCommand cmd.Connection = cn REM Build our parameterized insert statementDim sql As New StringBuilder sql.Append("INSERT INTO TickerMaster ")sql.Append("(Ticker,Company,Exchange,) ")sql.Append("VALUES (@Tickr,@Comp,@Exch,)") cmd.CommandText = sql.ToString REM Add parameter values to command REM Parameters used to protect DB from SQL injection attacksWith cmd.Parameters .Add("Tickr", SqlDbType.Int).Value = Tickr.Add("Comp", SqlDbType.VarChar).Value = Comp .Add("Exch", SqlDbType.VarChar).Value = Exch End With REM Now execute the statement cn.Open() cmd.ExecuteNonQuery() cn.Close() End Sub
I am creating a scheduling web application. I have managed to insert data into the database. The code is as follow:
Dim insertSQLDatasource As New SqlDataSource() insertSQLDatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString
Try insertSQLDatasource.Insert() Catch ex As Exception Panel1.Visible = True InsertMsgLabel.Text = ex.Message.ToString Finally insertSQLDatasource = Nothing End Try
Now I am trying to select the values for a particular event. For example, if the user selects an event with id 40, the values that corresponds to that row will be read and filled into the respective controls. I tried the following code, but doesn't work.
Dim sqlConn As SqlConnection = New SqlConnection("ScheduleConnectionString") Dim com As SqlCommand = New SqlCommand("SELECT * FROM Demo_Theatre_DB WHERE ID=@ID", sqlConn) sqlConn.Open() Dim r As SqlDataReader = com.ExecuteReader() While r.Read() StartTimeTextBox.Text = r("StartTime") EndTimeTextBox.Text = r("EndTime") CompanyNameTextBox.Text = r("CompanyName") PurposeTextBox.Text = r("Purpose") AccountManagerTextBox.Text = r("AccountManager") PresenterTextBox.Text = r("Presenter") ColorDDL.SelectedValue = r("ColorCode") StatusRadioButtonList.SelectedValue = r("Status") CommentTextBox.Text = r("Comments") End While
I have a table 'wRelated' with the following columns
[related_id] [int] [channel_id] [int] [mui] [varchar] [price_group_id] [type_id] [int] [related_mui] [varchar] (100) [date_started] [smalldatetime] [date_ended] [smalldatetime] [date_entered] [datetime] [deleted] [tinyint], [rank] [int] data in column [mui] is repeated as the table has more than one entries for the same [mui], The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui] The recordset expected should be something like this.
I am using the below script and I am getting data for 15 minutes interval. I would like to aggregate this data to hourly so instead of reading for 2014-01-01 00:15:00.000 and 2014-01-01 00:30:00.000 I want all the data aggregated for 2014-01-01 00:00:00.000 and then for 2 o’clock. how should I tweak this query to sum the interval values and display it?
SELECT r.MeterId, r.ReadingDate, r.Reading FROM MeterReading r, MeterDetail d, Building b where r.MeterId = d.MeterId and d.BuildingId = b.BuildingId and b.BuildingName like '%182%' and r.ReadingDate between '2014-01-01'and '2014-01-10' order by r.MeterId
--CREATE TEMP TABLE IF OBJECT_ID('tempdb..#TEMP_WK_STOCK') IS NOT NULL DROP TABLE #TEMP_WK_STOCK CREATE TABLE [#TEMP_WK_STOCK]( [WMNUMBER] [int] NOT NULL, [STOCKID] [int] NOT NULL)
[Code] ...
returns just 2 rows and misses the fact that the wmNumbers stop and start a few diffrent times. I cant see how to set up the query to bring back the 6 rows i would be expecting it this case, without going to a cursor which i really don't want to.
I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters: @subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:
FRRIJ FRRIC FRMM ROBECO FRJV MAIL FRUKV FRICE
Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?
@subaccount VARCHAR(8) = NULL , @numDaysCutoff INT = 1 , @numDaysPcts INT = 1
SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount) , SecurityID = ISNULL(h.security_id, lo.security_id) , SecurityName = s.name , QtyHeldAndPending = ISNULL(h.quantity, 0) + (CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1) WHEN lo.type = 2 THEN lo.resulting_quantity ELSE 0 END ) , L.AverageDailyVolume , XDaysVol = L.AverageDailyVolume * @numDaysPcts , CutoffVol = L.AverageDailyVolume * @numDaysCutoff , DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0) , HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100 , TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd , CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd , TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100 , DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * s.price_usd), 0) , NDaysCutoff = @numDaysCutoff , NDaysPcts = @numDaysPcts FROM subaccount_positions_table h --vGlobalHoldings h JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id) LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99 WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount) OR lo.subaccount = ISNULL(@subaccount, h.subaccount) ) AND status = 1 AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol OR -- Targeted Vol exceeds cutoff ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) > ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol ) ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)
If a button is never clicked in a given month, it never gets logged to the table. In this example, the 5th button was not clicked during the month of December, so it does not appear in the results. I want to modify my query so it displays the name of the button and a zero (in this case "5th Button 0") in the results for any buttons that were not clicked. For some reason I am drawing a blank on how to do this. Thanks in advance.
We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).
When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.
We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.
I'm trying to import some data from a spreadsheet to a database table from a package in integration services. The problem is that I see the data when I open the excel file but when I try to run my package , it doesn't insert any rows in the table and it finishes with a success status.
My excel file has some formulas to get the data from other worksheets. I added a Data Viewer and all I see is null values in every cell.
I have a checkbox on my webform that is bound to a bit field in my SQL table. I'm fine as long as I've got the bit field set to 0 or 1, but if the field is NULL, the checkbox throws an exception during the databind.
Is there any way to handle this without removing the data binding and manually setting the value (ie: some way to intercept it before the exception gets thrown and then setting the field value in the dataset)?
If I have a column in one table (contracts) that contains a set of values (codes that identify the fields contract code) like as follows...
Code:
Contracts table -------------------------------- Concode - description -------------------------------- KIDD - Kidderminster General UNIV - University Hospitals
and then another table (controls) which has a column called contracts where the data within the 'Contracts' field is set out like the following (yes that is right, each of the data in this column are the Foreign Keys which are separated by '/' in which I need to query against the contracts table ) ...
With the those tables and the columns like they are, how can I get my query to display the following information...
PHP Code:
--------------------------------------------------- Con(Controls table) - Description(contracts table) --------------------------------------------------- BA - Kidderminster General BA - University Hospitals ---------------------------------------------------
I'm guessing my first job is to somehow extract those Foreign Keys from the 'Contracts' column in the 'Control' table
