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
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".
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
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.