sql connection using vb.net

Question

0.00/5 (No votes)

See more:

Hello,
I created a windows form where we use a text box and a button.
What I want is when i click on the button the data is input in the textbox will store in the MSSQL table name as student
and the col name is stuname
When i run the form its shows the form and when i input the data in the text box
and click on the button nothing happens,
and data is also not stored in the database table
please help me out
the code is given below

VB

Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms

Public class Form1
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con As SqlConnection
        Dim cmd As SqlCommand
        Dim row As Integer
        'Dim str As String
        con = New SqlConnection("server=YASH;database=test;integrated security=true")
        con.Open()
        cmd = New SqlCommand("insert into([stuname]) student values ('" & TextBox1.Text & "')", con)
        row = cmd.ExecuteNonQuery()
        If row > 0 Then
            MessageBox.Show("the row inserted" & row)
        End If
        con.Close()
    End Sub
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub
End Class

Posted 17-Mar-11 21:35pm

ahsan.subiya

Updated 17-Mar-11 22:17pm

Tarun.K.S

v2

Add a Solution

Comments

Toniyo Jackson 18-Mar-11 3:48am

So, what is your problem?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tarun.K.S · Accepted Answer · 2011-03-17T22:35:00

This is how i did (Its an Example) :

VB

Dim conString As String = "Server=hcl\sqlexpress;Initial Catalog=vbtry;Integrated Security=True"
Dim conn As SqlConnection = New SqlConnection(conString)
Dim adap As New SqlDataAdapter
Dim ds As New DataSet

VB

Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
    Dim selCmdText As String = "SELECT *FROM DEPARTMENTS"
    Dim selCommand As New SqlCommand(selCmdText, conn)
    Dim insCmdText As String = "INSERT INTO DEPARTMENTS(Department_id,Department_name,Manager_id,Location_id)" & _
                            "VALUES(@DepId,@DepName,@ManId,@LocId)"
    Dim insCommand As New SqlCommand(insCmdText, conn)
    Dim adp As New SqlDataAdapter(selCommand)
    adp.InsertCommand = insCommand

    Dim param As New SqlParameter
    param.ParameterName = "@DepId"
    param.SqlDbType = SqlDbType.SmallInt
    param.Value = Integer.Parse(txtDepId.Text)
    insCommand.Parameters.Add(param)

    param = New SqlParameter
    param.ParameterName = "@DepName"
    param.SqlDbType = SqlDbType.VarChar
    param.Size = 15
    param.Value = txtDepName.Text
    insCommand.Parameters.Add(param)

    param = New SqlParameter
    param.ParameterName = "@ManId"
    param.SqlDbType = SqlDbType.SmallInt
    param.Value = Integer.Parse(txtManId.Text)
    insCommand.Parameters.Add(param)

    param = New SqlParameter
    param.ParameterName = "@LocId"
    param.SqlDbType = SqlDbType.SmallInt
    param.Value = Integer.Parse(txtLocId.Text)
    insCommand.Parameters.Add(param)

    Dim dt As New DataTable
    adp.Fill(dt)
    Dim newRo As DataRow = dt.NewRow
    newRo.Item("Department_id") = Integer.Parse(txtDepId.Text)
    newRo.Item("Department_name") = txtDepName.Text
    newRo.Item("Manager_id") = Integer.Parse(txtManId.Text)
    newRo.Item("Location_id") = Integer.Parse(txtLocId.Text)
    dt.Rows.Add(newRo)
    adp.Update(dt)

    lstView.ItemsSource = dt.DefaultView 'This is WPF way to fill the ListView, don't know about Winforms.
    End Sub

As you can see, I have used SqlDataAdapter class. With this I don't have to care about the opening and closing of connection. It will fill the values in the DataTable which on updating can be updated back to the Database. Second thing is the use of SqlParameters for each values that I want to insert to prevent SQL Injections. Avoid directly writing TextBox1.text, if someone writes DROP TABLE and other malicious commands, you can imagine what will happen!
Thirdly, I would recommend you to use LINQ to SQL, it's very easy to use and you don't have to write the big coding stuff above. Here is the link : http://www.codeproject.com/KB/linq/linqtutorial.aspx[^]

Hope it helped! :)