Accessing SQL Database from VB.NET

[Sport Girl]

Hi everybody,

I am using SQL 2005 and VB.NET 2008 and i am really new in .net.

So please can anybody help me and give an idea about the procedure to access SQL database from vb.net. I have an idea about using data bind , data sources but just don't know the direction to follow.

Regards

[vee10]

Hi,

this is how u can open and close and execute queries

Partial Class Default4
    Inherits System.Web.UI.Page

    Private ConnectionString As String = "Data Source=PRAVEENAPC;Initial Catalog=testing;Integrated Security=True"
    Private reader As Data.SqlClient.SqlDataReader = Nothing
    Private conn As Data.SqlClient.SqlConnection = Nothing
    Private cmd As Data.SqlClient.SqlCommand = Nothing
    Private dr As Data.SqlClient.SqlDataReader = Nothing
    Private AlterTableBtn As System.Web.UI.WebControls.Button
    Private sql As String = Nothing

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        ' Create a connection
        conn = New Data.SqlClient.SqlConnection(ConnectionString)
        ' Open the connection
        Dim sql As String = "select * from emp"
        ExecuteSQLStmt(sql, conn)
    End Sub 'CreateDBBtn
    Private Sub ExecuteSQLStmt(ByVal sql As String, ByVal conn As Data.SqlClient.SqlConnection)
        ConnectionString = "Data Source=PRAVEENAPC;Initial Catalog=testing;Integrated Security=True"
        conn.ConnectionString = ConnectionString
        conn.Open()
        cmd = New Data.SqlClient.SqlCommand(sql, conn)
        dr = cmd.ExecuteReader()
        If dr.HasRows Then
            GridView1.DataSource = dr
            GridView1.DataBind()
        End If
        conn.Close()



    End Sub 'ExecuteSQLStmt 
End Class

or check this url

http://www.vbdotnetheaven.com/UploadFile/mahesh/CreateSQLDatabase04252005064419AM/CreateSQLDatabase.aspx

Hi everybody,

I am using SQL 2005 and VB.NET 2008 and i am really new in .net.

So please can anybody help me and give an idea about the procedure to access SQL database from vb.net. I have an idea about using data bind , data sources but just don't know the direction to follow.

Regards

[Sport Girl]

But I am working in Windows Forms application and i always get the following error for:

application_cmb.DataBind()

the error is:
DataBind is not a member of System.Windows. Forms.ComboBox

What should i write in the code

[jamesd0142]

vb2005 code i use all the time: (With sql server 2000)

[code=vbnet]
Imports System.Data.Ole Db
[/code]

then declare: (Dont need them all - i just cant be bothered to remove them :))
[code=vbnet]
'---Database Declarations
Dim Cmd As OleDb.OleDbComm and
Dim Con As OleDb.OleDbConn ection
Dim Sql As String = Nothing
Dim Reader As OleDb.OleDbData Reader
Dim ComboRow As Integer = -1
Dim Columns As Integer = 0
Dim Category As String = Nothing
Dim da As System.Data.Ole Db.OleDbDataAda pter
Dim oDS As New System.Data.Dat aSet
Dim oDS2 As New System.Data.Dat aSet
'---Declaration END
[/code]

query:
[code=vbnet]
dim Con as string = New OleDb.OleDbConn ection("Provide r=sqloledb;Data Source=serverna me;Initial Catalog=db;User ID=sa;Password= password"
Sql = "Select * from Users order by [login]"
oDS.Clear()
Con.Close()
Cmd = New OleDb.OleDbComm and(Sql, Con)

Con.Open()
da = New OleDbDataAdapte r(Sql, Con)
da.Fill(oDS, "Users1")
DGV.DataSource = oDS.Tables("Use rs1").DefaultVi ew
DGV.Refresh()
[/code]

non query
[code=vbnet]
dim StrConn as string = "Provider=sqlol edb;Data Source=server;I nitial Catalog=db;User ID=sa;Password= password"
Dim oConn As New System.Data.Ole Db.OleDbConnect ion(StrConn)
oConn.Close()
Dim con As New System.Data.Ole Db.OleDbCommand
con.Connection = oConn
con.CommandType = CommandType.Tex t
con.CommandText = "update users set [password] = " & "'" & txtpasschange.T ext & "'" & ", [passwordchanged] = " & "convert(varcha r(20), convert(smallda tetime, getdate()), 101)" & " where [login] = " & "'" & txtlogchange.Te xt & "'"
oConn.Open()
'display no of rows affected
a = con.ExecuteNonQ uery()
[/code]

any use?

James

[Plater]

.DataBind() is only for web applications.

Have you gone through this:

http://bytes.com/forum/thread635615.html

[Curtis Rutland]

Have you gone through this:
http://bytes.com/forum/thread635615.ht ml

This is a very good starting point. If you are connecting to an MS SQL Server, the SqlConnection and SqlCommand objects are great. If you are connecting to some other database, use OleDbConnection s and OleDbCommands. If you are using ODBC, use OdbcConnection and OdbcCommand.

Note, to use these, you need to
[code=vbnet]
Imports System.Data.Sql Client
Imports System.Data.Ole Db
Imports System.Data.Odb c
[/code]
use the one that you need.
If you need help with the Connection Strings, try here.

Also, I would suggest you learn about [Sql/OleDb/Odbc]DataAdaper as well. They are very useful because you can fill a table in a dataset automatically with your results.

For example:
[code=vbnet]
Imports System.Data
Imports System.Data.Sql Client
...
...
...
Dim connStr, sqlQueryText As String
connStr = "connection string text here"
sqlQueryText = "select * from table"
Dim Conn As New SqlConnection(c onnStr)
Dim Adapt As New SqlDataAdapter( sqlQueryText, Conn)
Dim ds As New DataSet()
Adapt.Fill(ds, "tableName" )
Dim table As DataTable
table = ds.Tables("tabl eName")
DataGridView1.D ataSource = table
[/code]

Now, you have a DataTable (table) that has all the data from your select statement. Then you can do with it what you will. (What I did was fill a DataGridView object) But be careful, when the DataAdapter loads the data, it loads it all into memory. If you accidentally select 2 million records with 100 columns (I've done this) it all has to load into RAM and may crash.