Results 1 to 28 of 28

Thread: Stored Procedures, msSQL

  1. #1

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547

    Stored Procedures, msSQL

    I figured I'd start a new thread for this
    Ok, these things are frustrating the hell outa me! grrr...

    ok, why would this not work
    Code:
    CREATE PROCEDURE sp_getTableWhere 
    	
    	@vTable varchar(20),
    	@vcharValue1 varchar(20),
    	@vcharValue2 varchar(20)
    AS
    
    	SELECT * FROM @vTable
    	WHERE @vcharValue1 = @vcharValue2
    Sais I need to declare @vTable

    What im trying to do is select all records from a certain table by Value 1 and Value 2 where
    Value1 is the Column, and Value 2 is the matching value for that column.



    Problem #2)
    I wrote this simple function to see if a user exists
    Code:
    CREATE PROCEDURE sp_isValidLogin 
    
    	@charUsername varchar(50),
    	@charPHash varchar(50)
    
    	AS
    	if(DATALENGTH(@charUsername) = 0 AND DATALENGTH(@charPHash) = 0)
    		RETURN 0
    
    		if exists(SELECT * FROM tblUser
    	
    			WHERE @charUsername = charUsername
    	
    		 AND
    	
    			@charPHash = charPHash)
    			return 1
    		else
    			return 0
    GO
    When you call a return, does it terminate the stored procedure like a javascript or c++ statement would? Because it keeps giving me this message if @charUsername and @charHash are empty

    [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use empty object or column names. Use a single space if necessary.
    If i do pass in the value, and it does exist... SQL sais: Missing Default Property. What, huh? I thought returning 0 or 1 is enough?

    Arghhhhh... i hate when things dont work
    Last edited by invitro; Aug 29th, 2004 at 05:19 PM.
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  2. #2

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    Also, are stored procedures really THAT much more efficent?
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  3. #3
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    Originally posted by invitro
    Also, are stored procedures really THAT much more efficent?
    Yes it IS!

    What you are trying to do is create Dynamic Stored procedure. You can pass field name as variable to Stored Proc without any problem, but you cant pass Table Name as variable. If you want to do that you have to use following syntax.



    Code:
    CREATE PROCEDURE sp_getTableWhere 
    	
    	@vTable varchar(20),
    	@vcharValue1 varchar(20),
    	@vcharValue2 varchar(20)
    AS
    --Declaring variable to store our query
    Declare sql nvarchar(500)
    
    --assigning the query to our variable
    @sql = 'SELECT * FROM @vTable WHERE ' + @vcharValue1 + '''' + '= ' + @vcharValue2 + ''''
    
    --execute the dynamic sql query
    exec(@sql)
    
    return
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  4. #4

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    When I execute the above I get a nvarchar is not a recognized cursor option. Must declare @sql

    ...

    also shouldent the dynamic query look like this

    Code:
    @sql = 'SELECT * FROM ' + @vTable + ' WHERE ' + @vcharValue1 + '''' + '= ' + @vcharValue2 + ''''
    Notice the ' + + ' where @vTable is
    Last edited by invitro; Aug 29th, 2004 at 05:45 PM.
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  5. #5
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    Sorry was in a hurry so few mistakes. Code highlighted are the one i missed out.

    Here :

    Code:
    CREATE PROCEDURE sp_getTableWhere 
    	
    	@vTable varchar(20),
    	@vcharValue1 varchar(20),
    	@vcharValue2 varchar(20)
    AS
    --Declaring variable to store our query
    --I missed out the @ here
    Declare @sql nvarchar(500)
    
    --assigning the query to our variable
    --you need select variable name, to assign variable
    select @sql = 'SELECT * FROM ' + @vTable + ' WHERE ' + @vcharValue1 + '= ' + '''' + @vcharValue2 + ''''
    --execute the dynamic sql query
    exec(@sql)
    
    return
    Last edited by Danial; Aug 29th, 2004 at 05:53 PM.
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  6. #6

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    hm it worked...
    1). why use nvarchar not varchar
    2). What does the SELECT actually do?

    and whoa... something worked for once, WOO!
    Last edited by invitro; Aug 29th, 2004 at 06:00 PM.
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  7. #7
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    Originally posted by invitro
    hm it worked...
    1). why use nvarchar not varchar
    2). What does the SELECT actually do?
    1. you could use varchar, nvarchar allows you to store chas which are not standard such as alphabets from different language. Though it takes up double the memory then varchar. Find out about data type and use then to your need as that will make your query more efficient

    2. Select is used to asssign value to a variable its just the TSQL syntax, in say vb you would say

    x = 1 where as in TSQL you would have to use

    select x=1 (note that a variable in TSQL must be prefiiexed with @

    Hope this helps.

    Danial
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  8. #8
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    One thing about Dynamic TSQ query(the one using exec @sql), i was told by few that Dynamic TSQL query is not efficient and should be avoided as it affects the performance. Though i have not verified it with other developer.

    I avoid using dynamic query anyway since most of my queries are too complex to be server by only one. I use dynamic query for Search stroed proedure and Deleting rows.

    For Select, Update and Inser, i have seprate Proc for each table.

    Just thought i will warn you .

    Have fun, happy learning.
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  9. #9

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    I keep gettin
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC SQL Server Driver]Syntax error or access violation

    when i try to run this:
    Code:
    CREATE PROCEDURE sp_getTableWhere
    
    	
    	@vTable varchar(25),
    	@vcharValue1 varchar(25),
    	@vcharValue2 varchar(25)
    AS
    	
    	--Declaring variable to store our query
    	Declare @sql nvarchar(500)
    	
    	--assigning the query to our variable
    	SELECT @sql = 'SELECT * FROM ' + @vTable + '  WHERE ' + @vcharValue1 + '= ' + '' + @vcharValue2 + ''
    	
    	--execute the dynamic sql query
    	EXEC(@sql)
    
    	return
    GO
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  10. #10

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    Wait i think i found the solution
    SYMPTOMS
    When you use the Microsoft ODBC Driver for SQL Server 2000, you receive the "Syntax Error Or Access violation" error message when you submit a stored procedure parameter that ends with the three ASCII characters in the following exact sequence: 0x0D, 0x1C, and 0x0D.
    RESOLUTION
    A supported fix is now available from Microsoft, but it is only intended to correct the problem described in this article. Only apply it to systems that are experiencing this specific problem. This fix may receive additional testing to further ensure product quality. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next Microsoft Data Access Components (MDAC) 2.8 that contains this fix.
    Im passing in a guid as one of the parameters... yep, its the same guid() problem. Wonedr why it keeps inserting junk at the end of my guids? Anyway, snipping time.
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  11. #11
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    SELECT @sql = 'SELECT * FROM ' + @vTable + ' WHERE ' + @vcharValue1 + '= ' + '' + @vcharValue2 + ''
    [/code] [/B]
    You have two single quote('') instead of four. (''''). Note they are not double quote, they are four single qute.

    copy the following and try.


    Code:
    SELECT @sql = 'SELECT * FROM ' + @vTable + '  WHERE ' + @vcharValue1 + '= ' + '''' + @vcharValue2 + ''''
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  12. #12

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    Sorry still dosent work
    I even cut the guid, and it gives me the same error...

    Actually, after cutting the guid it gives me another error
    [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.


    What does Line1 mean... is it saying theres a error in the Stored Procedure or somewhere in my code.

    I'm going insane
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  13. #13

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    Yeah the guid is causing this, i passed in a 0 and its fine.
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  14. #14
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    Originally posted by invitro
    Sorry still dosent work
    I even cut the guid, and it gives me the same error...

    Actually, after cutting the guid it gives me another error
    [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.


    What does Line1 mean... is it saying theres a error in the Stored Procedure or somewhere in my code.

    I'm going insane
    First of all I assumed those two fields you are comparing is String, if you have those field as number/integer then you wouldnt need to wrap them with ''''.

    Firstly simplify things, dont use dynamic query, learn how to use the normal query then move one to dynamic ones.


    Write the query seperately.

    e.g
    Code:
    CREATE PROCEDURE sp_getTableWhere
    	@value varchar(25)
    AS
    	
    	SELECT * FROM YourTable WHERE  FieldName=@value
    	
    	return
    GO
    Try it and then convert the field name to a variable and so on.

    Learn how to execute query in SQL server and Query Analyser, you can test your query there.

    Hope this helps.
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  15. #15

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    See, the problem isent the query... something else is going on here. I wrote a function to trim the guid so its a number without the { and -'s

    D479F7E0EAAC46C4ADEB450153EAD5C4

    now, when i do a query through query analyzer...
    Code:
    sp_getTableWhere tblUser, charUsername, 'myname'
    it works, selects the entire row... yay

    now, I do the EXACT same function but i change two fields
    Code:
    sp_getTableWhere tblUser, guidUserID, 'D479F7E0EAAC46C4ADEB450153EAD5C4'
    It dosent find anything... i konw that is the right guid. I checked 20 times, even compared it to the one stored in the database... but it wont find it in the query analyzer.

    I even made a query using the username, and copied the GUID that was displayed in the field... then when i went to use it, it didnt find anything.

    However, doing
    Code:
    SELECT * FROM tblUser WHERE guidUserID = 'D479F7E0EAAC46C4ADEB450153EAD5C4
    '
    Does work.. so, something is going on with the SP
    Last edited by invitro; Aug 29th, 2004 at 08:13 PM.
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  16. #16
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    Since you declared your variable as 20 chars :
    @vcharValue1 varchar(20),
    @vcharValue2 varchar(20)

    and your GUID is longer then 20 chars, i suspect it is truncuating the string thats why you are not finding it.

    change the size to 100 and try it.
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  17. #17

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    OMG... why didnt i see that. God thanks again for saving me the headacke, wow...

    *sigh* yea works great, Danial to the rescue again. Thanks, you've been a huge help, your my hero for today man. (and yes back to your previous comment, i AM thinking of migrating to .net)
    hehe
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  18. #18

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    Before I forget.. im passing in an email address as a nvarchar

    its giving me an error on the . in the email address. Whats the deal? how can i avoid that.
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  19. #19
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    Originally posted by invitro
    Before I forget.. im passing in an email address as a nvarchar

    its giving me an error on the . in the email address. Whats the deal? how can i avoid that.
    That shouldnt be any problem. What error are you getting? You can pass anything as long as you have passed it as nvarchar/string type.
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  20. #20

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.

    i know its the email because when i use the same function with another string it works.

    This is what happened with the guid.. with the guid it had a problem with the -

    would it matter that its a varchar not a nvarchar?
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  21. #21
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    Originally posted by invitro
    [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.

    i know its the email because when i use the same function with another string it works.

    This is what happened with the guid.. with the guid it had a problem with the -

    would it matter that its a varchar not a nvarchar?
    It shouldnt matter whether you use varchar or nvarchar.

    What exactly are you doing? Running select query? Inserting ?

    If its a select query I suspect you are not wrapping it with ' '

    Give me more details on how and what you are doing?

    I gotta hit the bed now, its 3.30 am here, will answer tomorrow morning.
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  22. #22

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    im using our getTableWhere function

    Code:
    CREATE PROCEDURE sp_getTableWhere
    
    	
    	@vTable varchar(50),
    	@vcharValue1 varchar(50),
    	@vcharValue2 varchar(50)
    AS
    	
    	--Declaring variable to store our query
    	Declare @sql nvarchar(500)
    	
    	--assigning the query to our variable
    	SELECT @sql = 'SELECT * FROM ' + @vTable + '  WHERE ' + @vcharValue1 + ' = ' + '''' + @vcharValue2 + ''''
    
    	--execute the dynamic sql query
    	EXEC(@sql)
    
    	return
    GO
    It works great for anything that dosent have odd characters like periods and such... but the second i put an email address in it gives me that. Maybe it has a problem with the @

    heres the error
    Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.
    Last edited by invitro; Aug 29th, 2004 at 09:59 PM.
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  23. #23
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    Did you manage to get it working? Like i said you should not have any problem passing @

    How are you passing the parameter to the proc?
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  24. #24

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    No, it wont work... i had to do this to get it working, but it only works for a single SP function. I updated my avatar to reflect my mood. HAH! Ok, I'm going to load a bunch of code on you here is how I do everything:

    Code:
    function sCart_GetRecordset(strSQL)
    {
    	if (!this.m_objConn)
    		this.m_objConn = this.GetConnection();
    
    	var objRS = Server.CreateObject("ADODB.RecordSet");
    	objRS.ActiveConnection = this.m_objConn;
    	objRS.CursorLocation = adUseClient;
    	objRS.LockType = adLockBatchOptimistic;
    	
    	objRS.Open(strSQL);
    
    	return objRS;
    }
    I pass my record set through this function.

    sCart is an object that I created and can call functions on a fly.
    Anyhow, in order to use the function above I pass in something like this

    Code:
    strSQL = 'INSERT INTO testTable WHERE blah blah...";
    this.GetRecordset(strSQL);
    The function I am using to pass in the insert is this:
    Code:
    	var strGuid = this.getGuid();
    	var pHash = objSI.md5.getHash(strPassword);
    
    	
    	
    	strSQL = "EXECUTE sp_insertUser ";
    	
    	strSQL += "\'" + strGuid + "\',2,\'" + strUser;
    	strSQL += "\',\'" + pHash + "\',\'";
    	strSQL += strFullname + "\',\'" + strAddress + "\',\'" ;
    	strSQL += strCity + "\',\'" + strCountry + "\',\'" + strPostal + "\',\'";
    	strSQL += strPhone + "\',\'" + strWPhone + "\',\'";
    	strSQL += strCPhone + "\',\'" + strFax + '\',\'' + strEmail + '\',\'';
    	strSQL += strActivation + "\',0";
    	
    	
    	Response.Write(strSQL);
    	this.ExecuteCommand(strSQL);
    Theoreticaly, it is suppose to call EXECUTE sp_insertUser from the database. However, sp_insertUser dosent like when I pass in a . or a & or anything like that. Here is sp_insertUser
    Note that the insertUser function DOES work if i leave out any wierd characters such as periods and percents.

    Code:
    CREATE PROCEDURE sp_insertUser 
    
    	@guidUserID varchar(32), @intAccessID smallint, @charUsername varchar(30), 
    	@charPHash varchar(50), @charEmail varchar(50), @charFullname varchar(50), 
    	@charStreetAddress varchar(50), @charCity varchar(25), @charCountry varchar(25), 
    	@charPostalCode varchar(9), @charPhone varchar(15), @charWorkPhone varchar(15),
    	@charCellPhone varchar(15), @charFax varchar(15), @charActivation varchar(4),
    	@blnActivated tinyint
    
    AS
    
    INSERT INTO tblUser (guidUserID, intAccessID, charUsername, charPHash, charFullname, charStreetAddress,
    			charCity, charCountry, charPostalCode, charPhone, charWorkPhone, charCellPhone, charFax,
    			charEmail, charActivation, blnActivated, datCreationDate)
    
    	VALUES (@guidUserID, @intAccessID, @charUsername, 
    		 @charPHash, @charFullname, 
    		 @charStreetAddress, @charCity, @charCountry, 
    		 @charPostalCode, @charPhone, @charWorkPhone,
    		 @charCellPhone, @charFax, '''' + @charEmail + '''',@charActivation,
    		 @blnActivated, getdate())
    
    	return
    GO
    As you will notice, I added the \' escape characters when im making the strSQL += statement. I thought this would work sincei it worked for my other function.

    Code:
    CREATE PROCEDURE sp_getTableWhere
    
    	
    	@vTable varchar(50),
    	@vcharValue1 varchar(50),
    	@vcharValue2 varchar(50)
    AS
    	
    	--Declaring variable to store our query
    	Declare @sql nvarchar(500)
    	
    	--assigning the query to our variable
    	SET @sql = "SELECT * FROM " + @vTable + "  WHERE " + @vcharValue1 + " = " + '''' + @vcharValue2 + ''''
    
    	--execute the dynamic sql query
    	EXEC(@sql)
    
    	return
    GO
    The above function will behave exactly the same, for instance if I use this statement:
    Code:
    function sCart_getRecordWhere(tblName, strCol, strVal)
    {
    
    
    	strSQL = 'sp_getTableWhere ' + tblName + ',' + strCol + ',' + strVal + ';
    
    	return this.GetRecordset(strSQL);
    }
    ...if theres a . or $ in strVal, it will give me that Line1 error bs. However, when I pass in the values like so:
    Code:
    function sCart_getRecordWhere(tblName, strCol, strVal)
    {
    
    
    	strSQL = 'sp_getTableWhere ' + tblName + ',' + strCol + ',\'' + strVal + '\'';
    
    	return this.GetRecordset(strSQL);
    }
    notice the \' chars by strVal, it works. SO, I figured I could do that for the insertUser function, but this wont work. It still gives me an error. I even tried adding the escape characters to @charEmail but no luck. This is really frustrating since im on a deadline, but thats my problem... i just wish i was more expirienced in SP's.
    Last edited by invitro; Aug 30th, 2004 at 09:14 PM.
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  25. #25
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    I am getting a bit confused, when are you getting the error. When executing Select or Insert? How are you passing the parameter to the Insert query?

    Have tried executing the quer from query manager with @ in it? That will tell you whether you have problem with your proc or the way you are passing parameter.

    If thats the case i would suggest you use Command Object instead, look at me example i posted on your other thread.

    Also post your connection string.

    Originally posted by invitro



    Code:
    CREATE PROCEDURE sp_insertUser 
    
    	@guidUserID varchar(32), @intAccessID smallint, @charUsername varchar(30), 
    	@charPHash varchar(50), @charEmail varchar(50), @charFullname varchar(50), 
    	@charStreetAddress varchar(50), @charCity varchar(25), @charCountry varchar(25), 
    	@charPostalCode varchar(9), @charPhone varchar(15), @charWorkPhone varchar(15),
    	@charCellPhone varchar(15), @charFax varchar(15), @charActivation varchar(4),
    	@blnActivated tinyint
    
    AS
    
    INSERT INTO tblUser (guidUserID, intAccessID, charUsername, charPHash, charFullname, charStreetAddress,
    			charCity, charCountry, charPostalCode, charPhone, charWorkPhone, charCellPhone, charFax,
    			charEmail, charActivation, blnActivated, datCreationDate)
    
    	VALUES (@guidUserID, @intAccessID, @charUsername, 
    		 @charPHash, @charFullname, 
    		 @charStreetAddress, @charCity, @charCountry, 
    		 @charPostalCode, @charPhone, @charWorkPhone,
    		 @charCellPhone, @charFax, '''' + @charEmail  + '''',@charActivation,
    --Highlighted bits are not needed
    		 @blnActivated, getdate())
    
    	return
    GO
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  26. #26

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    Yeah the statement executes in Query Manager fine.

    Ok, so to simply things, heres how im passing it in.

    Code:
    	var strGuid = this.getGuid();
    	var pHash = objSI.md5.getHash(strPassword);
    
    	
    	
    	strSQL = "EXECUTE sp_insertUser ";
    	
    	strSQL += "\\'" + strGuid + "\\',2,\\'" + strUser;
    	strSQL += "\\',\\'" + pHash + "\\',\\'";
    	strSQL += strFullname + "\',\'" + strAddress + "\\',\\'" ;
    	strSQL += strCity + "\\',\\'" + strCountry + "\\',\\'" + strPostal + "\\',\\'";
    	strSQL += strPhone + "\\',\\'" + strWPhone + "\\',\\'";
    	strSQL += strCPhone + "\\',\\'" + strFax + "\\',\\'" + strEmail + "\\',\\'";
    	strSQL += strActivation + "\\',0";
    	
    	
    	Response.Write(strSQL);
    	this.ExecuteCommand(strSQL);
    After I run that function, I get

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.

    /shopping_cart/CartDatabase.asp, line 88
    Line 88 is this line:

    Code:
    function sCart_GetRecordset(strSQL)
    {
    	if (!this.m_objConn)
    		this.m_objConn = this.GetConnection();
    
    	var objRS = Server.CreateObject("ADODB.RecordSet");
    	objRS.ActiveConnection = this.m_objConn;
    	objRS.CursorLocation = adUseClient;
    	objRS.LockType = adLockBatchOptimistic;
    	
    LINE 88>  objRS.Open(strSQL);
    
    	return objRS;
    }
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

  27. #27
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    This is not the propery way to execute Stored procedure. The way you are doing it you might as well use Embeded SQL insert query!

    Define SQL parameter object and then pass it to the Stored Proc, or use the way i posted, it will work fine.

    Did you read my PM?
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  28. #28

    Thread Starter
    Fanatic Member invitro's Avatar
    Join Date
    Jan 2000
    Location
    Outside your window
    Posts
    547
    D,

    After an hour of searching online, I have not found any tutorials that teach how to properaly execute a Stored Procedure in Javascript. What we discussed earlier does not work.

    Any other ideas?
    ok, so... windows takes 1 minute to search for a file on my PC yet google.com takes 1 second to search the entire internet?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width