question on sql : The Official Microsoft ASP.NET Forums

Reply

question on sql

Sep 23, 2009 03:31 AM|LINK

I have a few sql codes which I dont quite understand because its written in vb.

set getbal = server.createobject("adodb.recordset")
	sqlquery="select bal, name from members_acct where mobileno='"&session("mobile")&"'"
	getbal.open sqlquery, conn, 3, 3,0
	if not getbal.eof then
		bal=getbal(0)

I understand each line until "bal=getbal(0)". In the sqlquery, where does 0 go to?

And this 2nd sql code.

SQLStatement = "Select topupamt from members_topuplog where convert(varchar,date,105) =convert(varchar,getdate(),105) and username='"& session("mobile")&"' and status=1"

I dont understand what "convert(varchar,date,105) =convert(varchar,getdate(),105)" does. Can anyone explain to me what the code lines mean?

Thanks!

Jessie

* C# Please :)

Moved by mbanavige on Sep 23, 2009 04:20 AM
Moved by SGWellens on Sep 23, 2009 04:21 AM

Reply

Re: question on sql

Sep 23, 2009 04:34 AM|LINK

Hello,

convert(varchar,date,105) means

select date from database with specified format as

select convert(varchar,columnname,105)as dt from Tablename output format is 23-12-2008 rather than 12/23/2008

and convert(varchar,getdate(),105)

means select system date with specified format it picks 23-09-2009 rather than 09/23/2009

you can use more formats as

<div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">0 or 100 mon dd yyyy hh:miAM (or PM) </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">101 mm/dd/yy </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">102 yy.mm.dd </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">103 dd/mm/yy </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">104 dd.mm.yy </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">105 dd-mm-yy </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">106 dd mon yy </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">107 Mon dd, yy </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">108 hh:mm:ss </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM) </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">110 mm-dd-yy </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">111 yy/mm/dd </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">112 yymmdd </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">13 or 113 dd mon yyyy hh:mm:ss:mmm(24h) </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">114 hh:mi:ss:mmm(24h) </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">20 or 120 yyyy-mm-dd hh:mi:ss(24h) </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h) </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces) </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">130 dd mon yyyy hh:mi:ss:mmmAM </div> <div style="position: absolute; left: -10000px; top: 0px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste">131 dd/mm/yy hh:mi:ss:mmmAM </div> <div></div>

0 or 100 mon dd yyyy hh:miAM (or PM)

101 mm/dd/yy

102 yy.mm.dd

103 dd/mm/yy

104 dd.mm.yy

105 dd-mm-yy

106 dd mon yy

107 Mon dd, yy

108 hh:mm:ss

9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)

110 mm-dd-yy

111 yy/mm/dd

112 yymmdd

13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)

114 hh:mi:ss:mmm(24h)

20 or 120 yyyy-mm-dd hh:mi:ss(24h)

21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)

126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)

130 dd mon yyyy hh:mi:ss:mmmAM

131 dd/mm/yy hh:mi:ss:mmmAM

<div>
</div><div> so it means where database date matches from current date as 23-09-2009=23-09-2009.</div><div>Thanks</div>

Rajneesh Verma's Blog
www.rajneeshverma.com
Keep Forums Clean || Use Alert Moderators.

Edited by Rajneesh Verma on Sep 23, 2009 04:36 AM
Marked as answer by babymonsta on Sep 23, 2009 07:21 AM

Reply

om_prakashp

Contributor

2586 Points

379 Posts

Re: question on sql

Sep 23, 2009 04:37 AM|LINK

In the first code, bal=getbal(0) gets the value of "bal" column of first record. There is no loop

convert(varchar,date,105)
Converts date to dd-mm-yy (italian) format

Example:

SELECT convert(varchar,getDate(),105)
Gives output as 23-09-2009

Om Prakash

Edited by om_prakashp on Sep 23, 2009 04:40 AM
Marked as answer by babymonsta on Sep 23, 2009 07:21 AM

Reply

karthicks

All-Star

32172 Points

5534 Posts

Re: question on sql

Sep 23, 2009 04:37 AM|LINK

hi,

they are reading from a ado recordset , so getbal(0) will gives the first row of the recordset from that you can read all the column values

in sql convert will give you the current date with specific format (105 format code)

Refer : http://msdn.microsoft.com/en-us/library/ms187928.aspx

http://programming.top54u.com/post/SQL-Server-2005-Convert-Date-Format.aspx

Thanks,
Karthick S

Marked as answer by babymonsta on Sep 23, 2009 07:21 AM

Reply

lax4u

Participant

1592 Points

902 Posts

Re: question on sql

Sep 23, 2009 04:37 AM|LINK

In the first query the "0" is the index of the column return. The select statement is selecting two columns bal and name when you do bal = getbal(0) its selecting first column which is bal in second query its converting date column and todays date in certain format and them comaparing it

Marked as answer by babymonsta on Sep 23, 2009 07:21 AM

Reply

babymonsta

Member

250 Points

649 Posts

Re: question on sql

Sep 23, 2009 05:23 AM|LINK

OOOHHHHHH, now that seems so simple after explaining the bal(0) and bal(1). I still dont really get the dates though... So meaning, its just trying to convert the date to look like 11-11-2009?

And I missed out a few lines in the previous code. Here's the full one.

SQLStatement = "Select topupamt from members_topuplog where convert(varchar,date,105) =convert(varchar,getdate(),105) and username='"& session("mobile")&"' and status=1"
RS.Open SQLStatement, conn, 3, 3
  

		%>
		



		<%
	

while not RS.EOF


		topupTotal=topupTotal + CDbl(RS.Fields("topupamt").Value)




RS.MoveNext
wend
RS.Close

The part which I do not understand (besides the date which I still dont really get) is what is RS.Fields? Im trying to write this in C#, I understand CDbl means convert to double.

Jessie

* C# Please :)

Edited by babymonsta on Sep 23, 2009 05:27 AM

Reply

kpyap

Contributor

5212 Points

989 Posts

Re: question on sql

Sep 23, 2009 05:54 AM|LINK

Hi,

convert(varchar,getdate(),105) should give you output like 23-09-2009 (dd-MM-yyyy)

RS.Fields is reading fields from recordset (RS). If you are converting from recordset to datareader, it should be
datareader["topupamount"]

Article about DataReader and ADO Recordset:
http://msdn.microsoft.com/en-us/library/ms810288.aspx

Cast and Convert (T-SQL) reference:
http://msdn.microsoft.com/en-us/library/ms187928.aspx

Hope it help

Marked as answer by babymonsta on Sep 23, 2009 07:21 AM

Reply

babymonsta

Member

250 Points

649 Posts

Re: question on sql

Sep 23, 2009 06:11 AM|LINK

Ahh, I get the date part now. And I wrote this code in C#, please help me see if I wrote it correctly.

private String gettopuptotal(String username)
    {
        String totalbal = "";


        using (SqlConnection conn = new SqlConnection(GetConnectionString()))
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM members_topuplog WHERE mobileno = @mobileno AND status = @status", conn);
            cmd.Parameters.AddWithValue("@mobileno", username);
            cmd.Parameters.AddWithValue("@status", 1);
            conn.Open();
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    totalbal += rdr["topupamt"].ToString();
                }
            }
        }


        return totalbal;
    }

The code is not finished because I dont know how to write "convert(varchar,date,105) =convert(varchar,getdate(),105)" in the sqlcmd. I know to get the current date, its DateTime.Now.ToString() but Im not sure how to write the entire line "convert(varchar,date,105) =convert(varchar,getdate(),105)" in my sqlcmd.

Can anyone help me out?

Jessie

* C# Please :)

Reply

om_prakashp

Contributor

2586 Points

379 Posts

Re: question on sql

Sep 23, 2009 06:11 AM|LINK

RS.Fields("topupamt") will give you the field value from your recordset.

The while loop updates the topupTotal variable and sums RS.Fields("topupamt")

You can rewrite the SQL query as:

SQLStatement = "Select sum(topupamt) from members_topuplog where convert(varchar,date,105) =convert(varchar,getdate(),105) and username='"& session("mobile")&"' and status=1"

Check the following example:

Int32 sumtopupamt = 0;
    //set connection string variable connString
    string sql = "Select sum(topupamt) from members_topuplog where convert(varchar,date,105) =convert(varchar,getdate(),105) and username=@username and status=1"
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@username", SqlDbType.VarChar);
        cmd.Parameters["@username"].Value = session("mobile"); //this can be relpaced with value you require
        try
        {
            conn.Open();
            sumtopupamt = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    // (int)sumtopupamt; - will give you the sum

Om Prakash

Reply

om_prakashp

Contributor

2586 Points

379 Posts

Re: question on sql

Sep 23, 2009 06:15 AM|LINK

Please check the following examples:

//Simple Query

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;


class MainClass
{
  [STAThread]
  static void Main(string[] args)
  {
    string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
    using ( SqlConnection conn = new SqlConnection( cstr ) )
    {
      conn.Open();


      SqlCommand cmd = new SqlCommand( "select * from Employee", conn );
      SqlDataReader rdr = cmd.ExecuteReader();


      while ( rdr.Read() )
      {
        System.Console.WriteLine( "{0}", rdr.GetString( 1 ) );
      }
      rdr.Close();
    }
  }
}

//Execute Scalar Example

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

class Program {
    static void Main(string[] args) {
        SqlConnection thisConnection = new
        SqlConnection(@"Server=(local)\sqlexpress;Integrated Security=True;" +
                  "Database=northwind");
        thisConnection.Open();
        SqlCommand thisCommand = thisConnection.CreateCommand();
        thisCommand.CommandText = "SELECT COUNT(*) FROM Customers";
        Object countResult = thisCommand.ExecuteScalar();
        Console.WriteLine("Count of Customers = {0}", countResult);

        thisConnection.Close();
    }
}

Om Prakash

question on sql RSS

25 replies

babymonsta

question on sql

Rajneesh Ver...

Re: question on sql

om_prakashp

Re: question on sql

karthicks

Re: question on sql

lax4u

Re: question on sql

babymonsta

Re: question on sql

kpyap

Re: question on sql

babymonsta

Re: question on sql

om_prakashp

Re: question on sql

om_prakashp

Re: question on sql

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft