Click here to Skip to main content
15,891,033 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I have written code to show records in a datagridview but it shows nothing. I have tried to debug & when it step into GetData function following codes
VB
Catch ex As Exception
returnData = Nothing
If connection.State = ConnectionState.Open Then
    connection.Close()
End If


become gray & it shows error sign. When I take my mouse pointer on it it shows message. It says "Cannot open database SUIMT requested by the login. The login failed. Login failed for user 'MY-PC\James'". Please check my code & help me to get rid of this problem & dont forget to add example with your answer.
Necessary information : I am using SQL SERVER 2000 PERSONAL EDITION. Name of my database is "SUIMT" & table which I want to show in datagridview is called "monthly_instal".
VB
Imports System.Data.SqlClient
Public Class Form34
    Private Const ConnectionString As String = "Server=.\SQLEXPRESS;" & _
    "Database=SUIMT;Trusted_Connection=True"

    Private Sub Form34_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cmbdmiid.Items.Clear()
        cmbdmiid.Text = "ID NUMBER"
        cmbdmidt.Items.Clear()
        cmbdmidt.Text = "DATE"
        txtdmiron.Text = "ROW NUMBER"
        con = New ADODB.Connection
        con.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SUIMT")
        rst = New ADODB.Recordset
        With rst
            .Open("Select * From monthly_instal", con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
            If .BOF = False Then
                .MoveFirst()
                While .EOF = False
                    If Not cmbdmiid.Items.Contains(.Fields("stu_id").Value) Then
                        cmbdmiid.Items.Add(.Fields("stu_id").Value)
                    End If
                    .MoveNext()
                End While
            End If
            .Close()
        End With
        rst = New ADODB.Recordset
        With rst
            .Open("Select * From monthly_instal", con, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)
            If .BOF = False Then
                .MoveFirst()
                While .EOF = False
                    If Not cmbdmidt.Items.Contains(.Fields("dt").Value) Then
                        cmbdmidt.Items.Add(.Fields("dt").Value)
                    End If
                    .MoveNext()
                End While
            End If
            .Close()
        End With
        Me.CREATEUSERToolStripMenuItem.Enabled = False
        Me.DELETEUSERToolStripMenuItem.Enabled = False
        Me.CHANGEPASSWORDToolStripMenuItem.Enabled = False
        Me.ASSIGNPERMISSIONToolStripMenuItem.Enabled = False
        Me.SHOWALLToolStripMenuItem.Enabled = False
        Me.CREATEACADEMICYEARToolStripMenuItem.Enabled = False
        Me.DELETEACADEMICYEARToolStripMenuItem.Enabled = False
        Me.CREATESESSIONToolStripMenuItem.Enabled = False
        Me.DELETESESSIONToolStripMenuItem.Enabled = False
        Me.CREATEDEPARTMENTToolStripMenuItem.Enabled = False
        Me.DELETEDEPARTMENTToolStripMenuItem.Enabled = False
        Me.CREATEEXAMTYPEToolStripMenuItem.Enabled = False
        Me.DELETEEXAMTYPEToolStripMenuItem.Enabled = False
        Me.CREATESUBJECTToolStripMenuItem.Enabled = False
        Me.DELETESUBJECTToolStripMenuItem.Enabled = False
        Me.ADMISSIONToolStripMenuItem.Enabled = False
        Me.DELETEADMISSIONToolStripMenuItem.Enabled = False
        Me.ADMISSIONREPORTToolStripMenuItem.Enabled = False
        Me.STUDENTINFORMATIONToolStripMenuItem.Enabled = False
        Me.DELETESTUDENTINFORMATIONToolStripMenuItem.Enabled = False
        Me.SEARCHSTUDENTINFORMATIONToolStripMenuItem.Enabled = False
        Me.STUDENTINFORMATIONREPORTToolStripMenuItem.Enabled = False
        Me.CREATESTUDENTRESULTToolStripMenuItem.Enabled = False
        Me.DELETESTUDENTRESULTToolStripMenuItem.Enabled = False
        Me.STUDENTRESULTREPORTToolStripMenuItem.Enabled = False
        Me.RESULTSHEETREPORTToolStripMenuItem.Enabled = False
        Me.CREATEMONTHLYINSTALLMENTToolStripMenuItem.Enabled = False
        Me.DELETEMONTHLYINSTALLMENTToolStripMenuItem.Enabled = False
        Me.MONTHLYINSTALLMENTREPORTToolStripMenuItem.Enabled = False
        Me.CREATEBALANCESHEETToolStripMenuItem.Enabled = False
        Me.DELETEBALANCESHEETToolStripMenuItem.Enabled = False
        Me.BALANCESHEETREPORTToolStripMenuItem.Enabled = False
        Me.ABOUTTHISPROGRAMToolStripMenuItem.Enabled = False
        Me.EXIToolStripMenuItem.Enabled = False

    End Sub
    Public Function GetData() As DataTable
        Dim SelectQry = "Select row_num, stu_id, tot_amou, paid, du, aoins, due, dt From monthly_instal where stu_id = '" & cmbdmiid.Text & "' AND dt = '" & cmbdmidt.Text & "'"
        Dim connection As New SqlConnection(ConnectionString)
        Dim returnData As New DataTable("monthly_instal")
        Try
            connection.Open()
            Dim command As New SqlCommand(SelectQry, connection)
            Dim adapter = New SqlDataAdapter(command)
            adapter.Fill(returnData)
            con.Close()
        Catch ex As Exception
            returnData = Nothing
            If connection.State = ConnectionState.Open Then
                connection.Close()
            End If
        End Try
        Return returnData
    End Function

    Private Sub butdmiclo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butdmiclo.Click
        speak34.Speak("Delete monthly installment window has been closed successfully")
        Me.Close()
    End Sub

    Private Sub cmbdmidt_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmbdmidt.Leave
        dtg1.DataSource = Nothing
        dtg1.DataSource = GetData()
    End Sub


End Class


Necessary information : Name of my computer is MY-PC. When I am clicking on SQL SERVER SERVICE MANAGER it shows SERVER - MY-PC. I guess MY-PC must be used as a server name. As a result I used it
VB
Private Const ConnectionString As String = "Server=.\MY-PC;" & _
  "Database=SUIMT;Trusted_Connection=True"


But it shows error. It says "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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
I am confused it says error has occurred to create connection with SQL SERVER 2005! But I need to connect it with SQL SERVER 2000. Yes I have SQL SERVER 2000 & 2005(SQL SERVER 2005 was installed with Visual studio). But I am using SQL SERVER 2000.
Posted
Updated 13-Sep-10 1:44am
v2
Comments
Dalek Dave 13-Sep-10 7:44am    
Edited to Correct the Code Block Tags.

You should call server by it's instance name.
If you have installed SQL server on local computer you can build connection string like this:
Private Const ConnectionString As String = "Server=(local);" & _
"Database=SUIMT;Trusted_Connection=True"

or
Private Const ConnectionString As String = "Server=MY-PC\SQLEXPRESS;" & _
"Database=SUIMT;Trusted_Connection=True"
 
Share this answer
 
go into your SQL server logs and look at the messages.

Then, see here: http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx[^]

This page tells you what the state means. This way you can tell if the password wasn't right, if the user doesn't exist, etc...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900