Connection strings

arun_babu_a · October 31st, 2011, 03:52 AM

I am using asp.net2.0
Through my webpage created
I want to open a table in sql server database and check whether the data entered by the user in text box in webpage exists in the table. I am using an oledb data connection.How to create data connection and query the database
how can i do all these things in the code behind file using C#.
In vb I use the following code and it works.what to do it to work in C#.

Code:

  Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
        db = Server.CreateObject("ADODB.Connection")
        db.Open("Provider=SQLOLEDB;Data Source=something;Password=pwd;User ID=sa;Initial Catalog=something")

    End Sub
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
    Public Sub clearall()
        txtCmpnyname.Text = String.Empty
        txtUname.Text = String.Empty
        txtPwd.Text = String.Empty

    End Sub
    Protected Sub btnLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        If txtCmpnyname.Text = String.Empty Then

            MsgBox("Plese fill company name", MsgBoxStyle.Information, "Login page")
            txtCmpnyname.Focus()
            Exit Sub


        End If
        If txtUname.Text = String.Empty Then
            MsgBox("Please fill username", MsgBoxStyle.Information, "Login page")
            txtUname.Focus()
            Exit Sub


        End If
        If txtPwd.Text = String.Empty Then
            MsgBox("Please fill password", MsgBoxStyle.Information, "Login page")
            txtPwd.Focus()
            Exit Sub


        End If
        Dim rec As String
        rec = "select Companyname,user_id, password from companydet where user_id='" & Trim(txtUname.Text) & " ' and password='" & Trim(txtPwd.Text) & " 'and companyname='" & Trim(txtCmpnyname.Text) & "'"
        rs.open(rec, db, 3, 3)
        If rs.eof Then
            MsgBox("Incorrect company name,username or password", MsgBoxStyle.Information, "Login page")
            rs.close()
            clearall()
            txtCmpnyname.Focus()

            Exit Sub


        End If



        If Trim(rs.fields("user_id").value) <> Trim(txtUname.Text) Then
            MsgBox("Check your capskey lock", MsgBoxStyle.Information, "Login page")
            txtUname.Text = String.Empty
            txtPwd.Text = String.Empty
            txtUname.Focus()
            Exit Sub

        End If




        If Trim(rs.fields("password").value) <> Trim(txtPwd.Text) Then
            MsgBox(" Check your capskey lock", MsgBoxStyle.Information, "Login page")
            txtPwd.Text = String.Empty
            txtPwd.Focus()
            rs.Close()
            Exit Sub


        End If
        clearall()

        Response.Redirect("Default2.aspx")

    End Sub

    Protected Sub btnExit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExit.Click

please provide some examples

Imar · October 31st, 2011, 08:32 AM

Hi there,

You can http://converter.telerik.com to convert your code to C#. However, you need more than just a conversion. Some tips:

1. Since this is about ASP.NET 2, and not about my book Beginning ASP.NET 3.5, you're better off posting questions like these in a generic ASP.NET 2 category: http://p2p.wrox.com/asp-net-asp-12/

2. Your code is open to SQL Injection meaning anybody could log in without knowing a password. Even worse, they could take over your machine.

3. Running under the SA account is a bad idea as it has way too many privileges. This is even more problematic if your code is open to SQL Injection attacks.

4. Don't use MsgBox in ASP.NET It appears to work on your machine as the Server and the Client are the same. However, MsgBox appears on the server, and not in the client's browser.Send JavaScript to the client instead that uses alert or other ways to present a message to the user.

5. Don't use ADODB.Connection. This is the old / COM way of doing things used in classic ASP years ago. Instead, use ADO.NET.

6. Consider using the Validation controls so you don't have to validate each field by hand.

Hope this helps,

Imar

arun_babu_a · November 1st, 2011, 12:15 AM

I converted the code to c#.Now using windows authentication and validation tools are included.
To connect to Sql server i created one oledb connection class.

Code:

 OleDbConnection connection = new OleDbConnection(conn);
        OleDbCommand cmd = new OleDbCommand(SQL, connection);
        connection.Open();
        OleDbDataReader reader = cmd.ExecuteReader();
       if (reader.Read())
       {
           reader.Close();
           connection.Close();
           clearall();
           Response.Redirect("Default2.aspx");
       }

Anyway its working fine;
My query is using this oledb connection how can i check each fields in the table with some value in my textbox.I am very new to asp and following your asp 3.5 book.so i posted the query here.please help

Imar · November 1st, 2011, 12:52 PM

Quote:

Since this is about ASP.NET 2, and not about my book Beginning ASP.NET 3.5, you're better off posting questions like these in a generic ASP.NET 2 category: http://p2p.wrox.com/asp-net-asp-12/

Even though you're following my book, you're much better off posting in the category I suggested as more people will view posts created there, increasing your chances on an answer.

If you want to use techniques from my book, check out Chapter 13 that deals with LINQ and LINQ to SQL, concepts you can use to access your database.

Cheers,

Imar