[jeff87]

i used to write a program with ms access database...but thn now i would like to try on sql server...and its seems quite tough for me since i am new on it....this is the way i code,and i got the error on the bold line...

 Dim constr As String = "Data Source=Kindergarten.mdf"";Integrated Security=True;Connect Timeout=30;User Instance=True"
	Dim sqlstr As String = "SELECT * FROM [Table1]"

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
	  [b]  Dim dataadp As New OleDb.OleDbDataAdapter(sqlstr, constr)[/b]
		dataadp.Fill(Subjects)
		dataadp.Dispose()
	End Sub

[searockruz]

you are passing the connection string for access database

you can find your connection string here

Dim constr As String =" Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
    Dim sqlstr As String = "SELECT * FROM [Table1]"

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      [b]  Dim dataadp As New OleDb.OleDbDataAdapter(sqlstr, constr)[/b]
        dataadp.Fill(Subjects)
        dataadp.Dispose()
    End Sub

Data Source=Computer name
Initial Catalog=DataBase Name
if you have blank password then you can leave the field blank

[jeff87]

is my code correct?

 Dim data As Integer = 0
	Dim constr As String = "data source=Jeff-PC;uid=;password=;initial catalog=Kindergarten.mdf;"
	Dim con As New SqlConnection(constr)
	Dim query As String

	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		query = "Select * From Table1"
		Dim cmd As New SqlCommand(query, con)
		Dim ada As New SqlDataAdapter(cmd)
	   
	End Sub
	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		Dim newcon As New OleDb.OleDbConnection(constr)
		Dim addstr As String = "INSERT INTO `Table1`(`ID`, `name1`) VALUES (add1, add2)"
		Dim addcomm As New OleDb.OleDbCommand

		addcomm.Connection = newcon
		addcomm.CommandText = addstr

		addcomm.Parameters.AddWithValue("add1", TextBox1.Text)
		addcomm.Parameters.AddWithValue("add2", TextBox2.Text)

		newcon.Open()
		addcomm.ExecuteNonQuery()
		newcon.Close()

		MsgBox("Account Created Successful")
		retable()

but my insert function is not function...i get this
An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

This post has been edited by jeff87: 01 June 2009 - 11:18 PM

[searockruz]

no

your connection string should be

Dim constr As String = "Server=Jeff-PC;Database=Kindergarten;User ID=sa;Password=;Trusted_Connection=False"

your username cant be left blank, normally sa
it can happen that your password is blank

you dont have to mention database file name in database attribute
just specify the name
like

Database=Master

This post has been edited by searockruz: 01 June 2009 - 11:36 PM

[jeff87]

ok i edited...but still same error..

[noorahmad]

for DataSource you can use DOT

str = "Datasource=.;Database=mydb;Integrated Security=TRUE;"
//or
str = "Server=.;Database=mydb;Integrated Security=TRUE;"

[jeff87]

same problem...

[noorahmad]

ahh i was thinking that you are using MS SQL
the connection String for OleDb is

Dim constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\b1.mdb;"

or

Dim constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Application.StartupPath & ";"

[jeff87]

yea i m using sql server 2005!not microsoft access!!

[noorahmad]

then check out your code some codes are SQL and some are OleDB

[jeff87]

yea !!coz i duno how to use sql .....

[noorahmad]

your Error is you are not opening you connection con.open()
here is the code

  Dim data As Integer = 0
	Dim con As New SqlConnection
	Dim cmd As New SqlCommand

	Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		Dim constr As String = "data source=Jeff-PC;uid=;password=;initial catalog=Kindergarten.mdf;"
		con.ConnectionString = constr
		con.Open()
	End Sub

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		Dim str As String = "INSERT INTO `Table1`(`ID`, `name1`) VALUES (" & txtid.text & ", '" & txtname.text & "')" 'Here you can use update query
		cmd = New SqlCommand(str, con)
		cmd.ExecuteNonQuery()
		MessageBox.Show("Data Saved")
	End Sub

This post has been edited by noorahmad: 02 June 2009 - 01:22 AM

[jeff87]

and this is my full path of the sql database
Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\Jeff\Documents\Visual Studio 2008\Projects\test\test\Kindergarten.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

but still got error with it...blue liine under it....

[noorahmad]

use this

strcon = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Jeff\Documents\Visual Studio 2008\Projects\test\test\Kindergarten.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

[jeff87]

An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

when i click my insert button...it come out this error!!omg...