I have a problem in date format in binding gridview [Resolved]

Posted by Nuwan under ASP.NET on 3/27/2012 | Points: 10 | Views : 1979 | Status : [Member] | Replies : 21
Dear All,

I have a sql table field like

Name , date .

i want to show that data in gridview selecting month & year,
I using ajax calender. normaly i binding selecting date format. its ok.
But i want to show that data selecting only month & Year.

Please help that matter.

Rathnayake


Responses

Posted by: Sksamantaray on: 3/27/2012 [Member] Silver | Points: 50

Up
0
Down

Resolved

If you want to show data of input month and year then
in sql procedure pass month and year as parameter.
create procedure spGetData

@Month int,@Year int
as
begin

select col1,col2....... from table where month(datefieldname)=@Month and year(datefieldname)=@Year
end


Then Bind the result in Gridview

Thanks,
Sanjay

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nuwan on: 3/27/2012 [Member] Starter | Points: 25

Up
0
Down
Dear Sir,

Its work perfectly. But sir im using ajax calender. So i get a month and year one text box,
after select that loading data in gridview. how i pass that text box value in your sql function.

Please help me....

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/27/2012 [Member] Silver | Points: 25

Up
0
Down
If your text box has only month and year then split the values and keep it in separate variables and then pass them as parameter.

Thanks,
Sanjay

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 3/27/2012 [Member] Bronze | Points: 25

Up
0
Down
Do you have month in number or in text (like JAN, FEB...)
if in text then first convert it into number than only pass to the procedure.

because month(datefield) returns number value for month.

Thanks,
Rajni Shekhar

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nuwan on: 3/27/2012 [Member] Starter | Points: 25

Up
0
Down
Dear Sir,

I create that programme. But I have get a error.
if you can please provide a sample programme.

Thank You....

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 3/27/2012 [Member] Bronze | Points: 25

Up
0
Down
please post your error here, so that we can identify your error.... and try to resolve it.

Thanks,
Rajni Shekhar

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nuwan on: 3/27/2012 [Member] Starter | Points: 25

Up
0
Down

Dear Sir...

Exception Details: System.FormatException: String was not recognized as a valid DateTime.


Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 3/27/2012 [Member] Bronze | Points: 25

Up
0
Down
ok, means you are trying to convert your string into dateformat and that string was having only month and year....
is it so?


Thanks,
Rajni Shekhar

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nuwan on: 3/27/2012 [Member] Starter | Points: 25

Up
0
Down
Dear Sir,

how I convert it.....?

Please give me a example.

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 3/27/2012 [Member] Bronze | Points: 25

Up
0
Down
ok, post me your lines of code, i will try to resolve it.

Thanks,
Rajni Shekhar

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nuwan on: 3/27/2012 [Member] Starter | Points: 25

Up
0
Down
Dear Sir......
This is my code...





Dim datM, datM1, datY, datY1 As String


Protected Sub Show_Data(ByVal strbank As Object, ByVal strmonth As Object, ByVal stryear As Object)

Dim con As SqlConnection = New SqlConnection(GetConnectionString)
Dim cmd As SqlCommand = New SqlCommand()
cmd.CommandText = "SELECT BANKBOOK.BA_REM, BANKBOOK.BA_DATE, BANKBOOK.BA_TYPE, BANKBOOK.BA_CHEQUE,BANKBOOK.BA_REF, VOUCHER.V_NAME, BANKBOOK.BA_AMTCR,BANKBOOK.BA_REF, CH_ACCOUNTS.A_DESC, BANKBOOK.BA_BRANCH, BANKBOOK.BA_CNO,BANKBOOK.BA_ACCODE, CH_ACCOUNTS.A_HEADER6 FROM BANKBOOK INNER JOIN VOUCHER ON BANKBOOK.BA_REF = VOUCHER.V_REFNO INNER JOIN CH_ACCOUNTS ON BANKBOOK.BA_ACCODE = CH_ACCOUNTS.A_HEADER4 WHERE(BANKBOOK.BA_SOURCE = 'B') AND (BANKBOOK.BA_BCODE = @BANK) AND (MONTH(BANKBOOK.BA_DATE) = @Month) AND (YEAR(BANKBOOK.BA_DATE) =@Year) GROUP BY BANKBOOK.BA_CNO, BANKBOOK.BA_REM, BANKBOOK.BA_DATE, BANKBOOK.BA_TYPE, BANKBOOK.BA_REF, VOUCHER.V_NAME, BANKBOOK.BA_AMTCR, CH_ACCOUNTS.A_DESC, BANKBOOK.BA_BRANCH,BANKBOOK.BA_CHEQUE,BANKBOOK.BA_ACCODE,CH_ACCOUNTS.A_HEADER6 ORDER BY BANKBOOK.BA_CHEQUE"

cmd.Parameters.Add("@BANK", SqlDbType.NChar, 50).Value = strbank 'Bank Selecting query
cmd.Parameters.Add("@Month", SqlDbType.DateTime).Value = strmonth 'month
cmd.Parameters.Add("@Year", SqlDbType.DateTime).Value = stryear 'Year
cmd.CommandTimeout = 0
cmd.Connection = con

Dim reader As SqlDataReader = Nothing
Try

con.Open()

reader = cmd.ExecuteReader()

Catch ex As SqlException

lblerror.Text = ex.Message.ToString()

End Try
Try
If (reader.HasRows) Then
GridView1.Visible = True
lblerror.Visible = False
GridView1.DataSource = reader
GridView1.DataBind()
reader.Close()
con.Close()

Else
lblerror.Visible = True
lblerror.Text = "There are No Records available"
lblerror.ForeColor = Drawing.Color.Red
GridView1.Visible = False

End If
Catch ex As Exception
lblerror.Visible = True
Me.lblerror.Text = ex.ToString()
End Try



End Sub




Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click

datM = txtdate.Text
datM1 = datM.Substring(0, 2)'get a month


datY = txtdate.Text
datY1 = datY.Substring(2, 5)'get a year




Me.Show_Data(Me.cmbbankacc.Text, datM1, datY1)'call the method



End Sub



''''''''''''''''''''''
don't afraid my query, do you have any problem in my programme Please ask....

Thank You.........

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nuwan on: 3/27/2012 [Member] Starter | Points: 25

Up
0
Down
Dear Sir......

Urgent that matter , if you can please give me a simple sample.


Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 3/27/2012 [Member] Bronze | Points: 25

Up
0
Down
change SqlDbType.DateTime to varchar in your code and in procedure

SqlDbType.DateTime).Value = strmonth 'month
SqlDbType.DateTime).Value = stryear


Thanks,
Rajni Shekhar

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sabarimahesh on: 3/27/2012 [Member] Bronze | Points: 25

Up
0
Down
Rajni.Shekhar

S Correct



Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nuwan on: 3/27/2012 [Member] Starter | Points: 25

Up
0
Down
System.Data.SqlClient.SqlException: Conversion failed when converting datetime from character string. at

Now get a this error

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 3/27/2012 [Member] Bronze | Points: 25

Up
0
Down
I got your error, i am saying
change SqlDbType.DateTime to varchar in your code and in change parameter as varchar in procedure
And in procedure where you are using datefield use convert function there to convert date to month or year.

cmd.Parameters.Add("@Month", SqlDbType.DateTime).Value = strmonth 'month
cmd.Parameters.Add("@Year", SqlDbType.DateTime).Value = stryear 'Year

-----
cmd.Parameters.Add("@Month",SqlDbType.NVarchar,10).Value = strmonth
cmd.Parameters.Add("@Year",SqlDbType.NVarchar,10).Value = stryear




Thanks,
Rajni Shekhar

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nuwan on: 3/27/2012 [Member] Starter | Points: 25

Up
0
Down
Dear Sir....

I implement that code , But get a error .......

System.Data.SqlClient.SqlException: Conversion failed when converting datetime from character string

Please help me.

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 3/27/2012 [Member] Bronze | Points: 25

Up
0
Down
have you changed in your procedure parameter and in query

Thanks,
Rajni Shekhar

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nuwan on: 3/27/2012 [Member] Starter | Points: 25

Up
0
Down
Dear Sir how I change it. Please give me a example.

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nuwan on: 3/28/2012 [Member] Starter | Points: 25

Up
0
Down
Thanks Dear Sksamantaray , and Rajni.Shekhar I solve that matter. thanks a lot. its not a binding Gridview problem.

Thanks a lot.....

Rathnayake

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 3/28/2012 [Member] Bronze | Points: 25

Up
0
Down
hi Nuwan,

Your most welcome.
And if your problem solved plz mark as answer.

Thanks,
Rajni Shekhar

Nuwan, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response