|
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
|
|
|
|
|
I am not seasoned in databases in general, let alone in MS SQL Server administration in particular. I have installed MS SQL Server 2000 on my computer - authentication set to SQL Server and Windows. My problem is, that after I have designed my database tables in an Enterprise Manager the tables appear to be 'read only' when I access them in a Query Analyzer. It doesn't help to set 'enabled' to all of the existing permissions to my database and all its tables for my account, a public and a guest. I can't append a row neither in Query Analyzer, nor programmatically. Of course I can access the database itself and its tables as well. This applies to all my databases. I know I have overlooked something in SQL Server settings, but I haven't a clue what it is.
Can you give me a hint?
Regards,
Zdenek
|
|
|
|
|
Zdenek Navratil wrote: It doesn't help to set 'enabled' to all of the existing permissions
Including the DenyWriter permission?
It would appear to me that you are logging in with different accounts in Query Analyzer and Enterprise Manager.
|
|
|
|
|
I don't think so. I run both Query Analyzer and Enterprise Manager in the same account.
Regards,
Zdenek
|
|
|
|
|
So, you can modify data in Enterprise Manager, but not in Query Analyzer? If they are running in the same account this cannot happen.
|
|
|
|
|
Could anyone suggest me good books for Data modeling and RDBMS?
|
|
|
|
|
Handbook of Relational Database Design (Paperback)
by Candace C. Fleming, Barbara von Halle
Regards
RR
|
|
|
|
|
|
Besides buying books, there are good tutorials available from the big database makers - Microsoft, Oracle, IBM, MySQL and so on, and not to forget the various university resources.
Why not visit them. OR, google for then such as [^]
modified 1-Aug-19 21:02pm.
|
|
|
|