Why Is Not Working Select REPLACE(strata, '_*','**') As Abc From Abc??

May 5, 2008

why is not working

select REPLACE(strata, '_*','**') as abc from abc
??

I want to replace sign "*" and preceding

sign with "**" signs.

thank you

View 17 Replies


ADVERTISEMENT

Replace NOT Working!!!

Jun 24, 2008

hi, I'm trying to get rid of the intermediate double space from the following string:

DOC-#911585 P.O.-Box-41

so I do:

select replace('DOC-#911585 P.O.-Box-41',' ','-')
and it yields:

DOC-#911585--P.O.-Box-41

BUT

if I do:
select replace(fieldFrommyTable,' ','-') from myTable
where myaddressVal='DOC-#911585 P.O.-Box-41'

SQL Server does nothing with the double white space...
the double space remains, what am I missing?

HELP!

thank you

View 8 Replies View Related

Replace Function Not Working

Oct 19, 2005

Hi,
I posted a request here and am still working on it when I landed on this bug.


select top 10 replace(comma_separated_string,',','giveaverylongp assagehere') from table



The function works fine if the comma separated string is small or if the passage is small. It fails for long passages..

Is this a mssql bug?

View 1 Replies View Related

Replace Zeros And Nulls With 1 In Table -- Using Case, But Not Working

Jul 20, 2005

Hi folks,I'm doing calculations based on data in a table, but the data has somezeros in the field I'm dividing by. I'm trying to write a script toreplace any field with 0 or null with 1, but it's not working. HEre'swhat I've got:Update A Set A.deptcode = A.deptcode,A.type = A.Type,A.Volume = (case A.VolumeWhen Null Then 1When 0 then 1Else A.VolumeEnd)From Data_Unsorted A Join Data_Unsorted B OnA.deptcode = B.deptcode and A.type = B.TypeMy table is data_unsorted and deptcode and type are my primary keysVolume is the item I want to put 1 if null or zero, and I'd thing theabove statement would work, but it doesn't. This table has 383 rows,and it says it updates 383 rows, but when I run the following query totest:select a.deptcode, a.type, a.volumefrom data_unsorted awhere a.AveMonthVolume = 0 or a.AveMonthVOlume is nullIt didn't work... still TONS of nulls and zero's. Is there a trick tothis???Thanks,Alex.

View 2 Replies View Related

Select Replace Help Please

Jun 27, 2006

hi,

SELECT @SQL = 'select REPLACE('+@t_name+','/','-')'
Exec (@SQL)

i have a @t_name having names like this EUR/USD and i wanna replace it like this EUR-USD but above statement did not do it. Would you please help?

View 3 Replies View Related

Select Query With Replace

Jan 8, 2008

hi,i have entered records using replace functionnow i have to retrieve that records, i have replaced <'> with <`> character, how to write the query to get a record replacing again <`> with <'>  

View 2 Replies View Related

Replace In Select Statement???

Jun 13, 2004

Hi ;)

I have a select statement where i need to Replace some Chars
Maybe someone can help

SELECT * FROM reguser WHERE REPLACE(" & whereTable & ",'-','')

I want to Replace the "-" and the "/"

Thanks in advance

View 4 Replies View Related

Select &#043;replace Querry Problem..

Dec 20, 2006

Hi.every one. I have a problem when I use this querry
select replace(Title,'.','')AS Title1, ID from tbl_Sim Where Title1 LIKE '%377%'

And here is my record
Title : 0988.613.775
ID : 2

and here is the error: Invalid column name 'Title1'.
Can any one show me how to run this querry, I want to TRIM the 'Title' before it can be compared with another condition
I am a beginner. Thanks alots!

View 2 Replies View Related

Within A SELECT, How Do I Replace An Empty Or Null Value With A Value From Another Table?

Apr 20, 2008

Hello,I'm a beginner in SQL and I have been searching through the SQL Cookbook and Google but I can't seem to find an example of what I want to do. I want to create a report that will return names and emails using two of my tables. I want to use the email in my primary table in the select but if it is null or empty I want to replace it with an email from my secondary table. Below is what I would like to do but I got a syntax error with it in SQL Server 2000. SELECT MemberID As ID, MemberFirstName As FirstName, MemberLastName As LastName, (IF MemberEmail = '' THEN SELECT TOP 1 OtherEmail FROM OtherTable WHERE OtherID = MemberID) As EmailFROM PrimaryTableThanks for your time.Jason  

View 8 Replies View Related

SQL Server 2012 :: Select Query With REPLACE Function?

May 22, 2015

using below code to replace the city names, how to avoid hard coding of city names in below query and get from a table.

select id, city,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,
'JRK_Ikosium', 'Icosium'), 'JRK_Géryville', 'El_Bayadh'),'JRK_Cirta', 'Constantine'),'JRK_Rusicade', 'Philippeville'),
'JRK_Saldae', 'Bougie')))
New_city_name
from towns

View 3 Replies View Related

Select Qry: 1 Real Value To Formatted Decimal, 2. Replace NULL With String, How Do I?

Jun 14, 2005

Hi.

1.
Have a query that fetches a real value (e.g. 4.3345643)

Let say the field is called TheReal.

How can I format the value in the select statement so I get a thousand separator(s) and two decimals in the resultset.

2. In the same query I have a left join as well.
Table 2 retur (sometimes) <NULL>.
Is it possible to force this <NULL> value to be a fixed string value instead in the select statement.

View 6 Replies View Related

SQL Server 2012 :: How To Replace Multiple Values In Single Select Statement

Aug 18, 2015

how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.

DECLARE @V1 NVARCHAR(100)
SELECT @V1 = 'FirstName: @FN, LastName: @LN, Add1: @A1, Add2: @A2 '
DECLARE @T1 TABLE
(FN VARCHAR(100), LN VARCHAR(100), A1 VARCHAR(100), A2 VARCHAR(100))

[code]....

View 7 Replies View Related

Can't Get This Select Working...

Jan 26, 2006

Hello,

I have two tables, one is a "header" and the other is a "Footer" for a customer DB I wrote. The footer has 4 records for each header.

I need a field off of the footer to do a 'status count' and I can't get it to work

for example

select A.Status,count(*) from Header a inner join Footer b on a.CustomerID = b.CustomerID where b.SalesRep='Joe'

Basically..I am trying to get an output of

Complete 2
Cancelled 1
In Progress 3
Waiting 1

Etc.

The problem is the footer table has 4 records for each ... and even if I use a distinct clause... It multiplies the correct result by 4.


Select * from users where clue > 0

View 4 Replies View Related

Select TOP 1 In SP Not Working

Jan 31, 2008

I have the following select statement within a stored procedure:

DECLARE @first_date DATETIME

select @first_date = top 1 (g_collectdate)
from ESS_Coefficients es, ProgramXRef
where es.Program_Id = ProgramXRef.Program_Id
AND @In_Program = ProgramXRef.Program_Name
AND es.Scope LIKE @In_Scope
order by g_collectdate asc

The syntax check tells me "Error 156: Incorrect syntax near keyword 'top'".

I took out the @first_date = portion, substituted in values and ran the query from SQL query analyzer and it worked fine.

select top 1 (g_collectdate)
from ESS_Coefficients es, ProgramXRef
where es.Program_Id = ProgramXRef.Program_Id
AND 'ABC' = ProgramXRef.Program_Name
AND es.Scope LIKE 'abc/%'
order by g_collectdate asc

What syntax problem is causing the error?

View 4 Replies View Related

SqlDataSource.Select Command Not Working?

May 26, 2007

My compiler says that the line in bold below is illegal. The error msg I'm getting is: No overload for method 'select' takes '0' arguments. How can I correct this error and execute a SELECT?
 protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource2.Select ();
 }
 protected void SqlDataSource2_Selected(object sender, SqlDataSourceStatusEventArgs e)
{string strReadyFirstName = e.Command.Parameters["@FirstName"].Value.ToString();string strReadyLastName = e.Command.Parameters["@LastName"].Value.ToString();
}
 <asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [User_ID], [User_Name], [FirstName], [LastName], [Company_Name], [Department_Name] FROM [CompanyDepartment] WHERE ([User_Name] = @User_Name)" OnSelected="SqlDataSource2_Selected">
<selectparameters>
<asp:sessionparameter DefaultValue="TheirUserName" Name="User_Name" SessionField="TheirUserName" Type="String" />
</selectparameters>
</asp:SqlDataSource>

View 1 Replies View Related

Working On The Result Set Of Select Query

Sep 20, 2007



Hi
I have a table as follows

Table Cats
{

catergory varchar(20)
Update datetime
}


And the data in the table is as follows

Category Update
------------- --------------
cat1 d1
cat2 d2
cat3 d3

I would like to get only 'Category' in result set and work on it ( similar to foreach in C# )

select Category from Cats will return the required result , but how can i iterate thru then in T-Sql
Can any one please throw some light

Thank you
~Mohan Babu


View 1 Replies View Related

SqlDataSource.Select Is Working, But Assigning Null

Mar 28, 2008

The problem I'm having is described below all this code.---------------------------------------------------------My content page has a SqlDataSource: <asp:SqlDataSource ID="sqlGetUserInfo" runat="server"
ConnectionString="<%$ ConnectionStrings:RemoteNotes_DataConnectionString %>" SelectCommand="SELECT [UserFirstName], [UserLastName] FROM [Users] WHERE ([UserGUID] = @UserGUID)"
onselecting="sqlGetUserInfo_Selecting"> <SelectParameters> <asp:Parameter Name="UserGUID" Type="String" /> </SelectParameters> </asp:SqlDataSource> -----------------------------------------------------Inside my OnSelecting event, I have: protected void sqlGetUserInfo_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { sqlGetUserInfo.SelectParameters["UserGUID"].DefaultValue = Membership.GetUser().ProviderUserKey.ToString(); } ---------------------------------------------------------------------And, inside my Page_Load, the relevant code is: //String strUserFirstName = ((DataView)sqlGetUserInfo.Select(DataSourceSelectArguments.Empty)).Table.Rows[0]["UserFirstName"].ToString();
DataView dvUserDetails = (DataView)sqlGetUserInfo.Select(DataSourceSelectArguments.Empty);
if ((dvUserDetails != null) && (dvUserDetails.Count > 0)) { DataRow drUserInfo = dvUserDetails.Table.Rows[0]; lblHelloMessage.Text = "Hello, " + drUserInfo["UserFirstName"].ToString() + ((drUserInfo["UserLastName"].ToString() == "") ? "" : " " + drUserInfo["UserLastName"].ToString()); }  ---------------------Now, here's the problem. My "if" statement is never executing because "dvUserDetails" is null. However, when I break the execution and put awatch on the actual Select() statement, it shows a DataView with the correct return rows! You can see the commented line where I tried tobypass the DataView thing (just as a test), but I get an object reference is null error.The weird thing is that it was working fine one minute, then started getting "funky" (working, then not, then working, then not), and now it just doesn't work at all. All thiswithout me changing one bit of my code because I was checking out some UI flow and stopping and restarting the application. I've tracked downthe temporary files directory the localhost web server runs from, deleted all those files, and cleaned my solution. I've even triedrebooting, and nothing seems to make it work again.My relevant specs are VS2008 9.0.21022.8 RTM on Vista Enterprise x64. 

View 3 Replies View Related

Select Employees By Start Day For 8 Month Working

Jul 15, 2013

I have this column Sdate in my Employees table. This value represent the start date working.

I need to get all of the employees that between 8 and 10 month of work from today. How can I do it?

View 3 Replies View Related

Msg 1013 Select Not Working -- Correlation Names?

Apr 7, 2006

I am trying to run a select query and getting the following error:
Server: Msg 1013, Level 15, State 1, Line 12
Tables or functions 'PatientVisit' and 'PatientVisit' have the same exposed names. Use correlation names to distinguish them.

Not sure what I am doing wrong! Here is the query. Can anyone point me int the right direction?

SELECT Batch.Entry AS [Date Of Entry], PatientProfile.[Last] + ', ' + PatientProfile.[First] AS Name,
MedLists.Description AS [Adjustment Type], PatientVisit.TicketNumber, PatientVisitProcs.Code AS [Procedure Code],
TransactionDistributions.Amount AS [Adjustment Amount], PatientVisitProcs.DateOfServiceFrom,
ISNULL(CONVERT(varchar(255), Transactions.Note), ' ') AS Notes,
DoctorFacility.DotID, DoctorFacility.ListName as DrName, PatientVisit.DoctorID as DrID
FROM PaymentMethod
INNER JOIN VisitTransactions ON PaymentMethod.PaymentMethodId = VisitTransactions.PaymentMethodId
INNER JOIN Transactions ON VisitTransactions.VisitTransactionsId = Transactions.VisitTransactionsId
INNER JOIN TransactionDistributions ON Transactions.TransactionsId = TransactionDistributions.TransactionsId
INNER JOIN Batch ON PaymentMethod.BatchId = Batch.BatchId
INNER JOIN PatientVisit ON VisitTransactions.PatientVisitid = PatientVisit.PatientVisitId
Inner Join PatientVisit ON DoctorFacility.DoctorFacilityID = PatientVisit.DoctorID
INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId
INNER JOIN MedLists ON Transactions.ActionTypeMId = MedLists.MedListsId
INNER JOIN PatientVisitProcs ON TransactionDistributions.PatientVisitProcsId = PatientVisitProcs.PatientVisitProcsId

Any Idea on how to fix it up? Thanks

View 1 Replies View Related

Nested Select Statements Not Working The Way I Was Hoping...

Apr 18, 2008

So I have another query I can't seem to function the way I was hoping. I've learned a lot in this past month or so but I've hit another challenge. Anyways what I'm trying to do is when a user/student wants to add a new major I want to show a list of majors that are NOT already in his/her profile. So basically if I had a full list of majors:
Accounting
Computer Science
Mathematics

and the user already had Mathematics in his/her profile I'd like it to display only:

Accounting
Mathematics

Below is the layout of the tables, my attempt at the query, and then below that some example data.





Code Snippet

USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Majors] Script Date: 04/17/2008 22:38:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Majors](
[MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Majors] PRIMARY KEY CLUSTERED
(
[MajorID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MajorDisciplines](
[MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL,
[DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED
(
[MajorDisciplineID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID])
REFERENCES [dbo].[Degree] ([DegreeID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID]
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID])
REFERENCES [dbo].[Majors] ([MajorID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]

USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MajorDisciplines](
[MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL,
[DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED
(
[MajorDisciplineID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID])
REFERENCES [dbo].[Degree] ([DegreeID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID]
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID])
REFERENCES [dbo].[Majors] ([MajorID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]

SELECT MajorID
FROM Majors majs
WHERE majs.MajorID NOT IN (SELECT majDis.MajorID FROM MajorDisciplines majDis WHERE majDis.MajorDisciplineID NOT IN (SELECT sMajors.MajorDisciplineID FROM Student_Majors sMajors WHERE sMajors.StudentID = 0))
dbo.Majors
MajorID
Accounting
Computer Science
Mathematics

dbo.MajorDisciplines
MajorDisciplinesID ... MajorID ...
1 Computer Science
2 Accounting
3 Accounting
4 Mathematics

dbo.Student_Majors
MajorDisciplineID StudentID
1 0

Oh also for the MajorIDs I don't want it to return duplicates such as the Accounting in MajorDisciplines...which I was hoping my query wouldn't do but it returns absolutely random data...

Hope someone can help!

View 5 Replies View Related

Why SELECT AVG(column) From Table Is Not Working In Jet Database?

Feb 10, 2007

Hello,

XPSP2
VB 2005 Express
Jet/Access database

One of the Forms of these application display a Jet database table. Why querries Min, Max, Avg do not work?

Thanks,

View 8 Replies View Related

My Update Statement Isn't Working But Select And Insert Are. What's Wrong?

Aug 11, 2007



here is my code:


Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())

cn.Open()

Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()

adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products
set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" &
textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)

cn.Close()

Response.Redirect("database.aspx")

it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?

View 5 Replies View Related

SELECT @@identity Working In SQL Server Compact Edition

Jun 5, 2007

Does SELECT @@identity works with in SQL Server compact edition? I have tried in SQL management sql query. It does work however when i use in VS.NET 2005 / Orcas beta1 , the generated tableAdapter does not recongize the syntax. So any workaround with it ? or any alternative that i can return the new inserted ID in my application.







Thanks.



View 1 Replies View Related

Stored Procedure Version Of SELECT Statement - Can't Understand Why It's Not Working

Apr 25, 2006

Hi,
[I'm using VWD Express (ASP.NET 2.0)]
Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:

<asp:DropDownList ID="ddlContacts" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"

DataTextField="ContactNameNumber" DataValueField="ContactID" Font-Names="Tahoma"
Font-Size="10pt" OnDataBound="ddlContacts_DataBound" OnSelectedIndexChanged="ddlContacts_SelectedIndexChanged" Width="218px">
</asp:DropDownList>&nbsp;<asp:Button ID="btnImport" runat="server" Text="Import" />
<asp:Button ID="Button2" runat="server" OnClick="btnNewAccount_Click" Text="New" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ICLConnectionString %>"
SelectCommand="SELECT Contacts.ContactID, Contacts.ContactLastName + ', ' + Contacts.ContactFirstName + ' - ' + Contacts.ContactNumber AS ContactNameNumber FROM Contacts INNER JOIN AccountContactLink ON Contacts.ContactID = AccountContactLink.ContactID WHERE (AccountContactLink.AccountID = @AccountID) ORDER BY ContactNameNumber">
<SelectParameters>
<asp:ControlParameter ControlID="ddlAccounts" Name="AccountID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>

View 3 Replies View Related

Transact SQL :: Select Statement Not Working In User Defined Function

Nov 3, 2015

The select statement:

SELECT DATEDIFF(n , LAG(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),
       CAST(Date AS DATETIME) + CAST(Time AS DATETIME))
   FROM [DataGapTest]

Gives the right output:

NULL
1
1
3548
0

However, when I put the statement in a function, I get only zeros as the output. It's as if the lag and current value are always the same (but they are not of course).

CREATE FUNCTION dbo.GetTimeInterval(@DATE date, @TIME time)
RETURNS INT
AS
  BEGIN
 DECLARE @timeInterval INT
   SELECT @timeInterval = DATEDIFF(n , LAG(CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ),
       CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME))
   FROM dbo.[DataGapTest]
  RETURN @timeInterval
  END

View 5 Replies View Related

How To Select Value From Table B To Replace Value In Table A

May 29, 2001

I have an automatic data collection system. The large majority of the time, all data is electronically gathered. On occasion, the need arises to substitute a manually entered value into the data when a valuable piece of information cannot be electronically secured. When a piece of data is open to substitution, it arrives with a value of -9999 (a value not naturally occuring) in Table A. I want to be able to use a trigger on table A to get its substitution value from a corresponding column (Same name) in Table B. Tables A & B will have an identical schema. A is raw data, B is the manually updated table. Any value in the raw table with the content of -9999 gets its replacement from Table B.


I'm open to any ideas!!

RC =:((

View 1 Replies View Related

Some Things Not Working In 2005 And Working In 2000

Mar 3, 2006

hi

I had a view in which I did something like this
isnull(fld,val) as 'alias'

when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.

Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???

any clues or answers ?? it is some configuration issue ?

Thanks in advance.

View 5 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

Select Convert(varchar(16), Getdate(), 101)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4)

Sep 26, 2007



select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

From above query I get

mmddyyyyhhmm

but it' s yyyy and hour can not be separated

04/12/200702:05

How can I separated the year and hour ?

Thanks
Daniel

View 2 Replies View Related

ExecuteNonQuery - Add Working/Update Not Working

Jan 7, 2004

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:


Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

Catch ex As Exception
Label1.Text = ex.Message
End Try


When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...

View 2 Replies View Related

SQL Replace '-' By ''

Oct 16, 2007

Hi all i have a question regarding sql, i want to replace some characters...
 
any knows simply how to do this?
 
I want to replace "999-25000-69" by "9992500069"
 
grtz

View 1 Replies View Related

Replace?

Nov 28, 2006

i got a 100k rows column contain first name + last name. but half of them are got comma between first and last name. how can i update and remove all the comma. can anyone provide a statment please thanks so much

View 2 Replies View Related

Replace

Aug 15, 2006

can I use a replace on text type field?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved