ISNUMERIC() In LIKE Statement

Jan 10, 2007

Hi

Here's the problem:

I need to search a postcode database by the first one or two letters.

Problems occur for example when i want to search north London postcodes (N) when using:

postcode LIKE @postcode + '%'

As this picks up everything beginning with N, eg, NG for Nottingham, or NE for Newcastle. So i need a like statement which searches for the first one or two digits followed by a number!

I've found the ISNUMERIC() function but not sure what the best way to use it with the like statement - or even if there is a better way altogether - can you use regular expressions in MSSQL?

thanks

View 4 Replies


ADVERTISEMENT

Using IsNumeric Within A Case Statement (within An Insert)

Mar 25, 2003

Hi - Please excuse me if this is really simple, but I'm fairly new to this lark.

My (made up) code is below... I'd be grateful for any pointers.

insert into [tblInvoices]
(full_period,
supplier_no,
account_code,
tran_amount,
function)
select
full_period,
supplier_no,
account_code,
tran_amount
case
when substring(account_code,1,2) = 'FY' then '-'
when isNumeric(account_code) then left(account_code, 2)
when not isNumeric(substring(account_code,1,2)) then left(account_code, 1)
else 'oops'
end as function,
from
tblLoadMSV900_i
end

Is this even close?

I'm using a stored proc to insert the data from tblLoadMSV900_i into tblInvoices and at the same time insert some data into the function field.

In plain english I want make sure that:
If the first 2 chars of account_code are 'FY' then function='-',
If the first char of account_code is numeric then function=left(account_code, 2),
If the the first char is not numeric (and if first two chars are not 'FY' i.e. first char could be 'F') then function=left(account_code, 1)

And there's plenty more where this came from! But if I can crack this with your help then I should have a better idea about the rest of the proc.

Thanks
Sara

View 2 Replies View Related

Bug In ISNUMERIC ?

Apr 13, 2004

strange thing I just ran into, not sure if this is a bug or what ... but pretty annoying.
In MS SQLServer 2000 :

SELECT (ISNUMERIC('0E010101'))

returns "1"

but

SELECT CAST ('0E010101' AS numeric)

returns "Error converting data type varchar to numeric"

any idea?

View 14 Replies View Related

IsNumeric() - It Can Not Be Right?

Mar 27, 2006

hi,

i am migrating data from a legacy system with a not nice front-end.
as a result, i have all sorts of garbage stored on the tables.

i
am trying to convert values from varchar(12) to float, but i have an
error during selecting data that says that data can not be converted
eventhough i am using the ISNUMERIC() function to check.

case when
isNumeric( myCol01 ) = 0 then null
else
convert( float , myCol01 )
end

but my error occours when ISNUMERIC() encounters the value  '.              ' ; that is a dot with spaces after it.

try the expression below;

SELECT

  myValue = '.              '
, is_numeric = ISNUMERIC('.              ')
, converted = CONVERT( FLOAT , '.              ')



has anyone got any idea how to work around this?

nicolas

View 5 Replies View Related

Sum And IsNumeric

Feb 27, 2008

Hi,

I have a table described as follows:

TableA
GrpDate DateTime;
grpMiscError varchar(1);


Ex Data:

2/1/2008 1
2/1/2008 1
2/1/2008 0
2/1/2008 x
2/1/2008 0

The grpMiscError can contain 0, 1 or x only. I need to sum up this column for all the zeros by a particular date.
I have the following but doesn't work:
SELECT
SUM(CASE ISNUMERIC(grpMiscError) WHEN 0 THEN 1 ELSE 0 END)AS MiscError
FROM TableA
WHERE GrpDate = '2/1/2008'

I get back an answer of 1 MiscError instead of 2
What am I doing wrong here?

Thanks,
J

View 6 Replies View Related

IsNumeric Does Not Appear To Work

Feb 5, 2001

Hi all,
I have never seen this problem in SQL Server 7. The isnumeric() returns true for non-numeric data, as show in the following example:

declare @x as varchar(5)

select @x = '00d01'
select isnumeric(@x)

I would expect the isnumeric() function to return false. Can anyone give a reason why this should occur.

Does SQL Server think that this is a hex value and is performing an implicit conversion? If so how horrible...

Nick

View 2 Replies View Related

Isnumeric Function

Mar 4, 2002

The isnumeric function returns 1 in examples where it should not:

select isnumeric('1D9') returns 1.

Can anyone explain this?

View 1 Replies View Related

Isnumeric In SQL SERVER 7

Feb 23, 2005

Hello,

I would like to perform a delete statement on one of my tables.

I have a code column that has data in the form 011-234-12
and at some point in this column the data is like R0 or D1

I want to delete the rows where the code data is R0 or D1, E3, etc...

I would like to know how to create an SQL Command for Sql Server 7 that would delete from Soumission_detail where:

first left char is not Numeric in the code column.

Thanks.

View 4 Replies View Related

ISNUMERIC Is Sneaky

May 18, 2004

sneaky, sneaky, sneaky
ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.

thanks, but which one??

numeric as far as float is concerned, is not the same thing as numeric as far as money is concerned

create table isnumerics
( id integer not null identity
, txtfld varchar(11)
)

insert into isnumerics (txtfld) values ( '1' )
insert into isnumerics (txtfld) values ( '937' )
insert into isnumerics (txtfld) values ( '937.0' )
insert into isnumerics (txtfld) values ( '$937' )
insert into isnumerics (txtfld) values ( '$937.00' )
insert into isnumerics (txtfld) values ( 'free' )
insert into isnumerics (txtfld) values ( '.50' )
insert into isnumerics (txtfld) values ( '1,000' )
insert into isnumerics (txtfld) values ( '' )

select id
, txtfld
, isnumeric(txtfld)
from isnumerics

111
29371
3937.01
4$9371
5$937.001
6free0
7.501
81,0001
90


select id
, txtfld
, isnumeric(txtfld)
, case when isnumeric(txtfld) = 1
then cast(txtfld as money)
else cast(null as money)
end as case1
from isnumerics

1111.0000
29371937.0000
3937.01937.0000
4$9371937.0000
5$937.001937.0000
6free0
7.501.5000
81,00011000.0000
90

select id
, txtfld
, isnumeric(txtfld)
, case isnumeric(txtfld)
when 1
then cast(txtfld as money)
else cast(null as money)
end as case2
from isnumerics

1111.0000
29371937.0000
3937.01937.0000
4$9371937.0000
5$937.001937.0000
6free0
7.501.5000
81,00011000.0000
90

select id
, txtfld
, isnumeric(txtfld)
, case isnumeric(txtfld)
when 1
then cast(txtfld as float)
else cast(null as float)
end as case2
from isnumerics


1111.0
29371937.0
3937.01937.0

6free0
7.5010.5
the others got "Error converting data type varchar to float"

no, there wasn't a question here, but yes, i'd love to hear your comments

View 6 Replies View Related

Isnumeric Issue

Aug 28, 2007

Hi,

I'm casting a varchar field to a decimal field using the format

CASE ISNUMERIC(GrossMktCapGbp)
WHEN 1 THEN CONVERT(DECIMAL(18,6),GrossMktCapGbp)
ELSE NULL
end

Thinking this would ensure that any spurious rows got set to null.

However I had a problem with some values that were set to '.', it seems that isnumeric thinks these are numbers but casting them to decimal produces an error.

SELECT ISNUMERIC('.')
SELECT CAST('.' AS DECIMAL(18,6))

Should I have been doing something different in my check possibly.




Sean

View 10 Replies View Related

Iif And IsNumeric Functions

Nov 29, 2007

=iif(

IsNumeric(Fields!Accreditation.Value),

Int(Fields!Accreditation.Value),

Fields!Accreditation.Value

)

The above expression seems to work fine if Fields!Accreditation.Value is a number. However, if Fields!Accreditation.Value is not a number, it gives an #Error. Why is the true part evaluated when the expression is false?

View 6 Replies View Related

IsInteger - Replacement For IsNumeric

Dec 13, 2005

Been meaning to post this for a while. It does a very limited job of only allowing [0-9], but could be extended to allow negative numbers, or numeric values that are suitable for numeric types other than INT, but avoiding the pitfalls of IsNumeric() which might allow through data not suitable for some of the numeric datatypes

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_IsINT]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.kk_fn_UTIL_IsINT
GO
CREATE FUNCTION dbo.kk_fn_UTIL_IsINT
(
-- String to be tested - Must only contain [0-9], any spaces are trimmed
@strINTvarchar(8000)
)
RETURNS int-- NULL = Bad INT encountered, else cleanedup INT returned
/* WITH ENCRYPTION */
AS
/*
* kk_fn_UTIL_IsINTCheck that a String is a valid INT
*SELECT dbo.kk_fn_UTIL_IsINT(MyINTColumn)
*IF dbo.kk_fn_UTIL_IsINT(MyINTColumn) IS NULL ... Bad INT
*
* Returns:
*
*int valueValid integer
*NULLBad parameter passed
*
* HISTORY:
*
* 30-Sep-2005 Started
*/
BEGIN

DECLARE@intValueint

SELECT@strINT = LTRIM(RTRIM(@strINT)),
@intValue = CASE WHEN @strINT NOT LIKE '%[^0-9]%'
THEN CONVERT(int, @strINT)
ELSE NULL
END
RETURN @intValue

/** TEST RIG

SELECTdbo.kk_fn_UTIL_IsINT('123'),IsNumeric('123')
SELECTdbo.kk_fn_UTIL_IsINT(' 123 '),IsNumeric(' 123 ')
SELECTdbo.kk_fn_UTIL_IsINT('123.'),IsNumeric('123.')
SELECTdbo.kk_fn_UTIL_IsINT('123e2'),IsNumeric('123e2')
SELECTdbo.kk_fn_UTIL_IsINT('XYZ'),IsNumeric('XYZ')
SELECTdbo.kk_fn_UTIL_IsINT('-123'),IsNumeric('-123')
SELECTdbo.kk_fn_UTIL_IsINT('-'),IsNumeric('-')

**/
--==================== kk_fn_UTIL_IsINT ====================--
END
GO

Kristen

View 15 Replies View Related

ISNUMERIC In Derived Column

Feb 14, 2008

I would like to validate datatype using Derived Column.My data type are such as numeric(X,X),datetime,int, and varchar.How do I do this using Derived Column.Example if row does not qualify as ISNUMERIC()...throw it in ERROR table else send it to SUCCESS table.Any Idea ?

View 4 Replies View Related

Testing For IsNumeric In SSIS

Dec 18, 2007

Hello,
I have searched the forum, and have discovered that the DTS method using IsNumeric to check for numierc values (ActiveX) is not valid in SSIS. Most of what I have seen prescribes using the script component to handle this.

So formerly, I checked to see if a column was numeric. If it was, then I needed to use the numeric value as is, or in some cases, I needed to perform a calculation on the value and use the result. If the value was not numeric, then whatever the value was needed to be changed to zero.

Here is an example of how I would use the current value, or set the value to zero:

If IsNumeric(DTSSource("Col003")) Then DTSDestination("ADepTrnx") = CLng(DTSSource("Col003")) Else DTSDestination("ADepTrnx") = 0 End If

This is an example of how I would use the current value in a calculation, or set the value to zero:

If IsNumeric(DTSSource("Col012")) Then DTSDestination("AlliStdFee") = CLng(DTSSource("Col012"))/100 Else DTSDestination("AlliStdFee") = 0 End If

Does anyone have an example of how I would handle both situations in a script component?

Thank you for your help!

cdun2

View 3 Replies View Related

SQL 2005: ISNUMERIC() And Views

Sep 10, 2007

Hello all!

I've wrote a small query for SQL 2005 and it's doesn't seem to work.

I have a table that contains two columns (X and Y), X is an int and Y is an nvarchar(50). I've populated this table with some data where Y contains numbers and some strings (e.g. "1", "2", "foo", etc). I've then got a view which only returns the rows where Y is numeric - now, I then query this table stating I only want numbers greater than 0 (i've casted the column) but this throws an error stating "foo" can't be casted. This is strange because the view doesn't return that.

What's going on? All of this works fine in SQL 2000 but not in SQL 2005 - looks like it's looking at the underlying table rather than the view. Sample code below to help you all out: -

Create Table
============
CREATE TABLE [dbo].[tblTest](
[X] [int] NOT NULL,
[nvarchar](50) NOT NULL
) ON [PRIMARY]


Insert Data
===========
INSERT INTO tblTest(X, Y) VALUES(1, '1')
INSERT INTO tblTest(X, Y) VALUES(1, '2')
INSERT INTO tblTest(X, Y) VALUES(2, 'foo')
INSERT INTO tblTest(X, Y) VALUES(2, 'bar')


Create View
===========
CREATE VIEW [dbo].[vwTest]
AS
SELECT X, Y
FROM dbo.tblTest
WHERE (ISNUMERIC(Y) = 1)


Finally
=======
SELECT X, Y
FROM dbo.vwTest
WHERE (CONVERT(int, Y) >= 0)

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'foo' to data type int.




Thanks in advance!

View 10 Replies View Related

Interesting IsNumeric Result

Aug 20, 2007

Does anyone else get the following result when running this query?


Select isnumeric('4D7')


-----------

1

(1 row(s) affected)




Does anyone know why this would return true for numeric?

Thanks,

Ray

View 4 Replies View Related

How To Handle ISNUMERIC() Function ?

Aug 31, 2006

I have a task (Derived Column Task) and I want to write something like this :

IsNumeric(aColumnOfString) == true ? "All numbers" : "there are some characters"

Here aColumnOfString can be something like "123a5" or 12345". I do not want to simply check if the left-most character is a number or not. I want to check the entire expression and return me a TRUE or false.



A TRUE is returned if the entire expression contains ONLY numbers, and FALSE otherwise.

I read some posting using regular expression. But that is not a solution for this situation.

Anyone knows how to accomplish this, please help!

View 3 Replies View Related

T-SQL - Check For Decimal?? Like ISNULL, ISDATE, ISNUMERIC, Etc.

Apr 14, 2008

There is a MSSQL function that check the value. Like ISNULL(), ISDATE() and ISNUMERIC(). I don't see a function that check for decimal. If there isn't any then is there an user-defined function for it? I need to be able to validate the string value for decimal before it get assigned to a decimal datatype or T-SQL will run into an error.

I'm using MS-SQL 2000...

Thanks...

View 3 Replies View Related

Transact SQL :: Using CAST (column AS BIGINT) And ISNUMERIC

Aug 27, 2015

I found this to work:

SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV

[Code] ....

It Returns:

uri          
evFieldUri          
evFieldVal          
(No column name)
224016  3267      
+000089243829 89243829
224019  2717      
+000089243825 89243825
224472  3333      
+000000000000000000000017     17
225052  3267      
+000089243829 89243829
225055  2717      
+000089243825 89243825

So, then I went back to:

SELECT uri, evFieldUri, evFieldVal
, CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV

[Code] ....

And it returns this error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

So, I tried again, and this worked…

SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
AS BIGINT),
ISNUMERIC(evFieldVal)
FROM TSEXFIELDV WHERE URI
> 0 AND evFieldUri
IN ( SELECT URI
FROM TSEXFIELD WHERE exFieldFormat
IN (1,11))

I logged out and came back and tried again, and it still worked. So then I tried…

SELECT uri, evFieldUri, evFieldVal,CAST(evFieldVal
AS BIGINT)
FROM TSEXFIELDV
WHERE URI
> 0

[Code] ...

And it fails.

View 5 Replies View Related

IsNumeric Does Not Work On Data From Data Conversion Task

Jan 3, 2008

Hi,

I have another issue. I have an excel file that I pipe through a "data conversion" task. I have set all the column data types to strings, because there's no way to know beforehand if a particular column will be number or text because the file is very non-standard (it looks more like a formatted report).

After the data conversion, I send all the rows to a script task. In the script task, I do a check on the numeric fields.

for example:


If Not IsNumeric(Row.Price) Then


Row.Price_IsNull = True

End If


However, this check fails each and every time, even if the field contains a number! I don't have this problem when using flat file sources.

So, none of my numeric fields are getting loaded to my ole db destination.

Help, is there a way around this? Or am I forced to just skip this number check altogether? I'd prefer not to.

Thanks

View 10 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

------------- ----------------------

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

DB Engine :: Can't Use The MERGE Statement / How To Design WHERE Condition For Insert Statement

Nov 5, 2015

I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.

View 2 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

Compiler Is Not Recognizing My Using Statement For SglConnection Statement

Feb 4, 2006

I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code.
Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be?  Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message.
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request.Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error:
Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");
1 <asp:sqldatasource runat="server"></asp:sqldatasource>
2 <%@ Page Language="C#"%>
3 using System;
4 using System.Data;
5 using System.Collections;
6 using System.Data.SqlClient;
7
8 <script runat=server>
9
10 protected void Page_Load(object o, EventArgs e)
11 {
12 ObjectDataSource dsa; // This works no problems from the compiler here
13 SqlDataSource ds; // This works no problems from the compiler
14
15 if (IsPostBack)
16 {
17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text))
18 {
19 instructions.Text = "Congratulations, your authenticated!";
20 instructions.ForeColor = System.Drawing.Color.Red;
21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");
22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'";
23 }
24 else
25 {
26 instructions.Text = "Please try again!";
27 instructions.ForeColor = System.Drawing.Color.Red;
28 }
29 }
30
31 }
32
33 bool AuthenticateUser(string username, string password)
34 {
35 // Authentication code goes here
36
37 }

View 1 Replies View Related

Case Statement Error In An Insert Statement

May 26, 2006

Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View 10 Replies View Related

How To Use Select Statement Inside Insert Statement

Oct 20, 2014

In the below code i want to use select statement for getting customer

address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname

from customer table.Rest of the things will be as it is in the following code.How do i do this?

INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,

[code]....

View 1 Replies View Related

How To Show Records Using Sql Case Statement Or If Else Statement

Feb 20, 2008

i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows


as per data in MS Sql

my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --

2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no

3)Other_project
fields are -- other_proj_id,other_custname,po

for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id custname contract_no field_no
1 abc 234 66
2 xyz 33 20

Other_project
============
other_proj_id other_custname po
1 xxcx 111
2 dsd 222

material
=========
material_id project_type project_id qty
1 AB Corporate 1 3
2 Other Project 2 7

i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project


sample query i write :--

select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op


but this query not work,also it gives errors

i want sql query to show data as follows


material_id project_type project_id custname other_custname qty
1 AB Corporate 1 abc -- 3
2 Other Project 2 -- dsd 7

so plz help me how can i write sql query for to show the output
plz send a sql query

View 8 Replies View Related

Help With Delete Statement/converting This Select Statement.

Aug 10, 2006

I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID

I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:

SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180

View 1 Replies View Related







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