Opening an SQL Connection

Hello,

I am very new to SQL and do not understand as much about the language as I had hoped I would after a couple months of looking at it. I am creating a procedure database for work where we store word documents of procedures in a folder and use that to populate our database. I've been getting connection errors because I didn't realize I was trying to use the server.CreateObject command with an Apache DB and then using the connection string with an SQL Server DB. I am using SQL Server 2008 and I just ahd no idea why it wasn't connecting because I had been googling most errors.

A co-worker referred me to this site saying that I would be able to get good advice here. If anybody can reply with some samples SQL Server 2008 connection scripts, that would be great. I want to also mention that I am using VB .NET 4.0 Framework.

I'd like to stress again that I am a beginner in this programming fashion and would like to know. If any part of my questions aren't clear to you, please let me know so I can figure out what you're asking to help me get a better grasp. Thanks!

Have you looked at www.connectionstrings.com

Yes I have. Unfortunately as I said I don't know enough about SQL to understand which connection string I should be using and under which options. One thing I want people from anywhere in the plant I work in to be able connect so it will have to connect via IP probably. Like I said I'm not 100% sure which one to use in this case.

I guess I'm wondering what the command is for Server.CreateObject for SQL Server 2008?

I'm not sure what you are asking. In the case of web apps I have did the Server.Create object portion is the same as always. The connection string is the part that is different.

Under ADO my connection string would look something like this

Code:

---

Provider=MSDASQL; DRIVER=Sql Server; SERVER=MyServerName; DATABASE=MyDatabase; UID=MyUserID; PWD=MyPassword;

---

Under ADO.Net it would look something like this

Code:

---

Data Source=MyServerName;Initial Catalog=MyDatabase;Integrated Security=True

---

These work with various versions of SQL Server

The link I gave before gives sample connection strings for pretty much anything you would ever need.

This is the error message I am getting when trying to connect to my database.

Server Error in '/' Application.
--------------------------------------------------------------------------------

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

Source Error:


Line 43: If Len(Trim(strSearch)) > 0 Then
Line 44: ' Set up our connection.
Line 45: objConnection = Server.CreateObject("ADODB.Connection")
Line 46: objConnection.ConnectionString = "Driver={SQL Server}; Server=MECONTROLS_PLT1\SHAREDB; Database=master; UID=Controls; PWD=Password1"
Line 47: objConnection.Open()


Source File: C:\inetpub\wwwroot\bluemasters\sql_conn_test.aspx Line: 45

Stack Trace:


[InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.]
ASP.bluemasters_sql_conn_test_aspx.btnSearch_OnClick(Object sender, EventArgs e) in C:\inetpub\wwwroot\bluemasters\sql_conn_test.aspx:45
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +140
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.5446; ASP.NET Version:2.0.50727.5420

What type is your objConnection defined as?
In VB I would just define it as Object and that works fine.

Here is my full connection string...feel free to critique my coding methods as I am new to this and would like to become better, also please let me know what exactly you would do to change my code. You've been a great help so far!


----------------------------------------------------------------------------------------------

<%@ Page Language="VB" Debug="true" aspcompat=true %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">

Sub btnSearch_OnClick(sender as Object, e as EventArgs)
Dim objConnection As Object
Dim objCommand As Object
Dim objAdapter As Object
Dim objDataSet As Object
Dim strSearch As String
Dim strSQLQuery As String


' Get Search (currently using 'backup' and a fill word so it doesn't skip the SQL connection)
strSearch = "backup"
' If there's nothing to search for then don't search
' o/w build our SQL Query and execute it.
If Len(Trim(strSearch)) > 0 Then
' Set up our connection.
objConnection = Server.CreateObject("ADODB.Connection")
objConnection.ConnectionString = "Provider=MSDASQL; DRIVER=Sql Server; SERVER=MECONTROLS_PLT1\SHAREDB; DATABASE=master"
objConnection.Open()

' Set up our SQL query text.
strSQLQuery = "SELECT Article + ' ' + ProcedureName AS Article, ProcedureName " _
& "FROM dbo.searchterms " _
& "WHERE Keywords1 LIKE strsearch" _
& "OR ProcedureName LIKE '%" & Replace(strSearch, "'", "''") & "%' " _
& "ORDER BY Article;"

' Create new command object passing it our SQL query
' and telling it which connection to use.
objCommand = New SqlCommand(strSQLQuery, objConnection)

' Get a DataSet to bind the DataGrid to
objAdapter = New SqlDataAdapter(objCommand)
objDataSet = New DataSet()
objAdapter.Fill(objDataSet)

' DataBind DG to DS
dgPaging.DataSource = objDataSet
dgPaging.DataBind()

objConnection.Close()
Else
strSearch = "Enter Search Here"
End If
End Sub

</script>

Well I do not have a lot of experience in ASP and most of what I do have is in ASP rather than ASP .net.

When I use the createobject I am using it to create a recordset and giving it a connection string to use for the active connection.

Like this

Code:

---

Dim adors As Object = Server.CreateObject("adodb.recordset")
        Dim strSql As String = ""
        strSql = "select m.M_JobCode_Class, m.M_JobCode_Code,m.M_JobCode_Desc from M_JobCode m inner join M_JobClass c on c.m_JobClass_no=m.M_jobCode_Class where c.Labor=1 and m.M_Jobcode_job='" & SelectedJob & "' group by m.M_jobcode_Code,m.M_JobCode_Class,m.M_JobCode_Desc;"

        adors.source = strSql '"select empid from tk_employees where jobno='" & selectedjob & "' group by empid"
        adors.activeconnection = Session("dbcn")

---

Where Session("dbcn") has already been set to the connection string for the db in use.

I have used ASP.Net to create a connection object and run a stored procedure that code goes something like this

Code:

---

Dim CN As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("MainConnection").ConnectionString)

        CN.Open()

---

Where MainConnection is stored in the Web Config file and contains the proper connection string for the db.

You should probably look into using the second type for your project.

I have found the cause of that problem and have fixed it. However I am still having problems related to it. One of my close friends is very proficient in ASP.NET so he has also given me some edits that have worked. Thanks for your help and I'll repost with any other issues I think you might know.

I believe I am past all of my connection string problems at the moment. Now I'm running into permissions errors with this.

Exception Details: System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'searchterms', database 'master', schema 'dbo'.

I had specified the UID and PWD for the Sql Server 2008 account in the connection string as well as being logged onto the admin account of this computer. If you know any possible reason for this, please let me know. Thanks!

You really should not be using Master as your database.
You should istead create a new database for your project.
This may be the source of your porblem.
You also may want to check the permissions of the login you are using with sql server.

I fixed the problem. I went ahead and created a new database and didn't use master. It didn't immediately solve the problem but I did get it working after altering authentication settings in the SQL Management Studio program. The search function works now. The only problems I'm having now is I can only execute a one word search that exactly matches a result in a column, I don't know how to set up a "hyperlink" to one of the result column to link it to a file hosted on the computer, and some server issues which can be addressed later. If you know answers or solutions to the first two please let me know and thanks for your previous and continued help!!!

Quote:

---

The only problems I'm having now is I can only execute a one word search that exactly matches a result in a column, I don't know how to set up a "hyperlink" to one of the result column to link it to a file hosted on the computer

---

I think you will need to provide more information. I am not sure what you mean here.

What did you try?
What do you want to be able to do?
What was the result you got?
If any errors occurred what were they?

Be specifc especially related to what you are trying to accomplish.