SQL In VB.NET

ptaylor965 1 Tallied Votes 8K Views Share

Connecting and using SQL in VB.NET

'Declare outside of class
Imports System.Data.SqlClient


'Declare inside of class >
Dim SQLStr As String
Private ConnString As String

'Connstring = Server Name, Database Name, Windows Authentication 
connstring = "Data Source=myserver;Initial Catalog=databasename;Integrated Security=True"
      
'SQL Staments
      
'SQL query = myQuery = "SQL Statment"
      
SQLStr = "SELECT * FROM tblQuestion"
      
SQLStr = "INSERT into tblQuestion(Name, Question) VALUES('Fred', 'How to use SQL?')"
      
SQLStr = "UPDATE tblQuestion SET Answer = 'Like this' Where Question = 'How to use SQL?'"
      
SQLStr = "DELETE FROM tblQuestion WHERE Question='How to use SQL?'"
        
'Write to SQL
      
Dim SQLConn As New SqlConnection() 'The SQL Connection
Dim SQLCmd As New SqlCommand() 'The SQL Command
      
SQLConn.ConnectionString = ConnString 'Set the Connection String
SQLConn.Open 'Open the connection
      
SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command
SQLCmd.CommandText = SQLStr 'Sets the SQL String
SQLCmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only
      
SQLConn.Close() 'Close the connection  
        
      







'Read from SQL
      
Dim SQLConn As New SqlConnection() 'The SQL Connection
Dim SQLCmd As New SqlCommand() 'The SQL Command
Dim SQLdr As SqlDataReader        'The Local Data Store
      
SQLConn.ConnectionString = ConnString 'Set the Connection String
SQLConn.Open 'Open the connection
      
SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command
SQLCmd.CommandText = SQLStr 'Sets the SQL String
SQLdr = SQLCmd.ExecuteReader 'Gets Data
      
While dr.Read() 'While Data is Present        
      MsgBox(dr("Column Name")) 'Show data in a Message Box
End While

Loop While SQLdr.NextResult() 'Move to the Next Record
SQLdr.Close 'Close the SQLDataReader        

SQLConn.Close() 'Close the connection
majestic0110 187 Nearly a Posting Virtuoso

Nice snippet, the comments are especially useful to gain a better understanding of what is going on. Good work!

chan_lemo 0 Newbie Poster

Dim connetionString As String
Dim cnn As SqlConnection
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
cnn = New SqlConnection(connetionString)
Try
cnn.Open()
MsgBox("Connection Open ! ")
cnn.Close()
Catch ex As Exception
MsgBox("Can not open connection ! ")
End Try


for more ,

http://vb.net-informations.com/ado.net-dataproviders/ado.net-sqlconnection.htm

chan

ERICKFB5 0 Newbie Poster

hello guys from daniweb®!
My name's Erick i'm from Brazil.
I was wondering whether anybody could help me out...

¿ how do i execute a sqlstring like this below ?:
stringSql = "delete from voucher" & _
"where including_sinalization = 'S'" & _
" and reference_yr = " & year & _
" and reference_month = " & month & _
" and yr_reembo = 0" & _
" and month_reembo = 0"

alex.ubeda 0 Newbie Poster

hey your code is incomplete but it helps a little thanks

css_maya 0 Newbie Poster

hello guys
i'm using vb.net 2008 and sql server 2008 on one computer, developing a program
my first problem is when i'm getting query in sql server manager, creating a new query when i'm connected to (local), using "select * from Appointments" will give me error but using "use MTBDB;select * from Appointments" will give me the proper result.
now here is the problem, when i'm using vb.net even if i use the second command, i won't get result and will receive error. my connection string is "Data Source=(local);Initial Catalog=MTBDB;Integrated Security=SSPI;"
where is the problem?

tendaimare 0 Junior Poster in Training

your code looks good but i didnt understad it

Netcode 33 Veteran Poster

good code snippet

Member Avatar for inthewind
inthewind

This works on mine, using VB.net 2008 on SQL Express 2008.

sqlConnection1 = New System.Data.SqlClient.SqlConnection("Data Source=localhost\SQLEXPRESS;Initial Catalog=responders;Integrated Security=true")

But, if you have a different 'named instance' it could fail.

HTH

aniketcool 0 Newbie Poster
Module1.vb

Imports System.Data.SqlClient

Module Module1


    Public Con As SqlConnection
    Public constr As String
    Public usertype As String


    Public Sub main()
        constr = "server=ANIKET\SQLEXPRESS;database=myjob ;Trusted_connection=True"
        Con = New SqlConnection(constr)

    End Sub
End Module



Form1.vb
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlException
Public Class Form1
 
    Inherits System.Windows.Forms.Form
    Dim da As SqlDataAdapter
    Dim ds2, ds3 As DataSet
    Dim dt As DataTable
    Dim a As String
    Dim sqlq As String
    Dim sqlq1 As String
    Dim r As Integer
    Dim rowno As Integer
    Dim mycurr As CurrencyManager
    Dim dv As New DataView
    Dim row As Integer = 0


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call main()
        Con.Open()
        sqlq = "select * from basic"
        da = New SqlDataAdapter(sqlq, Con)
        ds2 = New DataSet
        da.Fill(ds2)
        DataGridView1.DataSource = ds2.Tables(0)
        fillDataSet_N_View()
    End Sub




    Private Sub fillDataSet_N_View()
        da.Fill(ds2, "basic")
        dv = New DataView(ds2.Tables("basic"))
        mycurr = CType(Me.BindingContext(dv), CurrencyManager)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save.Click
        Dim dr As DataRow

        dr = ds2.Tables(0).NewRow
        dr.BeginEdit()
        dr.Item(0) = cust_name.Text
        dr.Item(1) = cust_add.Text
        dr.Item(2) = cust_email.Text
        dr.Item(3) = cust_tel.Text
        dr.EndEdit()
        ds2.Tables(0).Rows.Add(dr)
       Dim myqb As SqlCommandBuilder = New SqlCommandBuilder(da)
        Try
            da.Update(ds2)
            MessageBox.Show("Record added")
            cust_name.Clear()
            cust_add.Clear()
            cust_email.Clear()
            cust_tel.Clear()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        fillDataSet_N_View()

    End Sub

   Private Sub cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cancel.Click
        clear()

    End Sub



    Private Sub clear()
        cust_name.Clear()
        cust_add.Clear()
        cust_email.Clear()
        cust_tel.Clear()
    End Sub

 


  Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Close()

    End Sub
End Class
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.