|
SQL Server Query Help (Trying to compare two tables and display the nonmatching records)otherwise delete data from one table after comparing it to another table 
|
|
|
|
|
A vague question deserves a vague answer:
SELECT
Table1_Id
FROM
Table1
WHERE
Table1_FK NOT IN (SELECT
Table2_Id
FROM
Table2)
|
|
|
|
|
Hello ,
Here is the SQL I am trying to run against our database.
*****************************************************
SELECT * FROM Part WHERE (Company_Abrv = 'TSBI')
AND (Catalog_PartType = 'Component')
AND (TypeName = 'Generic')
AND (Version = 'Space Holder')
AND (KeyComponentName = 'SPACER')
AND ( Part_Number IN (SELECT Part_Number FROM PartAttributes
WHERE (TypeName = 'Generic')
AND (Version = 'Space Holder')
AND (KeyComponentName = 'SPACER')
AND (AttributeName = 'Inside Diameter'
AND AttributeValue <= 18))
********************************************************
the last part of the WHERE clause AttributeValue is a Varchar type column. But as we are building the SQL query dynamically we also might get integer values at times. So our query may look like the above.
So in this case I am trying to convert the AttributeValue column as an int or decimal type when it has a numerical or a decimal value.
so I am trying to do it like 'convert(decimal,AttributeValue) <= 18' but it doesn't accept it.
could some one please help me with how do I do it?
Thanks
-Learner
|
|
|
|
|
cast(AttributeValue as decimal(20)) <= 18
|
|
|
|
|
Hi everybody,
Can anybody tell me the exact difference between
SqlCommand and SqlCommandBuilder.
Thanking you.
param
|
|
|
|
|
A Command object is used to execute scalar or non query commands to a database. You would set a command objects CommandText property to and sql statement that you want to run and then use it's ExecuteScalar or ExecuteNonQuery method to run it. Here is an example:
Dim conn As New SqlClient.SqlConnection(strMyConnectionString)
Dim cmd As New SqlClient.SqlCommand()
cmd.CommandText = "UPDATE myTable SET col1='my value'"
cmd.ExecuteNonQuery()
A CommandBuilder object is used to automatically create Update, Delete, and Insert SQL statements for you, based on a Select statament that you supply. You would declare a DataAdapter object, set it's SelectCommand.CommandText property to your Select SQL statement. Then when you declare a CommandBuilder object, you include the dataadapter in the CommandBuilder's constructor parameter and it will automatically create the other statements for you when you run a DataAdapter. Here is an example:
Dim conn As New SqlClient.SqlConnection(strMyConnectionString)
Dim cmd As New SqlClient.SqlCommand()
cmd.CommandText = "SELECT * FROM myTable"
Dim da As New SqlClient.SqlDataAdapter(cmd)
Dim cb As New SqlClient.SqlCommandBuilder(da)
da.Update(myDataTable)
If you hadn't of used a CommandBuilder, the program would have blown up on the da.Update(myDataTable) statement, because the adapter wouldn't have known how to insert new rows that had been added to the table, or delete rows that had been removed, etc.
|
|
|
|
|
An SQLCommand object can also be used to return query results. Under the covers, this is what happens in the DataAdapter - the command buider builds commandtext for the other sqlcommand instances used by the adapter, using the commandtext provided for the query or read command. SqlCommand instances can also be used to return query results as a datreader, which is like a fast forward-only cursor.
|
|
|
|
|
I'm not sure if I'm in the right forum, I'm looking at Categories and can't really find Reporting Services. I would like it if you can point me then I'll have this posted again. THanks!
Is it possible in Reporting services 2005 for parameter dependencies?
If I enter some values in Parameter1 and click 'View Reports',
I would like to inform user that Parameter2 cannot be left blank.
Thanks!
Gerri
|
|
|
|
|
Sorry, I have this sql statement where I supply the column as parameter
and then use it to retrieve the values of the specified column.
Instead result is column name =(
declare @sql varchar(200)
SELECT @sql = 'SELECT COLUMN_NAME AS Columns
FROM information_schema.columns
WHERE (TABLE_NAME = ''MyTable'') and
(Column_name = ''Column1'')'
exec ('select (' + @sql + ') from MyTable')
RESULT:
Columns
-------
Column1
Column1
Column1
Column1
Column1
Help Please! Thanks!
Gerri
|
|
|
|
|
If you are having complete sql statement in a variable then by using sp_executesql you can execute sql statement in that variable. 
Best Regards,
Apurva Kaushal
|
|
|
|
|
-- stored procedure
CREATE PROCEDURE returncolname @tablename varchar(255), @colname varchar(255)<br />
<br />
AS<br />
declare @tblname as varchar(255)<br />
set @tblname = @tablename <br />
declare @clnName as varchar(255)<br />
set @clnName = @colname<br />
<br />
SELECT @clnName AS [Columns] <br />
FROM information_schema.columns <br />
WHERE (TABLE_NAME = @tablename) and (Column_name =@colname )<br />
<br />
GO
-- execution of returncolname procedure
exec returncolname 'tablename','columname'
|
|
|
|
|
Sorry, I tried this and it returns Column instead of Values (of the Column).
Thank you. Help please
G
|
|
|
|
|
I usually write out the SQL Statement using place holders. In this case ?COLUMN_NAME? fills in for the variable that will be passed in. I then replace the place holders with using REPLACE. ** Be sure you read up on SQL Injection Attacks if you allow free form entry from the users.
This may not be as optimal as building the string on the fly but, it is much less error prone and much more maintainable.
CREATE PROCEDURE DynamicSqlTest
(
@columnName NVARCHAR(100)
)
AS
DECLARE @sql NVARCHAR(1000)
SET @sql =
N'SELECT COLUMN_NAME AS Columns
FROM information_schema.columns
WHERE (TABLE_NAME = ''MyTable'') and
(COLUMN_NAME = ''?COLUMN_NAME?'')'
SET @sql = REPLACE(@sql,'?COLUMN_NAME?',@columnName)
EXEC(@sql)
|
|
|
|
|
|
Hi all,
I'am using mysql database and vs 2003. While i'am updating a record i'am getting the following error
System.Data.DB.ConcurrencyException:Concurrency violation: the UpdateCommand affected 0 records
Can anyone has solution for this
thanks
Kishore
|
|
|
|
|
The exception that is thrown by the DataAdapter during an insert, update, or delete operation if the number of rows affected equals zero.
Best Regards,
Apurva Kaushal
|
|
|
|
|
yes. but i'am able to insert new rows, but while updating that row i'am getting this problem. hre is the code
Try
msgResult = MsgBox("Do you want to update the record", MsgBoxStyle.YesNo)
If msgResult = 6 Then
dsPrd.Tables("rddefbdg").Rows(currentRow)("bdgNo") = cmbBdgNo.SelectedItem
dsPrd.Tables("rddefbdg").Rows(currentRow)("SDate") = Format(Date.Parse(dtpicker.Text), "yyyy-MM-dd")
dsPrd.Tables("rddefbdg").Rows(currentRow)("BrPtHol") = Double.Parse(txtBdgPH.Text)
dsPrd.Tables("rddefbdg").Rows(currentRow)("BrSigns") = Integer.Parse(txtSigns.Text)
dsPrd.Tables("rddefbdg").Rows(currentRow)("BrGrdStn") = Integer.Parse(txtGS.Text)
dsPrd.Tables("rddefbdg").Rows(currentRow)("BrStmMaint") = Double.Parse(txtBtrMaint.Text)
dsPrd.Tables("rddefbdg").Rows(currentRow)("BrWtrDbrs") = Double.Parse(txtWtrWay.Text)
rdProgCmdBld = New OdbcCommandBuilder(dapPrdChk)
dapPrdChk.Update(dsPrd, "rddefbdg")
dsPrd.Tables("rddefbdg").AcceptChanges()
MessageBox.Show("Record successfully updated!")
End If
Catch ex As Exception
MsgBox(ex.ToString)
End Try
thanks
kishore
|
|
|
|
|
What would be happening is while updating it would not be getting any records to update. Check whether you are getting any record for update or not.
Best Regards,
Apurva Kaushal
|
|
|
|
|
how to get it can uyou please tell me
thanks and regards
kishore
|
|
|
|
|
Check the record which you are going to update i.e whether that record exits in the database or not.
Best Regards,
Apurva Kaushal
|
|
|
|
|
i kept record navigation in my form. i'am navigating trough the records and i'am selecting a record and updating it. so there is no question of record doesn't exists in database
thanks and regards
kishore
|
|
|
|
|
Then in this case you check the sql query which you are passing and see whether everything is correct with that.
Best Regards,
Apurva Kaushal
|
|
|
|
|
i'am not passing any queries. i'am using using commandbuilder. i'am giving the code
dsPrd - dataset; dapPrdChk-dataadapter; rdProgCmdBld -commandbuilder
dsPrd.Tables("tablename").Rows(currentRow)("col1") = Double.Prse(txtDrng.Text)
dsPrd.Tables("tablename").Rows(currentRow)("col2") = Double.Prse(txtDrng.Text)
.
.
.
rdProgCmdBld = New OdbcCommandBuilder(dapPrdChk)
dapPrdChk.Update(dsPrd, "tablename")
MsgBox(rdProgCmdBld.GetUpdateCommand.CommandText)
dsPrd.Tables("tablename").AcceptChanges()
when the execution comes to dapPrdChk.Update(dsPrd, "roaddefectsbridge")
then i'am getiing the error databse concurrency exception. i'am not able to get the update query also, because i can't pass the msgbox command before update
thanks and regards
kishore
|
|
|
|
|
Hi - Want to search for a word - in all the existing stored procs in SQL2005?
Is there a way we could do this?
Many thanks.
|
|
|
|
|
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE CHARINDEX('SearchWord', ROUTINE_DEFINITION) > 0
This searches only 1 database; to search all databases:
SELECT name FROM sysdatabases
and loop through each database, doing the first query each time.
Scott
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
