none
Problems trying to create a table adapter usng a new stored procedure or an existing strored procedure RRS feed

  • Question

  • I am using VB.NET in Visual Studio 2008 to connect to a SQL Server database. Some of the fields in the database are encrypted. I have written an SQL stored procedure to decrypt the fields and return the result set.  The stored procedure takes one parameter, the passphrase.   Using VS2008 I am trying to create a TableAdapter using the TableAdapter Configuration Wizard to get the data from the stored procedure. I have tried two ways and both have problems. Either method, if it worked, would be fine.

    1. When I choose “Use existing stored procedures” and select the stored procedure, the data set has names of Column1, etc. Since there are 278 fields in 5 tables, renaming them manually could be error-prone. The previewed data is correct. How can I have VS pickup the column names from the result set returned by the stored procedure?
    2. When I choose “Create new stored procedures” and enter the SQL from the stored procedure, I get the error “Parameter or variable @phrase has an invalid data type.” This is because the generated SQL code shown in “Preview SQL Script” gives the parameter a type of “unknown”. When I preview the data in the Query Builder, it is correct. The column names are correct. How can I specify the type of the parameter so that it will not cause the error?  

    Sample SELECT statement from the stored procedure is below. This may not be the best way to get the data, but it works. I haven’t used CAST, CONVERT, or DECRYPTBYPASSPHRASE before this.

    SELECT CAST(CAST(DECRYPTBYPASSPHRASE(@phrase, mass_enc) AS DECIMAL(19, 16)) AS FLOAT) AS mass FROM Hardware
    

    The complete error message mention above is “There was a problem with the SELECT stored procedure. The stored procedure was not created. Adding new stored procedure to database failed. Column, parameter, or variable #1: Cannot find data type unknown. Parameter or variable ‘@phrase’ has an invalid data type.”

    ADDTIONAL:  The problem with #1 appears to be caused by CAST or DECRYPTBYPASSPHRASE.  It works fine without those, except of course that I don't get unencrypted data.
    • Edited by Wayne Goode Tuesday, December 1, 2009 6:01 PM added more info under ADDITIONAL
    Monday, November 30, 2009 10:09 PM

Answers

  • After finding out that the problem was related to CAST or DECRYPTBYPASSPHRASE and not all such cases in VS, I decided to try creating a function to wrap all the CAST and DECRYPTBYPASSPHRASE functions.  I quickly learned how to create functions and added a function the meat of which is:

    RETURN CAST(CAST(DECRYPTBYPASSPHRASE(@phrase, @Encrypted) AS DECIMAL(19, 1)) AS FLOAT)
    I had to write 19 of these because the second parameter to DECIMAL can't be a variable. When I used this function in the SELECT query, the columns names were returned.  The SQL looks like:

    SELECT dbo.Decrypt16(@phrase, mass_enc) AS mass FROM Hardware
    This doesn't explain why the two problems exist, but it gives me a work-around.
    Wednesday, December 2, 2009 5:52 PM

All replies

  • Apparently you are using strongly typed dataset
    I have tested a regular stored procedure with a typed dataset and I got field names back.
    To answer your questions:

    1. I dragged and dropped TableAdapter Component to the Dataset Designer and went thru the wizard "Use Existing stored procedures" the field names showed up.

    2. I dragged and dropped TableAdapter Component and this time I selected "Create New stored Procedure" you need to enter your select statement with the parameter and the designer will create the stored procedure for you something like this. It worked for me
    SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
    FROM Employees INNER JOIN 
    	(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 
    	ON Employees.EmployeeID = Orders.EmployeeID
    WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date


    John
    Monday, November 30, 2009 10:59 PM
  • JohnFL,

    I tried a similar query using the same table as I used above, but with an unencrypted field and using the parameter in a WHERE clause.  You're right.  That worked fine.

    SELECT mass_enc AS mass FROM Hardware

    I even put in an AS clause and it worked.  It appears that the problem (#1) is caused by the CAST or DECRYPTBYPASSPHRASE.  This narrows it down but does not solve it.
    Tuesday, December 1, 2009 5:59 PM
  • is it crucial in your development to use strongly typed dataset? you can get around this problem by using untyped dataset
     
    John
    Tuesday, December 1, 2009 6:05 PM
  • After finding out that the problem was related to CAST or DECRYPTBYPASSPHRASE and not all such cases in VS, I decided to try creating a function to wrap all the CAST and DECRYPTBYPASSPHRASE functions.  I quickly learned how to create functions and added a function the meat of which is:

    RETURN CAST(CAST(DECRYPTBYPASSPHRASE(@phrase, @Encrypted) AS DECIMAL(19, 1)) AS FLOAT)
    I had to write 19 of these because the second parameter to DECIMAL can't be a variable. When I used this function in the SELECT query, the columns names were returned.  The SQL looks like:

    SELECT dbo.Decrypt16(@phrase, mass_enc) AS mass FROM Hardware
    This doesn't explain why the two problems exist, but it gives me a work-around.
    Wednesday, December 2, 2009 5:52 PM
  • Hello Wayne,

     

    I think you have found the workaround.  Do you have any further questions on this problem?  If so, please feel free to let me know.

     

    Have a nice weekend!

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, December 4, 2009 11:34 AM
    Moderator