Newbie - SqlConnection Statement

[Randy]

I'm trying to get a combobox to fill with values from a table. I've
set up an example using the Northwind database with the Categories
table. Dragging in the CategoryID field onto my form as a combobox,
I've set the following properties for the CategoryID combobox:

Value member = "CategoriesBindingSource - CategoryID"
Display Member = "CategoriesBindingSource - CategoryID"

This seems to bring in the correct values in the two non-index fields
(CategoryName and Description). I've then added the following code to
the combobox:

Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()

Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet

da.Fill(ds, "Categories")

CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"

End Sub


The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.

I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database. I am working on a
standalone pc.

Can anybody see where I am going wrong?
Thanks,
Randy

 

[Cor Ligthert [MVP]]

Inline typed in this message so watch typos or whatever:

Dim Conn As New SqlConnection("Database=Northwnd.mdf")

incomplete see www.connectionstrings.com

Conn.OpenDim da As New SqlDataAdapter("SELECT CategoryID FROM Categories",
Conn)

Dim ds As New DataSet

da.Fill(ds, "Categories")

CategoryIDComboBox.DataSource = ds

CategoryIDComboBox.ValueMember = "CategoryID"
CategoryIDComboBox.DisplayMember = "CategoryID"

Conn.Close

End Sub

Cor

 

[Randy]

Still as confused as ever. Did I mention the newbie part? I
appreciate your help, but giving me a link to a site with about 4,000
different possibilities is the proverbial needle in a haystack. Sorry
for the frustration, but I've been at this for days with no progress
and its getting old.

 

[RobinS]

Don't repost your questions once you've asked them. I already answered this
the first time you posted it.

Robin S.

 

[Cor Ligthert [MVP]]

Randy,

I did change completely all your code, and than this answer,

I am glad you are no sample for others.

Cor

 

[Randy]

I owe you an apology. It was late and I was frustrated from days of
spinning my wheels. I got your reply and was thinking that my problem
was over, but then I couldn't decipher the link that you sent (still
can't for that matter). I appreciate your help and am embarrassed
that I responded the way that I did. I shouldn't have taken it out on
you. My apologies. I hope you will forgive me.

Randy

 

[Cor Ligthert [MVP]]

No problem

Cor

 

[RobinS]

Here's what Cor meant: Your connection string is wrong. Most people look at
www.connectionstring.com for help with that.

Robin S.

 

[Cor Ligthert [MVP]]

Robin,

No that was not what Cor meant, Cor gave Randy completely revisted code.

However withouth the connectionstring. That could Randy find on the link to
the website he gave.

Randy saw that latter as well.



Cor

 

[Randy]

Sorry, I didn't mean to start anything. I tried the code that Cor
gave me, but I simply cannot find out how to complete the connection
string. It if very obvious to me that that is where I am stuck.
However, after countless searches of this board, MSDN, and
connectionstrings.com, I am making absolutely no progress. I've
worked extensively with Excel, Access, and VBA, but never with
connecting to outside databases.

If I may ask (humbly) can I enlist one of you to help me complete that
single line of code? I will give you whatever specific pieces of
information that you need to help me find this answer.

Conn = New SqlConnection("Database=Northwnd.mdf")

My dataset is called NorthwndDataSet
My sole table is Categories
I am working on a standalone PC, so no servers or other machines
involved.

Is there anything else that you would need to complete this line of
code?

Again, thank you very much for your help. Sorry that I am having such
a mental block over this thing. I guess the light hasn't clicked on
yet (to say the least).

Randy

 

[Branco Medeiros]

I'm trying to get a combobox to fill with values from a table.

Dim Conn As SqlConnection
Conn = New SqlConnection("Database=Northwnd.mdf")
'Conn.Open()

Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT
CategoryID FROM Categories", Conn)
Dim ds As New DataSet

da.Fill(ds, "Categories")

CategoryIDComboBox.DataSource = ds
'CategoryIDComboBox.ValueMember = "CategoryID"
'CategoryIDComboBox.DisplayMember = "CategoryID"

End Sub

The lines that are commented out are other ideas that I've tried to no
avail. I left them here in case they are relevant.

I suspect that at least part of my problem is in the SqlDataAdapter
statement where I am pointing to the database.

<snip>

As others pointed out, the main problem in your code (at least for
now) is the invalid connection string you're using. Connection strings
are hard to get at first, and after you learn how to use a tool to
create then for you, they're easy to forget.

The site Cor provided is quite a treasure in this regard, but I supose
it's one of those situations where you ought to know exactly what to
look for. Not your case, yet. A few more weeks, maybe. =)

One way to have a connection string automaticaly built for you is
using the IDE. If you're using VB Express your only choice at this
point is using .mdf or .mdb files (not that you can't use other
databases, but VB Express won't automatically create the connection
for you). Visual Studio, by its turn, will offer a large variety of
database types.

Anyhow, just select the menu 'Data' >> 'Add New Data Source...' and
select the Database icon. Then click Next.

Here you must select the apropriate database type. Click "New
Connection..." and then, when the Add Connetion window pops up, click
"Change..." to specify a db type and data provider.

The Change Data Source window will pop up, from where you must select
"Microsoft SQL Server Database File" as datasource and ".Net Framework
dataprovider for SQL server" as the provider.

Click Ok and then, back to the Add Connection window, click
"Browse..." to navigate to the Northwind.mdf file. After selecting the
file and clicking Open, you'll probably select "Use Windows
Authentication" as the logon method, but I guess this will depend on
how you made the logon choices when you installed the database.

Click "Test Connection" to see if everything went right -- improvise a
little if not -- and click Ok once you get a valid connection.

Now -- roll the drums -- if you look below the "Connection String" tag
(you may need to click the plus sign for it to appear) you'll see the
so called connection string ready for you to use. Select it, copy it,
hit cancel and fly back to your code. Paste it in place of your old
connection string text (don't forget the quotes) and try it (you'd be
in better shape if you'd created a Private Const CON_STR As String,
global to the form, and assigned the string to it, using the Const
name in the actual SqlConnection() line, but I guess this is too much
information, for now).

In my case the connection string was:

"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Temp
\tests.mdf;Integrated Security=True;Connect Timeout=30;User
Instance=True"

(Since I didn't install the Northwind DB, I created a dummy test.mdf
file)

Come back here if anything went wrong (of course, you'll want to
uncomment the Conn.Open line and add a Conn.Close at the end of the
procedure, as subtly suggested by Cor).

HTH.

Regards,

Branco.

 

[Cor Ligthert [MVP]]

Mostly,

Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" &
_
" Data Source=C:\XXXX.mdb;User Id=admin;Password=;")

However if it is created by office than you can have some problems, by
instance with internal passwords.

Cor

 

[Cor Ligthert [MVP]]

Doh,

I saw to late that it is mdf, go to the Tab data and click on databases,
than follow the instructions.

Cor

 

[RobinS]

You're right. I was confused because apparently I was having trouble
reading the inline posting. My bad!

Robin S.

 

[RobinS]

Sorry, I didn't mean to start anything.

Don't worry; Cor and I have been peacefully coexisting for months now, and
will continue to do so. (At least, I assume!)

For regular SQLServer, it would be something like this:

connString = "Data Source=myservername;Initial Catalog=databasename;" & _
"IntegratedSecurity=true"

That's assuming you know the name of your server and your database, and
that you are using Windows security.

If you are using SQLServerExpress, Data Source might be something like
"(localhost)". I'm using the full enchilada, so I can't give you a 100%
factual answer.

Robin S.

 

[PFC Sadr]

right click in Windows explore, build a new text file, chnge the
extension to UDL

double click; set the connection info in a UDL

change the extension back to txt and you've got your connection string

 

[Branco Medeiros]

right click in Windows explore, build a new text file, chnge the
extension to UDL

double click; set the connection info in a UDL

change the extension back to txt and you've got your connection string

hey, this was quite cool!

Regards,

Branco.

 

[Randy]

I tried this, but surprise, it didn't work. What does this mean:

"set the connection info in a UDL"

Am I to conclude that connection strings are the bane of Visual Basic?

Thanks again.
Randy

 

[Branco Medeiros]

I tried this, but surprise, it didn't work. What does this mean:
"set the connection info in a UDL"

It means than when you double click the UDL file, a window will popup
where you can set options much like the ones I told you previously.

Am I to conclude that connection strings are the bane of Visual Basic?

=)))

They're not *so* bad once you get the hang of it. Having XML
constructs as part of the language (as we'll have in next VB) --
*that*, my friend, will be the wierdest thing yet to appear in
programming language (to this old-timer)...

Regards,

Branco.