Connecting to sql server

  • Thread starter Thread starter don
  • Start date Start date
D

don

If this is not correct group, please point me in right direction.

I am very new to vb.net. I purchased the standard version of Visual
Basic.net.

I am trying to get a row of data from a SQL Server on our network.

I have gone to tools > connect to databases and set up a connection to
our sql server. When I test the connection, it says "Test conection
succeeded".

When I click ok in the Data Link Properties Window which just validated the
connection, I am asked for password to the server and I respond, then I get
an error message as follows:

Unable to connect to database.
It is only possible to connect to SQL Server Desktop Engine databases and
microsoft access databases with this version of Visual Studio.

Do I need to purchase something else in order to do this. This small
project is the reasonn I purchased the Visual Basic.Net software in the
first place. It said I could connect to a wide range of data sources.

Thanks in advance for any help.
 
For the record: I hate Visual Basic .NET Standard.

Having said that, you should be able to do what you desire by moving away
from the "automagic" tools (wizards). Here is some code that might help:

Private Function GetData() As DataSet

'Change the server name, database and user info
'for your DB
Dim connStr As String = "Server=(local);Database=" & _
"pubs;UID=sa;PWD=;"
Dim sql As String = "SELECT * FROM Authors"

Dim conn As New SqlConnection(connStr)
Dim cmd As New SqlCommand(sql, conn)

'Name here should be a friendly name
'for your dataset
Dim ds As New DataSet("nameHere")
Dim da As New SqlDataAdapter(cmd)

'Change FriendlyName to a table name for your
'data.
da.TableMappings.Add("Table", "FriendlyName")

Try
conn.Open()
da.Fill(ds)
Finally
If conn.ConnectionState = _
ConnectionState.Open Then
conn.Close()
End If

conn.Dispose()
End Try

End Function

You can test this by binding a DataView to this routine:

DataView1.DataSource = GetData()
DataView1.DataBind()

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************
 
For the record: I hate Visual Basic .NET Standard.

Having said that, you should be able to do what you desire by moving away
from the "automagic" tools (wizards). Here is some code that might help:

Private Function GetData() As DataSet

'Change the server name, database and user info
'for your DB
Dim connStr As String = "Server=(local);Database=" & _
"pubs;UID=sa;PWD=;"
Dim sql As String = "SELECT * FROM Authors"

Dim conn As New SqlConnection(connStr)
Dim cmd As New SqlCommand(sql, conn)

'Name here should be a friendly name
'for your dataset
Dim ds As New DataSet("nameHere")
Dim da As New SqlDataAdapter(cmd)

'Change FriendlyName to a table name for your
'data.
da.TableMappings.Add("Table", "FriendlyName")

Try
conn.Open()
da.Fill(ds)
Finally
If conn.ConnectionState = _
ConnectionState.Open Then
conn.Close()
End If

conn.Dispose()
End Try

End Function

You can test this by binding a DataView to this routine:

DataView1.DataSource = GetData()
DataView1.DataBind()
Click to expand...

I probably did not make myself clear. I can get data on my pc, just can't
access a MSSQL Database on a server on the network. I get this message:

Unable to connect to database.
It is only possible to connect to SQL Server Desktop Engine databases and
microsoft access databases with this version of Visual Studio.


I did not understand everything you wrote, but when you used the word
"local", I assume that is my pc.

Thanks
 
I will take a step back.

What I was getting at is the Visual Basic Learning Edition (aka, VB .NET
Standard) will not let you use the wizards to connect to certain databases.
You end up having to write the code manually for this funcationality. The
code I had is the manual code.

The connection string with (local) is an acronym for the local machine. But,
it could have been Server=MyServer or any other server name. the "Standard"
version tries to stop you from doing "Enterprise" level work, which means
connecting to _________ (fill in your favorite database that does not work
with the wizards. The idea is you learn on "Standard" and then move up to
"at least" Professional of VS .NET.

Personally, I wish MS had never released the product with the name
"Standard" as it sounds like it is more than it is. Not to malign the
product, overall, as it can do some great coding, but the word "Standard"
makes it seem like the standard, rather than basic, version.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************
 
I will take a step back.
other lines removed
Click to expand...

Cowboy (Gregory), I will take two steps back. I did not understand what
you were trying to tell me. I apologize!! This is my first "Real" program
that I am trying to write.

I have added your code, modifying it for my server and database.

When I add a dataview from the tool box and put the code:
DataView1.datasource = getdata()

I get an error (squiglly line under dataview1.datasource) and when I put my
cursor on it, it shows:

'datasource' is not a member of 'System.Data.Dataview'

Is that what you intended by the coding:

You can test this by binding a DataView to this routine:

DataView1.DataSource = GetData()
DataView1.DataBind()


Any help at this point?

TIA

Don
 
Back
Top