ADO.NET Simple Usages....

Hello, i am inquring about ADO.NET and want to know the following using code only...

I want to open the database using a connection string of any kind

Then i want to open a specfic table in a database

Then i want to be able to have the following:
  2 text boxs, and a button, basicly when the user enters a first name in the first textbox and presses the button, it will then open the database and search for the record, then once the record has been found (the first occurance) then it will display the last name in textbox2 (in vb.6 i use to use rs.fields("l_name") etc...)

Then once it does this, i want the user to be able to change the last name and have the user hit a second button to update the field

then i want the table updated and saved (database)

then i want the database closed..

I would also like to know how to do simple quaries like insert, delete tables, etc....
kwickwayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mani_saiCommented:
Hello kwickway,


Here is the sample code:

1) You have to Import
Imports System.Data.SqlClient
namespace.

2) Add three text boxes one for input and next two for results

3)Add one selectbutton and updateButton to your form.

Select button event pulls information from employee table--northwind database for the typed forst name

Update button event updates the last name and homephone for the firstname.



 Private Sub selectButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles selectButton.Click
        'Clear the result textboxes
        TextBox2.Text = ""
        TextBox3.Text = ""

        'build sql connection string at run time
        Dim connect As New SqlConnection("Initial Catalog = Northwind;Data Source=(local); User ID=sa; password =test;")

        'open the connection
        connect.Open()

        'build sql string
        Dim sqlString As String
        sqlString = "Select LastName, HomePhone From Employees Where FirstName = '" & TextBox1.Text & "'"


        'open sql command object
        Dim command As New SqlCommand(sqlString, connect)

        'open sql data reader
        Dim reader As SqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection)

        ' retrieve results through data reader
        If reader.Read() Then

            TextBox2.Text = reader("LastName")
            TextBox3.Text = reader("HomePhone")

        End If


        'clean up code
        reader.Close()
        command.Dispose()
        connect.Close()
        connect = Nothing

    End Sub


Private Sub UpdateButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateButton.Click

        'build sql connection string at run time
        Dim connect As New SqlConnection("Initial Catalog = Northwind;Data Source=(local); User ID=sa; password =test;")

        'Opns the SQL Connection
        connect.Open()

        'open command object(takes two parameters sqlstring, connection)
        Dim command As New SqlCommand("Update Employees Set LastName = '" & TextBox2.Text & "'," _
                         & " HomePhone = '" & TextBox3.Text & "' where FirstName = '" & TextBox1.Text & "'", connect)

        'execute command object
        command.ExecuteNonQuery()


        'clean up code
        command.Dispose()
        connect.Close()
        connect = Nothing



    End Sub




Hope it help.

Thanks

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kwickwayAuthor Commented:
Mani, Can i pretty much put any DSN or ODBC connection string in there?  And how do i create a database or table in that example?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

kwickwayAuthor Commented:
(and that is ADO.NET right)
0
mani_saiCommented:
yes you can use ODBC connection string.

Strictly follow the syntax. The connection string syntax is given below in this article:

http://www.sqlstrings.com/

http://www.sqlstrings.com/SQL-Server-connection-strings.htm

http://www.able-consulting.com/ADO_Conn.htm

you can use according to your scenario.


Hope it helps

Thanks
0
mani_saiCommented:

Regarding your question:
And how do i create a database or table in that example?

1) you can create database and tables at the backend.(recommended)
CREATE DATABASE
Creates a new database and the files used to store the database, or attaches a database from the files of a previously created database.



Note  For more information about backward compatibility with DISK INIT, see Devices (Level 3) in Microsoft® SQL Server™ Backward Compatibility Details.


Syntax
CREATE DATABASE database_name
[ ON
    [ < filespec > [ ,...n ] ]
    [ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]

< filespec > ::=

[ PRIMARY ]
( [ NAME = logical_file_name , ]
    FILENAME = 'os_file_name'
    [ , SIZE = size ]
    [ , MAXSIZE = { max_size | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment ] ) [ ,...n ]

< filegroup > ::=

FILEGROUP filegroup_name < filespec > [ ,...n ]

----------------------------------------------------------------------------

CREATE TABLE
Creates a new table.

Syntax
CREATE TABLE
    [ database_name.[ owner ] . | owner. ] table_name
    ( { < column_definition > 
        | column_name AS computed_column_expression
        | < table_constraint > ::= [ CONSTRAINT constraint_name ] }

            | [ { PRIMARY KEY | UNIQUE } [ ,...n ]
    )

[ ON { filegroup | DEFAULT } ]
[ TEXTIMAGE_ON { filegroup | DEFAULT } ]

< column_definition > ::= { column_name data_type }
    [ COLLATE < collation_name > ]
    [ [ DEFAULT constant_expression ]
        | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ]
    ]
    [ ROWGUIDCOL]
    [ < column_constraint > ] [ ...n ]

< column_constraint > ::= [ CONSTRAINT constraint_name ]
    { [ NULL | NOT NULL ]
        | [ { PRIMARY KEY | UNIQUE }
            [ CLUSTERED | NONCLUSTERED ]
            [ WITH FILLFACTOR = fillfactor ]
            [ON {filegroup | DEFAULT} ] ]
        ]
        | [ [ FOREIGN KEY ]
            REFERENCES ref_table [ ( ref_column ) ]
            [ ON DELETE { CASCADE | NO ACTION } ]
            [ ON UPDATE { CASCADE | NO ACTION } ]
            [ NOT FOR REPLICATION ]
        ]
        | CHECK [ NOT FOR REPLICATION ]
        ( logical_expression )
    }

< table_constraint > ::= [ CONSTRAINT constraint_name ]
    { [ { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        { ( column [ ASC | DESC ] [ ,...n ] ) }
        [ WITH FILLFACTOR = fillfactor ]
        [ ON { filegroup | DEFAULT } ]
    ]
    | FOREIGN KEY
        [ ( column [ ,...n ] ) ]
        REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]
        [ ON DELETE { CASCADE | NO ACTION } ]
        [ ON UPDATE { CASCADE | NO ACTION } ]
        [ NOT FOR REPLICATION ]
    | CHECK [ NOT FOR REPLICATION ]
        ( search_conditions )
    }

-------------------------------------------------------------------------------



2)you can create databases and table using SQL enterprise manager(more easy to create).

refer SQL Books online documentation.

Good luck
0
kwickwayAuthor Commented:
I first have to tell you this.... ...I am impressed with this...  Thanks...

When you say backend your not talking about during the runtime are you?  Can i execute a "create database" command in run time?  I just want to make a personal knolage base for myself.
0
mani_saiCommented:
you can execute SQL statements using the code below:

Instead of update statement you can use any valid SQL statement like create database, create table etc....

Hope it helps.

 'build sql connection string at run time
        Dim connect As New SqlConnection("Initial Catalog = Northwind;Data Source=(local); User ID=sa; password =test;")

        'Opns the SQL Connection
        connect.Open()

        'open command object(takes two parameters sqlstring, connection)
        Dim command As New SqlCommand("Update Employees Set LastName = '" & TextBox2.Text & "'," _
                         & " HomePhone = '" & TextBox3.Text & "' where FirstName = '" & TextBox1.Text & "'", connect)

        'execute command object
        command.ExecuteNonQuery()


        'clean up code
        command.Dispose()
        connect.Close()
        connect = Nothing

0
clear100-comCommented:
Hello again....

I posted the file attachment at http://www.vbcity.com/forums/attachment.asp?id=9947 (nearly compleated...

I want to know how using this source code to take the BLOB data out of the northwind database (again using my example) showing the picture in the Nortwind.Employee Database for each employee onto a picturebox
0
kwickwayAuthor Commented:
Both of us are still trying to figure out this code, but Mani, if you can figure that out too, let us know.... :)  Im sure its easy...
0
mani_saiCommented:
i have not come across your situation.

please try to post one more time with your latest question, some one will definitely help you out.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.