How to use views in ASP.NET 2005

Hi,

I have created some views of the select join query in sql server 2005.

I want to make use of this views with the asp.net 2005 with C# code.

I m using SQLDATASOURCE to retrieve data from sqlserver.

Can anyone help me how to write in C#

VIEW

SELECTCAST(LEFT(SalesReturnDate, 11)AS datetime)AS Date,SUM(TotalAmount)AS SalesreturnFROM dbo.dfh_SalesReturnHeaderGROUP BYCAST(LEFT(SalesReturnDate, 11)AS datetime)

This is the sample view how to connect it with asp.net C#,sqldatsource of 2005
Thanxs in adv.
 

Hi Krish,

Lets call your view as uvw_SalesReturns

You use it like a table

You can give a sql command text or create a stored procedure containing the statement and then call the stored procedure

Sql statement is

select [Date], SalesReturn From uvw_SalesReturns

You can edit the sqldatasource and give this sql statement.

Or you can open an sqlconnection and call an sqlcommand to do it yourself without using sqldatasource if you have N-Tier Architecture.

Happy programming,
Anton

how to use UPC database in sql server

I am working in ASP.NET 2.0 and Sql Server 2005, I need to use UPCdatabase for functioning of my website as i need to fetch results fromthis databse by comparing it with the Code entered by the user.I am notsure how to use this UPC database.Can i directly fetch this databaseinformation into my local databse or i need to make calls to the centilizedlocation where the databse is stored and how to do in either way. Do ineed to pay for this or its available freee.

Please provide me the information and solution for this.

Thanks in advance

If the information in the UPC database is not to be updated then just copy the tables into your application database. This will mean one less connection string in use and thus improve performance. If the UPC database will be updated often then either you must set up a DTS job (SQL2000) or SSIS (SQL2005) to import the data, or just connect to it separatly.

As to licencing costs, please ask the vendor.

|||

How can i copy the tables.And if i want UPC databse to update then can you please send me the procedure or code to set up SSIS to import the data.I never worked on this.

Can we import the data into sql server from a text file ? If yes , then how we can do that.

Appreciate your reply.

|||

>And if i want UPC databse to update then can you please send me the procedure or code to set up SSIS to import the data.I never worked on this.
Without a copy of the UPC database I cannot do this.

I did ask you which version of SQL Server you are using?

>Can we import the data into sql server from a text file ? If yes , then how we can do that.
There are facilities within SQL Server to import such a file. How big is it? Can you send a sample via private message to me?

|||

Appreciate your reply.

I am working in SQL server 2005. The file size is approx. 10MB i suppose, didnt have file rite now with me.

|||

If the the records are all very much the same, just post a few (say 10!) and I will have a look at it tomorrow morning.

|||

This is the sample data i have for now.It can be available in Microsoft Word also.

Check it up and let me know the solution for this & let me know if any other information you need from me.I appreciate you response.

0000000000017,,"wrsi river cd"

0000000000093,,"treo 700w"

0000000000123,,"Wrsi Riversound cafe cd"

0000000000161,0,"Dillons/Kroger Employee Coupon($1.25 credit)"

0000000001090,VAR,"Rountech Asset 1"

0000000001205,"1 gal","GIANT NATURALMOUNTAIN SPRING WATER"

0000000001243,,"CVS Photo 1-Hour 4x6 Finishing"

0000000001601,,"Sainsbury's Red Pepper"

0000000002288,"18 oz","Winn Dixie HandLotion"

0000000002745,,sunglasses

0000000004145,"7.6 cm x 1.8m","ConformingBandages"

0000000005210,,maggi

0000000006217,"1 gal","Trader Joe's 1%Lowfat Milk"

0000000007511,"44oz","Maverik Plastic Soda Cup"

0000000008570,,jewerly

0000000009997,"32 FL OZ","Down to Earth -Goji Berry Juice"

0000000010344,CD,"WNCI Morning Zoo - Oops... We DidIt Again!"

0000000014946,"6.5mm thick - 3mroll","Florist Ribbon - Lemon Yellow"

0000000043816,"1.76 oz","Altoids/Peppermint"

0000000046268,,JICAMA

0000000049887,"1 x 8 oz","Coca Cola:DietCoke"

|||

Looking at

0000000001205,"1 gal","GIANT NATURALMOUNTAIN SPRING WATER"

I derived the following table structure
/****** Object: Table [dbo].[UPC] Script Date: 06/02/2007 06:05:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UPC](
[Id] [int] IDENTITY(1,1) NOT NULL,
[A] [char] (13) COLLATE Latin1_General_CI_AS NOT NULL,
[B] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[C] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_UPC] PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Identity primary key' ,@.level0type=N'SCHEMA',
@.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'UPC',
@.level2type=N'COLUMN', @.level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Possible key' ,@.level0type=N'SCHEMA', @.level0name=N'dbo',
@.level1type=N'TABLE', @.level1name=N'UPC', @.level2type=N'COLUMN', @.level2name=N'A'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Optional measure size' ,@.level0type=N'SCHEMA',
@.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'UPC', @.level2type=N'COLUMN', @.level2name=N'B'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Item description?' ,@.level0type=N'SCHEMA',
@.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'UPC', @.level2type=N'COLUMN', @.level2name=N'C'

I will save the supplied data as UPC.TXT

|||

Do i need to run this structure as it in Query analyzer.Is the location of the UPC.txt matters.

Please advise for the procedure to use the above script.

Thanks

|||

>Do i need to run this structure as it in Query analyzer.
Yes you you will need to run the script to create the table.

>Is the location of the UPC.txt matters.
No, the the location does not matter, just so long you can remember where it is.

The ASP.NET site was down when I put in my first reply this morning, so the reasoning concerning the column naming was lost.The column names are arbitrary, other than the Id column. Column A is possibly a unique value.

|||

Do the following:

Right click on the database, select tasks..., import Data|||

I created the table using your script.But i am not able to find the Import/Export functionlality when i right click on my database.I am using Microsoft Sql Server Management Studio Express.

|||

I have just had a hunt on the MSDN site, the screen of of the express range, do not have the Import..., Export... sub options. You will therefore need to write procedural code to:

Read the file record by record,|||

I am able to import tha data.What i done is first imprted the file in Sql server 2000 and then from 2000 to sql sever 2005. And import successed

I appreciate your reply.

sql

How to use UNC path to syncronize data

Hello:
I'm working with the SQL server database that is located on my web hosting
provider's server. I can control it by ASP.Net Enterprise Manager, which is
very limited comparing to the regular Enterprise Manager. I need to
syncronize data on this server with the data in another SQL server on my PC.
I've never done this before. I know that I have to do it programmatically
using T-SQL statements that use UNC path to import and export data. Where
can I learn more about it? Could you recommend me some articles about it or
give me some code examples?
I would appreciate your suggestions.
Thank you,
--
Peter AfoninCan you do a backup of that database on your WHP's server,
and then download the .bak file to your pc? If yes, then
you can restore the .bak file as a new database on your PC
or overwrite existing database.
I don't know anything about the ASP.Net Enterprise Manager
so I have to ask you this dumb question: What kind of
access do you have to the remote sql server from your
local pc?
hth.
>--Original Message--
>Hello:
>I'm working with the SQL server database that is located
on my web hosting
>provider's server. I can control it by ASP.Net Enterprise
Manager, which is
>very limited comparing to the regular Enterprise Manager.
I need to
>syncronize data on this server with the data in another
SQL server on my PC.
>I've never done this before. I know that I have to do it
programmatically
>using T-SQL statements that use UNC path to import and
export data. Where
>can I learn more about it? Could you recommend me some
articles about it or
>give me some code examples?
>I would appreciate your suggestions.
>Thank you,
>--
>Peter Afonin
>
>.
>|||Thank you.
My WHP can do daily backups of my database, but I cannot download them.
I don't actually have any access directly from my PC to the remote SQL
server. I use this ASP Enterprise Manager which is actually an ASP.Net
application (www.aspenterprisemanager.com) that I installed in my web space
and that imitates SQL server Enterprise manager, but is much more limited.
Peter
<anonymous@.discussions.microsoft.com> wrote in message
news:deb501c3f0b4$474bb7f0$a101280a@.phx.gbl...
> Can you do a backup of that database on your WHP's server,
> and then download the .bak file to your pc? If yes, then
> you can restore the .bak file as a new database on your PC
> or overwrite existing database.
> I don't know anything about the ASP.Net Enterprise Manager
> so I have to ask you this dumb question: What kind of
> access do you have to the remote sql server from your
> local pc?
> hth.
> >--Original Message--
> >Hello:
> >
> >I'm working with the SQL server database that is located
> on my web hosting
> >provider's server. I can control it by ASP.Net Enterprise
> Manager, which is
> >very limited comparing to the regular Enterprise Manager.
> I need to
> >syncronize data on this server with the data in another
> SQL server on my PC.
> >
> >I've never done this before. I know that I have to do it
> programmatically
> >using T-SQL statements that use UNC path to import and
> export data. Where
> >can I learn more about it? Could you recommend me some
> articles about it or
> >give me some code examples?
> >
> >I would appreciate your suggestions.
> >
> >Thank you,
> >
> >--
> >Peter Afonin
> >
> >
> >.
> >

how to use UDFs with asp.net

Hi there, i have a user defined function in my sql server so i can use from different pages. the question is, how do i call it from asp.net? can some one show me some code? and if u can, can u show me how to use it with data grid?
thanks.What does your UDF do? Does it return a Table value? You would treat the resultset the same way you would with any other table/stored procedure resultset.

I think more information is needed here.

Terri|||I am not aware that you can call a UDF from ADO.NET directly. You can call a stored procedure, and so perhaps you will need to wrap your UDF call in a stored procedure. More information about what the UDF does (and what it is returning) will help.|||my UDF returns a table, i have been reading some tutorials and books, and they say that "one of the advantages of using UDF is that it is possible to use it from many applications." however, i have been going nuts trying to find some help on how to code this.
if i cant use UDF, can i use stored procedures to retrive a table? i read that you can only update data with stored procedures, and that u get tables with UDF's...
thanks for your help guys.|||You can absolutely return tables from a stored procedure, and you can certainly call a stored procedure from ADO.NET on an ASP.NET page.

This link should help explain how to do it.|||thanks!|||And you can absolutely also call UDFs from ADO.NET on an ASP.NET page.

SELECT * FROM dbo.fnMyFunctionThatReturnsATable

But personally I wouldn't call the UDF directly. I'd make a stored procedure which calls the UDF.

Terri|||True, true. I have never used UDF's that way, but no reason you cannot. There is just not a CommandType.UserDefinedFunction...

Thanks!|||Hi All,

1. Here is an example of ASP.NET calling UDF returns a TABLE

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=265546
Re: Calling a SQL 2k UDF from .aspx page

-- See Posted: 07-02-2003 02:11 AM

2. As for using it, you make a judgement call.

3. You can use sp to wrap your UDF: There is an example

-CREATE FUNCTION dbo.udf_Txt_SplitTAB
-CREATE PROCEDURE sp_SELECT_IN
to wrap UDF.
-private void ASP_NET_SELECT_IN (string IN_List_Values)
to call sp.

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=280390
sql

How to use string values? I really need help for this. Thanks.

Hello,

I am working with ASP.NET/VB and Microsoft SQL 2000 database.

I have a search form where keywords are submitted.
Consider I write the the keywords 'asp' and 'book'. The results page is called as follows: results.aspx?search=asp%20book

Then I use this script in results.aspx to put the keywords in a string:

Sub Page_Load(sender As Object, e As System.EventArgs)
Dim keywords() As String = Request.QueryString("search").Split(CChar(""))
End Sub

My table is set for FULL TEXT SEARCH.
Consider the SQL when I look for records containing 'asp' and 'book':

SELECT *
FROM dbo.documents
WHERE CONTAINS (*, 'ASP or BOOK')

This SQL looks only for these words. What I need is to look for records that contain the Keywords included in the string keywords().

Can you tell me how to access the string values in the SQL and use it?

Thanks,
Miguel::Can you tell me how to access the string values in the SQL and use it?

Yes. You can not. A sql statement can not access your string variable. No way.

YOu have to handle this from the other side: you have to generate SQL that is correct in the first place. Means: the SQL has to contain all the data you want, so that it does not need to access the string value.

So start from the other end. How does your SQL have to look like (check it in query analyzer), then build code that generates exactly this sql statement. If you have trouble doing so (which I can not believe - I think you basically misunderstand that SQL is just a string that you pass forward and no, it can not connect to your variables anymore), a beginner programmer class will help, as well as any book teaching beginner programming - string manipulation is extremely beginner level.|||Assuming your string array keywords contain two strings "asp" and "book"

you could do this:

Dim s as string
Dim sep as string = ""
dim result as string
For each s in keywords
 result = result & sep & s
 sep = " OR "
Next

you now have a string var called result that looks like this: "asp OR book"

you then use the result string to help you complete the full sql stringsql

How to use SQLDataSource

Hello guys i am a bit confused, because i cannot do a simple select using the SQLDataSource from ASP.NET 2.0

here is how was doing after droping the SQLDatAdapter using the VS2003

Example

SqlDataAdapter1.SelectCommand="select * from users"

SQLDataAdapter1.Fill(Datasource1,"users")

TextBox1.DataBind()

my question is how to do it in VS2005 since that i dont see theDataAdapter anywhere, do i have to do by hand like declaring everything??

thank you in advance!!

Hi,

Because we are no longer have MSTray in the webform designer, we won't support SQLDataAdapter/DataSet on the designer. However, you can still use the old binding syntax with sqlDataAdapter/DataSet in the code.
For more information about new feature SQLDataSource, please seehttp://www.asp.net/Tutorials/quickstart.aspx

Hope that helps,

Lan

sql

How to use simple login form with SQL [newbie] ?

Heya Everyone !
I am new in Asp.net - I've read tutorial at this page, but i have some problems.
I want to have a login form at my website. I also want that login form is checking if user is in a user table (database). I mean simply login form - you write login and a password, than click button - site execute query (select * from user_table where user_id=login and user_password=password or something like this). But I have a problem with building this all code with sql connections. Can someone tell me how can I send query to my sql database ? And how can I display it? And once more - how set the cookies, but thats the other problem.

Hi there,

First of all, are you using VS 2003 or VS 2005? Because if you're using VS 2005, there are built in IDE to create a login form.

|||ronnyr --> i'm using VS 2005, but I want to have login through the sql database (simple CMS) not like this is in asp.net tutorial, where i'm using login form and changing parametrs in administration zone (I hope that you know what I mean).

I just want:
Two textboxes and simple button (like you have in html form). When I click my button, site should send a query which looks for user with user login the same as that in a textbox1 and with pass the same as that in a textbox2. If all is ok, user is logged in.|||And once more --> I have problem with:

Dim ConnectionString As String = System.Configuration.AppSettingsSection("ConnectionStrings")

How I should declare this string?
Im getting an error:
'AppSettingsSection' is a type in 'Configuration' and cannot be used as an expression.|||

So basically you just need to figure out the problem with your database connection.

Check this site:http://aspnet.4guysfromrolla.com/articles/110905-1.aspx

Hope this helps...

|||Okey, I've got connection. But I have another problems.

Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("forumConnectionString").ConnectionString
Dim test As String
test = Label1.Text

Using myConnection As New Data.SqlClient.SqlConnection(ConnectionString)
'Specify the SQL query
Const sql As String = "SELECT * FROM kontakty"

'Create a SqlCommand instance
Dim myCommand As New Data.SqlClient.SqlCommand(sql, myConnection)

'Get back a DataSet
Dim myDataSet As New Data.DataSet

'Create a SqlDataAdapter instance
Dim myAdapter As New Data.SqlClient.SqlDataAdapter(myCommand)
myAdapter.Fill(myDataSet)

'Bind the DataSet to the GridView
g.DataSource = myDataSet
g.DataBind()

'Close the connection
myConnection.Close()
End Using

And I have:
TextBox1, TextBox2, Button

How I should make that when I click button, query will be
"SELECT [user_id] FROM users WHERE [user_name]=TextBox1.Text, [user_pass]=TextBox2.Text"
This query is incorrect, but I hope that someone show me how is ok.|||And once more:
i can't see an Query Wizard (or Code builder wizard). I searched lotsof, but i didn't find anything. Anyone can help me how can I enableQuery Wizard? (VS 2005)|||

Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("forumConnectionString").ConnectionString
Dim test As String
test = Label1.Text

Using myConnection As New Data.SqlClient.SqlConnection(ConnectionString)
'Specify the SQL query
Const sql As String = "SELECT COUNT(*) FROM kontakty WHEREUsername=@.Username ANDPassword=@.Password"

'Create a SqlCommand instance
Dim myCommand As New Data.SqlClient.SqlCommand(sql, myConnection)
myCommand.AddParameter("@.Username",txtUsername.text)
myCommand.AddParameter("@.Password",txtPassword.text)

if myCommand.ExecuteScaler==0 then
txtError.text="Invalid Username or Password"
else
Session("Username")=txtUsername.text
response.redirect("somepage.aspx")
end if
End Using

Or something very similiar to that... (Sorry, coded by hand, no syntax check)

|||

I'm not sure what "Query wizard" you are referring to, but...

You can also do something similiar by dropping a sqldatasource object on the page, set it's connection properties, then on the selectcommand property, there will be a "..." button you can hit to help you make your query. Tell it you want to select something like the username from your users table, add a where clause to it whereusername=@.username andpassword=@.password, click the refresh parameters button, then tell it the @.username parameter comes from a control, tell it the controlid is txtUsername, tell it the @.password parameter comes from a control, tell it the controlid is txtPassword, then when you click the button, in it's click event do something similiar like this: (Sorry, I've never had to do this myself, so it's not going to be exact)

dim ds as dataset

ds=sqldatasource1.select

if ds.row(0).column(0).value=txtUsername.text then

' Do login stuff

else

'Do bad login stuff

end if

|||

Now that I've said all that, I'd recommend you don't do this, and use the built in log in form, and related controls. You can tie login form and controls to a SQL database extremely easily, and it'll take care of a HUGE chunk of the issues you'll face, such as:

How to hash a password into the database so it's secure.

How to allow a user to reset his password (in a fairly secure manner...)

Handle the cookies for "Remember me" type log ins.

Persist User preferences and profiles.

And it'll make it all much easier for you to do whatever it is your web app is supposed to do rather than concentrating on the unimportant stuff.

|||Motley --> big thanks, but I have few problems ...

1.
myCommand.AddParameter("@.Username", user_login.Text)
I have an error:

AddParameter' is not a member of 'System.Data.SqlClient.SqlCommand'.

I don't know how to change this, cause in VS 2005 there are other way to this all functions (I hope you understand :) )

2.
You mean that this simple login form which I have in toolbox I can changed for my SQL database? Not as it normal that I put login form and it logged me to the other database.

3.
As an Query Wizard I mean this from point 6 till 28
http://www.asp.net/webmatrix/guidedtour/section91/createloginpage.aspx
There Wizard make self a login procedure.

Thanks for this help which I already get :)
|||

1. It should have been:

myCommand.Parameters.Add(New system.data.sqlclient.sqlparameter("@.Username",user_login.text))

2. Yes, you can have the login control access a different database, by specifying the provider in your web.config. If your database is on SQL Server 2005, the web.config admin pages can create the tables and stored procedures for you automatically, or you can use a small batch file that is included with VS 2005 to create them for you on a SQL Server 2000 database.

3. That is a feature of Web Matrix, which is a different IDE than VS 2005.

How to use sql server

When i connect to SQL server from ASP, i could get through but i kindda of not sure how to use it.
Could you pls help me and show me the coding to use it.

My coding when i connect to SQL server from ASP:

Dim conn,strSQL,rs

set conn=Server.CreateObject("ADODB.Connection");
conn.ConnectionString="driver={SQL Server};"&_.........
..............

strySQL="Select empName from employee"
set rs=Server.CreateObject("ADODB.Recordset")
conn.Open
rs.Open strSQL,conn

Maybe you could show me how to save something into the database.
any field given.
Thanxdeclare one more variable in your asp script and change the strySQL code like the below,

Dim val
Dim conn,strSQL,rs

set conn=Server.CreateObject("ADODB.Connection");
conn.ConnectionString="driver={SQL Server};"&_.........
..............

strySQL="insert into employee values('& val &')"

set rs=Server.CreateObject("ADODB.Recordset")
conn.Open
rs.Open strSQL,conn

'try it.....|||http://support.microsoft.com/default.aspx?scid=kb;EN-US;176380 &
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q169/3/77.asp&NoWebContent=1 further information.

And http://www.devarticles.com/c/a/ASP/Two-Ways-To-Connect-To-SQL-Server-2000-Via-ASP/ loads of articles about ASP & SQL Server.|||try this code:

Dim conn,strSQL,rs

set conn=Server.CreateObject("ADODB.Connection");
conn.ConnectionString="driver={SQL Server};"&_.........
..............

strySQL="Select empName from employee"
set rs = conn.execute(strySQL)
.
.
.
and it returns the recordset to rs variable...then you can get data from rs like this:

do while not rs.EOF
Response.Write rs(0)
rs.movenext
loop

write if it works for you...

how to use sql encryption via an asp site. is this correct?

ive been playing around with encryption, just encrypting a column with a symmetric key, and having that symmetric key encrypted with a cert, and all that , and it works well through the SSMS, but i want to know how that will be used if im using sql server as a back end database for an asp web site, or an application. does the sql server have to be protected with an SSL cert, and once a user has logged in, what would the steps be to view encrypted data?

lets say i have an asp.net site wtih forms authentication, where the username and password are checked against values stored in a table, all done via https so the password data cant fall into the wrong hands. once the user is authenticate this is my idea:

each user has a status, stored in the status column of the users table : Admin or RegularUser.

if the user status is an Admin, and clicks on something that returns encrypted data, a sproc checks the user has admin status and uses impersonation to impersonate a login that has control to use the encryption key that encrypted the data. so the data is returned unencrypted.

is this a valid way of using encryption via asp.net, or are their security issues?

|||

SSL protects the communication with the server; the encryption you experimented with is for protecting the data within the server. For the steps required to view the encrypted data, it is up to you to determine those in your database application - you can have the user manually decrypt or you can have him access the data via views that automatically decrypt.

Thanks
Laurentiu

|||

You can provide access to data via a view that automatically decrypts using decryptbykeyautocert, for example. You can then group your Admin users in a role and grant permissions to that role to use the key. Admin users would then be able to decrypt the data and see it; regular users would not be able to.

Thanks
Laurentiu

|||that makes sense. thanks. one thing though. as the only account to access the sql server is the account the website is running under, how can i grant different groups of users different security levels? could it be as simple as once they log into the site ( which checks they are in the users table and checks a col called SecurityLevel) and if they have a level of "Admin User", the site will call sprocs for them that uses impersonation of a login that has admin access, where as if they only have "standard User" security level, the site will call sprocs that uses a login with low priviledges via impersonation to get their data. is this a viable solution or is it a round about way of doing something that could be much easier?

How to use SQL 2000 with firewall ?

Hi,

I have developed and ASP.net application that use SQL 2000 as database server.

on the production envarnument , the application and the SQL DB server are on separate servers , and there is a firewall between them.

the firewall open just Http ports , how can I my application connect to the SQL server with existance of the firewall?

Thanks in advance

Hi EmadH,

These KB articles may be of some assistance for configuring the firewall:

http://support.microsoft.com/kb/287932/en-us
http://support.microsoft.com/kb/841249/en-us

Il-Sung.

how to use parameters in method (filter by parameter)

Hello!

I've begin to do a tutorial of this site: Working with Data in ASP.NET 2.0 c#, but in the 3. step I can't make something:

In this step I can't add parameterized methods to the TableAdapter. The problem come up when I want to add Sql query.

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @.CategoryID

The error message:

Error in WHERE clause near'@.'.
Unable to parse query text.

So, how can i use parameter in this method?

tnx for the help

Simpson

Are you sure you are passing in the parameter values to the TableAdapter when calling in your code?

Thanks

|||Hi

I have the same problem have you found a solution.

Regards
Demetrios|||Hi

I have the same problem have you found a solution.

Regards
Demetrios|||Hi

I have the same problem have you found a solution.

Regards
Demetrios|||

Hi MaoBranca,

This might because the @.CategoryID was not declared properly.

If this is a SQL Server database, please make sure that you have added the parameter to the SqlCommand parameter collection. If it has been added, please also confirm that the data type, size and value are correct.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||i have found the solution

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = @.CategoryID

this works for sqlserver if you are using an access database then it is

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = ?

if you are using mysql then it is

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE CategoryID = #CategoryID

but i an not sure with mysql

how to use listavailablesqlserver method

Hai

I am studying sqldmo with asp.net . I wanted to generate a list of available sqlservers in my network. so i used the listavailablesqlservers method which is using the application object. when i tried to use that i am getting an error. the error is as below

QueryInterface for interface SQLDMO.NameList failed.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: QueryInterface for interface SQLDMO.NameList failed.

Source Error:

Line 128: Dim i As Integer
Line 129: cmbtest.Items.Clear()
Line 130: namex = oapp.ListAvailableSQLServers
Line 131: For i = 0 To namex.Count
Line 132: cmbtest.Items.Add(namex.Item(i).ToString)

The full coding is shown below for reference if anyone able to help me out to get rid of this problem do reply to my id
I have declared the oapp and namex in the begining itself as

Public oapp As New SQLDMO.Application()
Public namex As SQLDMO.NameList

Private Sub cmdcheck_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdcheck.Click
Dim i As Integer
cmbtest.Items.Clear()
namex = oapp.ListAvailableSQLServers
For i = 0 To namex.Count
cmbtest.Items.Add(namex.Item(i).ToString)
Next
End Sub

sasidar_d@.hotmail.com

Thanks in advance

SasidarHi Sasidar!
I m also facing the same problem. Kindly reply back if u got the soln...

Thnx & Bye.
Lita|||Hi!
Just install sqlserver 2000 service pack 3 (in case u r using sql server 2000) & it will be solved. Mine is solved...

Bye.|||Which SQL Server version and service pack are you running?

Terri

How to use comma separated lists with parameters in sqldatasource?

I'm new (very new) to asp.net and now at 3am after maybe 12 straight hours of trying to go through examples and understand the syntax, I have a somewhat working program... basically a query parameters screen that upon a click generates a report. The query parameter screen basically is a HTML form with a procedure that puts the field values into session variables. The report uses <asp:sessionparameters> in the sqldatasource to apply aforementioned session variables to filter the SELECT statement's results. They are then displayed using the gridview control. It actually works!

Now I'm trying to figure out: if I need/want my users to be able to enter comma-separated list of values into the HTML form field and somehow get these into the SELECT statement's WHERE clause, is there a way to do this? I'm not very experienced with asp nor sql server and have been struggling with this new challenge. I really could use pointers as to whether there's an easy way to handle this. In other languages in the past I would have had to parse the comma-separated list and use those to construct my SELECT statement programmatically. I get the impression that with all this ASP.NET fanciness, there has to be a better way?

If it matters, I'm using ASP.NET 2.0 with SQL Server 2005 Express and Visual Studio Web 2005 Express.

Thanks in advance for any replies, they would be GREATLY appreciated :)

Hi,

you either generate the SQL query programmatically based on the CSV, e.g split the CSV string with the delimiter, loop the array returned by String.Split and then buold the SQl string dynamically. Other way is to create a stored procedure to the Db and use functions to split CSVs for example:http://www.sqlteam.com/item.asp?ItemID=11499