July 29, 2002 at 4:46 pm
Hi Friends,
In my probject(vb6 + SqlServer7), I start develop with SQL SERVER ODBC Driver. Later I found Microsoft OLE DB provider for SQL Server is faster the ODBC driver and I hope it is true.
Now, I establish the connection. But the problem is while calling a parameter stored procedure, it seems, I can't open the recordset in my VB app which calling a select statement stored procedure from SQL Server. Here is my code which was working perfect in ODBC Driver for SQL Server. What changes should I make.
Dim Cmd as New ADODB.Command
Dim rstStudent as New ADODB.Recordset
With Cmd
.ActiveConnection = Cn
.CommandType = adCmdStoredProc
.CommandText = "spRecordSet"
.Parameters.Refresh
.Parameters(1).Value = "tb_students"
If txtSort <> "" Then .Parameters(2).Value = txtSort
If txtFilter <> "" Then .Parameters(3).Value = txtFilter
Set rstStudent = .Execute()
End With
I tried the other method through which we append parameters. Still SqlServer doesn't seems to return the recordset. Below is the code.
Dim Cmd as New ADODB.Command
Dim Param as New ADODB.Parameter
Dim rstStudent as New ADODB.Recordset
With Cmd
Set Param = Cmd.CreateParameter("vTableName", adVarChar, adParamInput)
.Parameters.Append Param
Param.Value = "tb_students"
Set rstStudent = .Execute()
End With
Pls help me to solve this problem. I don't know much detail about oledb provider for sqlserver.
Thanks in advance
Manoj
P.B. No 14337
Doha - Qatar
Ph: 4435361
web:www.fccqatar.com
P.B. No 14337
Doha - Qatar
Ph: 4435361
web:www.fccqatar.com
July 29, 2002 at 6:57 pm
Either method is valid. Generally you want to create the parameters yourself since the refresh method requires an additional round trip to the server. Are you sure you have a valid connection? Are you getting an error or just nothing back?
Andy
July 30, 2002 at 12:00 am
Hi Warren,
Thanks for your quick replay
here is my connection string
Dim Cn as NEW ADODB.Connection
With CN
.Connectionstring = "Provider=SQLOLEDB.1;Data Source=FAMILYSERVER;Initial Catalog=famDB;User ID=manoj;Password=man123"
.ConnectionTimeout = 100
.CursorLocation = adUserClient
.Open
End With
I also notice that we can provier an attribute called Persist Security Info = False; But when I provide this option, it displaying Login failed for user and then another error called No such interface support.
Is my connection string is correct. Should I make any changes in Stored procedure
Pls post your advice
Thanks
P.B. No 14337
Doha - Qatar
Ph: 4435361
web:www.fccqatar.com
P.B. No 14337
Doha - Qatar
Ph: 4435361
web:www.fccqatar.com
July 30, 2002 at 4:37 am
Connection looks ok. I notice you're missing the "Set" in front of .activeconnection = connection. Do you have error handling turned off when you test this? If that doesn't help post the structure of the table and one of will run through the code.
Andy
July 30, 2002 at 7:24 am
Hi Warren,
The problem solved.
I include SET NOCOUNT ON in begin of my SP
One more dobut Warren..
When I went through the OLEDB connection string, I seen a parameter called Persist Security Info = false;. But when I include that one, my VB app. display an error message call Login failed for user <username>. I bind my return recordset into a DataGrid. If my record set is empty it generate another error No such interface support. Is it really need to use persist security info. Can I skip that. I don't the usage of this optin.
Pls let me know your advice
Once again thanks
Manoj
P.B. No 14337
Doha - Qatar
Ph: 4435361
web:www.fccqatar.com
P.B. No 14337
Doha - Qatar
Ph: 4435361
web:www.fccqatar.com
July 30, 2002 at 11:24 am
Persisting it just means that the connection maintains the security info, not that its getting written out somewhere.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply