Database Discussion Boards

Re: is it possible??

Colin Angus Mackay22-Jan-06 23:30

Colin Angus Mackay

22-Jan-06 23:30

imsathy wrote:
muliple option from a drop down

A drop down only permits one option to be selected at a time.

imsathy wrote:
to the stored procedure as string

string value = MyDropDownList.SelectedValue;
SqlCommand cmd = new SqlCommand(myConnection, "StoredProc");
cmd.CommandType = CommandType.StoredProcdure;
cmd.Parameters.Add("@paramName", value);
SqlDataReader reader = cmd.ExecuteReader(); // Assumes connection is open.

imsathy wrote:
then pass the values to the conditional clause of the procedure

For example:

CREATE PROCEDURE dbo.StoredProc
@paramName varchar(100)
AS
SELECT * FROM SomeTable
WHERE someColumn = @paramName
GO

Does this help?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: is it possible??

imsathy22-Jan-06 23:43

imsathy

22-Jan-06 23:43

Select Products

im filling this dropdown dynamically thro xml and js

i want the multiple selected values from thus drop to be a comma separated string and pass it as a parameter to a stored procedure..

and is there a way to pass these multiple values to the where clause of the sp

here how to give the multiple values of the serial nos from the string of the sp to this query

SELECT
...
...
...
WHERE serial_number =@serialNo

sathy

Re: is it possible??

Paul Conrad23-Jan-06 19:21

Paul Conrad

23-Jan-06 19:21

Are you required to use a combobox dropdown? Why I ask is because listboxes can do multiple selections.

PJC

Re: is it possible??

imsathy23-Jan-06 22:30

imsathy

23-Jan-06 22:30

thanks guru but my requirement is for tht only ....

i have sorted tht out ..

Smile | :)

sathy

Re: is it possible??

Paul Conrad24-Jan-06 3:29

Paul Conrad

24-Jan-06 3:29

Glad you sorted that out.

Have a good day,

Paul

T-SQL Problem

WDI22-Jan-06 21:35

WDI

22-Jan-06 21:35

Hi guys
I have a problem:

I need to execute a command text in a stored procedure

begin
.
.
.
declare @sql nvarchar(1000)
set @sql = '
decalre @v1 int
declare @v2 int
...
'

exec(@sql)

end

i need to get value of @v1 and @v2 after exec(@sql)
without using temporary table.

Re: T-SQL Problem

Colin Angus Mackay22-Jan-06 21:54

Colin Angus Mackay

22-Jan-06 21:54

WDI wrote:
without using temporary table.

@v1 and @v2 exist only within the scope of the EXEC statement, in order to get them out you need to SELECT them and have the EXEC be the source of data for an INSERT statement (normally into a temporary table - although it could be a permanent table).

Why are you not wanting to use a temp table?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: T-SQL Problem

WDI22-Jan-06 21:58

WDI

22-Jan-06 21:58

cos I need to execute stored procedure more than 1000 time and i am worry about cpu and memory usage!

Re: T-SQL Problem

Colin Angus Mackay22-Jan-06 22:10

Colin Angus Mackay

22-Jan-06 22:10

WDI wrote:
I need to execute stored procedure more than 1000 time and i am worry about cpu and memory usage!

If you are worried about performance then why are you using dynamic statements in the first place?

Here is a tip: Develop the software first in a way that works and is maintanable - then seek out the bottlenecks (the areas where poor performance is a problem) and fix them. Putting performance up front will lead to poorly designed applications that will be difficult to maintain.

That said, another solution might be for your dynamic SQL to generate a stored procedure (assuming it has the rights to do that) with @v1 and @v2 as OUTPUT parameters. The dynamic SQL creates the stored procedure, and your non-dynamic SQL will execute it, retreiving the values and then DROP the stored procedure. However, the stored procedure created would need a unique name so calling it may be a problem. And, I don't know the performance impact of creating and dropping stored procedures 1000 times - it would probably be worst that the temp table solution.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

SQLDMO for SQL Server 2000

usernamed22-Jan-06 13:29

usernamed

22-Jan-06 13:29

Firstly, I apologise if this is the wrong group to post in, my problem involves technologies covered in three different groups. C# is the language, .NET 2.0 is the platform, SQL Server is the DBMS.

I'm developing on my local machine and have a named instance of SQL Server running. I'm trying to connect to the instance of SQL Server using SQLDMO (because I want the connection to return a list of all the databases available on that server, and SQLDMO seems the best way of doing it)

My user account is set up as a trusted connection via Enterprise Manager, but I'm unable to log onto the server. The app returns an error message of:

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'foo'

The code in question looks like:

SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();<br />
srv.Connect(this.cmbServer.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);

I have also tried connecting without providing username or password details, but have had the same result. Any ideas?

Re: SQLDMO for SQL Server 2000

Colin Angus Mackay22-Jan-06 20:25

Colin Angus Mackay

22-Jan-06 20:25

usernamed wrote:
srv.Connect(this.cmbServer.SelectedItem.ToString(),this.txtUser.Text,this.txtPassword.Text);

If you are using Windows Authentication why are you passing a user name and password? With Windows Authentication your credentials are passed automatically, this is sending a specific username and password which SQL Server will interpret as being for a SQL Server Account (not a windows account).

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: SQLDMO for SQL Server 2000

usernamed22-Jan-06 23:38

usernamed

22-Jan-06 23:38

Hi Colin,

Thanks for responding. I should have written in my initial post that I'd tried not passing a username or password, but was getting the following error:

No overload for method 'Connect' takes '1' arguments(CS1501)

As such, I thought that the Connect Method forced me to put in a username and password, even if they shouldn't be required.

Problem with SQL statement

Ahmed Al-Lakani22-Jan-06 11:45

Ahmed Al-Lakani

22-Jan-06 11:45

Hi,

I’m trying to run the following SQL statement against Oracle, but it fails
It generate the following error message
“missing double quote in identifier”

The code :

stBuilder = new StringBuilder();<br />
stBuilder.Append("UPDATE TABLE ");<br />
stBuilder.Append("SET COL_NAME = '{0}' , ");<br />
stBuilder.Append("COL_ADRESS = '{1}' ");<br />
stBuilder.Append("WHERE ");<br />
stBuilder.Append("COL_ID = '{2}' ");<br />
<br />
stUpdate = String.Format(stBuilder.ToString(), <br />
drRow["COL_NAME"].ToString(),<br />
drRow["COL_ADRESS"].ToString(),<br />
drRow["COL_ID"].ToString());

thanks Much

Lakani

Re: Problem with SQL statement

Colin Angus Mackay22-Jan-06 20:21

Colin Angus Mackay

22-Jan-06 20:21

You are injecting values directly into the SQL statement. This will make your application vulnerable to a SQL Injection Attack. You should learn how to prevent that. See SQL Injection Attack and Tips on How to Prevent Them.[^] (Although this article is aimed as SQL Server 2000 - the advice it gives is applicable to all database systems, includint Oracle)

My guess is that the error is caused by you blindly injecting values into the SQL statement. Values which may need some characters to be escaped in order to be interpreted correctly. The above article will also help to correct that error.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: Problem with SQL statement

Ahmed Al-Lakani23-Jan-06 2:49

Ahmed Al-Lakani

23-Jan-06 2:49

hello ColinMackay.net,
Well this article didn’t resolve my issue,
Anyway, I’ve discovered what its all about,

The data was having special characters in it,
Ex : COL_NAME = “Baryy’@123”

So I did something like this

stUpdate = String.Format(stBuilder.ToString(),
drRow["COL_NAME"].ToString().Replace("'", "''"),
drRow["COL_ADRESS"].ToString().Replace("'", "''"),
drRow["COL_ID"].ToString());

Thanks ColinMackay.net for your help,

Re: Problem with SQL statement

Colin Angus Mackay23-Jan-06 3:31

Colin Angus Mackay

23-Jan-06 3:31

Ahmed El-Lakani wrote:
Well this article didn’t resolve my issue,
Anyway, I’ve discovered what its all about,

I suggested it because your code had serious security issues that someone could use to attack your database. No bloody wonder the media is full of reports of security problems in software if software developers, when having their security holes pointed out to them do nothing about it!

Also, as I said the article was geared towards SQL Server but the advice is still valid and sound for Oracle, or any other database system. Using parameterised queries would have solved your problem.

Ahmed El-Lakani wrote:
So I did something like this

If you took the general advice in the article it would have solved your problem and the security issue in one. Please, please, please learn how to use PARAMETARIZED QUERIES in Oracle.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: Problem with SQL statement

Ahmed Al-Lakani23-Jan-06 5:21

Ahmed Al-Lakani

23-Jan-06 5:21

Thanks Much ColinMackay.net,

I know your concerns well, and me and my Company really crazy about security holes we get in, because of careless code,

But Unfortunately I’m tide to our customer database, we are not allowed to modify it Frown | :(

Thanks Much for your help,

Re: Problem with SQL statement

Colin Angus Mackay23-Jan-06 7:00

Colin Angus Mackay

23-Jan-06 7:00

Ahmed El-Lakani wrote:
But Unfortunately I’m tide to our customer database, we are not allowed to modify it

That is not an excuse. Oracle support parametarised queries so you can use them. A quick search on the web for Paramerarized query oracle .NET[^] returns a number of results about how to use paramerarized queries (and therefore follow the advice in the article I linked to earlier)

On the first page of results is Securing a .NET application on the Oracle Database[^] written by Oracle themselves. In it there is a section on "SQL Injection Attacks" which shows you how to use OracleParameter object in .NET

Does this help?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

date error in sql server

md_refay22-Jan-06 4:18

md_refay

22-Jan-06 4:18

hi i create school database and i make sp to insert employees information but
i can't insert date i want to know how to write the dates in sql server
like #1/1/2005# or '1/1/2005' or what

second

i want to modify the date apperance when i reterive it from table instead of
mdy it display ion dmy
answer me quickly thanks

ma_refay

Re: date error in sql server

Colin Angus Mackay22-Jan-06 20:17

Colin Angus Mackay

22-Jan-06 20:17

SQL Server can accept dates in various formats, but for consistency and locale neutrality I always use ISO Format - 'YYYY-MM-DD'

ma_refay wrote:
i want to modify the date apperance when i reterive it from table instead of
mdy it display ion dmy

Retrieve it to where? In .NET applications the date is retrieved into a DateTime object which will display it in what ever format you like.

It is a poor design descision to get the database to perform UI functions (such as rendering the date in a specific format).

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

CManualAccessor - No rows if using properties

Guybrush21-Jan-06 6:12

Guybrush

21-Jan-06 6:12

Hello all, I have been looking everywhere for a solution to this, but I couldn't find it.
I am trying to access a database using OLEDB Consumer Templates (of which I have little experience) in a WTL project.
Ok, the problem is that when I open a CCommand object passing a DBPropSet structure I get no rows in return.

Let me try to explain with the code..

With the following code everything works fine. I get columns and rows correctly (I removed a lot of stuff such as error handling, to simplify). The code it’s more or less the same used in the MSDN sample DBViewer:

<code>
CCommand<CManualAccessor> oCmd;
oCmd.Open(m_session, sSQL, NULL, &nRows, DBGUID_DEFAULT, FALSE)

if (oCmd.m_spRowset == NULL)
{
//ERROR STUFF
}

if (oCmd.GetColumnInfo(&ulColumns, &pColumnInfo, &pStrings) != S_OK)
ThrowOleDBError(oCmd.m_spRowset, IID_IColumnsInfo);
struct MYBIND* pBind = new MYBIND[ulColumns];

oCmd.CreateAccessor(ulColumns, &pBind[0], sizeof(MYBIND)*ulColumns);
for (ULONG l = 0; l < ulColumns; l++)
oCmd.AddBindEntry(l + 1, DBTYPE_STR, sizeof(TCHAR)*255, &pBind[l].szValue, NULL, &pBind[l].dwStatus);

oCmd.Bind();

ULONG ulFields = oCmd.GetColumnCount();

while(oCmd.MoveNext() == S_OK)
{
for (ULONG j = 1; j <= ulFields; j++)
{
lpszString = pBind[j-1].szValue;
// PRINTING STUFF
}
}
</code>

Now, if I add properties to the command using this:

<code>
CDBPropSet pset( DBPROPSET_ROWSET );
pset.AddProperty(DBPROP_IRowsetView, true);
pset.AddProperty(DBPROP_IRowsetScroll, true);
pset.AddProperty(DBPROP_IRowsetChange, true);
pset.AddProperty(DBPROP_UPDATABILITY,
DBPROPVAL_UP_INSERT | DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_DELETE);
pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );

/*…*/

oCmd.Open(m_session, sSQL, &pset, &nRows, DBGUID_DEFAULT, FALSE)

/*…*/
</code>

I get the columns shifted right (like if there is an empty column before all the others), and no records.
The m_spRowset member however it’s not null, as I check for it after the call to Open()

Can you please help me out, or at least tell me why this happens?

Thank you very much!

Guybrush Threepwood Smile | :)

-- modified at 12:14 Saturday 21st January, 2006

Connect to DB through proxy?

veljkoz21-Jan-06 5:15

veljkoz

21-Jan-06 5:15

The application is running behind a proxy server which is needed for internet access. The database needed for the application is on a remote server.

How can I make a connection to the database through this proxy?

The browser (IE) that is set up for proxy has access to the internet that way.

When I try the application from a computer that doesn't use proxy, it's working just fine.

I've tried something like:

<br />
System.Net.GlobalProxySelection.Select = New System.Net.WebProxy("proxy.xxxxx.xx.xx.xx", 8080)<br />
Dim conn As SqlConnection = New SqlConnection("server=xxxx;uid=testdata;pwd=sa;database=testdb")<br />
conn.Open()

but, it doesn't work...

Thank you in advance!
Veljko

Northwind

TheEagle21-Jan-06 4:53

TheEagle

21-Jan-06 4:53

Hi..
I have installed SQL server 2000 personal edition but i couldnt find the Northwind database how could i install it and where could i find it?

Re: Northwind

Dave Kreskowiak21-Jan-06 18:07

Dave Kreskowiak

21-Jan-06 18:07

Did you try looking on the front page of Microsoft's SQL Server 2000 Downloads[^] site?

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

-- modified at 0:08 Sunday 22nd January, 2006

Re: Northwind

TheEagle21-Jan-06 21:02

TheEagle

21-Jan-06 21:02

You are right i found them there..
I needed those samples to understand my SQL books lessons
thank you very very much Smile | :)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.