How come I can connect to a database through a datasource control, but not code?

Hi I am trying to open a database connection to a Northwind database. I can open it with a datasource control and return data to a gridview, but can't programically. I get a sqlexception error. this is ust for learning.

Protected Sub Button1_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Button1.Click
Dim SAs New SqlConnection
Dim builderAs New SqlConnectionStringBuilder

builder("Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"
builder("Integrated Security") =True
builder("Connect Timeout") = 30
builder("User Instance") =True

S.ConnectionString = builder.ConnectionString

Me.Label1.Text = S.ConnectionString

S.Open()
Me.Label2.Text = S.State.ToString
S.Close()
Me.Label3.Text =Me.SqlDataSource1.ConnectionString.ToString

End Sub

The text in label2 and Label three are identical except there are "" around the datasource.

How come I can connect through the datasource control but not through code?

TallMike:

I get a sqlexception error.

What's the exception?|||

I pasted at the bottom of the reply error below. I tried removing the " " but get a syntax error at

builder("Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"

Label1 reads

Data Source=".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True

Label2 reads "Closed"

Label3 reads

Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True

I tried running this code but using the ASPNETDB database in my app_code folder, cause I thought it might be a permissions thing and I didn't set any permission after I downloaded Northwind from MS, but I got the same results

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

|||

Seems the connection string is not valid? Check the connectionString refering to 'SqlConnection.ConnectionString Property' topic in VS2005 Documentation.

FYI, here is a typical connection string:

"Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=Northwind;server=(local)"

|||

The connection string you mentioned is for a database on a server. I am trying to connect to a SQL Express database file. The first string in my example works the second doesn't. The only difference is the " "., but you can't use the connection builder without them.

Mike

|||These two ways work

S.ConnectionString ="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"S.ConnectionString = ConfigurationManager.ConnectionStrings(1).ToString

This way doesn't

builder(

"Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"

builder(

"Integrated Security") =True

builder(

"Connect Timeout") = 30

builder(

"User Instance") =True

I give up

|||I've not used theSqlConnectionStringBuilder, but it seems to me that this one line:

builder("Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"

should be 2:

builder("Data Source") =".\SQLEXPRESS"
builder("AttachDbFilename")="C:\SQL\NORTHWND.MDF"

sql

How can i use format as HH:MM:SS from seconds within the SQL query

I have the following SQL query where i want thease to be populate to GridView, but the Duration field is in Second format, I want it would be in HH:MM:SS format.

cmd ="select subscriber_id as Subscriber_no,,amount,duration from MyTable" ;

Please help me how to format this within the Query to display in GridView.

Hi tapan.behera,

Here is an example that does what you want:

declare @.durationintset @.duration = 1082587selectcast(@.duration / 60 / 60AS nvarchar) +'h:' +cast(@.duration / 60 % 60AS nvarchar) +'m:' +cast(@.duration % 3600 % 60AS nvarchar) +'s'

Kind regards,
Wim|||

Thanks for your suggestion.

But how can i embed your variable in my sql statement.

I don't want to use it as separate variable, i want to use it within my sql statement.

i.e Select amount,call_id,duration from my Table.

So how can i use your suggestion here, please help me.

|||

hi tapan.behera,

tapan.behera@.hotmail.com:

I don't want to use it as separate variable, i want to use it within my sql statement.

I was just making an example! When you use a select statement, that will eliminate the need of a variable.

tapan.behera@.hotmail.com:

i.e Select amount,call_id,duration from my Table.

It would be something like:

select amount, call_id,cast(duration / 60 / 60AS nvarchar) +'h:' +cast(duration / 60 % 60AS nvarchar) +'m:' +cast(duration % 3600 % 60AS nvarchar) +'s'as durationfrom myTable

Kind regards,
Wim