Network Performance For Simple Select Statement

Jul 23, 2005

I've just inherited a system and have some concerns about the speed of
connections to a remote server (SQL2000). If I do a simple select
statement on the table below, it takes 14 minutes to retrive 6 million
rows across a 2Mb line. Obviously it's a reasonable amount of data to
retrieve, but I would have thought this would be quicker if I'm honest.
Run locally, this is 50 seconds.

My thoughts are that there may be some issues with our connection (we
get general network errors sporadically, which are being looked at),
but wanted some thoughts if the performance is acceptable for what it
is doing with what is available. I don't think there is a SQL issue,
but want to check if this sounds about right.

It's early days, so I'm after a general impression of the speed of
retrieval for the amount of data on the available bandwidth. Assuming a
best performance scenario, what is the minimum time it should take as a
best guess ?

Thanks

Ryan

CREATE TABLE [FIELD_VALUES] (
[DEALER_DATA_ID] [int] NOT NULL ,
[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FIELD_VALUE] [numeric](15, 5) NULL ,
[CHANGED_TYPE] [int] NULL ,
CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED
(
[DEALER_DATA_ID],
[FIELD_CODE]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

View 1 Replies


ADVERTISEMENT

Simple SELECT Statement

Dec 6, 2004

Ok, I do know SQL and have been using it for quite soem time. For some reason, it is giving me an error and I was wondering if someone could help.

Here is the few lines of interest


System.Data.SqlClient.SqlCommand command;
command = new System.Data.SqlClient.SqlCommand(@"SELECT Password FROM User WHERE Username='" + user + "'", this.sqlConn);

dataReader = command.ExecuteReader();


This is the error I am coming up with.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'User'.



I have tried enclosing the Table name in quotes and removing the @. What am I doing wrong? Thanks!

View 1 Replies View Related

Should Be A Simple SELECT Statement

Nov 15, 2007

I've been struggling with this problem, hope someone can help. I'm using SQL Server 2005 Express. I have a simple database with 7 tables (m64,m67,m69,m71,m87) all have an identical design(column name, type).
All I want to do is pull out one recordset (if it's in that table) from each of the tables.

This is what I have:
"SELECT * FROM m64, m67, m69, m71, M87 WHERE P_N Like '" & Request.Querystring("P_N") & "'"

If I only enter one table, I get a result.

View 12 Replies View Related

Simple Select Statement Problem

Aug 13, 2007

Hi,

I have several records in a table of "links", which has the fields ID, UserID, Description and URL. I'm trying to select all the records in this table for a specific user. The stored prodecure I'm using is:

ALTER PROCEDURE dbo.sprocLinkSelect (@UserID uniqueidentifier)

AS

SELECT ID, UserID, description, URL
FROM links
WHERE UserID = @UserID


When I execute the procedure without "UserID" in the SELECT part of the statement it works and returns the correct rows for the UserID entered as a parameter. However, when UserID is included in the SELECT part of the statement (as above), it says:

"No rows affected.
(1 row(s) returned)"

and just shows the column headings, but with no record details beneath. (When the example works as intended, it returns two rows.)

I've tried changing the procedure to take @description as its parameter and return all records with one specific description, and it also only works when UserID isn't included in the SELECT line. I've also tried using SELECT *, but that doesn't seem to work either.


Any help would be greatly appreciated, I'm using SQL Express.

View 3 Replies View Related

Simple Select Statement OrderBy

Feb 4, 2008

Well my problem lies in that I am generating reports with the data I retrieve from my sql database. However my problem resides in the fact that I am generating one report at a time and if I want to grab each entry in the order in which they were produced its no problem.

IE - Using PowerBuilder 10.0 as an IDE for my application to generate reports.

select i_id into :insp_id from inspection where i_id = :index order by i_id asc using sqlca;

But now if I want to grab them in alphabetical order from another table I have problems.

this is the code I am trying to use maybe I am just thinking it through wrong.
select s_id into :insp_id from section where s_id = :index order by s_name asc using sqlca;

any help is appreciated. Is there a way to grab each row in alphabetical order?

View 3 Replies View Related

Solution For Simple Select Statement.

Jul 30, 2007

I have a table with the name of customers in it. The problem is that I have one column with the full name in it like

|Last First Middle|.

As you can see this is a problem for me.
I need to find a way to put these values into 3 seperate columns.

|Last| First| Middle |

Is there a function to seperate them via a space?

I have been looking on the web and have not found any functions or solutions.
Any help would be most appreciated.

Thanks in advance,

Gene

View 3 Replies View Related

Simple Select Statement Gone Wild!!

Sep 14, 2007

I am very new to sql and would like some help with a simple select statement. I am trying to pull birthdates using the following code. I can get the query to run but some of the dates are not between the parameters. What am i doing wrong?

SELECT Employees.EmployeeStatusID, People.FirstName, People.LastName, People.BirthDate
FROM Employees FULL OUTER JOIN
People ON Employees.SystemAssignedPersonID = People.SystemAssignedPersonID
WHERE (People.BirthDate BETWEEN '10 - 01 - 1900' AND '12 - 15 - 2007')
ORDER BY People.BirthDate

<<<<<<<<<<<<<<<<<<<<<<<<<< Results




A
KWANG
TAK
10/25/1929 12:00:00 AM

T
ETHEL
MOE
5/24/1933 12:00:00 AM

T
GILBERT
BARAJAS
1/9/1937 12:00:00 AM

A
EDILBERTO
PENA
2/10/1937 12:00:00 AM

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

Large Log Table ....SELECT * FROM Statement....killing The Performance Of Server..Help Me Out..

Mar 12, 2008

Hi all

I have a Large log table with large size data(I month only),If I run a query like SELECT * FROM <table_name> Server will go€¦very very slow€¦.

Because of large Data system is going slow€¦..

Please some body helps me with suggestion how get good performance.

View 4 Replies View Related

Connecting To A Network Server - A Simple Question !!

Jul 12, 2007

Hi



My question should be relatively simple.



I have a server machine (Windows XP + SQL SERVER 2005 EXPRESS) : IP (192.168.1.1)





2 client machines:

Client 1 (Windows XP + SQL SERVER 2005 EXPRESS) : IP (192.168.1.100)

Client 2 (Windows XP + SQL SERVER 2005 EXPRESS) : IP (192.168.1.101)



I want to connect the 2 clients to the server database in a simple clientserver arch.

what should I do ?!!!



Thank you in advance.

View 5 Replies View Related

Index/performance Index For SELECT.... IN Statement

Sep 10, 2007



Hi All,

I 'm working to improve some sql performance.


One of the major syntax inside the SELECT statment is ..

WHERE FIELDA IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='A') AND
WHERE FIELDB IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='B') AND
WHERE FIELDC IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='C') AND
WHERE FIELDD IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='D') AND
WHERE FIELDE IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='E') AND
WHERE FIELDF IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='F')

(It's to compare the field content with some user input parameter inside a parameter table... )

I think properly is that the SELECT ... IN is causing much slowness in the sql statement. I have indexed FIELDA , FIELDB, FILEDC etc and those PARAVALUE and PARATYPE in the PARATABLE table. But perfromance is still slow and execution takes >20 seconds for 200000 rows of records.

Do any one know if still any chance to improvide the performance like this?

Much Thanks,

Andy

View 14 Replies View Related

Network Packet Size - WAN Performance

Apr 9, 2004

We have an application that runs across the WAN to multiple locations. Performance is poor and we are looking at ways to improve performance. One suggestion from our Sr. Network Administrator is to change our Network Packet Sizes across all points, SQL Server & NIC to match the outgoing Router. This would be a size of 1440.

Does anyone have any thoughts or recommendations on this?

View 3 Replies View Related

How To Improve Network Performance Of SQL Server 2000

Nov 15, 2007

Hi!!

In our SQL server is installed in Windows 2003 Standard edition. there about 30 clients using SQL server....

* How to improve SQL server performance ??
* How to improve Network traffic problem ??
* Should I use 2 Network Adapter Card???

Any idea please...

thanks...

View 4 Replies View Related

Simple Performance Help

Nov 2, 2007

I am looking to take unique key value from some data
Check if that key value ( or record ) exists within a table
If it already exists then

update the row with my data based on the unique key
else

insert the data as a new row


for example ..........

DECLARE
@myNewID integer
@Result integer
@myNewValue VARCHAR(10)

@myNewId = 1234
@myNewValue = 'abcdefg'

SELECT @Result = SELECT count(*) FROM mytable WHERE keyID = @myNewID

if @Result > 0

UPDATE mytable SET colValue = @myNewValue
WHERE @KeyID = @myNewID
else

INSERT into mytable ( keyID, colValue ) values ( @myNewID, @myNewValue )


My question is, is there a better, faster way of doing this. I have to do this check around 100 times per minute 24x7x365
In reality the actual table I am checking is approx 2.5 million rows and growing fast. I'd like to eliminate the SELECT COUNT(*) step if possible, I know I could use SELECT 1, or an EXISTS type approach.

I'm thinking would it be better to just go ahead and INSERT and then check for a primary key constraint error and if found perform an Update. I was also thinking about INSERT and/or UPDATE triggers.

Any thoughts ?

KD


View 5 Replies View Related

Select Statement Within Select Statement Makes My Query Slow....

Sep 3, 2007

Hello... im having a problem with my query optimization....

I have a query that looks like this:


SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)


it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View 3 Replies View Related

Performance In Simple Scenarios

Aug 22, 2007

I have done some performance testing to see if asynchronous triggers performs any better than synchronous triggers in a simple audit scenario -- capturing record snapshots at insert, update and delete events to a separate database within the same instance of SQL Server.

Synchronous triggers performed 50% better than asynchronous triggers; this was with conversation reuse and the receive queue activation turned off, so the poor performance was just in the act of forming and sending the message, not receiving and processing. This was not necessarily surprising to me, and yet I have to wonder under what conditions would we see real performance benefits for audit scenarios.

I am interested if anyone has done similar testing, and if they received similar or different results. If anyone had conditions where asynchronous triggers pulled ahead for audit scenarios, I would really like to hear back from them. I invite any comments or suggestions for better performance.

The asynchronous trigger:



Code Snippet
ALTER TRIGGER TR_CUSTOMER_INSERT ON DBO.CUSTOMER
FOR INSERT AS
BEGIN
DECLARE
@CONVERSATION UNIQUEIDENTIFIER ,
@MESSAGE XML ,
@LOG_OPERATION CHAR(1) ,
@LOG_USER VARCHAR(35) ,
@LOG_DATE DATETIME;

SELECT TOP(1)
@CONVERSATION = CONVERSATION_HANDLE ,
@LOG_OPERATION = 'I' ,
@LOG_USER = USER() ,
@LOG_DATE = GETDATE()
FROM SYS.CONVERSATION_ENDPOINTS;

SET @MESSAGE =
( SELECT
CUST_ID = NEW.CUST_ID ,
CUST_DESCR = NEW.CUST_DESCR ,
CUST_ADDRESS = NEW.CUST_ADDRESS ,
LOG_OPERATION = @LOG_OPERATION ,
LOG_USER = @LOG_USER ,
LOG_DATE = @LOG_DATE
FROM INSERTED NEW
FOR XML AUTO );

SEND ON CONVERSATION @CONVERSATION
MESSAGE TYPE CUSTOMER_LOG_MESSAGE ( @MESSAGE );
END;






The synchronous trigger:



Code Snippet
ALTER TRIGGER TR_CUSTOMER_INSERT ON DBO.CUSTOMER
FOR INSERT AS
BEGIN
DECLARE
@LOG_OPERATION CHAR(1) ,
@LOG_USER VARCHAR(15) ,
@LOG_DATE DATETIME;

SELECT
@LOG_OPERATION = 'I' ,
@LOG_USER = USER() ,
@LOG_DATE = GETDATE()

INSERT INTO SALES_LOG.DBO.CUSTOMER
SELECT
CUST_ID = NEW.CUST_ID ,
CUST_DESCR = NEW.CUST_DESCR ,
CUST_ADDRESS = NEW.CUST_ADDRESS ,
LOG_OPERATION = @LOG_OPERATION ,
LOG_USER = @LOG_USER ,
LOG_DATE = @LOG_DATE
FROM INSERTED NEW
END;





View 4 Replies View Related

Simple (peculiar ) Performance Issue.

Apr 4, 2007



Hi all,



When I execute a SELECT query on a table with around 25 million records, I get
performance difference based on the passed parameter value .



The below queries returns the output in 1 second.



SELECT TOP 10000 * FROM TestTable WHERE Column = 1
SELECT TOP 10000 * FROM TestTable WHERE Column = 2
SELECT TOP 10000 * FROM TestTable WHERE Column = 3



The below query alone takes 18 seconds to return the output.



SELECT TOP 10000 * FROM TestTable WHERE Column = 4



(FYI: The count of records for the column value 4 is lesser than the other column values)



Could anyone please let me know why this happens and how to resolve this ?



Thanks in advance,

DBLearner

View 2 Replies View Related

Slow Performance With A Simple Query In A Small Table?

Jul 9, 2001

In my database/MY SERVER (SQL7/Win2K), I run a simple query with a Table/10000 rows (without cluster index):
SELECT * FROM TABLE
it take over 30s. Why it's slow? How can I check for reason? How to configure my server to improve performance?
Thanks in advance.
TH
----------------------------------
SP_CONFIGURE's RESULT in MY SERVER
----------------------------------

Table 'spt_values'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask 0 2147483647 0 0
allow updates 0 1 1 1
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 52 52
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0

Table 'spt_values'. Scan count 43, logical reads 108, physical reads 0, read-ahead reads 0.
Table 'sysconfigures'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.

View 4 Replies View Related

Multiple Tables Select Performance - SQL 2005 - Should It Take 90 Seconds For A Select?

Dec 4, 2007

I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks

View 7 Replies View Related

Simple SQL Statement

Apr 19, 2004

I am trying to concatenate two columns together firstname, lastname
i have done some sql in oracle, but i'm noticing that mssql is different
what i have seen is

select firstname||lastname from table

that errors, what is the right syntax?

Thanks

View 1 Replies View Related

Simple SQL Statement,

Jan 16, 2005

I am attempting to do an insert on the DataGrids Delete command. The problem I am running into is that im not very good at SQL inserts.

Can anyone tell me how to insert a record into a table based on the ID of a record in another table? I want to do this.

As a record (datagrid Row) is deleted, I want to run code (that I dont know how to write) that does this...

Insert into Table-Interface-LOG (fields), from Table-Interface (fields)
Where Interface.Interface_ID = @interface_id

I just need the SQL to do the insert, no ADO code or anything. I dont understand how to use the SET in a SQL command.
What I have so far that Doesnt work.. not even sure why it doesnt work..



INSERT INTO InterfaceLog
SELECT Interface_ID, App_ID, SendsToApp_ID, InterfaceName, Description, ITContact_ID, Type, Frequency, DataFormat, Status, CommMethod, Complexity,
ProdEntryDate, ProdExitDate
FROM Interface
WHERE (Interface_ID = @interface_id)

any Ideas? Do I use the set comand somehow here?

Thanks for any code...

View 1 Replies View Related

Simple Sql Statement

Apr 23, 2006

 Hi, I feel a difficulty to understand the following procedure, it is simple but i am not sure the logic, could anyone give me some explain / hints , then I know how to write the similiar procedure next time. thanks for your time.   I extract the code from the Classifieds Starter Kit
For the procedure of GetCategoryByParentIdWHERE    (ParentCategoryId = @ParentCategoryId)OR (@ParentCategoryId = 0 AND ParentCategoryId IS NULL)
For the procedure of GetParentCategoryById WHERE (SELECT Path       FROM Category       WHERE Id = @Id) LIKE Path + '%'
ALTER PROCEDURE GetCategoryByParentId(@ParentCategoryId int = 0)ASSET NOCOUNT ON;SELECT     [Id], [ParentCategoryId], [Name], NumProductsFROM         CategoryWHERE    (ParentCategoryId = @ParentCategoryId)OR (@ParentCategoryId = 0 AND ParentCategoryId IS NULL)ORDER BY NameALTER PROCEDURE GetParentCategoryById @Id intASSELECT Id, ParentCategoryId, Name, NumProductsFROM CategoryWHERE (SELECT Path       FROM Category       WHERE Id = @Id) LIKE Path + '%'ORDER BY Path
Thanks a lot, Trevor

View 5 Replies View Related

It Seemed Like A Simple SQL Statement

Jun 6, 2008

I need to see whether there's any unapproved timecards, and if so compile a list of supervisors that need to make approvals.

my original statement was:
SELECT DISTINCT
EmployeeMaster.SupervisorNumber
, SupervisorMaster.EmailAddress
, SupervisorMaster.EmployeeMasterNumber
, RTRIM(SupervisorMaster.FirstName) + ' ' + RTRIM(SupervisorMaster.LastName) AS SupervisorName
FROM
Cards
LEFT OUTER JOIN EmployeeMaster ON Cards.EmployeeNumber = EmployeeMaster.EmployeeNumber
LEFT OUTER JOIN SupervisorMaster ON EmployeeMaster.SupervisorNumber = SupervisorMaster.EmployeeNumber
WHERE
(Cards.SupApp = 0)


And this worked great until they asked for a date range.

If I am correct the distinct will look for the first occurrence that meets the select criteria and ignore the rest of the rows that meet the criteria. Leading to the possiblity that the first row is not a timecard that meets the date requirement, and even though other timecards exist that will satisfy the condition, they will not be tested because of the Distinct clause.

What's the best way to handle a situation like this?

View 2 Replies View Related

Help With Simple Sql Statement

Aug 16, 2006

Hi.

Please help me.

Table 'Counter' has a date field and an integer field. I want to auto increase the integer field by 1 using the update statement. I DO NOT want to first run a select statement to find out the initial value of the integer field prior to incrementing it. how would i go about doing this?

Thanks a lot!

View 1 Replies View Related

Need Help For A Simple Formula For An Sql Statement

Apr 6, 2007

I have a products database on my online store that records 2 columns for each product:DateAdded: the datetime the item was addedTotalSales: the total sales generated since the item was added to the siteI have already figured out how to calculate best sellers with this formula:TotalSales / TotalDays I am adding a computed column to my database, I need to calculate the followingTotalSales / ("this is where i need something to use today's date and calculate how many days it has been since it was added")Then if I can do that, I will automatically have a column that will give me a number ranking each of my items. 

View 2 Replies View Related

Simple Insert Statement

Apr 27, 2006

I have a textbox with the id of txtName it is a name text box.
and I have a submit button.
How do i get the value of the text in the text box to popluate a new row of data in my Sql Server database.

View 13 Replies View Related

Simple Insert Statement

Apr 19, 2008

Here are my tables:

-------------------------------
Events
-------------------------------
ID | E_EventName
-------------------------------


-------------------------------
Photos
-------------------------------
ID | P_EventID | P_Filename
-------------------------------



Given an event like "2005 cookout", and a file like "bob.jpg", I'd like to insert the event id based on the event name and the filename into Photos. How can I do that?

View 1 Replies View Related

What's Wrong With My Statement? (simple)

Aug 3, 2007

Hello well-intentionned reader,

I'm having trouble figuring out what's wrong with my statement. This is what the table looks like. I want to display employees under the manager with the last name King. my statements are below the tableName Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
MANAGER_ID NUMBER(6)


My statement
SQL> SELECT last_name, salary
FROM employees
WHERE manager_id =
(SELECT employee_id
FROM employees
WHERE EMPLOYEE.LAST_NAME = .King.); 2 3 4 5 6
WHERE EMPLOYEE.LAST_NAME = .King.)
*
ERROR at line 6:
ORA-00936: missing expression


Thank you for your time.

/gozu

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

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

A Simple Question About SQL Update Statement

Jul 10, 2006

hi, everyone,
When I update a row that does not exist in a table using VBscript and SQL 2003 server, the row is automatically added to the table. Why does this happen?
Can somebody help me? Thanks in advance!

View 2 Replies View Related

Help Needed With Simple Case Statement In SQL

Mar 1, 2007

Hello,
 
I am looking to modify this Case Statement.  Where it says ELSE '' I need it to display the actual contents of the cell.  1 = Yes , 0 = No, (any other integer) = actual value. 
Right now if the value is anything other than 1 or 0,  it will leave the cell blank.
CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END AS TrainingStatus
Thank You.

View 1 Replies View Related

Pleas Help With Simple Sql Statement Question

Apr 24, 2006

Hello and thanks for looking! I have this sql stament:

Dim strInsertCommand As String = "INSERT INTO CurrentDrawVar (Username, GamesBought, DateLastBuy) VALUES ('" & UserID & "', '" & gamestobuy & "', '" & Now & "')"


It works great and inserts into a database like it should. My question is where can I insert a WHERE stament into it so that I can insert this data over information that already exists in the database. FOr instance. I have a user grogo21 and in this row I want to insert gamestobuy and now. Where can i put this and is the stament below right:

WHERE Username = 'grogo21'

I tried putting it after the parenthases after the values but it doesnt work. Thanks alot!!

View 1 Replies View Related







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