how to establish connection between vb.net and sql server 2005 database???

[rinu.urs]

Hello,

Hii friends,

I want to know how to connect sql server 2005 to vb.net windows application

1)is there any connection string ?

2)if so i am using windows authendication in sql server 2005 management studio so am not entering any user id and

password to enter in sql server

3)so what should be the userid and password for the connection string

can any one send me a sample code that i can refar

can any one help me with the process

[sknake]

>>1)is there any connection string ?
Please see code below
>>2)if so i am using windows authendication in sql server 2005 management studio so am not entering any user id and password to enter in sql server
Please see code below
>>3)so what should be the userid and password for the connection string
You need to define users in the SQL Server. This can be done using the SQL Server Management Studio. The "sa" password should have been defined at installation time if you enabled the account.

Imports System.Data.SqlClient

Public Class frmSqlConn

	Public Shared Function BuildSqlNativeConnStr(ByVal server As String, ByVal database As String)
		Return String.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database)
	End Function

	Public Shared Function BuildSqlNativeConnStr(ByVal server As String, ByVal database As String, ByVal username As String, ByVal password As String)
		Return String.Format("Data Source={0};Initial Catalog={1};Integrated Security=False;User Id={2};Password={3};", server, database, username, password)
	End Function

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		'Connecting without a password
		Dim connStr As String = BuildSqlNativeConnStr("apex2006sql", "mydatabase")
		Dim conn As New SqlConnection(connStr)
		conn.Open()
		'Do stuff
		conn.Close()
		conn.Dispose()
	End Sub

	Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
		'Connecting WITH a password
		Dim connStr As String = BuildSqlNativeConnStr("apex2006sql", "mydatabase", "sa", "myPass")
		Dim conn As New SqlConnection(connStr)
		conn.Open()
		'Do stuff
		conn.Close()
		conn.Dispose()
	End Sub
End Class

[raymyster]

You can do it using ODBC as following:

Imports System.Data.Odbc

Public Class frmMain
Dim MyConString = "Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"
    Dim Con As New OdbcConnection With {.ConnectionString = MyConString}
    Dim ds As New DataSet

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

        Con.Open()
        Dim cmd As New OdbcCommand
        cmd.CommandText = "select * from table1"
        cmd.Connection = Con
        Dim da As New OdbcDataAdapter

        da.SelectCommand = cmd
        da.SelectCommand.Connection = Con
        da.SelectCommand.ExecuteNonQuery()
        da.Fill(ds, "table1")
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "table1"
        
        Con.Close()
    End Sub
End Class

you can do it using OLEDB as following:

Imports System.Data.OleDb
Public Class frmMain

Dim MyConString = "Public con As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=pos.accdb"

 Dim Con As New OleDbConnection With {.ConnectionString = MyConString}
Dim ds As New DataSet

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
        Con.Open()
        Dim cmd As New OleDbCommand
        cmd.CommandText = "select * from table1"
        cmd.Connection = Con
        Dim da As New OleDbDataAdapter

        da.SelectCommand = cmd
        da.SelectCommand.Connection = Con
        da.SelectCommand.ExecuteNonQuery()
        da.Fill(ds, "table1")
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "table1"


        
        Con.Close()
    End Sub
End Class

[ken0111]

Pls i need Big help by the close of today, so if u can pls help me out

this three tables will work with each other
the fellowing are the code i have wrote and am not get getting the answer 
1.i want to subtruction to be than in Product(Remainders) tabel when insertions is than into Shops(Quantity)
2. iwant to subtruction to be than in Shop(Quantity) table when insertion is than into ProductSold(Quantity)



Create table Shop
(
    ItemCode varchar(10)not null Primary key,
    ShopName Varchar(50) not null,
    Product varchar(50) not null,
    Quantity int not null,
    OrderDate datetime NOT NULL DEFAULT GETDATE(),  
    UpdateDate varchar(11)not null
)
GO
Create Table Product 
(
    ProductCode varchar(10)Primary key,
    ShopName varchar(50),
    Product varchar(50),
    Quantity int ,
    UnitPrice money ,
    Defects int ,
    Remainders int ,
    TotalPrice money ,
    Benefit money ,
    OrderDate datetime NOT NULL DEFAULT GETDATE(),
    UpdateDate varchar(11)
)
Go
Create Table ProductSold 
(
    ShopName varchar(50)not null Primary key,
    Product varchar(50) not null,
    Quantity int not null,
    UnitPrice money not null,
    TotalPrice money ,
    OrderDate datetime NOT NULL DEFAULT GETDATE() 
)


Create Trigger TrgInsertProduct ON Product
For Insert
AS
Declare @Code varchar(10)
Declare @Name varchar(50)
Declare @Product varchar(50)
Declare @Quantity int
Declare @ODate DateTime
Declare @UDate DateTime

Select @Name = ShopName From Inserted
Select @Product = Product From Inserted
Select @Quantity = Quantity From Inserted
Select @ODate =  OrderDate From Inserted
Select @UDate = UpdateDate From Inserted 
Select @code = ProductCode From Inserted
Begin
Update Product
Set Remainders = Quantity - Defects,
    Totalprice = Quantity * UnitPrice,
    Benefit = (Quantity - Defects) * UnitPrice,
    UpdateDate = getDate()
Where ProductCode = @Code
Insert Shop
Values(@Code, @Name,@Product,@Quantity,@ODate,GetDate())
End
Go 

create PROCEDURE InsertProductSold(@Name varchar(50),@Product varchar(30),@Quantity int,@Price money)
AS
Insert ProductSold(ShopName,Product,Quantity,unitPrice)
Values(@Name,@Product,@Quantity,@Price)
Update Product
Set Quantity = Quantity - @Quantity,
    Remainders = Remainders - @Quantity,
    TotalPrice = (Quantity - @Quantity) * UnitPrice,
    Benefit = (Remainders - @Quantity) * UnitPrice
Where ShopName = @Name and Product = @Product
Update Shop 
Set Quantity = Quantity - @Quantity
Where ShopName = @Name and Product = @Product
GO

create Trigger TrgInsertProductSold ON ProductSold
For Insert
As
Declare @Name varchar(50)
Declare @Product varchar(50)
Declare @Quantity int

Select @Name = ShopName From Inserted
Select @Product = Product From Inserted
Select @Quantity = Quantity From Inserted
Begin
Update ProductSold
Set TotalPrice = Quantity * UnitPrice,
    OrderDate = GetDate()
where Product = @Product and ShopName = @Name
End

[Reverend Jim]

First of all, you have hijacked someone else's thread (and an old one at that). That is frowned upon here. For new questions please start new threads.

The query to update the quantity field in Shop would be

UPDATE Shop 
   SET Quantity = Quantity - 1
 WHERE ItemCode = 'some code'

For example, if the ItemCode is in txtItemCode (a textbox control) the query string can be created by

query = "UPDATE Shop " & _
        "   SET Quantity = Quantity - 1 " & _
        " WHERE ItemCode = '" & txtItemCode.Text & "'"

If ItemCode is entered into a textbox then you should use parameters to guard against SQL injection. If it is selected from a combobox then the above code is safe.

[ken0111]

thank you very much i have get it than

thank you very much i have get it than 
and one more thing i want to add my database to vb.net 2005 deployment packeg so when i do install the set it can can with the database 

[Reverend Jim]

and one more thing

Please start a new thread for this.