DataAdapter.Update Is Running This Query... What Is Wrong With It?

Jan 18, 2008

My DataAdapter.Update() Method is running this query against my database and all the parameters and the formatting looks correct to me. I was wondering if anyone could identify obvious errors.... Thanks!

exec sp_executesql
N'NewUpdateCommand',
N'@Name nvarchar(50),
@PriContactFName nvarchar(50),
@PriContactLName nvarchar(50),
@PriContactWork nvarchar(20),
@PriContactFax nvarchar(20),
@PriContactCell nvarchar(20),
@PriContactEmail nvarchar(50),
@MainOfficeLocationAddr1 nvarchar(50),
@MainOfficeLocationAddr2 nvarchar(50),
@MainOfficeLocationCity nvarchar(50),
@MainOfficeLocationState nvarchar(50),
@MainOfficeLocationZip nvarchar(15),
@Description nvarchar(50),
@Original_ClientID int,
@ClientID int',
@Name=N'Brinker International',
@PriContactFName=N'Steves',
@PriContactLName=N'Maubley',
@PriContactWork=N'913-876-9876',
@PriContactFax=N'913-098-8765',
@PriContactCell=N'913-987-7654',
@PriContactEmail=N'a@b.c',
@MainOfficeLocationAddr1=N'1234 Burkwood',
@MainOfficeLocationAddr2=NULL,
@MainOfficeLocationCity=N'Tallahassee',
@MainOfficeLocationState=N'Florida',
@MainOfficeLocationZip=N'99111',
@Description=N'This is a good client.',
@Original_ClientID=1,
@ClientID=1
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'NewUpdateCommand'.

View 1 Replies


ADVERTISEMENT

Tell Me Whats Wrong With This UPDATE Query

Oct 30, 2006

UPDATE CART SET TOTAL_COUNT = TOTAL_COUNT + 1, SET TOTAL_COUNT1 = TOTAL_COUNT1 + 1, SET DATE1 = 10/30/2006 6:17:02 PM, SET PERSON = luke WHERE CART_ID = 1

I have wacked my head over this query I dont see anything wrong with it

any ideas?

View 4 Replies View Related

DataAdapter Update Problems

Sep 25, 2007

Hi, I am populating a datagrid with data from one sql table, I add additional columns and then wish to write it back out to another table. I have written the following code to acheive this, however it keeps throwing the follwing exception - "Update requites a valid UpdateComman when passed a DataRow collection with modified rows"
I've looked and I can see what the problem is, can anybody help please?
 Private Sub FillData()
'SQL connection
Me.sqlconnection = New System.Data.SqlClient.SqlConnection
Me.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")

Dim sql As String
If Request("action") = "gen" Then
sql = "select item from tblPickList where GroupName = 'xxx'AND Category = '" & Category2 & "' ORDER BY item ASC"
ElseIf Request("action") = "fav" Then
sql = "select identifiertext as item from tblfavourites where HRID = " & Session("hrid") & " and type = 3 ORDER BY identifiertext ASC"
ElseIf Request("action") = "spec" Then
sql = "select item from tblPickList where GroupName = '" & GroupID & "' AND Category = '" & Category2 & "' ORDER BY item ASC"
End If

ds = New DataSet
da = New SqlClient.SqlDataAdapter(sql, sqlconnection)

Try
sqlconnection.Open()
da.Fill(ds, "tblPickListTemp")
sqlconnection.Close()
Catch ex As Exception
sqlconnection.Close()
End Try

ds.Tables(0).Columns.Add("HRID")
ds.Tables(0).Columns.Add("Flag")
'may possibly add a favourites column here at a later date to ensure no duplication

Dim dr As DataRow
For Each dr In ds.Tables("tblPickListTemp").Rows
dr("HRID") = Session("hrid")
dr("Flag") = 0
Next

DgrdFavourites.DataSource = ds
DgrdFavourites.DataBind()
End Sub

Public Sub CreateTempRecords()

'SQL connection
Me.sqlconnection = New System.Data.SqlClient.SqlConnection
Me.sqlcommand = New System.Data.SqlClient.SqlCommand
Me.da = New SqlClient.SqlDataAdapter
Me.sqlconnection.ConnectionString = ConfigurationSettings.AppSettings("CuroConnection")

Dim sql As String
sql = "Insert into tblPickListTemp (Item, HRID, Flag) values (@Item, @HRID, @Flag)"
sqlcommand.CommandText = sql
sqlcommand.Connection = sqlconnection

sqlcommand.Parameters.Add("@Item", "Item")
sqlcommand.Parameters.Add("@HRID", "HRID")
sqlcommand.Parameters.Add("@Flag", "Flag")
da.InsertCommand = sqlcommand
da.Update(ds.Tables(0))
End Sub 

View 10 Replies View Related

Update Dataadapter With Text Box Value

Sep 16, 2004

hi

this is probably a piece of pie to fix but i be having some problems......

i have this peice of code to update the sql dataapadapter

Dstest1.Tables("test").Rows(0)("hello") = Me.textboxhello.Text
SqlDataAdapter1.Update(Dstest1, "test")

this does not work, it does work however if i replace the Me.textboxhello.Text
with a value in quotation marks or with another text box not linked to that feild in the table... it does not work with the relevant text box!!!!!!!!

thanks for all your help

cq

View 1 Replies View Related

Dataadapter.Update() OR ExecuteNonQuery()

May 18, 2007

Hy, again! I am at the begining of an application. I have some modules that insert, delete and update only one row at one table. My question is should I use dataadapter.Update() or ExecuteNonQuery(). I prefer ExecuteNonQuery because I want to build a class : DataLayer to implement my own InsertProcedure(), UpdateProcedure(),DeleteProcedure(). I want speed in my application, so which is the best: dataadapter.Update() OR ExecuteNonQuery(). Thank you!

View 5 Replies View Related

DataAdapter.Update Updates Zero Records

Jul 13, 2004

I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?

View 1 Replies View Related

Create Trigger To Use AFTER DataAdapter.Update()

Feb 5, 2005

Hi,

I'm using DataAdapter.Update() to update data in a table. My question is; how do I create a trigger that works after the update has completely finished?

For example if my update adds 50 new rows to a table the trigger I've currently got fires after each new row that is added ie 50 times in total. Is it possible to get it to trigger after the last (ie 50th) row is added???

Thanks

View 2 Replies View Related

DataAdapter.Update Method Question.

Nov 30, 2006

Hi,

I am trying to use DataAdapter.Update to save a file stream into SQl Express.

I have a dialog box that lets user select the file:

openFileDialog1.ShowDialog();

I want to put

openFileDialog1.OpenFile();

Into

this.documentTableAdapter.Update(this.docControllerAlphaDBDataSet.Document.DocumentColumn);

I am thinking that it might just be some syntax issue, but I looked online, and didn't find much answers.

Thanks,
Ke

View 1 Replies View Related

Insert/Update Relational Tables Using Dataadapter

May 2, 2008

Hi!

I am trying to insert data into 2 different tables. I am using dataadapter and dataset.

Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
Call ConnectionString()

Dim insertSQL As New SqlCommand()
insertSQL.Connection = sqlConn
insertSQL.CommandText = "SELECT location.CountryName, location.CityName, location.BuildingName, location.FloorID, rooms.name, rooms.FloorID AS Expr1 FROM location INNER JOIN floors ON location.FloorID = floors.id INNER JOIN rooms ON floors.id = rooms.FloorID"

Dim ds As New DataSet()
Dim da As New SqlDataAdapter()

da.SelectCommand = insertSQL
Dim scb As New SqlCommandBuilder(da)

Try
da.Fill(ds)
Dim ndr = ds.Tables("location").NewRow
Dim ndr2 = ds.Tables("rooms").NewRow

ndr("FloorID") = FloorIDDDL.SelectedValue
ndr("CountryName") = CountryNameTextBox.Text
ndr("CityName") = CityNameTextBox.Text
ndr("BuildingName") = BuildingNameTextBox.Text
ndr2("name") = RoomNameTextBox.Text
ndr2("FloorID") = FloorIDDDL.SelectedValue
ds.Tables("location").Rows.Add(ndr)
ds.Tables("room").Rows.Add(ndr2)
da.Update(ds)
ErrMsgLbl.Text = "Information saved successfully"
Catch ex As Exception
ErrMsgLbl.Text = ex.ToString
End Try

sqlConn.Close()
End Sub

The above code does not throw any error. It also does not update the tables.

Your help will be appreciated.

Thanks!

View 5 Replies View Related

Update SubQuery(two Tables), Pls Advice Wats Wrong With The Query. 1 Hole Day I Spent Still Cant Figure

Oct 27, 2006

Table A, Table B. need to update table A balance field from table b sum of amount

UPDATE CUSTOMERS SET BALANCE=(SELECT SUM(AMOUNT) FROM PAYMENT,CUSTOMERS

WHERE CUSTOMERS.ID=PAYMENT.ID GROUP BY PAYMENT.ID)





Msg 512, Level 16, State 1, Line 25

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

View 4 Replies View Related

Very Slow Running Update Query Query

Nov 19, 2004

I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on

the query thats causing teh problem is


--//////////////////////////////////// this is the one thats running


Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions


any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one

Dave

View 6 Replies View Related

Update Query Running Very Slow

Apr 17, 2008

Hi all,

I'm having what you might call an optimisation issue - but I'm also not sure if my approach to this problem is right. I've spent the whole day trying various methods but none seem to be performing as admirably as I'd hoped.

Basically, here's the scenario:

1. Log files are being inserted into a SQL table via Log Parser 2.2.
2. I have a lookup table of ip address ranges to countries and other geographic data.
3. Once the log row has been inserted from Log Parser, I want to update the same log table with data from the lookup table.

The main problem seems to be the updating (the initial insert from Log Parser is lightning quick).

I initially wrote a trigger on AFTER INSERT on the log table that converted the actual user's IP address into IPNumber format using a simple algorithm, then pumped the IPNumber into a quick select query which retrieved the geolocation data. Then, in the same trigger, there was an update statement which basically said:

update dbo.Logs
set [Country] = @Country
where [IpNumber] = @IpNumber and [Country] = Null

Therein lies the problem. The update statement slows everything down to almost a standstill and also seems to obtain a lock on the table.

Critical factors:

1. The log table must remain readable.
2. The query must execute in seconds -- not over half hour :)

I've experimented with various combinations of indexing, so far to no avail.

Any suggestions would be very much appreciated.

Regards

View 10 Replies View Related

Error 25115 Wrong Page (potential Database Corruption). Try Running The Repair Utility.

May 2, 2008



Hi,

Please Help, I am very desparate for a solution to this one.

I have an error appearing

"Error 25115 Wrong Page (potential database corruption). Try Running the Repair Utility."

I have SQL Server Enterprise set up with Merge replication, it is being used to Sync with Windows Mobile Devices as the subscribers.

All the Windows Mobile subscribers have been synchronising fine up until recently, when 1 of the Subscribing Windows Mobile devices received this error.
The day after, another device Synchronised fine the first time (note that the Synchronisation was a long one and the SDF grew in size dramatically while the synchronisation occured, but the Sync worked fine and the SDF dropped back to its initial size) The 2nd sync 5 mins later failed however so this Mobile device has the same error also.

Any suggestions as to the below are much appreciated

a) Why this error occurs?
b)How do you resolve this error?
c)What is the Repair utility mentioned in the error, and is it of use to me?


Thanks in advance, I am very greatful for any advice.

View 8 Replies View Related

SSIS Project Running In Debug Mode Brings Up The Wrong Script Task In The Debugger

Mar 12, 2008



This would actually be funny if I weren't under serious time constraints right now. I have an SSIS project with several script tasks in the control flow. I put breakpoints in one of them to allow me to step through and see whats going on. However, when I start the project, the IDE brings up the script editor for a different script task. The breakpoints in this task are actually on the same line numbers as the task I need to debug but the code is all wrong. I checked the task for which the code is being displayed and there are no breakpoints there.

Thanks for the assist!

Don

View 11 Replies View Related

Update Gone Wrong

Nov 9, 2007


I am using SQL 2000 and have a major issue! I am new to SQL and not sure what to do about the license key being lost due to an update.

Background:
The server was originally loaded with a temp verison of SQL 2000 by the installer of the hardware until the Enterprise Edition arrived on site to insure that all the hardware worked fine. The Enterprise software was loaded onto the system and the license keys were installed. Everything has been working fine for several months. The server has been rebooted a few times after installing the license keys.

Trouble:
I loaded SP2 for Server 2003 and rebooted the server and now SQL thinks it is running the temp version and states that the license key has expired. I tried the suggested solution listed on the site of "upgrading with no changes" and the installation completes but never asks for my license keys. I rebooted the server and it states the samething thing that the temp has expired. It appears that one section can see the keys and another section does not. I have an active database that I do not want to loose but I am stumped on what to do to in order to re-install the license keys.

Any suggestions? Can copy the databases that I have created, uninstall SQL, re-install SQL from CD and then paste back my database? Will the databases restore correctly or is this to simple of a solution?

I have run out of ideas and options in order not to loose the database.

View 1 Replies View Related

Wrong Datetime When Update Using MSSQL

Dec 11, 2005

Dim ldDatetime As DateTime = Now() '11/12/2005 13:05:09
my command text is:
"Insert Into WebSiteThread (TransactionDate) Values ( " & " ' " & ldDatetime & " ' " & ") "
The result I got was "12/11/3091 13:05:09" Not "11/12/2005 13:05:09"
What's wrong please!!
 

View 6 Replies View Related

Whats Wrong With This Update Statement?

Dec 21, 2007

I wrote this update statement and it looks right but I keep getting an error that says "incorrect syntax next to c" what did I do wrong?

Here is the statement:

UPDATE tblclients c
SET intproviderId = (SELECT TOP 1 t.inttransferid FROM tbltransfer t WHERE t.intclientids = c.intclientid ORDER BY t.inttransferid DESC)

View 6 Replies View Related

How To Kill A Long Running Query Running On A Background Thread.

Sep 1, 2006


If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?

The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.

Is there a way to do this?

Thanks


View 1 Replies View Related

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

Aug 11, 2007



here is my code:


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

cn.Open()

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

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

cn.Close()

Response.Redirect("database.aspx")

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

View 5 Replies View Related

Can Anyone Please Tell Me What Is Wrong With This Query

Nov 26, 2007

Can anyone please tell me what is wrong with this query:
rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")
In DreamWeaver the bit in bold is greyed out, why?
rsOtherSubCatagories.Source = "SELECT *  FROM SubCatagories   WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")
Thanks Joe

View 2 Replies View Related

SQL Query Wrong - Please Help

Jan 4, 2005

I am attempting to count distinct orders and display by client for the preceding month. Below is my current query. It is providing inaccurate results. Could someone with a fresh look point out my error(s)?

SELECT
DISTINCT MtgeBroker,
COUNT(CreatedDate) AS [Title Orders for Current Month]

FROM RECalendar

WHERE
(
RECalendar.FileType IN ('COM','CONSTR','ConstrPerm','Constr Refi Table Fund','Conv Refi','FHA Refi','HELOC','Purchase 0 Loan','Purchase Loan','Purchase Cash','0 Is Not Closing','Witness')
AND
RECalendar.ModuleID IN ('594','603','675','814','815','816','817','818','819','820','821','822','823','824','825','826','827','828','829','830','831','832','833','834','887','888','889','890','891','892','893','894','895','896','897','898','899','900','901','902','903','904','905','906','907','908','909','910','911','912','913','914','915','974')
)
AND
(
MONTH(EventDateBegin)=MONTH(DATEADD(MM,-1,GETDATE()))
AND
YEAR(EventDateBegin)= YEAR(DATEADD(MM,-1,GETDATE()))
)
GROUP BY MtgeBroker

View 1 Replies View Related

What's Wrong With This Query?

Mar 15, 2006

Hi all!

I am trying to set up a poll in ASP.NET 2.0, using SQL database. My
problem is that it won't update the database. This is what it looks
like:

(master)
<asp:radiobuttonlist id="vmpoll"
                                    
runat="server"
                                    
OnSelectedIndexChanged="poll_SelectedIndexChanged">
                   
<asp:listitem>Object1</asp:listitem>
                   
<asp:listitem>Object2</asp:listitem>
                   
<asp:listitem>Object3</asp:listitem>
                   
<asp:listitem>Object4</asp:listitem>
                   
<asp:listitem>Object5</asp:listitem>
</asp:radiobuttonlist>
                <br />
               
<asp:Button ID="btnPoll" runat="server" CommandName="update"
Text="Rösta" />

(master.cs)
protected void poll_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (vmpoll.SelectedValue != "")
        {
           
string ConnectionString =
System.Web.Configuration.WebConfigurationManager.ConnectionStrings["blogg"].ConnectionString;
            SqlConnection con = new SqlConnection(ConnectionString);

           
string sql_update = "UPDATE poll SET votes = votes + 1 WHERE lagnamn =
'" + vmpoll.SelectedValue + "' LIMIT 1;";
            Label1.Text = "Du har röstat pÃ¥ " + vmpoll.SelectedValue;
        }
        else
        {
            Label1.Text = "Inget värde valt";
        }
    }

Finally, the database has a table called poll, including the fields
int      lagID Pk
string lagnamn
int     votes

What happens if I push the Rösta(Vote)-button is that it properly displays the text in it's label, but
it won't update the database. I hope someone will see where I am going wrong here.

Thanks in advance!!

View 5 Replies View Related

What Is Wrong With This Query

Apr 2, 2006

What is wrong with this query? How can may it work?
 
UPDATE T1 INNER JOIN T2 ON T1.cID = T2.cID SET T1.PSVP = T2.PSVP, T1.PSVR = T2.PSVR
WHERE (((T1.cID)=123))
 
 

View 1 Replies View Related

Getting Wrong ID On Query

Sep 2, 2006

I have a vb app that accesses a data base of participants who are in the db in primarily two tables, a "Roster" table and a "grades" table. This is designed to be able to track their grades from year-to-year. The "Roster" table has their first name, last name, and gender and the "Grade" table has a grade for them for each year that they have been in the database. Here is the problem that I have having:

If I have two different people with the same name and gender on the same team and I call them up to list them in a list box it shows the correct name(s) and grades (even if their grades are different) but shows the same unique id for both of them. It basically shows the id that is associated with the first of the two participants.

For instance, this code:

sql = "SELECT r.RosterID, r.FirstName, r.LastName, r.Gender, g.Grade" & sGradeYear & " FROM Roster r INNER JOIN Grades g "
sql = sql & "ON r.RosterID = g.RosterID WHERE r.TeamsID = " & lTeamID & " AND r.Archive = 'n' ORDER BY r.LastName, r.FirstName"
Set rs = conn.Execute(sql)
Do While Not rs.EOF
lstRoster.AddItem rs(0).Value & "-" & Replace(rs(2).Value, "''", "'") & ", " & rs(1).Value & " (" & rs(3).Value & ", " & rs(4).Value & ")"
rs.MoveNext
Loop
Set rs = Nothing


could show the following:
2441-John Doe (10)
2441-John Doe (11)
if there were two John Doe's on this team where one was in grade 11 and the other in grade 10. I assume there is something wrong with my join but it puzzles me that it lists both of them with the correct ages but only the first RosterID.

I appreciate any help you can give me!

Thanks~

View 1 Replies View Related

Wrong With Query!

Feb 5, 2004

select DATEPART(YEAR,DATE_CUST_INVOICE), PREPAID_COLLECT_FLAG, COUNT OF RECORDS
, SUM(inv_numb, freight_val)
FROM OPCSAHH
WHERE DATEPART(YEAR, DATE_CUST_INVOICE) > = '2000' AND
LESS THEN = 2003
AND PREPAID COLLECT FLAG = 'C'




What could be wron with this query. I must be overlooking something. I keep getting errors for the count of records, sum, and Less Then.

anyone know?

View 7 Replies View Related

What's Wrong With This Query?

May 19, 2008

THe following query generates, Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ',' errors.

Can anyone tell me what I am missing?

EXECUTE sp_MSforeachtable '
select if(COL_LENGTH(''?'',''rn_create_user'') > 0, rn_create_user, rn_create_user)
from ? as o
where
not exists(select * from users u
where (u.users_id = o.rn_create_user)
)';

View 3 Replies View Related

Wat's Wrong In The Query

Mar 27, 2007

insert into o_country(cname) values(select cname from countries where cid=5)

o_country
----------
Cid int(primary key)
Cname varchar(50)

countries
----------
Cid int(primary key)
Cname varchar(50)


Malathi Rao

View 1 Replies View Related

Where Is The Wrong In My Query

Jun 22, 2007

Dear experts,
please let me know where is the mistake in my query

select distinct (select COLUMN003 from TABLE021 where COLUMN001 = t.COLUMN001)

+ '(' +(select COLUMN002 from TABLE021 where COLUMN001 = t.COLUMN001)+ ')' as tablename

(select COLUMN003 from CATABLE009 where COLUMN001 = t.COLUMN003) modulename from TABLE022 t order by tablename

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 4 Replies View Related

What's Wrong In This Query ?

May 24, 2006

Hi. I have a SP named, for instance, SP1.I need to execute something likeSELECT Sum([Field1]) FROM SP1 WHERE [SP1].[Field1]='0'and I get the message:Server: Msg 208, Level 16, State 3, Line 1Invalid object name 'SP1'.However, SP1 *IS THERE* and runs fine !!!ThanksAlex

View 4 Replies View Related

Anything Wrong With This Query?

Jul 20, 2005

For a given table, I want to know all the columns that are included inan index. I have looked on the web and come up with this, which seemsto work, but just wanted some verification. Are there any reasons whyI should be using the metadata functions like OBJECT_NAME?ThanksBruceSELECTDISTINCT c.namefrom sysusers u,sysobjects o,syscolumns c,sysindexes i,sysindexkeys kWHERE o.uid = u.uidAND u.name = userAND o.name = 'ing_customer'AND o.id = i.idAND i.indid = k.indidAND OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0AND 1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics' ) ,INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,INDEXPROPERTY( i.id , i.name , 'IsHypothetical' ) )AND i.indid BETWEEN 1 And 250AND k.id = o.idand k.colid = c.colidand c.id = o.idORDER BY c.name

View 1 Replies View Related

What's Wrong With My Query

Dec 13, 2007

SELECT T1.lnumber AS LOT_Number,(T2.time_out - T1.time_in) AS Duration,
((DATEPART (hour, (T2.time_out-T1.time_in)) *60) + DATEPART (minute, (T2.time_out-T1.time_in))) AS Minutes,
COUNT(DISTINCT T2.test_desc) AS Number_of_Process

FROM Results AS T1 JOIN Results AS T2 ON T1.lnumber = T2.lnumber

WHERE T2.test_desc = 'Shipping' AND T1.test_desc = 'Receiving'


I have an error message that says:

Msg 8120, Level 16, State 1, Line 3
Column 'Results.lnumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


If I take this line out: COUNT(DISTINCT T2.test_desc) AS Number_of_Process
The query runs ok.

Also if I run [COUNT(DISTINCT T2.test_desc) AS Number_of_Process] in:

SELECT COUNT(DISTINCT T2.test_desc) AS Number_of_Process, T2.lnumber AS Lot_number
FROM Results AS T1 JOIN Results AS T2 ON T1.lnumber = T2.lnumber
GROUP BY T2.lnumber

It will also give me result.
Please help....
Thanks for your time

View 10 Replies View Related

What's Wrong In The Query

Mar 1, 2006

It can run in the sql server

select Id,Name from (select Id from TableB)

but it cannot run in the sql server mobile?

HOw do I ? Thank youï¼?

View 3 Replies View Related

What's Wrong With This Query?

Mar 6, 2008

plz help all experts.


select a.service_num, a.cust_num
from
table1 a,
table2 b
where
a.service_num = b.service_num
and b.cust_num = b.cust_num
and datediff(day, a.compl_date, b.compl_date) between case when month(getdate()+7) - month(getdate()) != 0 then -7 and 7 else -30 and 30 end
and a.qty+b.qty=0


it gives me an error message as follow:

Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'and'.


actually what i am trying to do is if the date of executing this query is the last week of each month, then the datediff between a.compl_date and b.compl_date is 30 days, if not then the datdiff is 7 days.

thanks in advance!

View 5 Replies View Related







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