Urgent Help

[gunapriyan]

Dear Friends,

I have developed one software with the help of VB.6.0 as Front End and SQL Server 2005 as Back end....

In my office, there is no dedicated server..

So i have selected one of the node in my network and installed the SQL Server 2005 and I have insalled SQL Native client in other client machines.

Project is running...But dead slow for every operation..

Local its working within a few second....

The following line is my connectionstring...

cn.ConnectionString = "Provider=SQLNCLI.1;UID=sa;PWD=******;Initial Catalog=TeaBoard;Connect Timeout=300;Data Source=PC"

*** PC is my server name...

Please do the needful to me...

If you give your email address to me, i will send the source code and DB for your reference..

Urgent Pls...

Yours
GUNA

[MarMan]

Post your code here. Its the way the forum works.

[LaVolpe]

At least post the source code you use to do the querying. The types of recordsets you create, amount of data being queried/returned, constant opening/closing db connections and various other issues can contribute to poorer performance.

[dilettante]

This sounds like an admin issue rather than a programming issue.


You probably have a problem with the Native Client trying one protocol, then timing out and trying another.

I believe a lot of the SQL Server 20XX Editions since 2000 don't enable Named Pipes out of the box, however that may be the protocol Native Client prefers and tries first.

See:

http://weblogs.asp.net/jgalloway/arc...on-string.aspx

You'll probably want to force TCP in your connection string.

[dilettante]

Also see: Choosing a Network Protocol.

[gunapriyan]

Dear Friends,

Thanks for your timely help..

Now i got strength to solve the issue.

I wish to furnish the coding for your kind perusal..

(If you found any error in coding.....Please feel free to inform me.....)

My class file....(Module1.bas)
----------------------------------------

Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public rs1 As New ADODB.Recordset
Public rs2 As New ADODB.Recordset
Public rs3 As New ADODB.Recordset
Public rs4 As New ADODB.Recordset
Public UserName As String
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public view As Integer
Public splashtime As Integer
Public welcometime As Integer
Public uname As String
Public Welcome As Boolean
Public dbpath As String
Dim str As String

Public Sub initDB()
On Error GoTo er
cn.Close
cn.ConnectionString = "Provider=SQLNCLI.1;UID=sa;PWD=arputham;Initial Catalog=TeaBoard;Connect Timeout=50;Data Source=MRINAL-PC"

'MsgBox cn.ConnectionString
cn.Open
er:
'MsgBox Err.Description
End Sub

Sub main()
'cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" & App.Path & "\Teaboard.mdb"
' cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
' "Persist Security Info=False;" & _
' "AttachDBFileName=C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\TeaBoard.mdf;Data Source=TeaTech-PC\sqlexpress"

' cn.ConnectionString = "Provider = sqloledb;" & _
' "Data Source={TeaTech-PC};" & _
' "Initial Catalog={TeaBoard};" & _
' "User ID={sa};" & _
' "Password={arputham};"

' cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Data Source=tcp:TcpIpAddress,port"
' cn.ConnectionString = "Provider=MSDATASHAPE; Data Provider=SQLOLEDB.1; Data Source=YourComputerName\YourDatabaseInstanceName; Initial Catalog=YourDatabaseName; User ID=sa; Password=YourPassword; Integrated Security=SSPI; Persist Security Info=False;"
' cn.ConnectionString = "Provider=SQLNCLI;DSN=TB;UID=sa;PWD=arputham;"
' cn.ConnectionString = "Data Source=TeaTech-PC; Initial Catalog =TeaBoard; User Id = sa; Password = arputham; Pooling=False"

'cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=" & App.Path & "\northwnd.mdf;Data Source=server1\sqlexpress"
'cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;DSN=TB;Data Source=teatech-pc\sqlexpress"
'cn.ConnectionString = "Provider=SQLNCLI.1;UID=sa;PWD=arputham;Initial Catalog=TeaBoard;Data Source=144.16.136.53"

cn.ConnectionString = "Provider=SQLNCLI.1;UID=sa;PWD=arputham;Initial Catalog=TeaBoard;Connect Timeout=50;Data Source=MRINAL-PC"
'MsgBox cn.ConnectionString
cn.Open
cn.CursorLocation = adUseClient
If cn.State = adStatusErrorsOccurred Then
MsgBox "Error connecting to the database", vbCritical, "Tea Board"
End
End If
'rs.Open "select Pass,Splashtime,Viewe,Welcometime,Welcome from Tbl_Custom", cn, adOpenStatic, adLockOptimistic
rs.Open "select Pass,Splashtime,Viewe,Welcometime,Welcome from Tbl_Custom", cn, adOpenDynamic, adLockOptimistic
If rs(0) = "Admin" Then
Load Frm_admin
Frm_admin.Show
Else
Call globalload
End If
End Sub

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

And in every form i have called "initDB" for connection in form load event..

In my project, the village master is one. Coding is follows,

Village Master Code:
--------------------------

Private Sub cmdadd_Click()
On Error Resume Next
Dim id As Integer
If cmdadd.Caption = "Save" Then
rs.Open "select * from Tbl_village", cn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs(0) = txtcatid.Text
rs(1) = txtcatname.Text
rs.Save
rs.Close
MsgBox ("The village details were successfully saved")
Call fLoadData
cmdadd.Caption = "Add"
cmdedit.Enabled = True
cmddelete.Enabled = True
txtcatid.Text = ""
txtcatname.Text = ""
txtcatname.Enabled = False
cmdadd.SetFocus
' Unload Me
' frmemployee.Show
Else
txtcatid.Text = ""
txtcatname.Text = ""
cmdadd.Caption = "Save"
cmdedit.Enabled = False
cmddelete.Enabled = False
txtcatname.Enabled = True
txtcatname.SetFocus
rs.Open "select max(village_id) from tbl_village", cn, adOpenDynamic, adLockOptimistic
id = rs(0)
id = id + 1
txtcatid.Text = id
rs.Close
End If
End Sub

Private Sub cmddelete_Click()
Dim id2 As Integer
On Error Resume Next
Dim a As String
cmdadd.Enabled = False
cmddelete.Enabled = False
txtcatname.Enabled = True
txtcatname.SetFocus
id2 = InputBox("Please enter the Village ID to delete")
rs.Open "select * from tbl_village where village_id=" & id2, cn, adOpenDynamic, adLockOptimistic
txtcatid.Text = rs(0)
txtcatname.Text = rs(1)
rs.Close
If MsgBox("Are you sure want to delete?", vbOKCancel) = vbOK Then
rs.Open "delete from tbl_village where village_id=" & id2, cn, adOpenDynamic, adLockOptimistic
rs.Close
txtcatname.Enabled = False
cmdadd.Enabled = True
cmdadd.Caption = "Add"
cmdedit.Enabled = True
cmdedit.Caption = "Edit"
cmddelete.Enabled = True
txtcatid.Text = ""
txtcatname.Text = ""
Call fLoadData
cmdadd.SetFocus
Else
txtcatname.Enabled = False
cmdadd.Enabled = True
cmdadd.Caption = "Add"
cmdedit.Enabled = True
cmdedit.Caption = "Edit"
cmddelete.Enabled = True
txtcatid.Text = ""
txtcatname.Text = ""
rs.Close
Call fLoadData
End If

End Sub

Private Sub cmdedit_Click()
On Error Resume Next
Dim id1 As Integer
If cmdedit.Caption = "Update" Then
rs.Open "select * from tbl_village where village_id=" & txtcatid.Text, cn, adOpenDynamic, adLockOptimistic
'rs.EditMode
rs(0) = txtcatid.Text
rs(1) = txtcatname.Text
rs.Update
rs.Close
MsgBox ("The village details were successfully updated")
Call fLoadData
cmdedit.Caption = "Edit"
txtcatid.Text = ""
txtcatname.Text = ""
cmdadd.Enabled = True
cmddelete.Enabled = True
txtcatname.Enabled = False
cmdadd.SetFocus
Else
cmdedit.Caption = "Update"
cmdadd.Enabled = False
cmddelete.Enabled = False
txtcatname.Enabled = True
txtcatname.SetFocus
id1 = InputBox("Please enter the Village ID")
rs.Open "select * from tbl_village where village_id=" & id1, cn, adOpenDynamic, adLockOptimistic
txtcatid.Text = rs(0)
txtcatname.Text = rs(1)
rs.Close
End If
End Sub

Private Sub Command1_Click()
txtcatname.Enabled = False
cmdadd.Enabled = True
cmdadd.Caption = "Add"
cmdedit.Enabled = True
cmdedit.Caption = "Edit"
cmddelete.Enabled = True
txtcatid.Text = ""
txtcatname.Text = ""
cmdadd.SetFocus
End Sub

Private Sub Form_Load()
On Error Resume Next
Call initDB 'calls the initDB function from the module, so that the connection between the program and the database is made
rs.Close

'rs.Open "select * from employee", cn, adOpenDynamic, adLockOptimistic
txtcatid.Enabled = False
txtcatname.Enabled = False
Call fLoadData
'mfgHelp.TextMatrix(0, 0) = "Emp_ID"
'mfgHelp.TextMatrix(0, 1) = "Emp_Name"
End Sub

Function fLoadData()
Dim icount As Integer
mfgHelp.Clear
rs.Open "select * from tbl_village", cn, adOpenDynamic, adLockOptimistic

mfgHelp.Rows = 2
mfgHelp.Row = 1
icount = 1

If rs.EOF = True Then
Exit Function
End If
rs.MoveFirst
mfgHelp.ColWidth(1) = 2000
While Not rs.EOF
mfgHelp.TextMatrix(icount, 0) = rs.Fields(0)
mfgHelp.TextMatrix(icount, 1) = rs.Fields(1)


mfgHelp.Rows = mfgHelp.Rows + 1
mfgHelp.Row = mfgHelp.Row + 1
rs.MoveNext
icount = icount + 1
Wend

mfgHelp.Row = mfgHelp.Row - 1
mfgHelp.Rows = mfgHelp.Rows - 1

rs.Close

End Function

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

I hope that you can come to conclusion about my coding style with the help of above.

And i have one bad coding practice, that "On error resume next".....I request you to please tell me the alternative for connection open or close and Recordset open or close.....

Important:

• In SQL Server, I have enable the TCP port and Name pipe port.....Afterwards only that project run...

• I have off the firewall and antivirus too....

• I am waiting for your nice reply....

Please do the needful to me...

Thanks and Regards,

Guna

[gunapriyan]

Dear Friends,

Sorry i just forgot to tell something in previous reply.

That my network speed is fine...

Copy files and open other computers.....are OK....

And the client machines and server (which is installed the SQL Server) also high end machines.....

These are all for your kind perusal..

Thanks & Regards,

Guna

[MarMan]

That's a lot of code. We need to take it one step at a time, there is too much code there to help. I should've been clearer.

Start with 1 thing that is going slow and point that out to us. Step through the code to identify the first line that is causing trouble. If you don't know how to step through, just ask.

[LaVolpe]

Be sure to change your database password soon if the real one is the same as what you posted in your code. You cannot just change it on this post as this post now exists on many servers around the world.

[gunapriyan]

Dear Marman,

Please help me in the coding part.

Waiting for your nice reply....

Thanks & Regards,

Guna

[MarMan]

I will be happy to help. Step through the code. You do this by clicking on the first line and pressing F9. Then run the program. When it stops, press F8. It will execute one line and you will see how fast that one line goes. Keep pressing F8 until you find a line that runs too slow. then tell us which line it is.

[akhileshbc]

Something you should read:

• Why is using the 'End' statement (or VB's "stop" button) a bad idea?
• How should I close my form/program/class?
• What is wrong with using "On Error Resume Next"?