Missing Semicolon (;) At End Of SQL Statement

Apr 27, 2005

Code:

<%@Language = "VBScript"%>
<%Option Explicit%>
<%
dim oRs,oConn,dateofleave,sql,uid

dateofleave = trim(request.querystring("leavedate"))

uid = trim(request.querystring("employeeID"))

set oConn = Server.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open(Server.MapPath("test.mdb"))

set oRs = Server.CreateObject("ADODB.Recordset")
sql ="INSERT INTO test (dateofleave)"
sql = sql & "VALUES ('&dateofleave') WHERE employeeID=&uid"

set oRs = oConn.execute(sql)

%>



can someone help me with this error?
Microsoft JET Database Engine- Error '80040e14'

Missing semicolon ( at end of SQL statement.

/test/booking.asp, line 18

where line 18 is "set oRs = oConn.execute(sql)"

View 4 Replies


ADVERTISEMENT

Please Help: Error: Missing Semicolon (;) At End Of SQL Statement.

Apr 10, 2004

Hey

I am trying to retieve a value from teh database and add one to it, then update the database with thenew value before redirecting to a page.

I am recieving this error and don't know why, i have the following coed below.

Dim objReaderQ as OleDBDataReader

Dim strSQLRead As String
Dim objCmd As New OleDbCommand

strSQLRead ="Select Quantity from tblCart Where (Productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "')"

objCmd = new OleDbCommand(strSQLRead, objConn)
objReaderQ = objCmd.ExecuteReader()

if objReaderQ.Read()
'update quantity by 1

Dim i as integer
i = objReaderQ("quantity")
i = i + 1

objReaderQ.Close()

Dim strSQLQuantity as String = "INSERT INTO tblCart (Quantity) VALUES (@quantity) WHERE (productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "');"

Dim objCmdQuantity As New OleDbCommand(strSQLQuantity, objConn)

objCmdQuantity.Connection = objConn

objCmdQuantity.Parameters.Add("@quantity", OleDbType.VarChar, 255)
objCmdQuantity.Parameters("@quantity").Value = i

objCmdQuantity.ExecuteNonQuery() ' <--- Error Is Occuring On This Line

Response.Redirect("ViewBasket.aspx")

end if


I really can't see what is wrong as i have placed the semi colon it wanted at the end of the string.

Thanks you for your time

Chris

View 1 Replies View Related

How To Deal With:the Previous Statement Must Be Terminated With A Semicolon.

Apr 28, 2008



when I backup the SQL Server 2005 database ,always get prompt
Error description: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'E'.
+48166A04.0004 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
+48166A04.0004 [Microsoft][ODBC SQL Server Driver][SQL Server]The label 'E' has already been declared. Label names must be unique within a query batch or stored procedure.}

how to deal with it, thanks

View 5 Replies View Related

Simple If Statement, What Am I Missing

Oct 13, 2007

Hi everyone and thank for taking the time to read, I am new to this forum and to SQL to.
I am taking a programmers course, i learned mainly C# for now, but i also know a bit of C and C++ and linux programming,
i am going through my SQL school book, (SQL Server 2000 design and implementation) and i am trying a simple IF statement. heres how it goes
DECLARE @P CHAR
SET @P=NULL

SELECT @P=Powerlck FROM vehicle
IF (@P=NULL)
PRINT 'got you'


powerlck is a collum taking char(1) which is either Null or 1.
there is five entry all but one is set to 1, null is the default

when i run this script, it does not print 'got you'

but if i run select * from vehicle
it will show one with <NULL> value.

what did i miss??

View 2 Replies View Related

Missing A Needed Using Statement But Not Sure Which One

Mar 31, 2006

I'm trying to get my feet wet with creating a Sql Server project within VS 2005 and I have the using statements below present but when I compile I get errors stating that SqlContext, SQLPipe, and SQLCommand do not exist in the current context. What am I missing? Under the references node I have 3 references present (System, System.Data, and System.XML). What am I missing?

TIA

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

View 4 Replies View Related

Can An Update Statement Be Used For Interpolating Missing Data?

Jul 23, 2005

Here is a small sample of data from a table of about 500 rows(Using MSSqlserver 2000)EntryTime Speed Gross Net------------------ ----- -----21:09:13.310 0 0 021:09:19.370 9000 NULL NULL21:09:21.310 NULL 95 NULL21:10:12.380 9000 NULL NULL21:10:24.310 NULL 253 NULL21:11:24.370 8000 NULL NULL21:11:27.310 NULL 410 NULL21:11:51.320 NULL 438 NULL21:11:51.490 NULL NULL 10After the first row, every row has only one value of the three.I would like to replace all the NULL values with calculatedinterpolations.I can do it w/ cursors or while loops.I could do it w/ VB (I think)Can this be done w/ an Update statement using self joins?What would be the best way?The value for speed can increase or decrease over time, but can neverbe < 0Net is always less than gross, and neither can go below 0.TIA for any helpful suggestions.Thanks,BM

View 9 Replies View Related

Missing Row In Simple Conditioned SELECT Statement

Nov 7, 2007

Hey there everyone,

I'm sure there's a good reason for this, I just have no idea what it is.

If I run a SELECT * statement on one of my tables, the result set is missing one of the records.

If I SELECT that specific row (by identifier or anything else really), it returns just fine.

Any kind of select (e.g. WHERE ID > X) fails to return that specific row.

Any idea why this might be happening?


Thanks in advance for any guidance!

View 4 Replies View Related

SQL Server 2012 :: Missing Months In A GROUP BY Statement

Jan 20, 2015

I am trying to get a count by product, month, year even if there are is no record for that particular month.

Current outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ April 2014 34
...

Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...

The join statement is simple:
Select Product, Month, Year, Count(*) As Count
From dbo.Products
Group By Product, Month, Year

I have also tried the following code and left joining it with my main query but the product is left out as is seen:

DECLARE @Start DATETIME, @End DATETIME;
SELECT @StartDate = '20140101', @EndDate = '20141231';
WITH dt(dt) AS
(
SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start), 0))
FROM ( SELECT TOP (DATEDIFF(MONTH, @Start, @End) + 1)
n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
FROM sys.all_objects ORDER BY [object_id] ) AS n
)

2nd attempt:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
NULL March 2014 0
XYZ April 2014 34
...

What I want is this (as is shown above). Is this possible?

Desired outcome:
Product Month Year Count
XYZ January 2014 20
XYZ February 2014 14
XYZ March 2014 0
XYZ April 2014 34
...

View 7 Replies View Related

Error: COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Why?

Nov 13, 2007

Hello:
I am implimenting the creation of sequence numbers .I use an insert proc on a table that generates the numbers using an identity field:
procedure usp_createidentity

begin transaction

insert into [tblOrderNumber] with default values

rollback ' done so no records in this table

select @OrderNumber = scope_identity()

I call this from another proc that inserts values into my order table:


procedure usp_Insert @OrderNumber int
as

SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION


EXEC usp_GetNewOrderNumber @OrderNumber = @OrderNumber output
INSERT INTO [dbo].[tblOrder] ([OrderNumber]) values (@orderNumber) ' inserts value from other stored proc


COMMIT TRANSACTION

END TRY

BEGIN CATCH

if (XACT_STATE() = -1)

ROLLBACK TRANSACTION

else

if (XACT_STATE() = 1)

COMMIT TRANSACTION
END CATCH

Here is the problem. When I run usp_Insert I get the following: Error 266 Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

This refers to the usp_GetNewOrderNumber that is called inside the other proc as shown above.

The problem does not happen if I put each statement in usp_Insert in its own try/catch. transaction statements.

Maybe it has something to do with the rollback call in the usp_getneworder.

What do I need to do to get rid of this. problem and still run these within one try/catch trans statement set.

Thanks

View 9 Replies View Related

Data With Semicolon Delimiter

Apr 18, 2008

Hi all......

I have an issue

There are 2 tables... source and target...

Data from source goes into target table under same field...

ISSUE IS -

Data from 'n' no. of records from source table goes into a single record in the target table with delimiter being a semi colon (;)...
where(n >=2)...

For example - if the source table has 'src1', 'src2' and 'src3' as the data then target table will have a single record with semicolon as delimiter as 'src1;src2;src3'

How do we compare the data under this particular field now...

Do we have to use a if then loop for identifying when the ; ends in target data...

kindly help by giving a example...



Thanks,
Avi.

View 1 Replies View Related

SQL Server 2012 :: Capture Statement From Missing Join Predicate Event?

Jan 27, 2015

After monitoring using SQL profiler, i found that Missing join predicate event is happening a lot.

The problem is that profiler doesn't allow me to select the textdata to know which SQL statement is causing the issue.

I tried using the spid to check what's that process is running but the problem is that application is running many sqls so when i run

select PROGRAM_NAME,hostname,qt.text from sys.sysprocesses as sps1 CROSS APPLY sys.dm_exec_sql_text(sps1.sql_handle) AS qt
where spid=169

it gets me the SQL being run at that time not the one that causing the event.

View 3 Replies View Related

How Can I Insert A Semicolon As User Data?

Sep 27, 2005

I'm working with an existing program that crafts an insert statement
from user input.  The data is machine generated, though, and some
of it has semicolons.  How can I "escape" those semicolons so that
I can insert them into the database?  Does the ESCAPE key work
with the insert statement, too?  Can I just replace the ";" with
some other character(s) to escape it?  I'm not finding much in the
online help.  Thanks!

View 5 Replies View Related

Connection String Has Semicolon (;) - How On Earth Can I Get This Working?

Aug 3, 2007

Ok, here's my setup. I've got a named instance in a SQL 2000 cluster. I only have dbo rights on my database, because it is a shared infrastructure. Here's my current web.config connection string (the meat, anyway):
When I'm at the office, this is my connection string, pretty normal:
connectionString="Data Source=ServerNameInstanceName;Initial Catalog=blah..."
But, when I connect through the VPN, I can't just connect through the named instance - I have a specific port. This is where things get odd.
First, if I try to connect through SQL Server Management Studio (2005), i get nothing. If I try to connect using "ServerNameInstanceName, (comma) Port Number" it connects, but not to my instance. I get a seperate set of databases that I believe are in the default instance. So, I changed the comma to a semicolon (;) - and it still connected to the same thing - connected to the database, but to the wrong set of databases. So, on a whim, I tried plunking my string, which was now "ServerNameInstanceName;(semicolon) PortNumber" into the SQL 2000 Tools and it worked in both Query Analyzer and in Enterprise Manager. So, I thought, I'll just slam this into my connection string and all will be well. No. I can't use a semicolon in my connection string, and I can't find an escape character to use. Double semicolons don't work, a comma doesn't connect me properly, double colons don't work, the JDBC brackets don't work {} - so I'm at a loss. I'm out of ideas. I've set up aliases, and those don't work earlier.
I'm using ASP.net 2 with VB & C# and Visual Studio 2005 Professional. Thanks for any help anyone can give on this!

View 3 Replies View Related

Wants Help For Extracting Semicolon Seprated Names From A Record

Aug 30, 2007

Hello friends,
I am designing an ETL process in which I have a source column which contains names seprated by semicolon( i.e Rajat Kr Sharma;Mr Sammer;Mr Ravi;Mr Ankur Bhatnagar) in each row.
ETL process should create n records,one for each single name in destination table as n numbers of name apprear
in each row of source table.

The number of names can vary in each row of source table as per no. of delimited character semicolon.

So Can some one suggest me to lay out some plan and what controls of SSIS I should use?

Here I m giving the pictorial view of activity I'm trying to carryout.



Source Table Row
______________________________________________________________________
|Rajat Kr Sharma;Mr Sammer;Mr Ravi;Mr Ankur Bhatnagar |
|_____________________________________________________________________ |

Changes to Destination Table
______________________________________
|Rajat Kr Sharma |
|_____________________________________|
|Mr Sammer |
|_____________________________________|
|Mr Ravi |
|_____________________________________|
|Mr Mr Ankur Bhatnagar |
|_____________________________________|

View 3 Replies View Related

Full Text Search With Characters Such As Colon Or Semicolon

Jun 20, 2008

Hi, I'd like to know if there's a way to get sql server NOT to ignore the colon when performing a full text search (CONTAINS) for a string "sometext:". At this moment the query works, only the results are not narrowed to the ones containing the specified colon. I've read about this and I saw that these kind of characters (word breakers and stemmers) are ignored and want to know if there's a way to work around this (obviously performing well - so LIKE fails the test). Thanks

View 2 Replies View Related

Union Returns Duplicates - Semicolon Or Comma Not Removed

Jan 7, 2012

This SQL is meant to show the changes that will be made, when removing a selected user's email address from a batch.

However, when executed, each row is duplicated, and in the duplication, the semi-colon or comma isn't removed. For example, if I wanted to remove user "sam@mail.com"

The table results displayed would be:

Row 1:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;frank@mail.com
NewParamValue: jack@mail.com;frank@mail.com

Row 2:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;john@mail.com
NewParamValue: jack@mail.com;;frank@mail.com

Ideally, it should only display each row once, and not have the semicolon error. It seems to be a union error, because when I comment out the First and second union statements, it runs fine.

-- Delete email address from a.Batch

IF(@EmailAddress IS NOT NULL)
BEGIN
IF(LEN(@EmailAddress) > 0)
BEGIN
IF(@ShowOnly = 1)

[Code] ......

View 2 Replies View Related

SQL Server 2012 :: String Concatenation Using + Operator With Semicolon Delimiter

Dec 5, 2013

I have 8 fields - I have requirement to concatenate using '+' operator with semicolon delimiter but issues is in the

Output I get semicolons for the fields that are empty below is my code :

-------------
case
when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%'
and [New Extension or Replacement Audit Flag] like 'FLAG%'
then 'Reject – New, Extension, Replacement invalid entry' --'it is jp'
else ''
end as [ES Fully approved data 1],
case

[Code] ....

View 6 Replies View Related

Reporting Services :: Concatenate 24 Columns With Semicolon Delimited Into Single Column

Sep 17, 2015

How do i concatenate 24 columns with semicolon delimited into a single column without getting data conversion error ....

View 2 Replies View Related

SQL Server Agent Job Step Properties Window Loses Property Values Containing Semicolon (;)

May 16, 2007

Here's a weird one:



We are setting up a job for the SQL Server Agent via SSMS. The Job Step Type is SSIS.



In the Job Step Properties window, on the Set values tab, you can enter Values to override your package variables - normally all well and good.



However in this particular case, the variable Value contains semicolons ( - it is a Connection String for an ODBC driver. Eg: Driver={Client Access ODBC Driver (32-bit)};system=MYSERVER;...



The behaviour for this Value is weird:

If the Value is not surrounded with double quotes ("), the job fails with "The command line parameters are invalid."
If the Value is surrounded with double quotes ("), the job will run as intended. The catch is: that entry and any subsequent "Set Values" entries disappear next time the Job Step Properties window is opened.

This looks like a bug with the parsing of those strings by the Job Step Properties window?



Or am I missing something?



Mike

View 5 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 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