KDC Problem: wrong

Showing posts with label wrong. Show all posts

Wednesday, March 28, 2012

Knickers in a Loop

Now I know I'm probably doing this all wrong but bear with me: I'm
trying to break a table down into separate rows so that these can be
used in the @.query in xp_sendmail. Now I've been able to create tables
per row but can't populate the tables. The problem is that it is
asking for a variable to be declared when if I ask select @.variable it
tells me what I want to know. So please help.
Thanks
John McGinty
TSQL
--create tables
declare @.rm_tkemail varchar(30)
declare @.rm_table varchar(10)
declare @.sql varchar(4000)
declare cc cursor for
select tkemail from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_tkemail
if @.@.fetch_status <>0
break
select @.rm_table = 'jpm_' + @.rm_tkemail
set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
'(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
(15),
total_time money, total_cost money, total_bill decimal (9), lowlimit
decimal (9), medlimit decimal (9), highlimit decimal(9)) '
exec (@.sql)
end
close cc
deallocate cc
go
--this works fine and creates tables called jpm_@.rm_tkemail for every
row in
--the table.
--populate table
--declare variables
declare @.rm_table varchar(10)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
--declare and open cursor
declare cc cursor for
select fee_earner, tkemail, mmatter from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
if @.@.fetch_status <>0
break
select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
select @.rm_table = 'jpm_' + @.rm_tkemail
--print @.rm_table this confirms @.rm_table has a value
insert into @.rm_table --but here its asking to declare @.rm_table!
(fee_earner, tkemail, mmatter)
values
(@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
end
close cc
deallocate cc
go
John,
You cannot substitute a table with a variable. Use dynamic SQL for that. The reason why you get a "strange"
error is that SQL Server assumes that the variable is a table variable...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John McGinty" <jpmcginty@.talk21.com> wrote in message news:87642ea9.0405130322.424e8227@.posting.google.c om...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go
|||Hi
Just wondering why you create the table? You code is assuming that tkemail
is unique (otherwise you would have duplicate table names) . Why not call
xp_sendmail in the loop or if necessary have a second cursor and call it
within the second loop?
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:87642ea9.0405130322.424e8227@.posting.google.c om...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go
|||thanks for the reply John.
What I'm trying to do is this:
Table 1: Contains a list of people and the current state of their
accounts which have all breached a set level.
I want to generate an individual email that notifies the person that
they have breached the limit on a certain account and show them the
details.
For example
Name Expenses Agreed Expeneses Email
Woody 150 100 Woody@.blah.com
Buzz 200 190 Buzz@.blah.com
Rex 60 50 rex@.blah.com
Send Email
To: Woody@.blah.com
From: DBA
Subject: Expenses Exceeded
[message] Woody, You have breached the agreed level on the following
accounts (select * from [appropriate table]
Name Expenses Agreed Expeneses
Woody 150 100
Please see the accounts manager
Now I could include all the people that have breached but the email will
contain that is not relevant and theres a good chance they would bother
with contact.
My idea was to create table that only contained data for one email so I
planned (I've simplied this but hopefully you'll get the idea)
create table [email]
(name, expenses, agreed_expeneses)
insert into [email] (name, expenses, agreed_expeneses)
values (@.name, @.expenses, @.agreed_expenses)
so that I could use xp_sendmail with the details of the table.
I am able to send emails to specific people, able to create tables with
the email but unable to insert data into the table and its a bit of a
bugger. As I said, as always, this is probably not the best way of doing
it but I don't know anything else as I'm still on that learning curve so
help, advise, critism would be appreciated.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi
This is just rough and untested as there is not enough information in your
postings:
declare @.rm_email_subject varchar(30)
declare @.rm_email_text varchar(100)
declare @.rm_email_content varchar(8000)
declare @.rm_email_signature varchar(100)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
declare @.rm_expenses varchar(10)
declare @.rm_agreed_expenses varchar(10)
set @.rm_email_text = ' You have breached the agreed level on the following
accounts
Name Expenses Agreed Expeneses
', @.rm_email_signature = '
Yours faithfully
John', @.rm_email_subject = 'Expenses Exceeded'
declare cc cursor for
select fee_earner, tkemail, mmatter, CONVERT(varchar(10),expenses),
CONVERT(varchar(10),agreed_expenses), from rm_80
where expenses > aggreed_expenses
open cc
while 1=1
begin
fetch next from cc
into @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter, @.rm_expenses,
@.rm_agreed_expenses
if @.@.fetch_status <>0
break
SELECT @.rm_email_content = @.rm_fee_earner + @.rm_email_text + @.rm_fee_earner
+ @.rm_expenses + ' ' + @.rm_agreed_expenses + @.rm_email_signature
EXEC master..xp_sendmail @.recipients =@.rm_tkemail, @.message
=@.rm_email_content ,@.subject =@.rm_email_subject
close cc
deallocate cc
go
This would work for 1 account per email, if you required more then there
would need to be a second cursor that loops through each row and appends
details to the message body.
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:upncqXQOEHA.1620@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply John.
> What I'm trying to do is this:
> Table 1: Contains a list of people and the current state of their
> accounts which have all breached a set level.
> I want to generate an individual email that notifies the person that
> they have breached the limit on a certain account and show them the
> details.
> For example
> Name Expenses Agreed Expeneses Email
> ----
> Woody 150 100 Woody@.blah.com
> Buzz 200 190 Buzz@.blah.com
> Rex 60 50 rex@.blah.com
> Send Email
> To: Woody@.blah.com
> From: DBA
> Subject: Expenses Exceeded
> [message] Woody, You have breached the agreed level on the following
> accounts (select * from [appropriate table]
> Name Expenses Agreed Expeneses
> --
> Woody 150 100
> Please see the accounts manager
> Now I could include all the people that have breached but the email will
> contain that is not relevant and theres a good chance they would bother
> with contact.
> My idea was to create table that only contained data for one email so I
> planned (I've simplied this but hopefully you'll get the idea)
> create table [email]
> (name, expenses, agreed_expeneses)
> insert into [email] (name, expenses, agreed_expeneses)
> values (@.name, @.expenses, @.agreed_expenses)
> so that I could use xp_sendmail with the details of the table.
> I am able to send emails to specific people, able to create tables with
> the email but unable to insert data into the table and its a bit of a
> bugger. As I said, as always, this is probably not the best way of doing
> it but I don't know anything else as I'm still on that learning curve so
> help, advise, critism would be appreciated.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Knickers in a Loop

Now I know I'm probably doing this all wrong but bear with me: I'm
trying to break a table down into separate rows so that these can be
used in the @.query in xp_sendmail. Now I've been able to create tables
per row but can't populate the tables. The problem is that it is
asking for a variable to be declared when if I ask select @.variable it
tells me what I want to know. So please help.
Thanks
John McGinty
TSQL
--create tables
declare @.rm_tkemail varchar(30)
declare @.rm_table varchar(10)
declare @.sql varchar(4000)
declare cc cursor for
select tkemail from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_tkemail
if @.@.fetch_status <>0
break
select @.rm_table = 'jpm_' + @.rm_tkemail
set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
'(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
(15),
total_time money, total_cost money, total_bill decimal (9), lowlimit
decimal (9), medlimit decimal (9), highlimit decimal(9)) '
exec (@.sql)
end
close cc
deallocate cc
go
--this works fine and creates tables called jpm_@.rm_tkemail for every
row in
--the table.
--populate table
--declare variables
declare @.rm_table varchar(10)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
--declare and open cursor
declare cc cursor for
select fee_earner, tkemail, mmatter from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
if @.@.fetch_status <>0
break
select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
select @.rm_table = 'jpm_' + @.rm_tkemail
--print @.rm_table this confirms @.rm_table has a value
insert into @.rm_table --but here its asking to declare @.rm_table!
(fee_earner, tkemail, mmatter)
values
(@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
end
close cc
deallocate cc
goJohn,
You cannot substitute a table with a variable. Use dynamic SQL for that. The
reason why you get a "strange"
error is that SQL Server assumes that the variable is a table variable...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John McGinty" <jpmcginty@.talk21.com> wrote in message news:87642ea9.0405130322.424e8227@.pos
ting.google.com...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go|||Hi
Just wondering why you create the table? You code is assuming that tkemail
is unique (otherwise you would have duplicate table names) . Why not call
xp_sendmail in the loop or if necessary have a second cursor and call it
within the second loop?
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:87642ea9.0405130322.424e8227@.posting.google.com...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go|||thanks for the reply John.
What I'm trying to do is this:
Table 1: Contains a list of people and the current state of their
accounts which have all breached a set level.
I want to generate an individual email that notifies the person that
they have breached the limit on a certain account and show them the
details.
For example
Name Expenses Agreed Expeneses Email
----
Woody 150 100 Woody@.blah.com
Buzz 200 190 Buzz@.blah.com
Rex 60 50 rex@.blah.com
Send Email
To: Woody@.blah.com
From: DBA
Subject: Expenses Exceeded
[message] Woody, You have breached the agreed level on the following
accounts (select * from [appropriate table]
Name Expenses Agreed Expeneses
--
Woody 150 100
Please see the accounts manager
Now I could include all the people that have breached but the email will
contain that is not relevant and theres a good chance they would bother
with contact.
My idea was to create table that only contained data for one email so I
planned (I've simplied this but hopefully you'll get the idea)
create table [email]
(name, expenses, agreed_expeneses)
insert into [email] (name, expenses, agreed_expeneses)
values (@.name, @.expenses, @.agreed_expenses)
so that I could use xp_sendmail with the details of the table.
I am able to send emails to specific people, able to create tables with
the email but unable to insert data into the table and its a bit of a
bugger. As I said, as always, this is probably not the best way of doing
it but I don't know anything else as I'm still on that learning curve so
help, advise, critism would be appreciated.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi
This is just rough and untested as there is not enough information in your
postings:
declare @.rm_email_subject varchar(30)
declare @.rm_email_text varchar(100)
declare @.rm_email_content varchar(8000)
declare @.rm_email_signature varchar(100)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
declare @.rm_expenses varchar(10)
declare @.rm_agreed_expenses varchar(10)
set @.rm_email_text = ' You have breached the agreed level on the following
accounts
Name Expenses Agreed Expeneses
--
', @.rm_email_signature = '
Yours faithfully
John', @.rm_email_subject = 'Expenses Exceeded'
declare cc cursor for
select fee_earner, tkemail, mmatter, CONVERT(varchar(10),expenses),
CONVERT(varchar(10),agreed_expenses), from rm_80
where expenses > aggreed_expenses
open cc
while 1=1
begin
fetch next from cc
into @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter, @.rm_expenses,
@.rm_agreed_expenses
if @.@.fetch_status <>0
break
SELECT @.rm_email_content = @.rm_fee_earner + @.rm_email_text + @.rm_fee_earner
+ @.rm_expenses + ' ' + @.rm_agreed_expenses + @.rm_email_signature
EXEC master..xp_sendmail @.recipients =@.rm_tkemail, @.message
=@.rm_email_content ,@.subject =@.rm_email_subject
close cc
deallocate cc
go
This would work for 1 account per email, if you required more then there
would need to be a second cursor that loops through each row and appends
details to the message body.
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:upncqXQOEHA.1620@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply John.
> What I'm trying to do is this:
> Table 1: Contains a list of people and the current state of their
> accounts which have all breached a set level.
> I want to generate an individual email that notifies the person that
> they have breached the limit on a certain account and show them the
> details.
> For example
> Name Expenses Agreed Expeneses Email
> ----
> Woody 150 100 Woody@.blah.com
> Buzz 200 190 Buzz@.blah.com
> Rex 60 50 rex@.blah.com
> Send Email
> To: Woody@.blah.com
> From: DBA
> Subject: Expenses Exceeded
> [message] Woody, You have breached the agreed level on the following
> accounts (select * from [appropriate table]
> Name Expenses Agreed Expeneses
> --
> Woody 150 100
> Please see the accounts manager
> Now I could include all the people that have breached but the email will
> contain that is not relevant and theres a good chance they would bother
> with contact.
> My idea was to create table that only contained data for one email so I
> planned (I've simplied this but hopefully you'll get the idea)
> create table [email]
> (name, expenses, agreed_expeneses)
> insert into [email] (name, expenses, agreed_expeneses)
> values (@.name, @.expenses, @.agreed_expenses)
> so that I could use xp_sendmail with the details of the table.
> I am able to send emails to specific people, able to create tables with
> the email but unable to insert data into the table and its a bit of a
> bugger. As I said, as always, this is probably not the best way of doing
> it but I don't know anything else as I'm still on that learning curve so
> help, advise, critism would be appreciated.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sql

Monday, March 19, 2012

Keyword not supported: driver

y am i having keywork not supported? is there anything wrong with my connection string ??

==========web config================

<appSettings>
<add key="db" value="icms" />
<add key="db_user" value="sqladmin" />
<add key="db_server" value="server" />
<add key="db_pwd" value="12345" />
<add key="session_timeout" value="600" />
</appSettings
==========DB.vb==============

Dim myDB As New SqlConnection
Dim myCMD As New SqlCommand
Public Sub New()
Dim db_server = AppSettings("db_server")
Dim db = AppSettings("db")
Dim db_user = AppSettings("db_user")
Dim db_pwd = AppSettings("db_pwd")
Dim DBConnection As String = "DRIVER={SQL Server};" & _
"SERVER=" & db_server & ";" & _
"DATABASE=" & db & ";" & _
"UID=" & db_user & ";" & _
"PWD=" & db_pwd & ";" & _
"OPTION=3;"
myDB.ConnectionString = DBConnection
myCMD.Connection = myDB
End Sub
=============================You're using the native SQL objects so it won't like you trying to tell it to use another driver. Plus I doubt some of the other ODBC style entries you have there are valid either.|||I changed the main differences
which i use System.Data.SQLClient, SQLConnection, SQLCommand, and SQLDataReader (instead of System.Data.Odbc, OdbcConnection, OdbcCommand, and OdbcDataReader)

i changed the entire project.. but it doesnt seems to work...

"You're using the native SQL objects so it won't like you trying to tell it to use another driver. Plus I doubt some of the other ODBC style entries you have there are valid either"

i dont quite get what u mean... can u please kindly elaborate ??
im very new to this sqlserver thingy..

thanks alot!|||The connect string you have there is for ODBC. You are not using ODBC you are using the low down and dirty SQL objects - and so you should if you're talking to MS SQL. However, when you use the SQL objects you need to use a different connection string. E.g. 'Password' instead of 'PWD'. If you look at the help for SQLConnection you'll see what it wants.|||ok i have changed it to sql connection.. now i have this strange error which i totally dont understand.. can someone help me?
thanks alot

==================Error================
An error has occurred.

Please report to us the error message
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'LIMIT'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at icms.DB.q(String myStr) in C:\icms\DB.vb:line 25

=================DB.vb=====================
Imports System.Configuration.ConfigurationSettings
Imports System.Web.HttpContext
Imports System.Data.sqlclient

Public Class DB

Dim myDB As New sqlconnection
Dim myCMD As New sqlCommand

Public Sub New()

Dim db = AppSettings("db")
Dim db_user = AppSettings("db_user")
Dim db_pwd = AppSettings("db_pwd")
Dim db_datasource = AppSettings("db_datasource")
Dim DBConnection As String = "DATA Source=" & db_datasource & ";" & _
"Initial Catalog=" & db & ";" & _
"User ID=" & db_user & ";" & _
"Password=" & db_pwd & ";"
myDB.ConnectionString = DBConnection
myCMD.Connection = myDB

End Sub

Public Function q(ByVal myStr As String) As SqlDataReader
myCMD.CommandText = myStr
Try
myDB.Open()
q = myCMD.ExecuteReader(Data.CommandBehavior.CloseConnection)
Catch ex As Exception
Err(ex.ToString)
End Try

End Function
Public Sub c(ByVal mySTR As String)

Try
myCMD.Connection.Open()
myCMD.CommandText = mySTR
myCMD.ExecuteNonQuery()
myCMD.Connection.Close()
Catch ex As Exception
Err(ex.ToString)
End Try

End Sub
Private Sub Err(ByVal strError As String)
Current.Response.Write("<h1>An error has occurred.</h1><br>" & vbCrLf)
Current.Response.Write("Please report to us the error message<br>" & vbCrLf)
Current.Response.Write(strError)
Current.Response.Flush()
Current.Response.End()
End Sub
End Class
============Web Config====================

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="db" value="icms" />
<add key="db_user" value="sqladmin" />
<add key="db_pwd" value="12345" />
<add key="db_datasource" value="server" />
<add key="session_timeout" value="600" />
</appSettings
<system.web
<compilation defaultLanguage="vb" debug="true" />
<trace enabled="true"/>
<customErrors mode="RemoteOnly" /
</system.web
</configuration|||What SQL Command are you issuing? Oh and I didn't know you can put a space between "Data Source", learn something new everyday!|||Here is the connection string constructing described:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassConnectionStringTopic.asp

Key word "Like"

Can anyone find what is wrong with this? I think I am using the keyword "Like" correctly. I am trying to enforce some rules by doing this this way. No obvious typos or syntax errors are jumping out at me.

CREATE DATABASE VetClinic

(NAME='VetClinic',

FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\VetClinic.mdf',

SIZE = 100MB,

MAXSIZE = 500MB,

FILEGROWTH = 10MB)

LOG ON

(NAME = 'VetClinicLog',

FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data\VetClinic.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB)

CREATE TABLE Customers

(

CustID int IDENTITY NOT NULL PRIMARY KEY,

FirstName varchar(15) NOT NULL,

LastName varchar(15) NOT NULL,

StreetAddress varchar(25) NOT NULL,

City varchar(20) NOT NULL,

CustState char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Phone1 char(13) NOT NULL

LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",

Phone2 char(13) NULL

LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",

Email varchar(30) NULL,

DateInSystem smalldatetime NOT NULL

DEFAULT GETDATE()

)

CREATE TABLE PetDetails

(

PetID int IDENTITY NOT NULL PRIMARY KEY,

CustID int NOT NULL

FOREIGN KEY REFERENCES Customers(CustID),

PetName varchar(20) NOT NULL,

BreedID int NOT NULL,

Gender char(1) NOT NULL

LIKE "m" OR "f",

Weight decimal NOT NULL,

DOB smalldatetime NULL,

DateFixed smalldatetime NULL,

DateLastSeen smalldatetime NULL,

VetLastSeen int NULL

)

CREATE TABLE Employees

(

EmpID int IDENTITY NOT NULL PRIMARY KEY,

PositionID int NOT NULL

FOREIGN KEY REFERENCES Positions(PositionID),

FirstName varchar(15) NOT NULL,

LastName varchar(15) NOT NULL,

SSN char(11) NOT NULL

LIKE "[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]",

StreetAddress varchar(25) NOT NULL,

City varchar(20) NOT NULL,

CustState char(2) NOT NULL,

Zip varchar(10) NOT NULL,

Phone1 char(13) NOT NULL

LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",

Phone2 char(13) NULL

LIKE "([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]",

Email varchar(30) NULL,

DateInSystem smalldatetime NOT NULL

DEFAULT GETDATE(),

HireDate smalldatetime NOT NULL,

TerminationDate smalldatetime NOT NULL,

PaySatus char(6) NOT NULL

LIKE "hourly" OR "salary",

PayAmount money NOT NULL

)

CREATE TABLE Appointment

(

ApptID int IDENTITY NOT NULL PRIMARY KEY,

EmpID int NOT NULL,

ApptDate smalldatetime NOT NULL,

ApptTime smalldatetime NOT NULL,

PetID int NOT NULL,

CustID int NOT NULL,

)

CREATE TABLE PetProcedures

(

ProcedureID int IDENTITY NOT NULL PRIMARY KEY,

ProcedureDescrip varchar(20) NOT NULL,

Cost money NOT NULL

)

CREATE TABLE Breed

(

BreedID int IDENTITY NOT NULL PRIMARY KEY,

BreedName varchar(20) NOT NULL

)

CREATE TABLE Prescriptions

(

PrescriptionID int IDENTITY NOT NULL PRIMARY KEY,

PetID int NOT NULL,

MedID int NOT NULL,

EmpID int NOT NULL,

DatePrescribed smalldatetime NOT NULL

)

CREATE TABLE Medications

(

MedID int IDENTITY NOT NULL PRIMARY KEY,

MedName varchar(30) NOT NULL,

MedType varchar(20) NOT NULL,

Dosage varchar(20) NULL,

Frequency varchar(10) NOT NULL

LIKE "daily", "weekly", "monthly" OR "annually"

)

CREATE TABLE Positions

(

PositionID int IDENTITY NOT NULL PRIMARY KEY,

PositionName varchar(20) NOT NULL

LIKE "vet", "tech", "front desk" OR "office manager"

)

- --

Here are the errors that I got

Msg 156, Level 15, State 1, Line 12