Opening an SQL Connection
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 14 of 14

Thread: Opening an SQL Connection

  1. #1
    Join Date
    Jan 2012
    Posts
    9

    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!

  2. #2
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,785

    Re: Opening an SQL Connection

    Have you looked at www.connectionstrings.com
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Jan 2012
    Posts
    9

    Re: Opening an SQL Connection

    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.

  4. #4
    Join Date
    Jan 2012
    Posts
    9

    Re: Opening an SQL Connection

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

  5. #5
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,785

    Re: Opening an SQL Connection

    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.
    Last edited by DataMiser; January 30th, 2012 at 09:24 AM.
    Always use [code][/code] tags when posting code.

  6. #6
    Join Date
    Jan 2012
    Posts
    9

    Re: Opening an SQL Connection

    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

  7. #7
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,785

    Re: Opening an SQL Connection

    What type is your objConnection defined as?
    In VB I would just define it as Object and that works fine.
    Always use [code][/code] tags when posting code.

  8. #8
    Join Date
    Jan 2012
    Posts
    9

    Re: Opening an SQL Connection

    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>

  9. #9
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,785

    Re: Opening an SQL Connection

    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.
    Always use [code][/code] tags when posting code.

  10. #10
    Join Date
    Jan 2012
    Posts
    9

    Re: Opening an SQL Connection

    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.

  11. #11
    Join Date
    Jan 2012
    Posts
    9

    Re: Opening an SQL Connection

    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!

  12. #12
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,785

    Re: Opening an SQL Connection

    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.
    Always use [code][/code] tags when posting code.

  13. #13
    Join Date
    Jan 2012
    Posts
    9

    Re: Opening an SQL Connection

    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!!!

  14. #14
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,785

    Re: Opening an SQL Connection

    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.
    Always use [code][/code] tags when posting code.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center