|
Yes, but it is an awkward solution.
You need to insert the result of the EXEC(@sqlQuery) into a temporary table, then get the value from the table
INSERT #MyTempTable EXEC(@sqlQuery);
SELECT @TempColumnValue = MyTempColumn FROM #MyTempTable
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
hi colinmackay
thanx for ur support1
sandeep
|
|
|
|
|
I am looking for suggestions on how to handle customer defined data. I currently have a database with a fixed number of rows for my standard data. However, I have the need to allow a customer to be able to add additional fields of data to the database for their own data. The three methods I have come up with so far are:
1. Have the ability to alter the table via SQL ALTER to add new fields as needed. I dont really like this method because the app will be modifying a database structure.
2. Create X number of extra fields and map the new fields/data to the already reserved data fields. For eample the customer adds field1 then the app maps field1 to AddField1 that already exists in the database. However, you would only be able to add as many fields as there are existing reserved fields.
3. Add a table that contains a id, field, and value column. For each record that uses the extended field, a new record will be inserted into this table that will contain a key id back to the main record, a field name, and the value for the field. This would allow an endless number of extra fields to be added.
Any suggestions/comments/examples would be greatly appreciated.
|
|
|
|
|
The third solution is best. i have implemented it, in some problems and the project are working fine.
|
|
|
|
|
Hi,
I'm new to Visual C++ database programing.
I'm using ODBC cursor to connect to an Access database *.mdb file.
retcode = SQLSetStmtAttr(hstmt1, SQL_ATTR_CURSOR_SCROLLABLE,SQLPOINTER)SQL_SCROLLABLE, SQL_IS_INTEGER);
I used the above line of code to make the cursor scrollable to I can do SQLFetchScroll to go backward and forward.
However I got the following error message: Optional feature not implemented.
Please help.
Thank you very much
nhuythanh@gmail.com
|
|
|
|
|
I am running Windows XP Pro with Service Pack 2, Visual Studio 2005, Microsoft SQL Server 2005 and Microsoft SQL Server Express Edition.
I have a file called EmployeeDirectoryOle.aspx in which I used a Repeater Control to display all the Employee records within the Employee table of the pfsnicom.mdb Microsoft Access file. I dont have a problem displaying records with the Repeater Control.
Another file, the EmployeeDirectorySql.aspx, contains the same codes (but different Namespace of course) connects and reads from SQL Database File. Whenever, I test query SELECT * FROM Employees under my 'Configure Data Source' window, it's fine. However, when I try to run this file, I get the error below:
" An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
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: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
How do I resolve this problem?
|
|
|
|
|
tanpanjang wrote: How do I resolve this problem?
If your SQL Server 2005 is installed on a separate machine then allow TCP/IP connections.
Open the SQL Server 2005 Surface Area Configuration. Navigate to the instance of SQL Server that you are interested in. In Database Engine click on Connections and select Allow Remote connections.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Is my correct?
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<title>The Dorknozzle.com Intranet Site</title>
<script runat="server" language="C#">
void Page_Load(){
SqlConnection objConn;
SqlCommand objCmd;
SqlDataReader objRdr;
objConn = new SqlConnection(
"Server=COMPUTERNAME\\NETSDK;" +
"Database=pfsnicom.mdf");
objCmd = new SqlCommand("SELECT * FROM Employees", objConn);
objConn.Open();
objRdr = objCmd.ExecuteReader();
rpcEmpDirectory.DataSource = objRdr;
rpcEmpDirectory.DataBind();
objRdr.Close();
objConn.Close();
}
|
|
|
|
|
tanpanjang wrote: objConn = new SqlConnection(
"Server=COMPUTERNAME\\NETSDK;" +
"Database=pfsnicom.mdf");
Don't specify the name of the MDF file. Specify the name of the database - SQL Server should already know about it.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Colin Angus Mackay wrote: Don't specify the name of the MDF file. Specify the name of the database - SQL Server should already know about it.
Still doesnt work... Changed the .mdf, removed the NETSDK, put back the NETSDK, etc. Still the same error.
There are a lot of people who ended up doing things wrongly, even if they had the best of intentions at the beginning. Then again, there is no guarantee the words they said were conveyed as they originally were intended to be. That's because those on the receiving end only hear what they want to hear.
|
|
|
|
|
Colin Angus Mackay wrote: If your SQL Server 2005 is installed on a separate machine then allow TCP/IP connections.
Open the SQL Server 2005 Surface Area Configuration. Navigate to the instance of SQL Server that you are interested in. In Database Engine click on Connections and select Allow Remote connections.
I tried but it didnt work.
SQL Server 2005 Surface Area Configuration --> Services and Connections --> (Under SQLEXPRESS -> DATABASE ENGINE -> REMOTE CONNECTIONS) Local and remote connections --> Using both TCP/IP and named pipes --> Apply --> OK.
Service --> Stop --> Start --> Apply --> OK.
Could it possibly be something wrong with my script? rpcEmpDirectory is my Repeater control.
|
|
|
|
|
I am having some difficulties getting my report to work properly. I am connected to an Access database. If i have all my links as inner joins then the report will show up but incorrectly. I asked another person about the problem and he said to change to outer left joins. I did that and i get "Query Engine Error" and it shows the path to the report. I basically need to show some information about a customer and their transactions. The problem is that when there are no transactions then no customer information shows up at all. In this case i would like to show the customer name, id, phone number etc without the transactions. The user would have to be able to specify which customers to show either by specifing a range of last names or a range of Customer ID's. Any help would be appreciated.
|
|
|
|
|
hi,
i want to fetch a data from the table which i have to give dynamically in my stored procedure
The thing I m stucked on is
---declaring a variable to hold table name
declare @TableName nvarchar(50)
--here i m setting the tablename for example my actual situation it will come from . result of queries prior to this
@Tablename='Annotationproperty'
--Select particular column from particular table for this row
select propertyID from @TableName where UserID=1
It gives an error that Must declare the table variable "@TableName"
So how I can give a dynamic table name in my sql query or is there some alternate way for this.
waiting ur replies
thanx
sandeep
|
|
|
|
|
As you have found, you cannot use a parameter as a table name. You need to build a dynamic SQL string and execute it.
You can do this:
DECLARE @sql nvarchar(4000);
SET @sql = 'SELECT * FROM ['+@TableName+']';
EXEC(@aSQLstring)
So, you need to build a string containing the whole SQL Statement and then execute it. However, be aware that this is prone to mallicious attack if you are not careful. Always verify that the contents of @TableName actually contain the name of a table in your database before performing an EXEC(@sql) by doing something like this
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName)
BEGIN
-- Put your code here
END
ELSE
BEGIN
-- Put your error handling code here. The table does not exist!
END
Finally, declaring @TableName as varchar(50) is potentially going to cause errors. If you are holding the table name in the variable declare it at sysname or nvarchar(128), e.g.
DECLARE @TableName sysname
Does this help?
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
hi
Colin Angus Mackay
Can u please help me in my other problem too. I m doing as u told , now i want to store the result of the exec (@sqlQuery) into a variable @TempColumnValue of type nvarchar(100)
Note: In my case exec (@sqlQuery) always returns a single value
example:
declare @sqlquery varchar(100)
declare @TempColumnValue nvarchar(50)
set @sqlQuery = 'select @TempColumnValue = firstname from usertable where userid=1'
exec (@sqlQuery)
but this gives error 'Must declare the scalar variable "@TempColumnValue"'.
Is there any other way to get it done
waiting ur response!
thanx
sandeep
|
|
|
|
|
Yes, but it is an awkward solution.
You need to insert the result of the EXEC(@sqlQuery) into a temporary table, then get the value from the table
INSERT #MyTempTable EXEC(@sqlQuery);
SELECT @TempColumnValue = MyTempColumn FROM #MyTempTable
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hi.
I am jawwad Khan From Pakistan.
I am using visual basic.NET 2005
While programming the database we were used to use the Filter property of the Recordset to select the records satisfying certain criteria.
i.e
dim rs as recordset<br />
.....<br />
......<br />
rs.filter="CustomerID=" & textbox1.text
suppose we have a datagridview showing data from a certain dataset. How can we filter the dataset and show the data in the datagrid.
i am not using the datasource or any binding control.
i want every thing to be done through code.
![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
thank you
|
|
|
|
|
What is the code in VB.NEt to retrieve data from ms-access
Thanks
|
|
|
|
|
|
hi all,
I have created DB using SQL Server 2000.
In a Table there is autoNumber field.
Now I need to set a Value in that auto Number Field.
i.e-->I need to set 1 in that Field.
How do I do it using a query?
if any one know it pl kindly reply asap.
thanks in advance
|
|
|
|
|
Run this query in QA
insert into Customers(customerid ,companyname)values(@@identity,'Exensys')
"I find that the harder I work, the more luck I seem to have."
|
|
|
|
|
 I need to make UPdate stored procedure...
like this..
create proc update_user
(@user_name nvarchar ,@password nvarchar, @firstname nvarchar ,@lastname nvarchar)
update Users
set [password]=@password
(but if @password=empty string then keep no change in db column "password" )
and update other fields ..
thanks
|
|
|
|
|
Retrieving old password and keep it in a variable or session.
use the condition stmt in the asp.net. For eg:
if txtpass.text="" then
cmd.parameter.add("@password",session("pass"))
else
cmd.parameter.add("@password",txtpass.text)
end if
Hope this will help to solve your prob.
|
|
|
|
|
Simple way:
create procedure update_user
@user_name nvarchar(20),
@password nvarchar(20),
@firstname nvarchar(20),
@lastname nvarchar(20)
as
if @password is null or @password=''
update Users set firstname=@firstname, lastname=@lastname where user_name=@username
else
update Users set firstname=@firstname, lastname=@lastname, password=@password where user_name=@username
|
|
|
|
|
if @password = '' set @password = null
update Users
set [password] = isnull(@password, [password])
etc.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
