Problem connecting to SQL Server

[bob]

Can anyone help me connect to SQL Server???

I am new to all this... it is been a titanic struggle consuming hours &

days & ....

I am trying to create an SQL database programatically. But my code
stalls before it gets started, and can't establish a connection to SQL
Server.
Here is my code:

========================================================

Sub MakeDatabase()
Dim cn As New SqlConnection, cmd As New SqlCommand
Dim connect$, sql$

connect$ = "Data Source=(local); Integrated Security=SSPI;
Initial Catalog=;"
cn.ConnectionString = Connect$

Try
cn.Open()
Catch ae As SqlException
MsgBox(ae.Message.ToString, MsgBoxStyle.Critical, "SQL
Connection")
Exit Sub
End Try

sql$ = "CREATE DATABASE Cf"

cmd.Connection = cn
cmd.CommandText = sql$
cmd.ExecuteNonQuery()

cn.Close()
End Sub

This thing stalls and can't connect, giving me an error #5. The error
message suggest that my installed SQL might not allow "remote access".
But I checked and it does. I am using VB 2005 Express and SQL 2005
Express.

 

[pvdg42]

Can anyone help me connect to SQL Server???

I am new to all this... it is been a titanic struggle consuming hours &

days & ....

I am trying to create an SQL database programatically. But my code
stalls before it gets started, and can't establish a connection to SQL
Server.
Here is my code:

========================================================

Sub MakeDatabase()
Dim cn As New SqlConnection, cmd As New SqlCommand
Dim connect$, sql$

connect$ = "Data Source=(local); Integrated Security=SSPI;
Initial Catalog=;"
cn.ConnectionString = Connect$

Try
cn.Open()
Catch ae As SqlException
MsgBox(ae.Message.ToString, MsgBoxStyle.Critical, "SQL
Connection")
Exit Sub
End Try

sql$ = "CREATE DATABASE Cf"

cmd.Connection = cn
cmd.CommandText = sql$
cmd.ExecuteNonQuery()

cn.Close()
End Sub

This thing stalls and can't connect, giving me an error #5. The error
message suggest that my installed SQL might not allow "remote access".
But I checked and it does. I am using VB 2005 Express and SQL 2005
Express.

Try replacing the "(local)" in your connection string with either
"<machinename>\SqlExpress", or ".\SqlExpress".

Here, SQL 2005 Express installs a named instance, using the name SqlExpress.
If your SQL Server instance is anonymous, don't add the \SqlExpress.
Also had issues here with (local). Replacing (local) with either of the
above fixed the connection.

 

[Guest]

Bob,

When you installed SQL Server 2005 Express did you take the default of
installing a named instance? (The install wants to create a named instance
named SQLExpress).

If so, then try changing the data source in the connect string to:

Data Source=(local)\SQLExpress;

Kerry Moorman

 

[Cor Ligthert [MVP]]

Bob,

Have a look at this sample on our website.

http://www.vb-tips.com/default.aspx?ID=e76b8450-4c8a-4662-8f41-d6dda3c888c8

I think there is more in than you need, but the information you can use.

You are the first one who uses still sql$ by the way in these newsgroups and
I have seen much.

There is nothing wrong with that, however as written tomorrow by GhostAD,
you don't write a program for the computers, write it to let others know
what you are doing.

I hope this helps,

Cor

 

[Cor Ligthert [MVP]]

Doh,

How I did think it was access is almost unbelievable reading the text again.

http://www.vb-tips.com/default.aspx?ID=73eab21d-db5f-46b2-8eea-6680e677e994

Sorry

Cor

 

[bob]

Thanks! That was the problem all along.

Try replacing the "(local)" in your connection string with either
"<machinename>\SqlExpress", or ".\SqlExpress".

Here, SQL 2005 Express installs a named instance, using the name SqlExpress.
If your SQL Server instance is anonymous, don't add the \SqlExpress.
Also had issues here with (local). Replacing (local) with either of the
above fixed the connection.

 

[bob]

That must have been it -- using the default during the SQL Server
Express install. Frankly, when I installed it I didn't really know
exactly what was going on... This is my first engagement/battle with
Sql Server.

Thanks so much.
Bob

 

[bob]

Cor, I'm not sure I understand you about still using "sql$". Anyway, I
got the problem fixed, and thanks for the help.

Bob

 

[Cor Ligthert [MVP]]

Bob,

When I had sent my both messages and had read the answers from Peter and
Kerry I saw as well the answer to your problem.

For that there was no need for me to sent another message. Your code is
however in a very personal style: good readable probably by VB diehards, but
not as we see code today. That was what I did mean with my text about the
mystring$. To say it in other words the old literals are not so common
anymore.

In my opinion( but not mine alone) is one of the first commands to create a
program using a program language as VBNet, is to make it maintainable by
others.

Cor

 

[bob]

Cor,

I'm glad you brought that up -- I am just in love with those "old
literals". I guess you mean by that that

Dim sql as String

is preferred to

Dim sql$

I think the last form is so much better. Especially when you see people
doing this:

Dim strSql

See what I mean?

(A lot of people must like the "sql$" approach, because I notice that
MS (after threatening to drop this capability) kept it in VB.NET all
the way through VB.NET 2005.

 

[Cor Ligthert [MVP]]

Bob,

It is in my idea not consistent (the last format we don't talk about that is
only possible with option strict of) and therefore something extra to remind
or to check for if you are not used to it.

Dim Str$ is the exception.

Dim str as String
Dim whatever as MyClass
Dim .. as etc etc.

Therefore in my idea
Dim str as String
tells directer to people (what program language they are used) what you
mean.

However feel free to do it your way, it is only my opinion.

Cor

 

[CT]

I agree with Cor; the use of type declaration characters is strongly
discouraged. One reason is that there isn't a type declaration character for
all data types, and it makes it difficult for other developers that aren't
used to read code you have written using type declaration characters.
Obviously it is a matter of preference, but personally I find code with type
declaration characters hideous and downright difficult to read, but hey
that's just my opinion.

Another issue you touch upon Cor, is that of using Hungarian Notation (VB
prefixes such as str, int etc.), which is also discouraged. FxCop would help
here.

Just my $0.02

 

[bob]

Cor,

I see what you been there -- your way is (a) consistent with the way
other objects work in vb, and (b) clear to others that don't know vb.

I would point out re (b), that no one is going to be studying my
programs that is (a) not me, or (b) doesn't already know vb. I don't
think vb beginners are going to be using my programs to learn on!

Regarding (a), there is a certain consistency to my method, because
other common types (though not all) also can use a suffux, such as %
for integer.Remember that its nice to have a reminder of the type of
variable in the body of the procedure. Lots of people use the prefix
system -- eg, strName -- to remind them. But "Name$" is to me a lot
better than "strName". With the latter, you are constantly seeing first
the "str" instead of the actual name of the variable, and it gets old.
The other way, you can just tack on $ at the end and know its a string.

Also, I often use one-letter variable names (such a "s" for a string I
might need, "n" for a number type, etc.) If I don't have a reminder of
which ones are strings, I can get confused. In such cases, "s$" is
makes a lot more sense than "strS"

Anyway, as you say, its all what you prefer and/or what you are used
to. I just think my system is as good or better than the "strName"
approach I see in books everywhere, and I get sick of that system and
can't understand why more people don't at least mention mine. By the
way, I notice that some very good authors do actually use my system,
such as the author of the book on Windows API for Visual Basic. Check
it out.

 

[bob]

CT --

Check out my last response to Cor's comments, it gives my opinion on
this about as well as I can explain it. Thanks for your comments
though. btw, while I freely use the suffixs when they are available for
a particular data type, I don't tack them onto the numeric variables
(such as "n%") in the body of my procedures, I just use the $ in the
body, to remind me which ones are strings.

 

[Cor Ligthert [MVP]]

Bob,

Feel free to do it the way you like.

By the way, I notice that some very good authors do actually use my
system,
such as the author of the book on Windows API for Visual Basic. Check
it out.

Did you know that I had in past sometimes long discussion about given
solutions with API's.

The first thing they took than was an API while there were decent Net
solutions.

You don't see those solution almost no more (expect there where really
needed and that is seldom).

About what you wrote, you are right that not everybody is using the same
methods to declare. Although I try to avoid forever global or in top of a
method placed names. Which makes it me easy to see what the format of
something is, but even that is not standard.

Cor