|
hi..
i have written code for inserting the records into the table but with it itself i need to do deleting and update of records.. how to include those things with in same code.. please help me..
code is
#include "stdafx.h"
#include <stdio.h>
#include <conio.h>
#include <windows.h>
#include "impfile.h"
int main(int argc, char* argv[])
{
HRESULT hr = S_OK;
try
{
CoInitialize(NULL);
// Define string variables.
_bstr_t strCnn("Provider=sqloledb;Data Source=(local);Initial Catalog=pubs;User ID=test;Password=test;");
//_bstr_t strCnn("Server=(local);Database=pubs;Trusted_Connection=Yes");
_RecordsetPtr pRstAuthors = NULL;
// Call Create instance to instantiate the Record set
hr = pRstAuthors.CreateInstance(__uuidof (Recordset));
if(FAILED(hr))
{
printf("Failed creating record set instance\n");
return 0;
}
printf("Created RecordSEt");
//Open the Record set for getting records from Author table
pRstAuthors->Open("SELECT au_lname,au_fname,city FROM Authors",strCnn, adOpenStatic,adLockReadOnly,adCmdText);
printf("Recordset Opened");
//Declare a variable of type _bstr_t
_bstr_t valField1;
_bstr_t valField2;
_bstr_t valField3;
pRstAuthors->MoveFirst();
//Loop through the Record set
if (!pRstAuthors->EndOfFile)
{
while(!pRstAuthors->EndOfFile)
{
valField1 = pRstAuthors->Fields->GetItem("au_lname")->Value;
valField2 = pRstAuthors->Fields->GetItem("au_fname")->Value;
valField3 = pRstAuthors->Fields->GetItem("city")->Value;
printf("%s \t - %s \t - %s \t\n",(LPCSTR) valField2, (LPCSTR) valField1,(LPCSTR)valField3 );
pRstAuthors->MoveNext();
}
}
pRstAuthors->Close();
getch();
}
catch(_com_error & ce)
{
printf("Error:%s\n",ce.Description);
}
CoUninitialize();
return 0;
}
yamuna
|
|
|
|
|
hi...
how to pass muliple option from a drop down to the stored procedure as string and then pass the values to the conditional clause of the procedure.
sathy
|
|
|
|
|
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();
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
|
|
|
|
|
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
|
|
|
|
|
Are you required to use a combobox dropdown? Why I ask is because listboxes can do multiple selections.
PJC
|
|
|
|
|
thanks guru but my requirement is for tht only ....
i have sorted tht out ..
sathy
|
|
|
|
|
Glad you sorted that out.
Have a good day,
Paul
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
cos I need to execute stored procedure more than 1000 time and i am worry about cpu and memory usage!
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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,
|
|
|
|
|
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
|
|
|
|
|
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 
Thanks Much for your help,
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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 
-- modified at 12:14 Saturday 21st January, 2006
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
