Data Source Configuration Wizard issue

[omacron]

Hi

First want to say I am very new to VB (like 6 hours into learning it) and using VB 2010 Express. I need to connect to a MS SQL DB, that is not on my computer. If I use the Data Source Configuration Wizard the only options for the type of Database connection are;

Microsoft SQL Compact 3.5
Microsoft SQL Server Database File

Issue is with both is they do not allow me enter in the server IP/DNS or change to SQL authentication (witch I need to use).

Now doing some quick search I am able to connect to a database on my machine (not the one I need to connect to) with a quick test;

Imports System.Data.SqlClient
Public Class Form1
    Private sqlString As New SqlConnection("Data Source=localhost;Initial Catalog=StockManagement;Integrated Security=True")


    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        sqlString.Open()
        MsgBox(sqlString.State.ToString)
        sqlString.Close()
    End Sub

End Class

Issue is unless I am greatly mistaken I have to manually program in all data grids (etc...) instead of using the UI. This means a lot more work for me as I have to learn the code not just select it from a right click.

I am assuming its some library that is currently missing from the project but cannot seem to be able to find an answer.

thanks

 

[Bluejay07]

Unless it's just me but your post seems to be all over the place. Are you having an issue with opening a connection, setting declaration or loading a datagrid?

If you are having connection issues, I believe most programmers here will advise you not to use a connection wizard - even if you are just starting to learn. You will have more control if you set the connections yourself. If you are having issues loading the data into a grid then you will require a dataset, datatable, sql adapter or several other data manipulation options.

Could you please provide more information on what you are trying to do or what you are having difficulty with and we may be able to guide you to a starting point.

Also, which version of windows and which version of sql are you using?

If at first you don't succeed, then sky diving wasn't meant for you!

 

[jebenson]

If Visual Studio 2010 Express is like the previous Express versions, then the only databases supported in the IDE are SQL Server Express and Access. To use other databases, you need to manually program the connections, etc. Honestly, I think this is the better way of doing it, as it gives you much more control over exactly how and when any changes are made to the data, and so forth.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[omacron]

Bluejay07 I will try to be more clear. To your point;

If you are having connection issues, I believe most programmers here will advise you not to use a connection wizard - even if you are just starting to learn. You will have more control if you set the connections yourself. If you are having issues loading the data into a grid then you will require a dataset, datatable, sql adapter or several other data manipulation options.

No I am not having an connection issue. I want to use the connection wizard but the only options are;

http://members.shaw.ca/mwence/changedatasource.jpg

The options for Microsoft SQL Server Compact 3.5 are;

http://members.shaw.ca/mwence/MSSQLCompactjpg.jpg

click the Browse button pulls up a file explore and only allows *.sdf. This option is not possible for two reason;

1. The database is not on my computer.
2. The database is a mdf not sdf

The options for Microsoft SQL Server Database File are;

http://members.shaw.ca/mwence/MSSQLDBFile.jpg

click the Browse button pulls up a file explore and only allows *.mdf. This option is not possible for two reason;

1. The database is not on my computer
2. I can not use windows authentication and if SQL authentication is select I get an error message "Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance. The connection will be close"

The version of Windows is Windows 7 64bit, and the version of MS SQL is MS SQL 2008 R2 Express.

What I can do; If I connect via System.Data.SqlClient, reading and writing to the database is not a problem. Yes I understand this will give me more control but the learning curve is quite high.

Having said all this I believe jebenson has the answer. Since I need to connect to MS SQL via a OLDE DB or MS SQL Server connection (note: not file like I have) VB Express does not support this is in the IDE.

thanks

 

[Bluejay07]

Hello,
Sorry for the delay in this response.
Have you obtained a solution yet?

If you are using SQL authentication, you may be required to first set up an ODBC connection. Since you are using a 64 bit machine, you need to determine if your program is a 32 bit or 64 bit program. Traditionally, VB programs are 32 bit applications.

64 bit ODBC driver:
Control Panel\System and Security\Administrative Tools\Data Sources (ODBC)

32 bit ODBC driver:
%WINDIR%\SysWOW64\odbcad32.exe

ODBC (Open Database Connectivity) provides a way for client programs (eg Visual Basic, Excel, Access, etc) to access a wide range of databases or data sources.

If at first you don't succeed, then sky diving wasn't meant for you!