[flameneo]

Hey am new to this site and programming generally. I am having an issue connecting to sql sever.

I installed visual studio 2010 and am working with visual basic

when installing the sql server it asked if i would like to use windows authentication or sql authentication

i selected windows authentication

i am trying to connect using adodb connection

and am having challenge with it.


i got this code online but its not working for me cause am not sure the username and password to use

Module General
    Public conn As New ADODB.Connection
    Public sql As String
    Public rs, rss As New ADODB.Recordset
    Public admission_no As Integer
    Public view_student As Boolean = False
    Public visitor_name, vdate, sname, comp, dat, stat
    Public visitor As Boolean = False
    Public complaint As Boolean = False
    Public abc As String
    Public Function OpenDB()
        Try


            If Conn.State = 1 Then Conn.Close()
            conn.Open("Provider=SQLOLEDB.1;Persist Security Info=False;user id=sa;password=q1w2e3r4/;Initial Catalog=hostel1;Data Source=SHARUN-PC")
            Return (0)
        Catch ex As Exception
            MsgBox("Database is not connected ..... Please Check Your Network Connection", MsgBoxStyle.Critical)
            End
        End Try
    End Function
End Module

i modified it as

Module General
    Public conn As New ADODB.Connection
    Public sql As String
    Public rs, rss As New ADODB.Recordset
    Public admission_no As Integer
    Public view_student As Boolean = False
    Public visitor_name, vdate, sname, comp, dat, stat
    Public visitor As Boolean = False
    Public complaint As Boolean = False
    Public abc As String
    Public Function OpenDB()
        Try


            If Conn.State = 1 Then Conn.Close()
            conn.Open("Data Source=dawise1-pc\sqlexpress;Initial Catalog=hostel1;Integrated Security=True")


            Return (0)
        Catch ex As Exception
            MsgBox("Database is not connected ..... Please Check Your Network Connection", MsgBoxStyle.Critical)
            End
        End Try
    End Function
End Module

connecting using server explorer is not an issue it connects very well once connected, i checked the property windows of the database and this is what i saw


(name) hostel1
cse sensitive False
connection string: (Data Source=dawise1-pc\sqlexpress;Initial Catalog=hostel1;Integrated Security=True)
owner: (DAWISE1-PC\(DaWise1))
provider: (.NET Framework Data Provider for SQL Server)
state: Open
type: (Microsoft SQL Server)
version (10.00.2531)

an working on my final year project and am stock at this point. any idea would be appreciated. Thanks in advance

[Ionut]

Whenever you have a doubt about a connectionstring, check this site.
Because you choose to login to the server only through Windows authentification, the connection string you are looking for is

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

[flameneo]

thanks alot. for your help. i tried it , it did not work for me

so i tried this also still not connecting

Module General
    Public conn As New ADODB.Connection
    Public sql As String
    Public rs, rss As New ADODB.Recordset
    Public admission_no As Integer
    Public view_student As Boolean = False
    Public visitor_name, vdate, sname, comp, dat, stat
    Public visitor As Boolean = False
    Public complaint As Boolean = False
    Public abc As String
    Public Function OpenDB()
        Try


            If Conn.State = 1 Then Conn.Close()
            conn.Open("Data Source=dawise1-pc\SQLExpress;Integrated Security=true; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\hostel1.mdf;User Instance=true;")


            Return (0)
        Catch ex As Exception
            MsgBox("Database is not connected ..... Please Check Your Network Connection", MsgBoxStyle.Critical)
            End
        End Try
    End Function
End Module

This post has been edited by smohd: 21 August 2012 - 12:22 PM
Reason for edit:: long quote removed

[rgfirefly24]

Try removing AttachDBfilename from your connection string and add Initial Catalog=<database Name>.

with out knowing exactly what errors your getting it will be hard to assist you.

[flameneo]

@ rgfirefly24 thanks alot for your efforts. from the above code the error am getting is obvious

If Conn.State = 1 Then Conn.Close()
	            conn.Open("Data Source=dawise1-pc\SQLExpress;Integrated Security=true; AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\hostel1.mdf;User Instance=true;")

	 
       Return (0)
       Catch ex As Exception
       MsgBox("Database is not connected ..... Please Check Your Network Connection", MsgBoxStyle.Critical)
       End
       End Try
    End Function

if if cannot connect to the database, it should display a message ( MsgBox("Database is not connected ..... Please Check Your Network Connection", MsgBoxStyle.Critical))after which it ends the program. thats the error message i get. i do believe the errore would be @ the line

conn.Open("Data Source=dawise1-pc\SQLExpress;Integrated Security=true;Initial Catalog=hostel1.mdf;User Instance=true;")

but i just can't place what is wrong.

[rgfirefly24]

See, you're giving us what your custom error says. What I want to know is what ex.Message is. Have you tried setting a breakpoint and debugging the application to see what the real error is?

[flameneo]

should i disable me custom error message, i get the an error that reads

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

if i force it past that point what i get is

The connection cannot be used to perform this operation. It is either closed or invalid in this context.

which is expected since the database connection is not successful.

what i don't understand is if i connect manually, using server explorer, and i check the properties of the connected database, i get

(name) hostel1
cse sensitive False
connection string: (Data Source=dawise1-pc\sqlexpress;Initial Catalog=hostel1;Integrated Security=True)
owner: (DAWISE1-PC\(DaWise1))
provider: (.NET Framework Data Provider for SQL Server)
state: Open
type: (Microsoft SQL Server)
version (10.00.2531)

but if i use the connection string for the connection using codes,

Module General
    Public conn As New ADODB.Connection
    Public sql As String
    Public rs, rss As New ADODB.Recordset
    Public admission_no As Integer
    Public view_student As Boolean = False
    Public visitor_name, vdate, sname, comp, dat, stat
    Public visitor As Boolean = False
    Public complaint As Boolean = False
    Public abc As String
    'Public conn As SqlClient.SqlConnection
    Public Function OpenDB()
        ' Try


        'If conn.State = 1 Then conn.Close()
        conn.Open("Data Source=dawise1-pc\sqlexpress;Initial Catalog=hostel1;Integrated Security=True")


        '  Return (0)

        ' Catch ex As Exception
        'MsgBox("Database is not connected ..... Please Check Your Network Connection", MsgBoxStyle.Critical)
        ' End
        ' End Try
    End Function
End Module

it gives me that error. that is

"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

please what am missing. and how can i fix this.

thanks in advance

i posted this screen shot also so you may take a look

Attached image(s)

• 

[rgfirefly24]

instead of using ADODB why don't you use the SQL classes: SqlConnection, SqlCommand, ect. which are apart of the SqlClientnamespace

if you want to use OLEDB then you need to make sure your connection string mirrors this one:

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;

This post has been edited by rgfirefly24: 20 August 2012 - 06:24 PM

[flameneo]

i iave zero idea about sqlclient that y i used adodb. Am working on my final year project and my defence is 2nd week of next month. I have zero time 2 fix it. That aside, someone else may be in need of this solution. If i get d answer right, someone else may be happy. Thanks alot

[rgfirefly24]

Have you updated your connection string to include the provider?

Here is an example that I took directly from production code I run at work. It uses OLEDB

"Provider=SQLOLEDB;Initial Catalog=<Database Name>;Data Source=<Server>;Integrated Security = SSPI;"

As far as changing to use the SqlClient stuff. There really isn't much too it and I'd bet any problems you're having will be gone. Here is a quick example of using SqlClient for what you want to do

Dim con as new SqlConnection(<connection string>)

Using con
    Dim com as new SqlCommand(<sqlCommand>,con)
    Using com
        Dim reader as SqlDataReader = com.ExecuteNonQuery()
        While reader.Read()
            'get data back by using reader["<column name>"]
        End While
    End Using
End Using 

This post has been edited by rgfirefly24: 21 August 2012 - 05:57 AM

[flameneo]

@ rgfirefly24 thanks a lot. worked like magic

here is what i did

Module General
    Public conn As New ADODB.Connection
    Public sql As String
    Public rs, rss As New ADODB.Recordset
    Public admission_no As Integer
    Public view_student As Boolean = False
    Public visitor_name, vdate, sname, comp, dat, stat
    Public visitor As Boolean = False
    Public complaint As Boolean = False
    Public abc As String
   

    Public Function OpenDB()

        Try


            If conn.State = 1 Then conn.Close()
            conn.Open("Provider=SQLOLEDB;Initial Catalog=hostel1;Data Source=dawise1-pc\sqlexpress;Integrated Security = SSPI;")
            

            Return (0)

        Catch ex As Exception
            MsgBox("Database is not connected ..... Please Check Your Network Connection", MsgBoxStyle.Critical)
            End
        End Try
    End Function
End Module

@Dream Kudos thanks i would look into it. But be there 2 drag me out of sinking water when i fall.

dream in code, u guys are the best