Database

_AK_10-Sep-06 23:29

Re: Condition as parameter of sp ... ? [modified]

10-Sep-06 23:29

Yor problem is not very clear what exactly you want? Do you want to fetch the recordset on basis of some condition, if so then that is where you can use "where" clause. Something like this:
select * from emp where empid = @EID
where @EID is the parameter to that stored procedure.

Best Regards,
Apurva Kaushal

devboycpp10-Sep-06 23:38

devboycpp

10-Sep-06 23:38

No No no.

I want the condition to be like a variable for example this:

condition -> (column1='x' AND column2='y' OR column3='z')
I want the stored procedure to take the aformentioned condition as a varchar and use it in WHERE clause. To take the whole condition part of WHERE CLAUSE as parameter.
Is there any way

-- modified at 5:50 Monday 11th September, 2006

_AK_10-Sep-06 23:55

10-Sep-06 23:55

ya sure there is a way what you can do is create your where condition in front end, send that complete condition as string to the stored procedure. In stored procedure you take one variable(where the sql statement will be stored) and append the where string and then use sp_executesql to execute the complete string. Smile | :)

Best Regards,
Apurva Kaushal

devboycpp11-Sep-06 0:24

devboycpp

11-Sep-06 0:24

Thank you very much for your help.Could you please show me some basic code ?

_AK_11-Sep-06 0:33

11-Sep-06 0:33

Here a sample code which you can use in your stored procedure taken from sqlserver help: Smile | :)

DECLARE @IntVariable INT<br />
DECLARE @SQLString NVARCHAR(500)<br />
DECLARE @ParmDefinition NVARCHAR(500)<br />
<br />
/* Build the SQL string once.*/<br />
SET @SQLString =<br />
     N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'<br />
SET @ParmDefinition = N'@level tinyint'<br />
/* Execute the string with the first parameter value. */<br />
SET @IntVariable = 35<br />
EXECUTE sp_executesql @SQLString, @ParmDefinition,<br />
                      @level = @IntVariable<br />
/* Execute the same string with the second parameter value. */<br />
SET @IntVariable = 32<br />
EXECUTE sp_executesql @SQLString, @ParmDefinition,<br />
                      @level = @IntVariable<br />

Hope this will serve the purpose.

Best Regards,
Apurva Kaushal

Eric Dahlvang11-Sep-06 3:08

Eric Dahlvang

11-Sep-06 3:08

CREATE PROCEDURE sp_GetList
@condition varchar(50)
AS

exec('SELECT * FROM Table_X WHERE ' + @condition)

Be sure to look at this:
SQL Injection Attacks and Some Tips on How to Prevent Them[^]

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Navigation buttons

Ranjita Ghosh10-Sep-06 21:41

10-Sep-06 21:41

Hi Everybody!
I am using VB6.0 & SQL Server 7.0
Following is my database Connection code

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set db = New ADODB.Connection
db.Provider = "SQLOLEDB"
db.ConnectionString = "server=SUNANDA;uid=sa;pwd=ranjita1;database=crm"
db.Open

Set rs = New ADODB.Recordset
sql = "Select * from AccountHead"
Set rs.ActiveConnection = db
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Open sql, , , , adCmdText

I have placed navigation buttons on my form, but the Next & Previous buttons are not working properly.
When I first time clicks the Next button , the recordset goes to the next record but when I click it second time, the record doesn’t get changed.

When I press the previous button, the recordset points to the first record, instead of pointing to the previous record.

Here is my code for navigational buttons Sniff | :^)

:

Public Sub MoveFields()
txtAcId.Text = rs("AcHeadID")
txtAcName.Text = rs("AcHeadName")
txtAcMobile.Text = rs("Mobile")
txtAcEmail.Text = rs("EmailID")
End Sub

Private Sub cmdFirst_Click()
rs.MoveFirst
MoveFields
cmdPrev.Enabled = False
cmdNext.Enabled = True
End Sub

Private Sub cmdLast_Click()
rs.MoveLast
MoveFields
cmdNext.Enabled = False
cmdPrev.Enabled = True
End Sub

Private Sub cmdNext_Click()
rs.MoveNext
If rs.EOF Then
rs.MoveLast
cmdNext.Enabled = False
End If
MoveFields
cmdPrev.Enabled = True
End Sub

Private Sub cmdRemove_Click()
rs.Delete
rs.MoveNext
If rs.EOF Then
rs.MoveLast
End If
End Sub

Private Sub cmdPrev_Click()
rs.MovePrevious
If rs.BOF Then
rs.MoveFirst
cmdPrev.Enabled = False
End If
MoveFields
cmdNext.Enabled = True
End Sub

Pls. tell me, where I am wrong.
Thanks

Re: Navigation buttons

Tushar Kothari11-Sep-06 1:06

11-Sep-06 1:06

Hi

Dear Please first check where you write the code for recordset and connection.

Then try following code for your record movement

Next record
if not rs.eof then
rs.movenext()
....
end if

previous record
if not rs.bof then
rs.moveprevious()
.....
end if

If the problem is there then please post new message with you complete form code so I can sort your issue.

Regards

Tushar kothari

Re: Navigation buttons

Ranjita Ghosh11-Sep-06 1:48

Catching errors raised by SP

11-Sep-06 1:48

I have written the code for recordset and connection in sub main()
I am calling the procedure in each click event.

Uma Kameswari10-Sep-06 21:40

Uma Kameswari

10-Sep-06 21:40

Hi,
I am using ASp.NET 1.1
Now when I call a stored procedure from code behind file and the stored procedure raises an error how to catch this error in code behind file

Thanks and Regards,
Uma

Re: Catching errors raised by SP

Tushar Kothari11-Sep-06 1:10

Re: Catching errors raised by SP

11-Sep-06 1:10

Hi

you can catch exception by following

Catch ex As SqlClient.SqlException

Tushar kothari

Amit Kumar G12-Sep-06 16:28

Amit Kumar G

12-Sep-06 16:28

You can use output paramter, return value or as mentioned SQlexception. I prefer return value as this help me to use customize message from resource file,

Cannot edit the existing record

Ranjita Ghosh10-Sep-06 21:09

10-Sep-06 21:09

:(Hi Everybody!
I am using VB6.0 & SQL Server 7.0
Following is my database Connection code

Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set db = New ADODB.Connection
db.Provider = "SQLOLEDB"
db.ConnectionString = "server=SUNANDA;uid=sa;pwd=ranjita1;database=crm"
db.Open

Set rs = New ADODB.Recordset
sql = "Select * from AccountHead"
Set rs.ActiveConnection = db
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Open sql, , , , adCmdText

I have placed buttons on the form to Add, Delete, Edit and Update the records of the table. But in the click event of the Update button, the recordset is not supporting the Edit method. The only method it is showing in (VBA) the dropdown list is EditMode.
How will I then Edit the records?
Pls. help me out.
Thankyou.

Tushar Kothari11-Sep-06 1:13

11-Sep-06 1:13

Hi

Dear please concentrate on your following code

rs.CursorType = adOpenStatic

As you had open the cursor in Static mode there is no edit permission for this record set.
You gave to open the recordset in non static mode.

Regards

Tushar kothari

Ranjita Ghosh11-Sep-06 1:36

11-Sep-06 1:36

Hi! Tushar
I have tried with
rs.CursorType=adOpenDynamic
But of no use.
Pls. suggest.
regards
ranjita

Tushar Kothari11-Sep-06 1:39

11-Sep-06 1:39

Hi

If you want just to update the record in recordset then you have to set the fileds values of rs then call rs.update

If this method is not wirking the please relpy back then I will try some odd solution for you.

Regards

Tushar kothari

Ranjita Ghosh11-Sep-06 1:51

11-Sep-06 1:51

I wants to edit the existing record and then update.
what u r suggesting that i am using in the click event of my save button.
regards
ranjita

Tushar Kothari11-Sep-06 2:14

how i populate the row of datagridview in edit mode

11-Sep-06 2:14

Hi

This is the tested code so please use it

Private Sub Form_Load()
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set db = New ADODB.Connection
db.Provider = "SQLOLEDB"
db.ConnectionString = "Provider=SQLOLEDB.1;Password=thk;Persist Security Info=True;User ID=thk;Initial Catalog=Dhanavantari;Data Source=TUSHAR"
db.Open

Set rs = New ADODB.Recordset
sql = "Select * from WardMaster"
Set rs.ActiveConnection = db
rs.CursorLocation = adUseServer
rs.CursorType = adOpenDynamic
rs.LockType = adLockPessimistic
rs.Open sql, , , , adCmdText

End Sub

on Save client button
rs.Fields(1) = trim(txtwardname.text)
rs.Fields(2) = val(txtrate.text)
rs.Update

So now you can edit the recordset and then update it
ADO is the connected type of recordset so you can update one record at a time.
If you have some other problem which i had not understand the please brief it

regards Smile | :)

Tushar kothari

tutum10-Sep-06 20:16

tutum

10-Sep-06 20:16

i am working in vb.net2.0 version.
can u help me with code that how can i populate the datagridview in edit mode which will open in another form when one selects the row of a datagridview.
regards
Taniya Banerjee

Stored Procedure

Uma Kameswari10-Sep-06 18:50

Uma Kameswari

10-Sep-06 18:50

Hi,
I have a stored procedure with 1 parameter.
The value that I pass to the parameter is a select statement.
The question is how do I execute this select statement that is passed as parameter that returns the rows?

Thanks and Regards,
Uma

Re: Stored Procedure

_AK_10-Sep-06 20:58

10-Sep-06 20:58

Hi,
You can use sp_executesql to execute the sqlquery which is in a variable.

Best Regards,
Apurva Kaushal

Re: Stored Procedure

Amit Kumar G12-Sep-06 16:30

Amit Kumar G

12-Sep-06 16:30

CReate proc test (@sName Char(4))
AS
BEGIN
SELECT col1 from table where name=@sname
end

To execute this

EXEC Test 'Amit'

Amit

Re: Stored Procedure

_AK_12-Sep-06 19:36