Help In Coding

Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim Value As String = EventCmb.SelectedItem.ToString()
objConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Documents and Settings\HP\My Documents\Visual Studio 2005\WebSites\FYP2\App_Data\Event.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True"
Try
objConn.Open()
objCmd.Connection = objConn
objCmd.CommandType = CommandType.Text
objCmd.CommandText = "SELECT EventTel FROM Event WHERE (EventID = @.Value)" ' See how i changed Value to @.Value. This is called a Named Parameter
objCmd.Parameters.AddWithValue("@.Value", Value) ' Add the @.value withthe actual value that should be put. This makes it securer
Dim RetVal As Object = objCmd.ExecuteScalar() ' This returns the First Column of the first row regardless of how much data is returned.
If Not ((RetVal Is Nothing) Or (RetVal Is DBNull.Value)) Then
ContactLbl.Text = RetVal.ToString()
Else
' noting was returned
End If
Catch ex As Exception
Throw ex
Finally
objConn.Close()
End Try

There's an error for in line "Dim RetVal As Object = objCmd.ExecuteScalar() "

Error Message is as follow"Conversion failed when converting the nvarchar value 'LTA' to data type int."

It is due to "ExecuteScalar() "can only store int? I just need to display one data, andValue data comes form a combo box "EventCmb", which i wanted to find the selected String, compared to the "Event" database to get the "EventTel" data How do i solved this problem, any advice? Thanks!

In your code

Dim Value As String = EventCmb.SelectedItem.ToString()

you defined Value as a string, but in our sql

"SELECT EventTel FROM Event WHERE (EventID = @.Value)"

EventID look like a int, this is where the convering error come from

Hope this help

sql

help for query

Hello,
I have a table "log" with the following fields:

-log_id(int)
-cust_id(string)
-type(int)
-service_id(int)

type may be 0,1 or 10

I need to fetch one row for each type for each service_id for a specific
cust_id.
That is, if I have 3 different service_id's and I assume that at least
one row for each type exists with the specific cust_id, I should get 3*3
= 9 rows: no more, no less.
I prefer not to use a stored procedure.
How may I do?
Is a self-join the right way?

Thank you
Regards

--
elimina carraro per rispondere via emailYou don't appear to have any criteria for
the one row you want if there are more than one.
If that's the case and log_id is unique, this should work

select max(log_id),cust_id,type,service_id
from log
group by cust_id,type,service_id|||markc600@.hotmail.com ha scritto:
> You don't appear to have any criteria for
> the one row you want if there are more than one.
> If that's the case and log_id is unique, this should work
> select max(log_id),cust_id,type,service_id
> from log
> group by cust_id,type,service_id

It work great, thanks!

I got the same result with the following, but was much more complicated:

select *
from log a, log b
where a.cust_id='myid' and a.type IN (0,10,1) and a.log_id=b.log_id
and b.log_id IN (select top 1 log_id from log c where
c.service_id=a.service_id and c.type=a.type)
order by a.service_id

--
elimina carraro per rispondere via email

Help Converting Character string to smalldatetime data type

I am attempting to pass a string variable from a form to an SQL statement an
d
I get this error "Syntax error converting character string to smalldatetime
data type"
Your assistance is appreciated in advance....
Here is my code
<%
Dim TESTTHIS__MMColParam1
TESTTHIS__MMColParam1 = "0"
If (Request.Form("StartDate") <> "") Then
TESTTHIS__MMColParam1 = Request.Form("StartDate")
End If
%>
<%
Dim TESTTHIS__MMColParam2
TESTTHIS__MMColParam2 = "0"
If (Request.Form("EndDate") <> "") Then
TESTTHIS__MMColParam2 = Request.Form("EndDate")
End If
%>
<%
Dim TESTTHIS
Dim TESTTHIS_numRows
Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
(SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
(SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") + "')
AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
GROUP BY Team, Site ORDER BY Team, Site) DERIVEDTBL"
TESTTHIS.CursorType = 0
TESTTHIS.CursorLocation = 2
TESTTHIS.LockType = 1
TESTTHIS.Open()
TESTTHIS_numRows = 0
%>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1Run a Profiler trace to see the actual select statement being sent to SQL
Sever. Also, consider using parameters instead of concatenating values.
Parameters are more secure and you don't have to double-up embedded quotes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6168f7c85cf47@.uwe...
>I am attempting to pass a string variable from a form to an SQL statement
>and
> I get this error "Syntax error converting character string to
> smalldatetime
> data type"
> Your assistance is appreciated in advance....
> Here is my code
> <%
> Dim TESTTHIS__MMColParam1
> TESTTHIS__MMColParam1 = "0"
> If (Request.Form("StartDate") <> "") Then
> TESTTHIS__MMColParam1 = Request.Form("StartDate")
> End If
> %>
> <%
> Dim TESTTHIS__MMColParam2
> TESTTHIS__MMColParam2 = "0"
> If (Request.Form("EndDate") <> "") Then
> TESTTHIS__MMColParam2 = Request.Form("EndDate")
> End If
> %>
> <%
> Dim TESTTHIS
> Dim TESTTHIS_numRows
> Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
> TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
> TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
> (SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
> (SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
> SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
> SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
> 'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") +
> "')
> AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
> GROUP BY Team, Site ORDER BY Team, Site)
> DERIVEDTBL"
> TESTTHIS.CursorType = 0
> TESTTHIS.CursorLocation = 2
> TESTTHIS.LockType = 1
> TESTTHIS.Open()
> TESTTHIS_numRows = 0
> %>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Also keep in mind that if something is a valid date it still might not
be able to be converted into smalldatetime (for example dates before
19000101 and after 20790606)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Chamark via webservertalk.com wrote:
> I am attempting to pass a string variable from a form to an SQL statement
and
> I get this error "Syntax error converting character string to smalldatetim
e
> data type"
> Your assistance is appreciated in advance....
> Here is my code
> <%
> Dim TESTTHIS__MMColParam1
> TESTTHIS__MMColParam1 = "0"
> If (Request.Form("StartDate") <> "") Then
> TESTTHIS__MMColParam1 = Request.Form("StartDate")
> End If
> %>
> <%
> Dim TESTTHIS__MMColParam2
> TESTTHIS__MMColParam2 = "0"
> If (Request.Form("EndDate") <> "") Then
> TESTTHIS__MMColParam2 = Request.Form("EndDate")
> End If
> %>
> <%
> Dim TESTTHIS
> Dim TESTTHIS_numRows
> Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
> TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
> TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
> (SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
> (SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
> SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
> SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
> 'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") + "')
> AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
> GROUP BY Team, Site ORDER BY Team, Site) DERIVEDTB
L"
> TESTTHIS.CursorType = 0
> TESTTHIS.CursorLocation = 2
> TESTTHIS.LockType = 1
> TESTTHIS.Open()
> TESTTHIS_numRows = 0
> %>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||I am able to see that it is getting to the server because I get a return of
the column headers but I am not getting any data. When I hard code the dates
in the query works fine. I am sending for example 8/1/2005 from the form. If
I take out the two / and send 812005 I get an overflow error?
Dan Guzman wrote:
>Run a Profiler trace to see the actual select statement being sent to SQL
>Sever. Also, consider using parameters instead of concatenating values.
>Parameters are more secure and you don't have to double-up embedded quotes.
>
>[quoted text clipped - 43 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||On Wed, 07 Jun 2006 20:12:46 GMT, "Chamark via webservertalk.com"
<u21870@.uwe> wrote:

>I am able to see that it is getting to the server because I get a return of
>the column headers but I am not getting any data. When I hard code the date
s
>in the query works fine. I am sending for example 8/1/2005 from the form. I
f
>I take out the two / and send 812005 I get an overflow error?
You will get an error, as '812005' does not convert to datetime:
select convert(datetime,'812005')
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
The most reliable format for a date string is YYYYMMDD:
select convert(datetime,'20050801')
--
2005-08-01 00:00:00.000
Roy Harvey
Beacon Falls, CTsql

help connecting via vbscript to sql 2005 express edition

Hi guys,
I need to connect to a newly created sql 2005 express edition database.
I tried to recycle the connection string used with sql 2000 server but
it doesn't work anymore:
dbConn.Connectionstring="DRIVER=SQL
Server;Trusted_Connection=Yes;DATABASE=" & sSQLDB & ";SERVER=" &
sSQLServer
database, ssqldb and ssqlserver are variables that correspond to
database names, table names, ecc.
IP and NamedPipes are enabled, so are remote connections (I'm actually
trying this from the same computer).
Any help ?Try specifying:
DRIVER={SQL Server};
Notice the parenthesis.
http://www.connectionstrings.com/
http://www.carlprothman.net/Default.aspx?tabid=81
<zerbie45@.gmail.com> wrote in message
news:1140536817.286087.242470@.g47g2000cwa.googlegroups.com...
> Hi guys,
> I need to connect to a newly created sql 2005 express edition database.
> I tried to recycle the connection string used with sql 2000 server but
> it doesn't work anymore:
> dbConn.Connectionstring="DRIVER=SQL
> Server;Trusted_Connection=Yes;DATABASE=" & sSQLDB & ";SERVER=" &
> sSQLServer
> database, ssqldb and ssqlserver are variables that correspond to
> database names, table names, ecc.
> IP and NamedPipes are enabled, so are remote connections (I'm actually
> trying this from the same computer).
> Any help ?
>|||Where are you trying to connect from? Are you sure the app code is running
as a windows authenticated user that has been granted access to the
database?
Also, I would use (more out of habit than anything):
"Provider=SQLOLEDB.1; Data Source=" & sSQLServer & "; Initial Catalog=" &
sSQLDB & "; Trusted_Connection=Yes;"
And I would try using SQL Authentication first, because you would have had
to do a lot more on purpose in order to get a trusted connection working.
<zerbie45@.gmail.com> wrote in message
news:1140536817.286087.242470@.g47g2000cwa.googlegroups.com...
> Hi guys,
> I need to connect to a newly created sql 2005 express edition database.
> I tried to recycle the connection string used with sql 2000 server but
> it doesn't work anymore:
> dbConn.Connectionstring="DRIVER=SQL
> Server;Trusted_Connection=Yes;DATABASE=" & sSQLDB & ";SERVER=" &
> sSQLServer
> database, ssqldb and ssqlserver are variables that correspond to
> database names, table names, ecc.
> IP and NamedPipes are enabled, so are remote connections (I'm actually
> trying this from the same computer).
> Any help ?
>|||Thanks for your answers. I'll give it a try tomorrow at work.
I'm actually trying to connect from the same machine where sql server
2005 express ed is installed. Sql server is using windows auth (I tried
configuring it to use both windows auth and sql auth). The user is
domain administrator, so it should not be a permission issue.
Thanks again for your attention.

Help adding tables to remote sql datasource

I'm using net2.0 and SQL server express locally and have succeseffully placed my connection string in my sebconfig file and made connection and grids and detail etc, etc. Now I want to publish what I've done. So I got a free account at maximumasp beta 2.0. They give some room on a sql 2000 server and a empty database. Now I want to add the Northwind table to that DB but I don't have Enterprise manager and have no idea how to add the tables.
Can someone please help?
Thanks!
RichNorthwind is a 13 tables database it is not a table, use the link below to download the eval version of SQL Server good for 120 days and the developer edition an Enterprise edition for five users is $30 or less on the web. Hope this helps.
http://www.microsoft.com/sql/evaluation/trial/default.mspx|||

Caddre wrote:

Northwind is a 13 tables database it is not atable, use the link below to download the eval version of SQL Servergood for 120 days and the developer edition an Enterprise editionfor five users is $30 or less on the web. Hope this helps.
http://www.microsoft.com/sql/evaluation/trial/default.mspx

Long term, the licensing for the Developer Edition does not allow youto use the Client Tools (Enterprise Managere, Query Analyzer) to managea production database. So be careful.
Also, you might try asking MaximumASP to set up the Northwind database for you.
|||

tmorton wrote:

Long term, the licensing for the Developer Edition does not allow you to use the Client Tools (Enterprise Managere, Query Analyzer) to manage a production database. So be careful.

The link below is one of the oldest Software resellers on the net the developer edition is $37.00 because it is $49 list price. The developer edition is what I use because it is light but it is Enterprise edition for five users every wizard to use to transfer data comes with it. I was one of the people who told the 2003 Microsoft Technical Advisory counsul about developers needs and the price was reduced to $49.00 I have credentials to protect I will never tell people to break the law and Microsoft provides the trial version to Osborne press a division of well managed McGraw-Hill, I have five of their books all have Enterprise edition in it.
http://www.provantage.com/buy-22053391-microsoft-backoffice-sql-server-2000-developer-edition-shopping.htmsql

HELP : empty string to Null conversion by SQL

Hi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHNHi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHN

Dunno how you're going to do it without naming column names.

UPDATE MyTable
SET Col1 = Null, Col2 = Null, Col3 = Null
WHERE [insert where clause here]

Note that this will set Col1, Col2 and Col3 to Null; not just where the are equal to an empty string, but where there are values as well.

You may consider the CASE statement to selectively change blank values to NULL.

Regards,

hmscott|||As an alternate consideration, you might try:

SELECT
'UPDATE [' + TABLE_NAME + '] SET [' + COLUMN_NAME + '] = NULL
WHERE [' + COLUMN_NAME + '] = '''
FROM INFORMATION_SCHEMA.columns
WHERE DATA_TYPE IN ('char','nchar','varchar','nvarchar')

This will give you a bunch of individualized UPDATE statements. You would then have to run each statement seperately.

Regards,

hmscott|||Ok, but please use:
set [YourColumn] = NULLIF([YourColumn], '')
...or you will wipe out all your data.