Search a Database in aspx

[PseudZ]

Good Day

I have searched this forum and Googled as well, but nothing is helping much.
I'm having to design a web application for a veterinarian, it just holds records of animals and owners and such. It's a varsity project.

Up until now I have not run into problems. I'm using Visual Web Developer and can create new records, edit and delete them.

The problem comes in when I wanted to add a search option, to search animal names in the database. I have a search text box and a search button, but when I enter an animals name and click search it crashes and says it cant open the database.

I'm just wondering if anyone has any ideas how I could go about getting the search function working?

Protected Sub SearchButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim strDBConnection As String = "server=(local);database=IPGDatabase.mdf;user id=Craig;password=Pwd;connection reset=false;connection lifetime=5;Trusted_Connection=Yes;"
        Dim dbConnection As Data.SqlClient.SqlConnection
        Dim SearchTextBox As String
        dbConnection = New Data.SqlClient.SqlConnection(strDBConnection)

        
        Dim strSelectSql As String = "Select * from [IPGDatabase.mdf].[CRAIG\Craig].RecordsTable WHERE AnimalName = '" & SearchTextBox & "' ;"

        
        dbConnection.Open()

        
        Dim selectSqlCommand As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(strSelectSql, dbConnection)
        Dim sqlData As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter(selectSqlCommand)
        Dim dsSelectData = New Data.DataSet()
        sqlData.Fill(dsSelectData)


    End Sub

Not to sure if I should of coded it in the search button, or as vbscript.
Any help would be appreciated.
Thanks

 

[hyperian]

What is the actual error message that you get? And why are you using VB and not C#!

 

[PseudZ]

What is the actual error message that you get? And why are you using VB and not C#!

Because I have no idea at all how to code in C#

I get this error message:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

 

[FarligOpptreden]

Change your connection string to:

server=localhost;database=IPGDatabase;user id=Craig;password=Pwd;

Just a tip: Store your connection string in a configuration file so it would be easier to change if you migrate your DB to another server. That way you won't have to recompile your code every time.

 

[Deenem]

You're using the SQL Server data provider to connector to a Access data file, not sure you can do that, rather use the ADO provider for Access files.

[Edit: Sorry thought I saw IPGDatabase.mdb in the connection string and though it was Access. You just need IPGDatabase]

And also, you've got a SQL injection vulnerability. I can hack your website and delete Fluffy's details

 

[FarligOpptreden]

You're using the SQL Server data provider to connector to a Access data file, not sure you can do that, rather use the ADO provider for Access files.

I also thought so, until I remembered that SQL Server database files also have a .mdf file extension. So, I presumed he IS connecting to a SQL Server database, but specifying the file extension with it, which is incorrect. Hence, my connection string suggestion...

 

[PseudZ]

Yeah it is .mdf... It's the SQL express thing that comes with VWD I think.

I tried it without the extension, but it still terminates at dbConnection.open()
Is there maybe a default Username and password? I never used one before

 

[dequadin]

Change your connection string to:

server=localhost;database=IPGDatabase;user id=Craig;password=Pwd;

Just a tip: Store your connection string in a configuration file so it would be easier to change if you migrate your DB to another server. That way you won't have to recompile your code every time.

ExtraTip: Use System.Data.SqlClient.SqlConnectionStringBuilder

 

[guest2013-1]

The error message clearly states no remote connections are allowed. (possibly)

(local) as your server connection BUT this depends on the instance it's installed to.

Do you have code that references the database successfully? If so, look at it. Copy/Paste and try it out.

Otherwise, configuration of sql express might be required. Is this project running on your local PC or a server? etc...

 

[Ianvn]

I have had a similar issue with SQLExpress.
It does not allow remote connections by default.

Have a look at the following
http://support.microsoft.com/kb/914277

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition
You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

1.Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.

2.On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.

3.On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.

Note Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.

4.On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

If this alone doesn't fix it have a look at the rest of the steps.

 

[guest2013-1]

Yea, unfortunately he can't tell us if he's connecting to it remotely though. Looking at his connection string it appears it's a local installation of it (and he references the physical file, which indicates the database wasn't normally created within the sql instance but only referenced using the sql engine, which can mean loads of processing going on but who knows, never did it the MS Access database way before)

So it really is up to him to find the answer really.

Here's a huge tip (literally):

Copy and paste the first part of the error message entirely into Google and search on that

That's how I debug 99% of my errors. In the end you get to know what to check without google, but I've seen a crap load of developers spend HOURS debugging and not finding a solution, when they only could paste the error message.

Someone out there already got that error message and asked that question. If google doesn't give you an answer within the first five hits then you're googling the wrong thing.

 

[dequadin]

Here's a huge tip (literally):

Copy and paste the first part of the error message entirely into Google and search on that

That should be in the FAQ

Anyway we solved a similar problem on this forum recently (read: search the forum before posting), where SQL Express was configured with dynamic ports which was causing the problem. Check out this thread.

As I said there, this problem can also be caused by a firewall (e.g. ZoneAlarm)

 

[PseudZ]

Yes I am connecting to it remotely, I did google the error message and did come to the solution of changing the remote connection, but it still did not work after that. I tried setting the server browser to startup as well and it didn't work.

I guess I'm doing something else wrong lol, Ill try to figure something out.
Thanks to all for the help

 

[FarligOpptreden]

What does your connection string look like and what is the IP address or Computer Name of the PC hosting the database?

 

[PseudZ]

Connection string is:

 Dim strDBConnection As String = "server=localhost;database=IPGDatabase;user id=Craig;password=Pwd;connection reset=false;connection lifetime=5;Trusted_Connection=Yes;"

and Computer name is "Craig's Computer"

 

[FarligOpptreden]

What's the name of the computer that you are running the application from? The same computer? The "server=localhost" assumes that the database is hosted on the same machine from which the application is run. If it isn't the same, change it to "server=Craig's Computer".

Also, only specify a user Id and password OR set Trusted Connection to true. Don't do both. If you specify "Trusted_Connection=true", it will try and authenticate with Windows Integrated Authentication. That then assumes that your SQL Server was installed with Mixed Mode authentication, which allows for Integrated as well as Username / Password authentication.

 

[PseudZ]

It's me again... I couldn't get anything working last time, so I decided to start from scratch.

So now I have done it again, in Visual Web Developer, I dragged a DataSourceControl so that the database connection has been automatically inserted.

Then I have my form which has the SeachTextbox and the SearchButton.
That's where I need help again, to code it so I can search the database, which already has a connection to it. Must the code go into the default.aspx.vb page or into the default.aspx page? What code could I use?

As I said before Google doesn't help much with this.
Thanks very much.

 

[FarligOpptreden]

This yielded this, amongst others...

 

[Raithlin]

This yielded this, amongst others...

I see you're already in good hands...

 

[guest2013-1]

It's me again... I couldn't get anything working last time, so I decided to start from scratch.

So now I have done it again, in Visual Web Developer, I dragged a DataSourceControl so that the database connection has been automatically inserted.

Then I have my form which has the SeachTextbox and the SearchButton.
That's where I need help again, to code it so I can search the database, which already has a connection to it. Must the code go into the default.aspx.vb page or into the default.aspx page? What code could I use?

As I said before Google doesn't help much with this.
Thanks very much.

I'm not trying to be nasty, and this may sound it. But you should really pay the people teaching you all this to teach you how to google instead There's loads of tutorials out there (highlighted) to do this.