Error on UPDATE statement??? : The Official Microsoft ASP.NET Forums

Reply

sry2002

Member

470 Points

Error on UPDATE statement???

Oct 08, 2003 07:07 PM|LINK

Hi, After update the textbox and push into update button update function has execute But in this time error occur Data type mismatch I use some data type Data time format at Acces it is Data/time short date or short time. Can you help me???

Sub MyDataGrid_Update(Sender As object, e As DataGridCommandEventargs)


	'Dim etlntext as textbox = e.item.Cells(0).Controls(0)
	Dim etldtext as textbox = e.item.Cells(1).Controls(0)
	Dim modutext as textbox =e.item.Cells(2).Controls(0)
	Dim jobntext as textbox =e.item.Cells(3).Controls(0)
	Dim crdtext as textbox = e.item.Cells(4).Controls(0)
	Dim crttext as textbox = e.item.Cells(5).Controls(0)
	Dim sldtext as textbox = e.item.Cells(6).Controls(0)
	Dim slttext as textbox = e.item.Cells(7).Controls(0)
	Dim endatext as textbox =e.item.Cells(8).Controls(0)
	Dim restext as textbox = e.item.Cells(9).Controls(0)
	

	'dim etln as string = etlntext.text
	dim etld as string = etldtext.Text
	dim modu as string = modutext.Text
	dim jobn as string = jobntext.Text
	dim crd as string = crdtext.Text    // Date/Time
	dim crt as string = crttext.Text     // Date/Time
	dim sld as string = sldtext.Text     // Date/Time
	dim slt as string = slttext.Text      // Date/Time
	dim enda as string = endatext.Text
	dim res as string = restext.Text
	

		

	Dim MyConn as  New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=d:\DWH\dwh.mdb")
	MyConn.Open()
	

	'dim comm as new OleDbCommand("UPDATE genel SET ETL_DATE="& etld &", MODUL='"& modu &"' , JOB_NAME='"& jobn &"', CRDATE="& crd &", CRTIME="& crt &", SLDATE="& sld &", SLTIME="& slt &", END_ACTION='"& enda &"', RESULT='"& res &"' WHERE ID='"& ID &"'  ", MyConn)
	dim comm as new OleDbCommand("UPDATE genel SET ETL_DATE={"& etld &"}, MODUL='"& modu &"' , JOB_NAME='"& jobn &"', CRDATE={"& crd &"}, CRTIME={"& crt &"}, SLDATE={"& sld &"}, SLTIME={"& slt &"}, END_ACTION='"& enda &"', RESULT='"& res &"' WHERE ID={"& ID &"}  ", MyConn)
	comm.ExecuteNonQuery
	MyConn.close()
	

	DataGrid1.EditItemIndex = -1
	veriler


	datagrid1.databind()
End Sub

Reply

douglas.reil...

All-Star

23315 Points

4647 Posts

Re: Error on UPDATE statement???

Oct 08, 2003 11:09 PM|LINK

dim comm as new OleDbCommand("UPDATE genel SET ETL_DATE=#"& etld &"#, MODUL='"& modu &"' , JOB_NAME='"& jobn &"', CRDATE={"& crd &"}, CRTIME={"& crt &"}, SLDATE={"& sld &"}, SLTIME={"& slt &"}, END_ACTION='"& enda &"', RESULT='"& res &"' WHERE ID={"& ID &"}  ", MyConn)

Note the use of # to delimit the date. Now, a better solution is to use parameters.

Starting with ASP.NET 2.0? Look at:
Programming Microsoft Web Forms
My Blog

Reply

SushilaSB

Star

11417 Points

2265 Posts

Re: Error on UPDATE statement???

Oct 09, 2003 03:38 AM|LINK

Try

dim crd as DateTime= Convert.ToDatetime(crdtext.Text    )

same for other relevant values Check if it helps

Sushila Bowalekar Patel
Visual ASP/ASP.NET MVP
http://weblogs.asp.net/sushilasb

Reply

sry2002

Member

470 Points

94 Posts

Re: Error on UPDATE statement???

Oct 10, 2003 12:59 PM|LINK

Thanks for your answer But some error continue If dim crd as DateTime= Convert.ToDatetime(crdtext.Text) The error like String was not recognized as a valid DateTime dim crdd as Date =CDate(crddtext.Text) the error like System.InvalidCastException: Cast from string "" to type 'Date' is not valid. And other my question if the text area blank how can I control the date/time area blank at access field. Because if the blank the filed access fill the blank are like 30.12.1899 I need your help. For your Date Time info Thanks thanks thanks

Reply

SushilaSB

Star

11417 Points

2265 Posts

Re: Error on UPDATE statement???

Oct 10, 2003 03:05 PM|LINK

Try giving DateTime.Parse Initially check for the crdText.Text if its null/not null and then Do the Parsing i.e crd = DateTime.Parse(crdText.Text) Also Check out Enter Null Values for DateTime Column of SQL Server

Sushila Bowalekar Patel
Visual ASP/ASP.NET MVP
http://weblogs.asp.net/sushilasb

Reply

sry2002

Member

470 Points

94 Posts

Re: Error on UPDATE statement???

Oct 11, 2003 09:17 AM|LINK

I examine the suggested link I saw there are are Null value for blank field cmd.Parameters["@Date"].Value = DBNull.Value; So I use if else statement f Not crdtext.Text = String.Empty Then crd = DateTime.Parse(crdtext.Text) else crd = DBNull.value End if But in my source ->Value of type 'System.DBNull' cannot be converted to 'Date'. I try like crd = Cdate(DBNull.value) or dim crd as Date = crdtext.Text So, What Can I do? Thanks

Reply

SushilaSB

Star

11417 Points

2265 Posts

Re: Error on UPDATE statement???

Oct 11, 2003 03:41 PM|LINK

The link had code

If (txtDate.Text = "") Then
	'cmd.Parameters("@Date").Value = sqldatenull
	cmd.Parameters("@Date").Value = DBNull.Value
Else
	cmd.Parameters("@Date").Value = DateTime.Parse(txtDate.Text)
End If

did this not help

Sushila Bowalekar Patel
Visual ASP/ASP.NET MVP
http://weblogs.asp.net/sushilasb

Reply

sry2002

Member

470 Points

94 Posts

Re: Error on UPDATE statement???

Oct 12, 2003 12:07 PM|LINK

I need your idea, I spent much time thking about the errot But I can't see? Please help me? Please. Last time. An OleDbParameter with ParameterName '@etld' is not contained by this OleDbParameterCollection.

Sub MyDataGrid_Update(Sender As object, e As DataGridCommandEventargs)


	dim cmd as  new OleDbCommand
	

	'Dim etlntext as textbox = e.item.Cells(0).Controls(0)
	Dim etldtext as textbox = e.item.Cells(1).Controls(0)
	Dim crdtext as textbox = e.item.Cells(4).Controls(0)
	Dim crttext as textbox = e.item.Cells(5).Controls(0)
	Dim sldtext as textbox = e.item.Cells(6).Controls(0)
	Dim slttext as textbox = e.item.Cells(7).Controls(0)
		

	

	'dim etln as string = etlntext.text
		

	dim etld as string = etldtext.Text
	dim crd as string = crdtext.Text
	dim crt as string = crttext.Text
	dim sld as string = sldtext.Text
	dim slt as string = slttext.Text
	

	If (etldtext.text = "") Then
		'cmd.Parameters.Add(New OleDbParameter("@etld", DBNull.value))
		cmd.Parameters("@etld").Value = DBNull.value
	else
		'cmd.Parameters.Add(New OleDbParameter("@etld", DateTime.Parse(etldtext.text)))
		cmd.Parameters("@etld").value =  DateTime.Parse(etldtext.text)
	End if
	

	If (crdtext.Text = "") Then
		cmd.Parameters("@crd").value = DBNull.value
	else
		cmd.Parameters("@crd").value =  DateTime.Parse(crdtext.Text)
	End if
	

	If (crttext.text = "") Then
		cmd.Parameters("@crt").value = DBNull.value
	else
		cmd.Parameters("@crt").value =  DateTime.Parse(crttext.text)
	End if
	

	If (sldtext.text = "") Then
		cmd.Parameters("@sld").value = DBNull.value
	else
		cmd.Parameters("@sld").value =  DateTime.Parse(sldtext.text)
	End if
	

	If (slttext.text = "") Then
		cmd.Parameters("@slt").value = DBNull.value
	else
		cmd.Parameters("@slt").value =  DateTime.Parse(slttext.text)
	End if
		

	Dim MyConn as  New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=d:\DWH\dwh.mdb")
	MyConn.Open()
	

	dim comm as new OleDbCommand("UPDATE genel SET ETL_DATE=#"& etld &"#,   CRDATE=#"& crd &"#, CRTIME=#"& crt &"#, SLDATE=#"& sld &"#, SLTIME=#"& slt &"#,  WHERE ID='#"& ID &"#'  ", MyConn)
	comm.ExecuteNonQuery
	MyConn.close()
	

	DataGrid1.EditItemIndex = -1
	veriler


	datagrid1.databind()
End Sub

Reply

SushilaSB

Star

11417 Points

2265 Posts

Re: Error on UPDATE statement???

Oct 12, 2003 09:28 PM|LINK

Build your update statment first as the sample code has

insert into Emp (FirstName,LastName,Date) Values (@FirstName,@LastName,@Date)

build your update statement as the insert is built (having the parameters listed and the assign values to the parameters

sqlStmt = "update tablename set etld=@etld, .....where.....  "
conString = "....."
cn = New oledbConnection(conString)
cmd = New oledbCommand(sqlStmt, cn)
If (etldtext.text = "") Then
'cmd.Parameters.Add(New OleDbParameter("@etld", DBNull.value))
cmd.Parameters("@etld").Value = DBNull.value
else
'cmd.Parameters.Add(New OleDbParameter("@etld", DateTime.Parse(etldtext.text)))
cmd.Parameters("@etld").value =  DateTime.Parse(etldtext.text)
End if
cn.Open()
cmd.ExecuteNonQuery()

typed online so there can be typos... do let me know this helps/if you still find any issues solving this

Sushila Bowalekar Patel
Visual ASP/ASP.NET MVP
http://weblogs.asp.net/sushilasb

Reply

sry2002

Member

470 Points

94 Posts

Re: Error on UPDATE statement???

Oct 13, 2003 12:55 PM|LINK

Hi Sushila, I apply your suggestion as you say, But Same error has continue ->An OleDbParameter with ParameterName '@etld' is not contained by this OleDbParameterCollection. Sub MyDataGrid_Update(Sender As object, e As DataGridCommandEventargs) Dim constring as string = "Provider=Microsoft.Jet.OLEDB.4.0; DATA Source=d:\DWH\dwh.mdb" Dim cn as OleDBConnection Dim cmd as OledbCommand Dim sqlstmt as string = "UPDATE genel SET ETL_DATE=@etld, MODUL=@modu, JOB_NAME=@jobn, CRDATE=@crd, CRTIME=@crt, SLDATE=@sld, SLTIME=@slt, END_ACTION=@enda, RESULT=@res WHERE ID=@ID" cn = new OledbConnection(Constring) cmd = new OleDbCommand(sqlstmt, cn) 'Dim etlntext as textbox = e.item.Cells(0).Controls(0) Dim etldtext as textbox = e.item.Cells(1).Controls(0) Dim modutext as textbox = e.item.Cells(2).Controls(0) Dim jobntext as textbox = e.item.Cells(3).Controls(0) Dim crdtext as textbox = e.item.Cells(4).Controls(0) Dim crttext as textbox = e.item.Cells(5).Controls(0) Dim sldtext as textbox = e.item.Cells(6).Controls(0) Dim slttext as textbox = e.item.Cells(7).Controls(0) Dim endatext as textbox = e.item.Cells(8).Controls(0) Dim restext as textbox = e.item.Cells(9).Controls(0) 'dim etln as string = etlntext.text dim modu as string = modutext.Text dim enda as string = endatext.Text dim res as string = restext.Text dim jobn as string = jobntext.Text dim etld as string = etldtext.Text dim crd as string = crdtext.Text dim crt as string = crttext.Text dim sld as string = sldtext.Text dim slt as string = slttext.Text If (etldtext.text = "") Then cmd.Parameters("@etld").Value = DBNull.value ***Error For this line else cmd.Parameters("@etld").value = DateTime.Parse(etldtext.text) End if ...... ...... Cn.Open() cmd.ExecuteNonQuery() DataGrid1.EditItemIndex = -1 Cn.close datagrid1.databind() End Sub

Error on UPDATE statement??? RSS

27 replies

sry2002

Error on UPDATE statement???

douglas.reil...

Re: Error on UPDATE statement???

SushilaSB

Re: Error on UPDATE statement???

sry2002

Re: Error on UPDATE statement???

SushilaSB

Re: Error on UPDATE statement???

sry2002

Re: Error on UPDATE statement???

SushilaSB

Re: Error on UPDATE statement???

sry2002

Re: Error on UPDATE statement???

SushilaSB

Re: Error on UPDATE statement???

sry2002

Re: Error on UPDATE statement???

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

Follow Us On:

Microsoft