How to connect ADODB with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Mic

Hi

We are checking VB 9 (Orcas).

we connected to database created under with sql server 7. with this code

Public cn As New ADODB.Connection

Public Sub OpenDB()

cn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial catalog=Reservation;Data Source=.")

End Sub

this code worked well.

we know sql7 is not compatiable with vista. please tell us how to connect it wiith sql2005 . we downloaded orcas express edition beta. we created a database also. please let u know how to connect with Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5).

Rgds

Pramod

Hi Pramod,

VB 08 (orcas) changed a lot. You can now make a connection without having to type one line of code (using Typed dataset)

But this still depend on your need !

|||

Hi

thanks verymuch for your valuable support . We tried dataset also. it dint worked.

please check the code

'================Sample Code==========

Dim conn As New SqlConnection("Server=.\SQLEXPRESS;Database=test;Trusted_Connection=false;")

Dim ds As New DataSet

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

conn.Open()

Dim adp As New SqlDataAdapter("Select * From personal", conn)

adp.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

conn.Close()

'================Sample Code==========

Cannot open database "test" requested by the login. The login failed. Login failed for user 'Pramod\Administrator'.

plz tell how to build a connection string. (We tried datasource Configuration wizard)

Plz help

rgds

Pramod

|||

try this code

Code Snippet

'Store a connection string
Dim connectionstring As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Omar Abid\Mes Documents\data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
'Create new SQL Connection
Dim sqlconnection As New SqlConnection(connectionstring)
Using sqlconnection
Dim sqlcommand As SqlCommand = sqlconnection.CreateCommand
Using sqlcommand
sqlcommand.CommandType = CommandType.Text
sqlcommand.CommandText = "select * from table1"
Dim adapter As New SqlDataAdapter(sqlcommand)
Using adapter
Dim datatable As New DataTable("user")
Using datatable
adapter.Fill(datatable)
DataGridView.DataSource = datatable

End Using
End Using
End Using
End Using

|||

Thanks dear

it worked well,

our practice is to use pass queries to database like

cn.execute (Insert into ((.......))

how the dame works with data adapter.

Plz help us with a sample code

rgds

Pramod

|||

Hi

please advice us regarding the code. is it the usual option available.

-

Dim ds As New DataSet

Dim adp As New SqlDataAdapter("Select max(" & Field & ") + 1 as MAxid from " & tableName & IIf(Condition <> vbNullString, " where " & Condition, ""), cn)

adp.Fill(ds)

If IsDBNull(ds.Tables(0).Columns("MaxID")) Then

GetMaxID = 1

Else

GetMaxID = ds.Tables(0).Columns("MaxID")

End If

--

|||

sorry i don't know a lot on SQL. But I think that Adapter is the new technology.

For VB 9 there's also LINQ you may be interested on it

|||Hi

how can i connect with a client system. (in case of server we are using a path from the system). we want to connect it from networks also)