Showing posts with label tutorial. Show all posts
Showing posts with label tutorial. Show all posts

Wednesday, March 28, 2012

how to use word and pdf filter in full text search (SQLServer2k5)

where to find and how to use full text search service to index word and pdf documents. please point me to a tutorial or give a step by step instruction. thanks for reading.http://www.codeproject.com/csharp/fulltextsearchingifinters.asp

Friday, March 23, 2012

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.

Wednesday, March 21, 2012

How to use SQL2005 Notification Services?

I followed the tutorial instructions came with SQL 2005 enterprised
edition about the Notification Services, but it failed at the final
step when I started the Notification Services. The Generator and
Distributor won't start (enable pending, then timeout).
I did everything as the database symin (a window account) directly
on the server. can anyone tell me how to make it work? (the tutorial
suggested a less previleged windows account and I had difficult times
to register/enable/start the services with that account as well).
Thank you!I went through the tutorial, and didn't have any problems until I tried
sending out notifications through email and usings triggers in external
databases as event providers.
When you register the instance of the tutorial notification service, use an
account that has the right to log on as a service. I created a user account
named 'nstutorial', and manually granted that user the 'logon as a service'
right using Local Security Settings. The account was a normal user account;
not a member of administrators. Also check to make sure that the account tha
t
you do specify has a login and appropriate permissions in the NSTutorialMain
and TutorialWeather databases.
"=== Steve L ===" wrote:

> I followed the tutorial instructions came with SQL 2005 enterprised
> edition about the Notification Services, but it failed at the final
> step when I started the Notification Services. The Generator and
> Distributor won't start (enable pending, then timeout).
> I did everything as the database symin (a window account) directly
> on the server. can anyone tell me how to make it work? (the tutorial
> suggested a less previleged windows account and I had difficult times
> to register/enable/start the services with that account as well).
> Thank you!
>|||can you explain the steps you took to '...manually granted that user
the 'logon as a service'
right using Local Security Settings...'?
I tried different approach but the generator and distributor always
displayed as 'enable pending'. I specifically added the windows account
to the local admin on the box and made sure they are dbo with run ns
service role on the database but nothing seemed to work.
it's frustrating that the tutorial is so difficult to follow... I
think Microsoft should better explain what happened when you 'update',
'register', 'enable', and 'start' a notification service so people can
have a better idea at trouble-shooting.|||start -> run -> secpol.msc
Expand "Local Policies" -> "User Rights Assignment"
Double-click "Log on as a service"
Add in the user account that you will run the NS$Tutorial instance under.
"=== Steve L ===" wrote:

> can you explain the steps you took to '...manually granted that user
> the 'logon as a service'
> right using Local Security Settings...'?
> I tried different approach but the generator and distributor always
> displayed as 'enable pending'. I specifically added the windows account
> to the local admin on the box and made sure they are dbo with run ns
> service role on the database but nothing seemed to work.
> it's frustrating that the tutorial is so difficult to follow... I
> think Microsoft should better explain what happened when you 'update',
> 'register', 'enable', and 'start' a notification service so people can
> have a better idea at trouble-shooting.
>|||Can you answer a couple of questions?
Does the event provider start? (You only mention the generator and
distributor.)
Are the event providers, generator, and distributor all located on the same
system? If you have scaled out the instance, then you must update the system
name values in the ADF.
Is the tutorial failing at Start or Enable? Enable/Disable is different than
Start/Stop.
If the service starts, the Windows account has the necessary permissions.
(This permission is granted when you register the instance of Notification
Services. The tutorial does not go into these details, but it is covered in
the Notification Services/Administration/Security section.)
If the service does not start, make sure that the account exists (either on
the domain or local computer) and has the permissions outlined in the
tutorial. This includes SQL Server, database, and Windows permissions.
If the service starts, but the status is stuck at Enabled Pending, then one
of the system name values might not be correct. Enabling and disabling sets
a value in the database. The Windows service checks the database for it's
enabled/disabled status every 30 seconds. If the machine the service the
database is on does not match the machine name provided for the generator
and distributor, the status gets stuck at Enable Pending.
-Diane Larsen
SQL Server User Education
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1135030125.536802.81030@.g14g2000cwa.googlegroups.com...
>I followed the tutorial instructions came with SQL 2005 enterprised
> edition about the Notification Services, but it failed at the final
> step when I started the Notification Services. The Generator and
> Distributor won't start (enable pending, then timeout).
> I did everything as the database symin (a window account) directly
> on the server. can anyone tell me how to make it work? (the tutorial
> suggested a less previleged windows account and I had difficult times
> to register/enable/start the services with that account as well).
> Thank you!
>|||This is the info of my setup and testing process:
Server OS: windows 20003 with sp2
Install SQL 2005 as default instance (Server name is Boo)

>Does the event provider start? (You only mention the generator and
distributor.)
To answer your question, yes, 'Application', 'Subscriptions',
and 'Event Provider' are enabled (with blank server name), but the
'Generator' and 'Distributor' are 'enable pending' and I
couldn't get them start (it always timed out).

>Are the event providers, generator, and distributor all located on the same
system? If you have scaled out the instance, then you must update the
system
name values in the ADF.
They should. I did a default complete install on server (with a windows
domain account, that account not only in the domain admin group, but
also in the local admin group, and has symin right for the
databases), so there is no name instance.
I followed the instructions in the tutorial to edit the TutorialICF.xml
file located at
C:\Program Files\Microsoft SQL Server\90\Samples\Notification
Services\Tutorial (on the server) and saved it.
I tried with two sets of paramters:
One has all the the default values such as
<Parameter>
<Name>_DBEngineInstance_</Name>
<Value>%COMPUTERNAME%</Value>
</Parameter>
<Parameter>
<Name>_ServerName_</Name>
<Value>%COMPUTERNAME%</Value>
</Parameter>
...
<Parameter>
<Name>_NSServer_</Name>
<Value>%_ServerName_%</Value>
</Parameter>
And the other set with alternative values, but it didn't make any
difference)
<Parameter>
<Name>_DBEngineInstance_</Name>
<Value>Boo</Value>
</Parameter>
<Parameter>
<Name>_ServerName_</Name>
<Value>Boo</Value>
</Parameter>
...
<Parameter>
<Name>_NSServer_</Name>
<Value>Boo</Value>
</Parameter>
When testing the Tutorial Notification Services, I tired two different
accounts as well,
One is the Windows Domain Account I mentioned above (the one in the
domain admin group, in the local admin group, and has symin right
for the database server).
The other is a less previlaged Windows Domain Account (in Local admin
group, not a symin for the database, but has dbo rights and role
for running notification services, NSRunService role for both
TutorialNSMain and TutorialWeather databases. I also manually granted
that user
the 'logon as a service'
right using Local Security Settings.
I tried both accounts with the 'Update', 'Register',
'Enable', and 'Start' work flow specified in the tutorial for
testing the Notfication Services. But neither of them worked. The
'Generator' and 'Distributor' are always displayed as 'enable
pending'. If I start them manually, then it timed out.
'... If the machine the service the
database is on does not match the machine name provided for the
generator
and distributor, the status gets stuck at Enable Pending.'
Can you explain how to verifty that?
Also, the tutorial is not clear that whether this should be done
directly on the server or thru the Server Manager Studio on my work
station, I tried both and it didnt' make any difference either. Some of
processes only worked if I did them directly on the server.|||I think the problem might be a registration issue. In the case of the
tutorial, registration creates a Windows service and grants a set of
permissions to the service account (and creates performance counters).
Registering an instance of Notification Services happens on the local
server. If you are registering the instance from a workstation, the Windows
service is created on that workstation. In order for the service to start
and for the components to change to Enabled, the server where the Windows
service runs much match the SystemName values in the ADF.
For example, if your database server name is Boo, and you register the
instance from a server named Client, the Windows service will be created on
Client. When you start the Windows service, it connect to the database
server (Boo) and look for any components that should run on Client. If no
components are configured to run on Client, they will not be set to Enabled.
If components are configured to run on server Boo, make sure you register
the instance on server Boo (using terminal services or a direct connection)
and then start the service on Boo.
As for permissions, Windows permissions, such as Log On As Service and
membership in the SQLServer2005NotificationServicesUser$Co
mputerName group,
are granted when you register the instance. That's the primary reason why
these permissions are not documented within the tutorial. See the
"Configuring Windows Accounts for an Instance of Notification Services "
topic for more information.
Here's a little more info about Update, Register, Enable, Start:
- Update is a database operation. It reads the ICF and related ADFs and then
updates the instance and application databases accordingly. You need to be
at least the database owner in all instance and application databases to run
Update.
- Register is a Windows operation. It runs locally, meaning that if you run
the register command on a client machine, the instance is registered on the
client machine. The name of the server on which you run the register command
much match the SystemName values for the components that run on that server.
(In other words, if you want the Generator to run on Boo, the generator's
SystemName value must be Boo, and you must register and start the instance
on Boo.) You must be a local Admin or a member of the
SQLServerNotificationServicesUser group to register an instance.
- Enable is a database operation. It simply sets bits in the instance and
application databases. These value are picked up by the correlating Windows
service (or NS engine) when you start the instance.
- Start (within Management Studio) runs a NET START command on each Windows
service associated with the instance. When each service starts, it looks for
the enabled/disabled status of its components in the database(s). You can
see the Windows services and servers in Management Studio by right-clicking
the instance and selecting Properties. You also can obtain status by running
nscontrol status.
One final note: Make sure to look for errors in the Windows Event Viewer. I
believe errors related to starting/stopping and enable/disable are now
written to the event viewer.
I hope this helps. Please let me know if you solve the issues you are
having. We tested the tutorial for single-server deployments, but if
something needs to be fixed or clarified, I will file a bug for the next
release.
Diane Larsen [MSFT]
Technical Writer
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1135112769.954593.248320@.o13g2000cwo.googlegroups.com...
> This is the info of my setup and testing process:
> Server OS: windows 20003 with sp2
> Install SQL 2005 as default instance (Server name is Boo)
>
> distributor.)
> To answer your question, yes, 'Application', 'Subscriptions',
> and 'Event Provider' are enabled (with blank server name), but the
> 'Generator' and 'Distributor' are 'enable pending' and I
> couldn't get them start (it always timed out).
>
same
> system? If you have scaled out the instance, then you must update the
> system
> name values in the ADF.
> They should. I did a default complete install on server (with a windows
> domain account, that account not only in the domain admin group, but
> also in the local admin group, and has symin right for the
> databases), so there is no name instance.
> I followed the instructions in the tutorial to edit the TutorialICF.xml
> file located at
> C:\Program Files\Microsoft SQL Server\90\Samples\Notification
> Services\Tutorial (on the server) and saved it.
> I tried with two sets of paramters:
> One has all the the default values such as
> <Parameter>
> <Name>_DBEngineInstance_</Name>
> <Value>%COMPUTERNAME%</Value>
> </Parameter>
> <Parameter>
> <Name>_ServerName_</Name>
> <Value>%COMPUTERNAME%</Value>
> </Parameter>
> ...
> <Parameter>
> <Name>_NSServer_</Name>
> <Value>%_ServerName_%</Value>
> </Parameter>
> And the other set with alternative values, but it didn't make any
> difference)
> <Parameter>
> <Name>_DBEngineInstance_</Name>
> <Value>Boo</Value>
> </Parameter>
> <Parameter>
> <Name>_ServerName_</Name>
> <Value>Boo</Value>
> </Parameter>
> ...
> <Parameter>
> <Name>_NSServer_</Name>
> <Value>Boo</Value>
> </Parameter>
> When testing the Tutorial Notification Services, I tired two different
> accounts as well,
> One is the Windows Domain Account I mentioned above (the one in the
> domain admin group, in the local admin group, and has symin right
> for the database server).
> The other is a less previlaged Windows Domain Account (in Local admin
> group, not a symin for the database, but has dbo rights and role
> for running notification services, NSRunService role for both
> TutorialNSMain and TutorialWeather databases. I also manually granted
> that user
> the 'logon as a service'
> right using Local Security Settings.
> I tried both accounts with the 'Update', 'Register',
> 'Enable', and 'Start' work flow specified in the tutorial for
> testing the Notfication Services. But neither of them worked. The
> 'Generator' and 'Distributor' are always displayed as 'enable
> pending'. If I start them manually, then it timed out.
> '... If the machine the service the
> database is on does not match the machine name provided for the
> generator
> and distributor, the status gets stuck at Enable Pending.'
> Can you explain how to verifty that?
> Also, the tutorial is not clear that whether this should be done
> directly on the server or thru the Server Manager Studio on my work
> station, I tried both and it didnt' make any difference either. Some of
> processes only worked if I did them directly on the server.
>|||I should say that events are written to the application log, which is
accessible through the event viewer.
Diane Larsen [MSFT]
Technical Writer
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1135112769.954593.248320@.o13g2000cwo.googlegroups.com...
> This is the info of my setup and testing process:
> Server OS: windows 20003 with sp2
> Install SQL 2005 as default instance (Server name is Boo)
>
> distributor.)
> To answer your question, yes, 'Application', 'Subscriptions',
> and 'Event Provider' are enabled (with blank server name), but the
> 'Generator' and 'Distributor' are 'enable pending' and I
> couldn't get them start (it always timed out).
>
same
> system? If you have scaled out the instance, then you must update the
> system
> name values in the ADF.
> They should. I did a default complete install on server (with a windows
> domain account, that account not only in the domain admin group, but
> also in the local admin group, and has symin right for the
> databases), so there is no name instance.
> I followed the instructions in the tutorial to edit the TutorialICF.xml
> file located at
> C:\Program Files\Microsoft SQL Server\90\Samples\Notification
> Services\Tutorial (on the server) and saved it.
> I tried with two sets of paramters:
> One has all the the default values such as
> <Parameter>
> <Name>_DBEngineInstance_</Name>
> <Value>%COMPUTERNAME%</Value>
> </Parameter>
> <Parameter>
> <Name>_ServerName_</Name>
> <Value>%COMPUTERNAME%</Value>
> </Parameter>
> ...
> <Parameter>
> <Name>_NSServer_</Name>
> <Value>%_ServerName_%</Value>
> </Parameter>
> And the other set with alternative values, but it didn't make any
> difference)
> <Parameter>
> <Name>_DBEngineInstance_</Name>
> <Value>Boo</Value>
> </Parameter>
> <Parameter>
> <Name>_ServerName_</Name>
> <Value>Boo</Value>
> </Parameter>
> ...
> <Parameter>
> <Name>_NSServer_</Name>
> <Value>Boo</Value>
> </Parameter>
> When testing the Tutorial Notification Services, I tired two different
> accounts as well,
> One is the Windows Domain Account I mentioned above (the one in the
> domain admin group, in the local admin group, and has symin right
> for the database server).
> The other is a less previlaged Windows Domain Account (in Local admin
> group, not a symin for the database, but has dbo rights and role
> for running notification services, NSRunService role for both
> TutorialNSMain and TutorialWeather databases. I also manually granted
> that user
> the 'logon as a service'
> right using Local Security Settings.
> I tried both accounts with the 'Update', 'Register',
> 'Enable', and 'Start' work flow specified in the tutorial for
> testing the Notfication Services. But neither of them worked. The
> 'Generator' and 'Distributor' are always displayed as 'enable
> pending'. If I start them manually, then it timed out.
> '... If the machine the service the
> database is on does not match the machine name provided for the
> generator
> and distributor, the status gets stuck at Enable Pending.'
> Can you explain how to verifty that?
> Also, the tutorial is not clear that whether this should be done
> directly on the server or thru the Server Manager Studio on my work
> station, I tried both and it didnt' make any difference either. Some of
> processes only worked if I did them directly on the server.
>|||Thank you so much for the reply, but it's still not working so I'm
providing step by step what I have done. Perhaps someone can tell me
what I did incorrectly.
I followed the instructions in the tutorial to edit the TutorialICF.xml
and ADF files located at
C:\Program Files\Microsoft SQL Server\90\Samples\Notification
Services\Tutorial (on the server itself) and saved them.
The Parameter value I used are (it's a default instance, and I
didn't use named instance)
<Name>_DBEngineInstance_</Name>
<Value>%COMPUTERNAME%</Value>
</Parameter>
<Parameter>
<Name>_ServerName_</Name>
<Value>%COMPUTERNAME%</Value>
</Parameter>
...
<Parameter>
<Name>_NSServer_</Name>
<Value>%_ServerName_%</Value>
</Parameter>
I deleted and re-created a new instance of Tutorial Notification
Service from scratch using the windows domain account (it's in domain
admin group, in the local admin group, and has symin right for the
database server. The account was also used for installing the SQL 2005
and is the account SQL Server services is running under). The new
notification instance has the following names and vaules:
Name Value
COMPUTERNAME Boo
ProgramFiles C:\Program Files
_DBEngineInstance_ %COMPUTERNAME%
_InstancePath_ %ProgramFiles%\Microsoft
SQL Server\90\Sample...
_ServerName_ %COMPUTERNAME%
I then went thru 'Update', 'Register' processes directly on the
server using the same account. But when I got to 'enable' step. The
'Genetor' and 'Distributor' are both Enable Pending and I was
unable to go further (it errored out if I started the service). The
values look like this:
Application: Weather
Server Component Name CurrentStatus
Blank Application Weather Enabled
Blank Subscriptions Blank Enabled
Blank Event Provider WeatherSPEventProvider Enabled
Boo Generator Boo Enable Pending
Boo Distributor Boo Enable Pending
I looked at the event log and here is the error message.
Event Source: Service Control Manager
Event ID: 7023
Computer: Boo
Description:
The NS$Tutorial service failed to start due to the following error:
An internal error occurred.
Also, just to be thorough, here are the TutorialICF.xml and WeatherADF
files:
TutorialICF.xml:
<?xml version="1.0" encoding="utf-8"?>
<NotificationServicesInstance
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.microsoft.com/MicrosoftNotificationServices/ConfigurationF
ileSchema">
<ParameterDefaults>
<Parameter>
<Name>_DBEngineInstance_</Name>
<Value>%COMPUTERNAME%</Value>
</Parameter>
<Parameter>
<Name>_ServerName_</Name>
<Value>%COMPUTERNAME%</Value>
</Parameter>
<Parameter>
<Name>_InstancePath_</Name>
<Value>%ProgramFiles%\Microsoft SQL
Server\90\Samples\Notification Services\tutorial\</Value>
</Parameter>
</ParameterDefaults>
<InstanceName>Tutorial</InstanceName>
<SqlServerSystem>%_DBEngineInstance_%</SqlServerSystem>
<Applications>
<Application>
<ApplicationName>Weather</ApplicationName>
<BaseDirectoryPath>%_InstancePath_%</BaseDirectoryPath>
<ApplicationDefinitionFilePath>%_InstancePath_%\Weather\WeatherADF.xml</Appl
icationDefinitionFilePath>
<Parameters>
<Parameter>
<Name>_NSServer_</Name>
<Value>%_ServerName_%</Value>
</Parameter>
<Parameter>
<Name>_AppPath_</Name>
<Value>%_InstancePath_%\Weather</Value>
</Parameter>
</Parameters>
</Application>
</Applications>
<DeliveryChannels>
<DeliveryChannel>
<DeliveryChannelName>FileChannel</DeliveryChannelName>
<ProtocolName>File</ProtocolName>
<Arguments>
<Argument>
<Name>FileName</Name>
<Value> %_InstancePath_%\Weather\Notifications\F
ileNotifications.htm</Value>
</Argument>
</Arguments>
</DeliveryChannel>
</DeliveryChannels>
</NotificationServicesInstance>
WeatherADF.xml
<?xml version="1.0" encoding="utf-8" ?>
<Application xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.microsoft.com/MicrosoftNotificationServices/ApplicationDef
initionFileSchema">
<!-- Version -->
<!-- Database Definition -->
<!-- Event Classes -->
<EventClasses>
<EventClass>
<EventClassName>WeatherData</EventClassName>
<Schema>
<Field>
<FieldName>City</FieldName>
<FieldType>nvarchar(35)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>Date</FieldName>
<FieldType>datetime</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>Low</FieldName>
<FieldType>float</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>High</FieldName>
<FieldType>float</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>Forecast</FieldName>
<FieldType>nvarchar(3500)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
</Schema>
<IndexSqlSchema>
<SqlStatement>
CREATE INDEX myIndex
ON WeatherData ( City );
</SqlStatement>
</IndexSqlSchema>
</EventClass>
</EventClasses>
<!-- Subscription Classes -->
<SubscriptionClasses>
<SubscriptionClass>
<SubscriptionClassName>WeatherCity</SubscriptionClassName>
<Schema>
<Field>
<FieldName>DeviceName</FieldName>
<FieldType>nvarchar(255)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>SubscriberLocale</FieldName>
<FieldType>nvarchar(10)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
<Field>
<FieldName>City</FieldName>
<FieldType>nvarchar(40)</FieldType>
<FieldTypeMods>not null</FieldTypeMods>
</Field>
</Schema>
<EventRules>
<EventRule>
<RuleName>WeatherEventRule</RuleName>
<EventClassName>WeatherData</EventClassName>
<Action>
INSERT INTO WeatherAlerts(SubscriberId,
DeviceName, SubscriberLocale, City, Date, Low, High,
Forecast)
SELECT s.SubscriberId, s.DeviceName, s.SubscriberLocale,
e.City, e.Date, e.Low, e.High, e.Forecast
FROM WeatherData e, WeatherCity s
WHERE e.City = s.City;
</Action>
</EventRule>
</EventRules>
</SubscriptionClass>
</SubscriptionClasses>
<!-- Notification Classes -->
<NotificationClasses>
<NotificationClass>
<NotificationClassName>
WeatherAlerts
</NotificationClassName>
<Schema>
<Fields>
<Field>
<FieldName>City</FieldName>
<FieldType>nvarchar(35)</FieldType>
</Field>
<Field>
<FieldName>Date</FieldName>
<FieldType>datetime</FieldType>
</Field>
<Field>
<FieldName>Low</FieldName>
<FieldType>float</FieldType>
</Field>
<Field>
<FieldName>High</FieldName>
<FieldType>float</FieldType>
</Field>
<Field>
<FieldName>Forecast</FieldName>
<FieldType>nvarchar(3500)</FieldType>
</Field>
</Fields>
</Schema>
<ContentFormatter>
<ClassName>XsltFormatter</ClassName>
<Arguments>
<Argument>
<Name>XsltBaseDirectoryPath</Name>
<Value>%_AppPath_%</Value>
</Argument>
<Argument>
<Name>XsltFileName</Name>
<Value>WeatherTransform.xslt</Value>
</Argument>
</Arguments>
</ContentFormatter>
<Protocols>
<Protocol>
<ProtocolName>File</ProtocolName>
</Protocol>
</Protocols>
</NotificationClass>
</NotificationClasses>
<!-- Event Providers -->
<Providers>
<NonHostedProvider>
<ProviderName>WeatherSPEventProvider</ProviderName>
</NonHostedProvider>
</Providers>
<!-- Generator -->
<Generator>
<SystemName>%_NSServer_%</SystemName>
</Generator>
<!-- Distributors -->
<Distributors>
<Distributor>
<SystemName>%_NSServer_%</SystemName>
<QuantumDuration>PT15S</QuantumDuration>
</Distributor>
</Distributors>
<!-- ApplicationExecutionSettings -->
<ApplicationExecutionSettings>
<QuantumDuration>PT15S</QuantumDuration>
<DistributorLogging>
<LogBeforeDeliveryAttempts>false</LogBeforeDeliveryAttempts>
<LogStatusInfo>false</LogStatusInfo>
<LogNotificationText>false</LogNotificationText>
</DistributorLogging>
<Vacuum>
<RetentionAge>P1D</RetentionAge>
<VacuumSchedule>
<Schedule>
<StartTime>23:00:00</StartTime>
<Duration>P0DT02H00M00S</Duration>
</Schedule>
</VacuumSchedule>
</Vacuum>
</ApplicationExecutionSettings>
</Application>

Monday, March 19, 2012

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