4 Replies - 850 Views - Last Post: 25 August 2009 - 11:31 AM Rate Topic: -----

#1 aks29921  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 84
  • View blog
  • Posts: 230
  • Joined: 24-August 09

formatting date in update query in vs 2008 for ms access

Post icon  Posted 25 August 2009 - 03:35 AM

i have to update the values in my database
some of them are integer, some string and some date (as you can infer from the formatting)

i am using the following query:
UPDATE PatientProfile SET HospitalRegNo=" + hregno + ", PatientName='" + pname + "', PatientAge=" + page + ", PatientSex='" + psex + "', Address='" + add + "', ContactNo='" + cno + "', AdmitDate=#" + adat + "#, OperDate=#" + odat + "#, DischargeDate=#" + ddat + "# WHERE PatientNo=" + pno + ""

but its not working...
i think there's a problem in formatting the date type variables

could someone tell what i am missing?

Is This A Good Question/Topic? 0
  • +

Replies To: formatting date in update query in vs 2008 for ms access

#2 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: formatting date in update query in vs 2008 for ms access

Posted 25 August 2009 - 03:51 AM

What is the error message you get?

If it is a updatestring in vb.net, then try replacing the "+" with "&"
Was This Post Helpful? 0
  • +
  • -

#3 aks29921  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 84
  • View blog
  • Posts: 230
  • Joined: 24-August 09

Re: formatting date in update query in vs 2008 for ms access

Posted 25 August 2009 - 05:48 AM

no error message, just that values in database not getting updated.....
also tried #"& adat &"# but that also doesn't work??
Was This Post Helpful? 0
  • +
  • -

#4 motcom  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 290
  • View blog
  • Posts: 1,371
  • Joined: 16-June 08

Re: formatting date in update query in vs 2008 for ms access

Posted 25 August 2009 - 05:56 AM

Please post the code of the sub/function where you rund this update...
Was This Post Helpful? 0
  • +
  • -

#5 aks29921  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 84
  • View blog
  • Posts: 230
  • Joined: 24-August 09

Re: formatting date in update query in vs 2008 for ms access

Posted 25 August 2009 - 11:31 AM

Public Sub Patient_Update(ByVal pno As String, ByVal hregno As String, ByVal pname As String, ByVal page As String, ByVal psex As String, ByVal add As String, ByVal cno As String, ByVal adat As String, ByVal odat As String, ByVal ddat As String, ByVal ci As String, ByVal sc As String, ByVal unit As String, ByVal diag As String, ByVal cls As String, ByVal opdat As String, ByVal oppro As String, ByVal opf As String, ByVal opo As String, ByVal our As String, ByVal opc As String, ByVal opr As String, ByVal opan As String, ByVal hpsdat As String, ByVal hcrno As String, ByVal sibdat As String, ByVal sibs As String, ByVal idat As String, ByVal xr As String, ByVal usdat As String, ByVal us As String, ByVal ctdat As String, ByVal ct As String, ByVal mrdat As String, ByVal mr As String, ByVal spdat As String, ByVal sp As String, ByVal adv As String, ByVal hosc As String, ByVal fp As String, ByVal rname As String, ByVal sname As String, ByVal eb As String) 
		str = String.Format("UPDATE PatientProfile SET HospitalRegNo=" + hregno + ", PatientName='" + pname + "', PatientAge=" + page + ", PatientSex='" + psex + "', Address='" + add + "', ContactNo='" + cno + "', AdmitDate='" + adat + "', OperDate='" + odat + "', DischargeDate='" + ddat + "', ConsultantsIncharge='" + ci + "', SecondConsultant='" + sc + "', Unit=" + unit + ", Diagnosis='" + diag + "', ClinicalSummary='" + cls + "', OperativeDate='" + opdat + "', OperativeProcedure='" + oppro + "', OperativeFindings='" + opf + "', OperativeOutcome='" + opo + "', OutcomeRemarks='" + our + "', OperativeConsultant='" + opc + "', OperativeResident='" + opr + "', OperativeAnaesthetic='" + opan + "', HistoPathologySentOnDate='" + hpsdat + "', HistopathologyCytopathologyReportNo=" + hcrno + ", SerologicalImmunologicalBiologicalDate='" + sibdat + "', SerologicalImmunologicalBiologicalSummary='" + sibs + "', ImagingDate='" + idat + "', XRay='" + xr + "', USGDate='" + usdat + "', USG='" + us + "', CTDate='" + ctdat + "', CT='" + ct + "', MRIDate='" + mrdat + "', MRI='" + mr + "', SplInvestigationDate='" + spdat + "', SplInvestigation='" + sp + "', AdviceOnDischarge='" + adv + "', HospitalCourse='" + hosc + "', FuturePlan='" + fp + "', ResidentName='" + rname + "', SurgeonName='" + sname + "', EnteredBy='" + eb + "'  WHERE PatientNo=" + pno + "")
		Using cmd As New OleDbCommand(str, con)

			Dim param(43) As Data.OleDb.OleDbParameter

			Try
				param(0) = New OleDb.OleDbParameter
				param(0).ParameterName = "@PatientNo"
				param(0).Value = pno
				param(0).OleDbType = OleDbType.Integer
				cmd.Parameters.Add(param(0))

				param(1) = New OleDb.OleDbParameter
				param(1).ParameterName = "@HospitalRegNo"
				param(1).Value = hregno
				param(1).OleDbType = OleDbType.Integer
				cmd.Parameters.Add(param(1))

				param(2) = New OleDb.OleDbParameter
				param(2).ParameterName = "@PatientName"
				param(2).Value = pname
				param(2).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(2))

				param(3) = New OleDb.OleDbParameter
				param(3).ParameterName = "@PatientAge"
				param(3).Value = page
				param(3).OleDbType = OleDbType.Integer
				cmd.Parameters.Add(param(3))

				param(4) = New OleDb.OleDbParameter
				param(4).ParameterName = "PatientSex"
				param(4).Value = psex
				param(4).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(4))

				param(5) = New OleDb.OleDbParameter
				param(5).ParameterName = "@Address"
				param(5).Value = add
				param(5).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(5))

				param(6) = New OleDb.OleDbParameter
				param(6).ParameterName = "@ContactNo"
				param(6).Value = cno
				param(6).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(6))

				param(7) = New OleDb.OleDbParameter
				param(7).ParameterName = "@AdmitDate"
				param(7).Value = adat
				param(7).OleDbType = OleDbType.Date
				cmd.Parameters.Add(param(7))

				param(8) = New OleDb.OleDbParameter
				param(8).ParameterName = "@OperDate"
				param(8).Value = odat
				param(8).OleDbType = OleDbType.Date
				cmd.Parameters.Add(param(8))

				param(9) = New OleDb.OleDbParameter
				param(9).ParameterName = "@DischargeDate"
				param(9).Value = ddat
				param(9).OleDbType = OleDbType.Date
				cmd.Parameters.Add(param(9))

				param(10) = New OleDb.OleDbParameter
				param(10).ParameterName = "@ConsultantsIncharge"
				param(10).Value = ci
				param(10).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(10))

				param(11) = New OleDb.OleDbParameter
				param(11).ParameterName = "@SecondConsultant"
				param(11).Value = sc
				param(11).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(11))

				param(12) = New OleDb.OleDbParameter
				param(12).ParameterName = "@Unit"
				param(12).Value = unit
				param(12).OleDbType = OleDbType.Integer
				cmd.Parameters.Add(param(12))

				param(13) = New OleDb.OleDbParameter
				param(13).ParameterName = "@Diagnosis"
				param(13).Value = diag
				param(13).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(13))

				param(14) = New OleDb.OleDbParameter
				param(14).ParameterName = "@ClinicalSummary"
				param(14).Value = cls
				param(14).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(14))

				param(15) = New OleDb.OleDbParameter
				param(15).ParameterName = "@OperativeDate"
				param(15).Value = opdat
				param(15).OleDbType = OleDbType.Date
				cmd.Parameters.Add(param(15))

				param(16) = New OleDb.OleDbParameter
				param(16).ParameterName = "@OperativeProcedure"
				param(16).Value = oppro
				param(16).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(16))

				param(17) = New OleDb.OleDbParameter
				param(17).ParameterName = "@OperativeFindings"
				param(17).Value = opf
				param(17).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(17))

				param(18) = New OleDb.OleDbParameter
				param(18).ParameterName = "@OperativeOutcome"
				param(18).Value = opo
				param(18).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(18))

				param(19) = New OleDb.OleDbParameter
				param(19).ParameterName = "@OutcomeRemarks"
				param(19).Value = our
				param(19).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(19))

				param(20) = New OleDb.OleDbParameter
				param(20).ParameterName = "@OperativeConsultant"
				param(20).Value = opc
				param(20).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(20))

				param(21) = New OleDb.OleDbParameter
				param(21).ParameterName = "@OperativeResident"
				param(21).Value = opr
				param(21).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(21))

				param(22) = New OleDb.OleDbParameter
				param(22).ParameterName = "@OperativeAnaesthetic"
				param(22).Value = opan
				param(22).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(22))

				param(23) = New OleDb.OleDbParameter
				param(23).ParameterName = "@HistoPathologySentOnDate"
				param(23).Value = hpsdat
				param(23).OleDbType = OleDbType.Date
				cmd.Parameters.Add(param(23))

				param(24) = New OleDb.OleDbParameter
				param(24).ParameterName = "@Histopathology/Cytopathology_ReportNo"
				param(24).Value = hcrno
				param(24).OleDbType = OleDbType.Integer
				cmd.Parameters.Add(param(24))

				param(25) = New OleDb.OleDbParameter
				param(25).ParameterName = "@Serological/ImmunologicalBiologicalDate"
				param(25).Value = sibdat
				param(25).OleDbType = OleDbType.Date
				cmd.Parameters.Add(param(25))

				param(26) = New OleDb.OleDbParameter
				param(26).ParameterName = "@Serological/ImmunologicalBiologicalSummary"
				param(26).Value = sibs
				param(26).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(26))

				param(27) = New OleDb.OleDbParameter
				param(27).ParameterName = "@ImagingDate"
				param(27).Value = idat
				param(27).OleDbType = OleDbType.Date
				cmd.Parameters.Add(param(27))

				param(28) = New OleDb.OleDbParameter
				param(28).ParameterName = "@XRay"
				param(28).Value = xr
				param(28).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(28))

				param(29) = New OleDb.OleDbParameter
				param(29).ParameterName = "@USGDate"
				param(29).Value = usdat
				param(29).OleDbType = OleDbType.Date
				cmd.Parameters.Add(param(29))

				param(30) = New OleDb.OleDbParameter
				param(30).ParameterName = "@USG"
				param(30).Value = us
				param(30).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(30))

				param(31) = New OleDb.OleDbParameter
				param(31).ParameterName = "@CTDate"
				param(31).Value = ctdat
				param(31).OleDbType = OleDbType.Date
				cmd.Parameters.Add(param(31))

				param(32) = New OleDb.OleDbParameter
				param(32).ParameterName = "@CT"
				param(32).Value = ct
				param(32).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(32))

				param(33) = New OleDb.OleDbParameter
				param(33).ParameterName = "@MRIDate"
				param(33).Value = mrdat
				param(33).OleDbType = OleDbType.Date
				cmd.Parameters.Add(param(33))

				param(34) = New OleDb.OleDbParameter
				param(34).ParameterName = "@MRI"
				param(34).Value = mr
				param(34).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(34))

				param(35) = New OleDb.OleDbParameter
				param(35).ParameterName = "@SplInvestigationDate"
				param(35).Value = spdat
				param(35).OleDbType = OleDbType.Date
				cmd.Parameters.Add(param(35))

				param(36) = New OleDb.OleDbParameter
				param(36).ParameterName = "@SplInvestigation"
				param(36).Value = sp
				param(36).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(36))

				param(37) = New OleDb.OleDbParameter
				param(37).ParameterName = "@AdviceOnDischarge"
				param(37).Value = adv
				param(37).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(37))

				param(38) = New OleDb.OleDbParameter
				param(38).ParameterName = "@HospitalCourse"
				param(38).Value = hosc
				param(38).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(38))

				param(39) = New OleDb.OleDbParameter
				param(39).ParameterName = "@FuturePlan"
				param(39).Value = fp
				param(39).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(39))

				param(40) = New OleDb.OleDbParameter
				param(40).ParameterName = "@ResidentName"
				param(40).Value = rname
				param(40).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(40))

				param(41) = New OleDb.OleDbParameter
				param(41).ParameterName = "@SurgeonName"
				param(41).Value = sname
				param(41).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(41))

				param(42) = New OleDb.OleDbParameter
				param(42).ParameterName = "@EnteredBy"
				param(42).Value = eb
				param(42).OleDbType = OleDbType.VarChar
				cmd.Parameters.Add(param(42))

				If con.State <> ConnectionState.Open Then
					con.Open()
				End If
				cmd.ExecuteNonQuery()
				If con.State <> ConnectionState.Closed Then
					con.Close()
				End If
			Catch e As Exception

			End Try
		End Using

	End Sub




here is the entire code of the sub, unable to understand why it does not work!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1