I have a problem in date format in binding gridview [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

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

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

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

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

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

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

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

Dear Sir,

how I convert it.....?

Please give me a example.

Rathnayake

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

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

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

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

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

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

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

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

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

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

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

Rathnayake

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

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

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