Database

Connection Issue While connecting to SqlServer 2000 from ASP.NET 2.0

21-Dec-06 23:10

kbalias wrote:
Can anybody give me some ideas?

Don't inject values into a SQL Statement. Use parameters. Please read this[^] to defend yourself against attack - Your code is vulnerable.

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

nesaraja21-Dec-06 18:29

nesaraja

21-Dec-06 18:29

HI
I have got some error while connecting Sql Server 2000 from ASP.NEt 2.0
i.e My ASP.NET Application and SqlServer 2000 are avaliable in diffrent machine.I remotly connecting the sql server 2000.

I am getting the following error

"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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at _Default.click_Click(Object sender, EventArgs e) in d:\WebSite2\Default.aspx.cs:line 27"

Please send me ur sugessions.

Thanks

Paul

Re: Connection Issue While connecting to SqlServer 2000 from ASP.NET 2.0

Colin Angus Mackay21-Dec-06 23:08

Deleted row outside program problem.

21-Dec-06 23:08

nesaraja wrote:
Please send me ur sugessions.

Send details. Such as your connection string, the name of the machine the instance name of the SQL Server (if there is one).

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

Tomy140221-Dec-06 15:43

Tomy1402

21-Dec-06 15:43

I have trouble with deleted row in my database program. When my program starts, I start another program too (MySQLBrowser) and from MySQLBrowser, I delete one row. After that in my Program, I refresh (re-fill) my relevant DataTable but the deleted row still exist in DataTable.

I found out a solution by clearing DataTable first, then re-fill it again. It Works.

Clearing DataTable really works. But, what if there are thousands rows?

Anywayay, here's the code:
I have a Static Class which holds the only DataSet in my program. All DataTables created are stored in that DataSet.

/*******************************************************************/
public static DataSet dsObj = new DataSet(); //the one and only DataSet in my program
public static MySqlConnection connObj = new MySqlConnection(connStr);

//method to add Table or Tables to DataSet
public static void addTblsToDsObj(String tblName)
{
String[] tblArr = tblName.Split(',');

dsObj.EnforceConstraints = false; //I remove constrain before clearing DataTable, because of ForeignKey problem

for (int i = 0; i < tblArr.Length; i++ )
{
if(dsObj.Tables.Contains(tblArr[i])){ //If DataSet has same Table, I clear that table before fill it again

dsObj.Tables[tblArr[i]].Clear();
}
}

dsObj.EnforceConstraints = true; //after needed tables are cleared, I set it to true because I want to load the primary key too.

for (int i = 0; i < tblArr.Length; i++)
{
MySqlDataAdapter daObj = new MySqlDataAdapter("SELECT * FROM " + tblArr[i], connObj);
MySqlCommandBuilder cbObj = new MySqlCommandBuilder(daObj);
daObj.MissingSchemaAction = MissingSchemaAction.AddWithKey;
daObj.Fill(dsObj, tblArr[i]);
}
}
/*******************************************************************/

Now, everytime my Form load a Table from MySQL, I call method addTblsToDsObj(String tblName).

Note that code above works fine with deleted data outside my Program environtment (because if Clear method).

I repeat my question above, what if I have thousand of rows? it means I will clear a lot of unnecessary rows right? so I guess it's not an optimal performance.

Thx for any help.

Re: Deleted row outside program problem.

Frank Kerrigan21-Dec-06 23:43

Re: Deleted row outside program problem.

21-Dec-06 23:43

Are you deleting the data from the table in your DATA set or sending a SQL command to your db.

If you are deleting a record from a datatable within a dataadapter your have to issue an update commaand to your dataAdapter.

Blog Have I http:\\www.frankkerrigan.com

Tomy140222-Dec-06 15:21

Tomy1402

22-Dec-06 15:21

I deleted row from MySQLBrowser (or inside MySQL itself) not from my Program. I didn't delete from DataTable because it's inside my Program.

If I add or modify row from MySQLBrowser than in my Program I re-fill DataTable again, the modified or added row is exist on my DataTable. but the problem is when I delete row from MySQLBrowser, then in my program I refresh(re-fill) DataTable, DataTable still contain the deleted row.

How do you quote parameters for an Oracle Select statement string when the parameter contains a quote?

howardjr21-Dec-06 12:43

Re: How do you quote parameters for an Oracle Select statement string when the parameter contains a quote?

21-Dec-06 12:43

I've been quoting string parameters using single qoutes as follows:

QueryString = "Select * " &  _
                  "From Employees " & _
                 "Where LastName = '" & EmpLastName & "'"

This works well unless the last name includes a quote, i.e., "Mc'Donald"
So how do you quote the string in visual basic so that Oracle won't hang up on the interior quote?

The same thing happens when you need to query or store feet and inches values ( 9'4" ).
So how would I quote that?

Thanks

P.S., this is for Oracle 8i, if it makes a difference.

Colin Angus Mackay21-Dec-06 13:24

Re: How do you quote parameters for an Oracle Select statement string when the parameter contains a quote?

21-Dec-06 13:24

howardjr wrote:
This works well unless the last name includes a quote, i.e., "Mc'Donald"

The name McDonald has no apostrophe in it. You may be thinking of a name such as O'Leary.

The simple answer is that you don't inject values in to SQL. It is a security risk and leads to SQL Injection Attacks.

Read this article[^] and it will tell you how to prevent SQL Injection Attacks. And by closing that security hole you solve your current problem.

howardjr wrote:
The same thing happens when you need to query or store feet and inches values ( 9'4" ).

You are kidding me when you say you store feet and inches like that?! How do you compare them? These should be stored as a number (probably inches) and the presentation layer of the application reformats the stored information to display as you need it. (Actually, I'm more surprised that people still use feet and inches and not SI units)

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

howardjr21-Dec-06 16:49

Re: How do you quote parameters for an Oracle Select statement string when the parameter contains a quote?

21-Dec-06 16:49

I understand your comment about not normalizing the feet and inches, but the value is stored in a text field as part of an inspection comment, which later I was asked to retrieve. I don't know how the comment was put in so, but the value is in the database, so I'd like to know how to quote the search value.

Comments aside, how do you quote strings that already have quotes in them, either a single or a single and a double quote?

Mairaaj Khan21-Dec-06 21:17

Mairaaj Khan

21-Dec-06 21:17

Perhaps this may help you getting some idea.

SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "SELECT f1 from t2 WHERE upper(f1) LIKE @criteria";
            cmd.Parameters.Add("@criteria", SqlDbType.VarChar).Value = "%'MAIRAJ%";

Where f1 is a varchar type field in table t2. I tested this against SQL Server 2000, and it works fine.
Parameters in command object provides you a greater flexibility in working with databases (either you want to update, delete, insert, or get data). Plus it also help in preventing SQL Injection Attacks [^]. I will strongly recommend, go for that what Colin Angus Mackay is suggested.

Moreover, .NET provide following types of parameters (among them one may serve you).
OleDbParameter[^]
SqlParameter[^]
OdbcParameter[^]

Regards

________________________________
Success is not something to wait for, its something to work for.

Re: How do you quote parameters for an Oracle Select statement string when the parameter contains a quote?

Colin Angus Mackay21-Dec-06 23:05

Re: How do you quote parameters for an Oracle Select statement string when the parameter contains a quote?

21-Dec-06 23:05

howardjr wrote:
how do you quote strings that already have quotes in them, either a single or a single and a double quote?

Read the article I linked to.

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

howardjr22-Dec-06 6:56

Re: How do you quote parameters for an Oracle Select statement string when the parameter contains a quote?

22-Dec-06 6:56

Thank you for your comments and the links.

It looks like the article on protecting against SQLInjection Attacks covers what I need very nicely. In my case I'm lucky in that I'm the one writing the select statement and dealing with the parameter values, so since I'm not going to Attack my own system Smile | :)

, I don't have to worry about this.

Thanks again.

Colin Angus Mackay22-Dec-06 7:00

Re: How do you quote parameters for an Oracle Select statement string when the parameter contains a quote?

22-Dec-06 7:00

howardjr wrote:
In my case I'm lucky in that I'm the one writing the select statement and dealing with the parameter values, so since I'm not going to Attack my own system , I don't have to worry about this.

Statistically 90% of all attacks are insider jobs. You might need protection from yourself. Poke tongue | ;-P

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

Paul Conrad22-Dec-06 7:24

Paul Conrad

22-Dec-06 7:24

Colin Angus Mackay wrote:
You might need protection from yourself.

Don't you also mean from employees within an organization?

Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon

Re: How do you quote parameters for an Oracle Select statement string when the parameter contains a quote?

Colin Angus Mackay22-Dec-06 8:03

Re: How do you quote parameters for an Oracle Select statement string when the parameter contains a quote?

22-Dec-06 8:03

PaulC1972 wrote:
Don't you also mean from employees within an organization?

Never underestimate the cunning of a master hacker. Big Grin | :-D

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

howardjr23-Dec-06 19:43

How to trim fields on the table Sql Server 2000 ?

23-Dec-06 19:43

True enough -- I never know what I'm going to do when I'm sleep-computing.

kindman_nb21-Dec-06 11:34

kindman_nb

21-Dec-06 11:34

Hi ..
when I build a table and indicate the size of all fields like
Name char(30)
Address char(100)

when I enter the data on the table I found that the program is complete the lengn of filed to equal it's size like 30 or 100 as shown above with spaces ...
then when I deal with the table with vb.net items like TextBox this spaces is appear and it's makeing a problem for me

please ... any one come to aid me , i searched alot without any result
Frown | :(

(

jooooo

Re: How to trim fields on the table Sql Server 2000 ?

Colin Angus Mackay21-Dec-06 13:26

Re: How to trim fields on the table Sql Server 2000 ?

21-Dec-06 13:26

kindman_nb wrote:
please ... any one come to aid me , i searched alot without any result

Use a varchar and not a char. Only use char if the value is always a fixed length or you are using it as a key. (Not that it is a good idea to key on a char column)

Upcoming Scottish Developers events:
* Glasgow: Tell us what you want to see in 2007

My: Website | Blog | Photos

kindman_nb22-Dec-06 10:19

kindman_nb

22-Dec-06 10:19

I reapeat my greating for you
and thanks Rose | [Rose]

jooooo

Re: How to trim fields on the table Sql Server 2000 ?

Frank Kerrigan21-Dec-06 23:46

Re: How to trim fields on the table Sql Server 2000 ?

21-Dec-06 23:46

Left Trim LTRIM(field)
Right Trim RTRIM(field)
Both LTRIM(RTRIM(field))

Blog Have I http:\\www.frankkerrigan.com

kindman_nb22-Dec-06 0:13

kindman_nb

22-Dec-06 0:13

Hi
Really thanks for all
I'm so happy with you , and thanks again Rose | [Rose]

jooooo

Data Link support for Data Transformation Services

Jay Legue21-Dec-06 4:07

Jay Legue

21-Dec-06 4:07

Hi All,

I am looking to use DTS to manipulate data from a number of sources (access, oracle, etc.) and am looking for some direction as to best practices and gotchas that I should worry about. Do people recommend using DTS or is there a better way of doing this type of data import/manipulation? What would I use Data Link for when using DTS?

Thanks in advance,

---Jay

Re: Data Link support for Data Transformation Services

Frank Kerrigan21-Dec-06 23:48

Re: Data Link support for Data Transformation Services

21-Dec-06 23:48

Look at the Data Access application block for this. It will drop straight in to your application and is design exactly for your needs

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp[^]

Blog Have I http:\\www.frankkerrigan.com

Frank Kerrigan21-Dec-06 23:49