1st time using SQL server 2005...

VS2008

  • (2 Pages)
  • +
  • 1
  • 2

26 Replies - 2491 Views - Last Post: 02 June 2009 - 11:34 PM Rate Topic: -----

#1 jeff87  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 17-March 09

1st time using SQL server 2005...

Posted 01 June 2009 - 08:49 PM

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



Is This A Good Question/Topic? 0
  • +

Replies To: 1st time using SQL server 2005...

#2 searockruz  Icon User is offline

  • D.I.C Regular

Reputation: 24
  • View blog
  • Posts: 460
  • Joined: 07-March 09

Re: 1st time using SQL server 2005...

Posted 01 June 2009 - 09:16 PM

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
Was This Post Helpful? 0
  • +
  • -

#3 jeff87  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 17-March 09

Re: 1st time using SQL server 2005...

Posted 01 June 2009 - 11:17 PM

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

Was This Post Helpful? 0
  • +
  • -

#4 searockruz  Icon User is offline

  • D.I.C Regular

Reputation: 24
  • View blog
  • Posts: 460
  • Joined: 07-March 09

Re: 1st time using SQL server 2005...

Posted 01 June 2009 - 11:35 PM

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

Was This Post Helpful? 0
  • +
  • -

#5 jeff87  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 17-March 09

Re: 1st time using SQL server 2005...

Posted 01 June 2009 - 11:38 PM

ok i edited...but still same error..
Was This Post Helpful? 0
  • +
  • -

#6 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: 1st time using SQL server 2005...

Posted 01 June 2009 - 11:39 PM

for DataSource you can use DOT
str = "Datasource=.;Database=mydb;Integrated Security=TRUE;"
//or
str = "Server=.;Database=mydb;Integrated Security=TRUE;"

Was This Post Helpful? 0
  • +
  • -

#7 jeff87  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 17-March 09

Re: 1st time using SQL server 2005...

Posted 02 June 2009 - 12:01 AM

same problem...
Was This Post Helpful? 0
  • +
  • -

#8 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: 1st time using SQL server 2005...

Posted 02 June 2009 - 12:31 AM

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 & ";"

Was This Post Helpful? 0
  • +
  • -

#9 jeff87  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 17-March 09

Re: 1st time using SQL server 2005...

Posted 02 June 2009 - 12:58 AM

yea i m using sql server 2005!not microsoft access!!
Was This Post Helpful? 0
  • +
  • -

#10 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: 1st time using SQL server 2005...

Posted 02 June 2009 - 01:04 AM

then check out your code some codes are SQL and some are OleDB :blink:
Was This Post Helpful? 0
  • +
  • -

#11 jeff87  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 17-March 09

Re: 1st time using SQL server 2005...

Posted 02 June 2009 - 01:07 AM

yea !!coz i duno how to use sql .....
Was This Post Helpful? 0
  • +
  • -

#12 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: 1st time using SQL server 2005...

Posted 02 June 2009 - 01:10 AM

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

Was This Post Helpful? 0
  • +
  • -

#13 jeff87  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 17-March 09

Re: 1st time using SQL server 2005...

Posted 02 June 2009 - 01:18 AM

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....
Was This Post Helpful? 0
  • +
  • -

#14 noorahmad  Icon User is offline

  • Untitled
  • member icon

Reputation: 209
  • View blog
  • Posts: 2,290
  • Joined: 12-March 09

Re: 1st time using SQL server 2005...

Posted 02 June 2009 - 01:24 AM

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"

Was This Post Helpful? 0
  • +
  • -

#15 jeff87  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 85
  • Joined: 17-March 09

Re: 1st time using SQL server 2005...

Posted 02 June 2009 - 01:26 AM

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...
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2