writing sql code in vb.net

Question

hi,

I have information entered into a text box on an ASP.net 3.5 page. when i click the submit button i would like this information written to a sql server database.

Can someone please tell me what I need to do to accomplish this. The end-user should not see anything.

I am using visual web developer 2008. the event handling code is placed in a separate file using vb.

Thank you.

Answer 1

Need to look into LINQ to SQL or ADO.NET (lower level)

Answer 2

There are many ways to handle it. I would start with a few ADO.NET (the library .NET uses to talk to databases) resources:

• Modifying Data with ADO.NET
• ADO.NET Overview
• Simple INSERT

Answer 3

There are lots of different ways to accomplish this: Dynamic Data, LinqToSQL, Typed data sets, Data Access Application Block, or another ORM. My preferred method is direct sql, which would use code something like this:

Public Sub SaveAnswer(ByVal answer As String)
    Dim sql As String = "INSERT INTO [table1] (ans) VALUES (@Answer)"

    Using cn As New SqlConnection(getConnectionString()), _
          cmd As New SqlCommand(sql)

        cmd.Parameters.Add("@Answer", SqlDbType.VarChar, 50).Value = answer

        cn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

Private Function getConnectionString() As String
    ''//normally read from a config file for this

    Return "Server=(local)\SQLEXPRESS;Database=testdb;Trusted_Connection=True;"
End Function

A few things to take from this sample:

• It properly closes the db connection, even if an exception is thrown, via the Using block
• Parameterized query to prevent sql injection
• getConnectionString() is private. You should abstract out your data access to a separate class or assembly, and this is one way to start enforcing that.

Answer 4

dot net 3.5 has a LINQ feature integrated with it... you need not write the sql code... it is SQL ORM which does the job for you

create a database (.mdb file) then a dbml file (which is datacontext) drag and drop the tables you created in the databse into the dbml...

then u are ready to code...

if the dbml file name is master ...and the table name is table1 and has a column column1

then this is how you use it

dim db = new masterdatacontext()
dim c = new table1()
c.column1 = textbox1.text()
db.table1.insertonsubmit(c)
db.submitchanges()

thats it...

Answer 5

It sounds like you're a beginner to ASP.NET. Your best bet is to look up tutorials online for building database-driven ASP.NET applications. To answer your question directly here would be rewriting better-written tutorials found elsewhere.

You should start with a few targeted Google searches for tutorials, such as this one.

My advice would be to start with 2.0, not 3.5 -- even though it's an older version, newer versions build upon it, so you'll be learning fundamentals that are still useful in 3.5.