Showing posts with label own. Show all posts
Showing posts with label own. Show all posts

Friday, March 9, 2012

How to use IF..THEN to select SQL Server/Database?

I have a single form that the users can do lookups for items. We have two locations each with its own SQL Server and database. Trying to use an IF...THEN statement as they select the DB they want to query it then creates the connection string for that particular database. I haven't a clue on how to do this. If someone could point me to the documentation to do this I would really appreciate it.

<%@. Page Language="VB" %><%@. ImportNamespace="System.Data" %><%@. ImportNamespace="System.Data.OleDB" %><%@. ImportNamespace="System.Data.SqlClient" %><!DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">Sub get_Dies(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Dim dbConnectionAs New SqlConnectionIf ddlDatabase.SelectedValue ="db2"Then ? dbConnection ="server=server2;Database=db2;UID=user;PWD=pass" dbConnection.Open()Else ? dbConnection ="server=server1;Database=db1;UID=user;PWD=pass" dbConnection.Open()End If Dim sqlStringAs String =" sql statement"Dim dbCommandAs New SqlCommand(sqlString, dbConnection)Dim dbDataReaderAs SqlDataReader dbDataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection) gvDies.DataSource = dbDataReader gvDies.DataBind() dbConnection.Close()End Sub </script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Die Usage Lookup</title><link rel="stylesheet" href="/intranet.css" type="text/css" /></head><body> <form id="form1" runat="server"> <div> Find dies matching: <asp:TextBox ID="tbDies" runat="server" /><br />Select database: <asp:DropDownList id="ddlDatabase" runat="server" AutoPostBack="true" OnSelectedIndexChanged="get_Dies"> <asp:ListItem Text="" Selected="true" Value="" /> <asp:ListItem Text="DB1" Value="db1" /> <asp:ListItem Text="DB2" Value="db2" /> </asp:DropDownList> <asp:gridview ID="gvDies" runat="server" GridLines="none" > </asp:gridview> </div> </form></body></html>

in your if statement you can assign the connectionstring to the connection object such as:

Dim connectionStringAsString

If ddlDatabase.SelectedValue ="db2"Then
connectionString ="server=server2;Database=db2;UID=user;PWD=pass"
Else
connectionString ="server=server1;Database=db1;UID=user;PWD=pass"
End If

Dim dbConnectionAs New SqlConnection(connectionString)

dbConnection.Open()


|||

That is brilliant!! Did exactly what I was looking for. Thank you very much.

How to use EXEC

Hi,
How can I run EXEC conditionally?
The EXEC syntax woks on its own but not when it's in a query.
TIA
Mike
select
'name','surname', case when datediff (dd, getdate(), getdate()+2) between 0
and 2
then EXEC master..xp_sendmail 'mike', 'The master database is full.' else ''
end as testYou need an IF statement. Also, your SELECT needs a FROM clause.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike_T" <nospam@.yahoo.com> wrote in message
news:uXYK4ZMZGHA.4060@.TK2MSFTNGP02.phx.gbl...
Hi,
How can I run EXEC conditionally?
The EXEC syntax woks on its own but not when it's in a query.
TIA
Mike
select
'name','surname', case when datediff (dd, getdate(), getdate()+2) between 0
and 2
then EXEC master..xp_sendmail 'mike', 'The master database is full.' else ''
end as test|||Try
If exists(select
'name','surname', case when datediff (dd, getdate(), getdate()+2)
between 0
and 2 from table)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
else
EXEC master..xp_sendmail 'mike', ''
Madhivanan|||Hi Madhivanan,
I tried the following using pubs
--
If exists(select
'au_fname','au_lname', case when datediff (dd, getdate(), getdate()+2)
between 0 and 2 from authors)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
else
EXEC master..xp_sendmail 'mike', ''
--
but I get this error:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'else'.
I understood the idea but not the error
Thank you.
"Madhivanan" <madhivanan2001@.gmail.com> escribi en el mensaje
news:1145615715.694251.14190@.t31g2000cwb.googlegroups.com...
> Try
> If exists(select
> 'name','surname', case when datediff (dd, getdate(), getdate()+2)
> between 0
> and 2 from table)
> EXEC master..xp_sendmail 'mike', 'The master database is full.'
> else
> EXEC master..xp_sendmail 'mike', ''
> Madhivanan
>|||On Fri, 21 Apr 2006 22:35:46 +0200, Mike_T wrote:

>Hi Madhivanan,
>I tried the following using pubs
>--
>If exists(select
> 'au_fname','au_lname', case when datediff (dd, getdate(), getdate()+2)
>between 0 and 2 from authors)
>EXEC master..xp_sendmail 'mike', 'The master database is full.'
>else
>EXEC master..xp_sendmail 'mike', ''
>--
>but I get this error:
>Server: Msg 156, Level 15, State 1, Line 3
>Incorrect syntax near the keyword 'from'.
>Server: Msg 156, Level 15, State 1, Line 5
>Incorrect syntax near the keyword 'else'.
>I understood the idea but not the error
Hi Mike,
The error is caused because the CASE expression is not closed. You need
to add at least one THEN clause and an END.
Also, a SELECT list is irrelevant in an EXISTS subquery; the database
will only look for matching rows and disregard the column list yoou
specified. So you could rewrite your code as
IF EXISTS (SELECT * FROM authors)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
ELSE
EXEC master..xp_sendmail 'mike', ''
However, I doubt that this does what you want. Maybe you should explain
what you try to accomplish instead of posting just a part of your code
and have us guess...
Hugo Kornelis, SQL Server MVP|||Try
If exists(select
'name','surname' from table where datediff (dd, getdate(),
getdate()+2) between 0 and 2)
EXEC master..xp_sendmail 'mike', 'The master database is full.'
else
EXEC master..xp_sendmail 'mike', ''
Madhivanan