SQL Data Adapter

[Guest]

I am relatively new to VB.Net so bear with me. I can add an OleDBDataAdapter
using the Jet 4.0 with no problem (although I'm having problems getting Date
and Time formats to carry over to the database). However, I can't seem to
get the SQLDataAdapter to work. When I click on the provider tab it defaults
to “Microsoft OLE DB Provider for SQL Serverâ€. On the connection tab I have
to enter the name of an SQL server name (the drop down doesn’t show
anything). By typing in “(local)â€, I can see the database on my PC. I can
select the database and test the connection and it tells me “Test connection
succeededâ€. I click “OK†to that and “OK†on the “Data Link Properties†box
and I get the 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.†Can anyone help me with this?

Thanks,

MikeS

 

[Cor Ligthert [MVP]]

Mike,

The versions VB 2002, VB2003 and VB2005 are working with the provider
wizards different. Than there is that inside those versions some of the
versions work different, therefore can you tell us what you are using?

Cor

 

[Lucky]

this must be because the code is not getting the Db server by the name
"(local)". u must have give the name of the server explicitly. check
out the server name. it may be possible that u missed something in the
name. i had this problem. in my case the server name is too long and
differnt then i was giving so it was not able to find and of course
there are some settings because of them i was not able to get the
server by "(local)". this might help u

 

[Guest]

I too am new to VB.NET(2 days), and am experiencing the exact problem you
are. After two days I have no light to shed on our problem.

 

[Guest]

The version I am using is:

Microsoft Development Environment 2003 Version 7.1.3088

This version came with a text book I purchased through the local community
college. I was thinking it might be a limited version because of being
distributed through the school system. My instructor tells me that it is not
a limited version.

I tried adding the adapter using OleDBDataAdapter, SqlDataAdapter, and
OdbcDataAdapter and none of these will work. Normally when I add an ODBC
adapter for SQL using the Administrative Tools, I can click on the Server
drop-down box and see a list of all the servers available to me. However,
when I click on the server drop-down box using the wizzard in VB.NET, I don't
see any servers. I figure I must be doing something wrong.

Thanks in advance for your help,

MikeS

 

[Lucky]

if you are able to see all the servers in "server explorer" of .net
ide, than select the server you want to use and goto the table you want
to use. now drag the table on your windows form and IDE will create
neccesory objects. this might solve your problem. if it dosent work
than at least you will get idea where is the problem. than we can
discuss it further

 

[Cor Ligthert [MVP]]

Mike,

In addition to Luke, are you sure that you have rights to access that
Server?

Cor

 

[Guest]

Cor and Luke,

I have admin rights...I can see everything on our LAN. Read my original
question. If I set up and ODBC using Control Panel | Admin Tools | Data
Sources(ODBC), I can click on the Server drop down list and see all SQL
servers that are available.
In VB.NET, when I try to set up the SqlDataAdapter, the list box does NOT
show anything. However, If I type in "(local)", then I can see the databases
listed the drop down list. (local) is one of the options when I set up an
ODBC because I have and instance of SQL server installed on my PC for
development. After entering "(local)" and selecting the appropriate
database, I can click on the Test button and it tells me the test was
successful. After I click OK I get the error message (see my original
question). Let me know if you need more information.

Thanks,

MikeS

 

[Cor Ligthert [MVP]]

Mike,

I may assume because of the given information by you that your Server is
not the SQLExpress?

Because that does not show up in version 2003, you can fill than the
"MachineName/SQLEXPRESS" in the box or make a connection string by hand for
that (containing as well that information).

I am curious if it was that.

Cor

 

[Guest]

Hi,

I hope that this will help, it solved my problem by taking Microsoft SQL
Server 2000 Developer edition off and installing the Microsoft SQL Server
2000 Desptop Engine packaged with the Developer edition or available at
http://www.asp.net/msde/Default.aspx?tabindex=0&tabid=1

Because of my lack of technical knowledge I am not sure why it is so or
whether it may help with your problem too.

Good Luck

 

[Guest]

Sorry it took so long for me to respond. The service hasn't been available
for a few days. But I think you have it. I can enter the server manually
and then I can see all the databases on my local server. And the test is
successful. But when I click ok to accept it gives me the message that says
"Unable to connect to database. It is only possible to connect to SQL
Desktop Engine databases and Microsoft Access databases with this version of
Visual Studio." Maybe I'm using the wrong provider or the wrong adapter
type? Are there any patches or updates to the version I'm using that might
help?

Thanks, Mike

 

[Cor Ligthert [MVP]]

Mike,

My question was if you are using the SQLExpress version. That does as well
not show up in my VS2003 wizards. The other servers including SQL server
2005 do.

Cor

 

[Guest]

Sorry. Guess I didn't understand your question. The only SQL adapters
availble are:

1. Microsoft OLE DB Provider ro SQL Server and
2. SQL Server Replication OLE DB Provider for DTS

Is this what you're asking me?

MikeS

 

[Jim Crawford]

I am in the same boat Even though it coonects and tests OK VB.net
Standard Edition will only connect to MS SQL Desktop Edition or Access
Data. We have to upgrade to Professional version.