Wrox Programmer Forums
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 11th, 2007, 09:43 AM
Registered User
Join Date: Jan 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default sql connectivity using stored procedure in asp.net

Hi! this is kk. I have been trying to connect to sql db server2000 using asp.net thru stored procedure. but while retreiving the result
parameter from the stored procedure, its given the error:
Syntax error converting the varchar value 'new' to a column of data type int.

Can anybody help me please to solve this problem i've written the follg code. i declared all the vars before page load, and used a fun for connection

Function CONCODE()
        CON = New SqlConnection("Persist Security Info=False;User ID=sa;Initial Catalog=phones4u;Data Source=ANSHULBA")
        Return CON
    End Function

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            QUERY = "pro_to_cheq_logindetails"
            CMD = New SqlCommand(QUERY, CONCODE())
            CMD.CommandType = CommandType.StoredProcedure
            CMD.Parameters.Add("@userid", LTrim(RTrim(TextBox1.Text)))
            CMD.Parameters.Add("@pwd", LTrim(RTrim(TextBox2.Text)))
            Dim p1 As SqlParameter
            p1 = CMD.Parameters.Add("@res", SqlDbType.VarChar)
            p1.Size = 3
            p1.Direction = ParameterDirection.Output
            p1.DbType = DbType.String
            Dim STR As String
            STR = CMD.Parameters("@res").Value
            Label3.Text = STR
        Catch ex As Exception
            Label3.Text = ex.Message
        End Try

    End Sub

Old January 11th, 2007, 12:48 PM
Wrox Author
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons

"'new' to a column of data type int" I think somewhere inside your stored procedure you are generating this error. Do this: run the procedure from query analyzer and supply it some values and see if you get an error.

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature
Old January 17th, 2007, 02:45 PM
Authorized User
Join Date: Jan 2007
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts

Hi Krishna,
I'm new, but this is how I use stored procedures with SQL2K from ASP.
I hope you can get some value from it:

cust_code = Session("Cust_Number")

set conn=server.createobject("adodb.connection")
set rs=server.CreateObject("adodb.recordset")
conn.open = "Driver={SQL Server}; server=<Insert a valid server name>; database=<Insert a valid database name>; uid=<Insert a valid username>; password=<Insert a valid password>;"

sqlrs="EXEC dbo.CMS_return_audit_survey_data @cust_number = '" & cust_code & "'"
rs.open sqlrs, conn, 2, 2

Note: replace "<Insert a valid ... >" with your actual values.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing value in a stored procedure using ASP.NET hbansal ASP.NET 1.0 and 1.1 Professional 5 July 30th, 2007 12:36 AM
ASP.NET & SQL Server 2K Stored Procedure kwilliams ASP.NET 2.0 Basics 7 May 10th, 2006 12:55 AM
ASP.NET connectivity with SQL server shupi Classic ASP Databases 0 September 19th, 2005 07:09 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.