Connect to SQL Server Express Database

[dwinn]

Hello everyone,

I am developing a form in VB.Net and ASP.Net that enables a user to grab data from a SQL Server 2005 Express database and display the data on the screen depending on the search criteria that they provide.

What would be the best way to go about doing this?

Many thanks in advance.

Dan

[Pgmer]

Ur application does only search? or any other pages are there?
If only Search page you can write the code in that page. if u have mupltiple pages probably u need to go for 3 layer architecute.

[dwinn]

Hi Pgmer,

No other pages will exist, will only be the one screen.

[Pgmer]

Then in the same page u write the code to fecth the records from data base.
Write the stored procedure which accepts the search parameters fetch the data and show

[dwinn]

Woul you mind explaining a bit more at all please?

[Pgmer]

You want the code to get the data from database? or you have any code?
what exactly u need?

[dwinn]

Well a suser enters a code in to a textbox and clicks on a button to retrieve the info for that code from the backend database and display it up on the screen.

Would I be right, therefore, in assuming that I need to set up a connection string to tell the program where the database is, the table its looking for and a select statement WHERE the code provided in the text box = the code that is in the table??

If so, what sort of details would I need to provide in the connection string?

Thanks,

Dan

[Pgmer]

Persist Security Info=False; Data Source=ServerName;UID=userId;PWD=Password ;Initial Catalog=DBName;

[dwinn]

Is that all I need to connect to the database?

Do I need any code to open and close the connection?

[Pgmer]

Dim mycn As SqlConnection
        Dim strCnn As String = Persist Security Info=False; Data Source=ServerName;UID=userId;PWD=Password ;Initial Catalog=DBName;        mycn= New SqlConnection(strCnn)
        mycn.Open()
        Dim comm As New SqlCommand("your Query", mycn)
        comm.CommandType = CommandType.StoredProcedure
        Dim da As New SqlDataAdapter(comm)
        Dim ds As New DataSet
        da.Fill(ds)
        mycn.close

[dwinn]

Thank you,

I have now put the ccode in to my program as above.

My sql connection variable is underlined saying that it is being used before being assigned a value.

Dim sqlConn As SqlConnection
Dim sqlCmd As New SqlClient.SqlCommand
Dim sqlReader As SqlDataReader
 
If TextBox1.Text = "" Then
           MsgBox("A centre code needs to be provided...")
End If
            
If TextBox1.Text <> "" Then
            
       'Telling the system the location of the database.
       sqlConn.ConnectionString = "server=server;Initial Catalog=dbname;Trusted_Connection=yes"

       'Here we are opening the connection to the database.
       sqlConn.Open()
            
       'This is to say that sqlCmd is a stored procedure.
       sqlCmd.CommandType = CommandType.StoredProcedure
                        
       'This is creating the command to execute the stored procedure based on the information given in the connection string.
       sqlCmd = sqlConn.CreateCommand
            
       sqlCmd.CommandText = "exec dbo.GetAllInformation '" & TextBox1.Text & "' "

End If

Many thanks,

Dan

[dwinn]

I realised what was going on, I declared sqlConn as a new sql connection and it now works fine.

Many thanks,

Dan