|
I had got the required output simply writing the sql query but I wanted it on stored procedure. I know i can do it something like this in my query.
string colname;
int ID;
string query;
.
.
.
query = "select " + colname + " from ItemList where ID = " + ID ;
SqlCommand cmd = new SqlCommand(query, sqlconn);
.
.
.
.
This had solved my problem but I wanted it on stored procedure. and I was stuck giving the column name itself as input parameter.
suchita
|
|
|
|
|
I can only point to jschells' comment 
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks Eddy for your time and views.
suchita
|
|
|
|
|
Dhyanga wrote: I don't know whelther we can send fieldname as input parameter
You can't.
Your choices are
1. Create the SQL dynamically in C# and then execute it.
2. Create the SQL dynamically in SQL and then execute it in SQL.
3. Use a fixed set of fixed SQL statements and select one based on the data passed in.
|
|
|
|
|
Thanks Jschell.. Just curious if that can be possible. If not, i can just execute it without using stored procedure. I was trying to use better way than that if I could...
suchita
|
|
|
|
|
Dhyanga wrote: i can just execute it without using stored procedure
My solutions work with or without stored procs.
|
|
|
|
|
Hi,
try like this..
CREATE PROCEDURE [dbo].[SelectItemList]
@ColName string,
@ID int
AS
BEGIN TRANSACTION
SELECT @ColName = ColumnName FROM ItemList where ID = @ID
COMMIT TRANSACTION
Karthik Harve
|
|
|
|
|
Am I missing something, you need dynamic SQL to meet this requirement, you solution does not work!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You have to use sp_executesql and construct the query string yourself. Do realize that you can open yourself up to SQL injection attacks if you don't properly test the variables.
CREATE PROCEDURE SelectItemList
(
@ColName string,
@ID int
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql = N'SELECT ' + @ColName + N' FROM ItemList where ID = ' + CAST(@ID AS VARCHAR(10))
EXEC sp_executesql @sql
|
|
|
|
|
Thank you Micheal. That's what I wanted but we need to give the @ColName nvarchar size too. It worked fantastic.
suchita
|
|
|
|
|
|
Keep in mind that as formatted that solution is open to SQL injection attacks.
|
|
|
|
|
Hi All,
How we make the .mdf file of a database ?
I want a script or C# code that could be used in a utility in C#.Net.
Please help !!
Regards,
Lalit
modified 27-Jun-12 4:58am.
|
|
|
|
|
lalitN wrote:
How we make the .mdf file of a database ?
That extension is used both for Sql Server as well as Microsoft Access. Which are you targetting?
Bastard Programmer from Hell
|
|
|
|
|
I am using SQL Server 2008 R2.
|
|
|
|
|
The command to create a database is documented here[^], examples are at the bottom of the page.
Bastard Programmer from Hell
|
|
|
|
|
Hi,
Can we create the mdf file by taking the backup of one existing database ?
Regrds,
Lalit
|
|
|
|
|
Yes. Backup the existing database, and restore[^] it. Keep in mind that this procedure will convert "older" database versions (backups from 2005) to the format of your server (2008R2).
Bastard Programmer from Hell
|
|
|
|
|
Hi, everyone, I'm using sqlite 3.6.22 in an ARM-linux based application, and I was annoyed a lot by a memory-leak problem.
this is the problem:
sqlite3_free_table(char **result) doesn't exactly release the memory allocated by sqlite3_get_table function.
To verify it I made it called dirrectly after sqlite3_get_table() function, but I can still access the azResult parameter.
bellow is part of my code:
sqlite3 *db;
char *sqlcmd;
int nrow ;
int ncolumn ;
char **azResult;
char *zErr;
int rc;
rc = sqlite3_open("/usr/YY_AEACard.db", &db);
if (rc) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return -1 ;
}
sqlcmd = sqlite3_mprintf("select CardID,CardType,BgnDate,EndDate,ValidTimes,CardRight,CardFlag, EnabledPorts,LastAccessPort,CardPsw from tb_CardInfo where CardID=%d and EnabledPorts = %d\n",card->CardID, nPort );
sqlite3_get_table( db, sqlcmd, &azResult, &nrow, &ncolumn, &zErr );
sqlite3_free( sqlcmd );
///////////////////////////////////////////////////////////
sqlite3_free_table(azResult);
///////////////////////////////////////////////////////////
// it still works when i do this here.
if (rc != SQLITE_OK) {
if (zErr != NULL) {
fprintf(stderr, "SQL error: %s\n", zErr);
sqlite3_free(zErr);
}
}
if(nrow <= 0)
{
printf("No data from db!\n");
return -1;
}
if(ncolumn < 9)
{
printf("Columns are not enough for card table!\n");
return -1;
}
bzero(card, sizeof(card));
card->CardID = atoi(azResult[ncolumn+0]);
card->CardType = atoi(azResult[ncolumn+1]);
strcpy(card->BgnDate, azResult[ncolumn+2]);
strcpy(card->EndDate, azResult[ncolumn+3]);
card->ValidTimes = atoi(azResult[ncolumn+4]);
card->CardRight = atoi(azResult[ncolumn+5]);
card->CardFlag = atoi(azResult[ncolumn+6]);
card->EnabledPorts = atoi(azResult[ncolumn+7]);
card->LastAccessPort = atoi(azResult[ncolumn+8]);
strcpy(card->CardPsw, azResult[ncolumn+9]);
sqlite3_free_table(azResult);
//this is a test for the memory leak
printf("%s\n", azResult[4]);
//funny enough, it still works here.
sqlite3_close(db);
return 0;
please help me, thank you.
|
|
|
|
|
There is no guarantee that the memory pointed to is overwritten when it is released. In my experience, only a very few debugging memory managers bother to overwrite released memory blocks. So what you observe is not evidence of a memory leak. Your system may have some functions to query the memory manager for such things as the total allocated memory size.
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
Thank you Peter, we usually meet a "Segment fault" when we access to the memory released, and further more, the memory of my application keeps expanding with the size of the data loaded from database, never the end.
|
|
|
|
|
My guess is that sqlite is keeping various things in memory as long as you have the database open. Is all the memory freed when you close the database(s)? Or, at least, does the memory usage stop increasing when you close and reopen the database?
Peter
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
Yes, Peter, SQL Server manages its memory this way, I don't exactly know how sqlite does with it. but what ever, it shouldn't expand to crash the operation system.
|
|
|
|
|
by the way, i closed the database after each opration.
|
|
|
|
|
I'm trying to debug whether or not a SSRS app is leaving open sql connections. I wrote a test app that uses a custom SSRS reporting object (written in VB.net) that calls reports in a loop. Each time the reporting object is destroyed and recreated.
The server is 2008 R2 and the SQL server is 2008 R2. I have Performance Monitor open on the server with the following counters added in:
ReportServer:Service - Active Connections
SQLServer:General Statistics - User Connections
SSRS.ActiveConnections is displaying 97 current connections.
SQL.UserConnections is displaying 38 current connections.
If I open Management Studio and use the Activity Monitor, selecting all databases - there are exactly 38 connections. If I run sp_who2 and remove the lines that are for the master DB and sa account, there are again 38 connections (prior 64)
I'm very confused on the different #s being reported in the counters. Can anyone shed light into this? Is the ReportServer:Service - Active Connections actual DB connections or connections to the service? Should the SQL.UserConnections include SSRS report connections?
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
