VB.NET - Cannot update sql database from vb.net, I can Add, Retreive data.

Asked By Lon Hanner on 15-Jan-13 04:01 PM



I have been fighting this for weeks. I can add and reteive data just fine. My update code below runs with out any errors from VB or SQL, but it dose not update the database table. I created the database and tables. I installed sql express with windows authnicaion.  I am using a THREE-LAYER APPLICATION, USING OBJECTS CLASSES. PLEASE HELP

    Shared

Function UpdateRiders(oldrider As Riders, newrider As Riders) As Boolean

002.

003. Dim connection As SqlConnection = dctsConnect.GetConnection

004. Dim updateStatement As String =

005. "UPDATE RIDERS SET " &

006. "L_NAME = @NewL_NAME, " &

007. "F_NAME = @NewF_NAME, " &

008. "RIDE_DATE = @NewRIDE_DATE, " &

009. "UP_DATE = @NewUP_DATE, " &

010. "MONTH = @NewMONTH, " &

011. "ADDRESS = @NewADDRESS, " &

012. "CITY = @NewCITY, " &

013. "STATE = @NewSTATE, " &

014. "ZIP = @NewZIP, " &

015. "PHONE = @NewPHONE, " &

016. "COUNTY = @NewCOUNTY, " &

017. "ALT_PHONE = @NewALT_PHONE, " &

018. "DOB = @NewDOB, " &

019. "AGE = @NewAGE, " &

020. "HDY_CAP = @NewHDY_CAP, " &

021. "CD = @NewCD, " &

022. "DCN = @NewDCN, " &

023. "LIFT = @NewLIFT, " &

024. "CHILD = @NewCHILD, " &

025. "SSAN = @NewSSAN, " &

026. "LOCATION = @NewLOCATION, " &

027. "AREA = @NewAREA, " &

028. "HSE_NO = @NewHSE_NO, " &

029. "SEX = @NewSEX, " &

030. "RACE = @NewRACE, " &

031. "EMER_CONT = @NewEMER_CONT, " &

032. "EMER_PHONE = @NewEMER_PHONE, " &

033. "COMMENTS = @NewCOMMENTS " &

034. "WHERE RIDER_NO = @OldRIDER_NO " &

035. "AND L_NAME = @OldL_NAME " &

036. "AND F_NAME = @OldF_NAME " &

037. "AND (UP_DATE = @OldUP_DATE " &

038. " OR UP_DATE IS NULL AND @OldUP_DATE IS NULL) " &

039. "AND (RIDE_DATE = @OldRIDE_DATE " &

040. " OR RIDE_DATE IS NULL AND @OldRIDE_DATE IS NULL) " &

041. "AND (MONTH = @OldMONTH " &

042. " OR MONTH IS NULL AND @OldMONTH IS NULL) " &

043. "AND (ADDRESS = @OldADDRESS " &

044. " OR ADDRESS IS NULL AND @OldADDRESS IS NULL) " &

045. "AND (CITY = @OldCITY " &

046. " OR CITY IS NULL AND @OldCITY IS NULL) " &

047. "AND (STATE = @OldSTATE " &

048. " OR STATE IS NULL AND @OldSTATE IS NULL) " &

049. "AND (ZIP = @OldZIP " &

050. " OR ZIP IS NULL AND @OldZIP IS NULL) " &

051. "AND (COUNTY = @OldCOUNTY " &

052. " OR COUNTY IS NULL AND @OldCOUNTY IS NULL) " &

053. "AND (PHONE = @OldPHONE " &

054. " OR PHONE IS NULL AND @OldPHONE IS NULL) " &

055. "AND (ALT_PHONE = @OldALT_PHONE " &

056. " OR ALT_PHONE IS NULL AND @OldALT_PHONE IS NULL) " &

057. "AND (DOB = @OldDOB " &

058. " OR DOB IS NULL AND @OldDOB IS NULL) " &

059. "AND (AGE = @OldAGE " &

060. " OR AGE IS NULL AND @OldAGE IS NULL) " &

061. "AND (HDY_CAP = @OldHDY_CAP " &

062. " OR HDY_CAP IS NULL AND @OldHDY_CAP IS NULL) " &

063. "AND (CD = @OldCD " &

064. " OR CD IS NULL AND @OldCD IS NULL) " &

065. "AND (DCN = @OldDCN " &

066. " OR DCN IS NULL AND @OldDCN IS NULL) " &

067. "AND (LIFT = @OldLIFT " &

068. " OR LIFT IS NULL AND @OldLIFT IS NULL) " &

069. "AND (CHILD = @OldCHILD " &

070. " OR CHILD IS NULL AND @OldCHILD IS NULL) " &

071. "AND (LOCATION = @OldLOCATION " &

072. " OR LOCATION IS NULL AND @OldLOCATION IS NULL) " &

073. "AND (AREA = @OldAREA " &

074. " OR AREA IS NULL AND @OldAREA IS NULL) " &

075. "AND (HSE_NO = @OldHSE_NO " &

076. " OR HSE_NO IS NULL AND @OldHSE_NO IS NULL) " &

077. "AND (SEX = @OldSEX " &

078. " OR SEX IS NULL AND @OldSEX IS NULL) " &

079. "AND (RACE = @OldRACE " &

080. " OR RACE IS NULL AND @OldRACE IS NULL) " &

081. "AND (EMER_CONT = @OldEMER_CONT " &

082. " OR EMER_CONT IS NULL AND @OldEMER_CONT IS NULL) " &

083. "AND (EMER_PHONE = @OldEMER_PHONE " &

084. " OR EMER_PHONE IS NULL AND @OldEMER_PHONE IS NULL) " &

085. "AND (COMMENTS = @OldCOMMENTS " &

086. " OR COMMENTS IS NULL AND @OldCOMMENTS IS NULL)"

087.

088. Dim updateCommand As New SqlCommand(updateStatement, connection)

089. If newRider.L_NAME = "" Then

090. updateCommand.Parameters.AddWithValue("@NewL_NAME", DBNull.Value)

091. Else

092. updateCommand.Parameters.AddWithValue("@NewL_NAME", newRider.L_NAME)

093. End If

094. If newRider.F_NAME = "" Then

095. updateCommand.Parameters.AddWithValue("@NewF_NAME", DBNull.Value)

096. Else

097. updateCommand.Parameters.AddWithValue("@NewF_NAME", newRider.F_NAME)

098. End If

099. updateCommand.Parameters.AddWithValue("@NewUP_DATE", newrider.UP_DATE)

100. updateCommand.Parameters.AddWithValue("@NewRIDE_DATE", newrider.RIDE_DATE)

101. If newrider.MONTH = "" Then

102. updateCommand.Parameters.AddWithValue("@NewMONTH", DBNull.Value)

103. Else

104. updateCommand.Parameters.AddWithValue("@NewMONTH", newrider.MONTH)

105. End If

106. If newrider.ADDRESS = "" Then

107. updateCommand.Parameters.AddWithValue("@NewADDRESS", DBNull.Value)

108. Else

109. updateCommand.Parameters.AddWithValue("@NewADDRESS", newrider.ADDRESS)

110. End If

111. If newRider.CITY = "" Then

112. updateCommand.Parameters.AddWithValue("@NewCITY", DBNull.Value)

113. Else

114. updateCommand.Parameters.AddWithValue("@NewCITY", newRider.CITY)

115. End If

116. If newrider.STATE = "" Then

117. updateCommand.Parameters.AddWithValue("@NewSTATE", DBNull.Value)

118. Else

119. updateCommand.Parameters.AddWithValue("@NewSTATE", newrider.STATE)

120. End If

121. If newRider.ZIP = "" Then

122. updateCommand.Parameters.AddWithValue("@NewZIP", DBNull.Value)

123. Else

124. updateCommand.Parameters.AddWithValue("@NewZIP", newRider.ZIP)

125. End If

126. If newRider.COUNTY = "" Then

127. updateCommand.Parameters.AddWithValue("@NewCOUNTY", DBNull.Value)

128. Else

129. updateCommand.Parameters.AddWithValue("@NewCOUNTY", newRider.COUNTY)

130. End If

131. If newRider.PHONE = "" Then

132. updateCommand.Parameters.AddWithValue("@NewPHONE", DBNull.Value)

133. Else

134. updateCommand.Parameters.AddWithValue("@NewPHONE", newRider.PHONE)

135. End If

136. If newRider.ALT_PHONE = "" Then

137. updateCommand.Parameters.AddWithValue("@NewALT_PHONE", DBNull.Value)

138. Else

139. updateCommand.Parameters.AddWithValue("@NewALT_PHONE", newRider.ALT_PHONE)

140. End If

141. updateCommand.Parameters.AddWithValue("@NewDOB", newrider.DOB)

142. If newrider.AGE = 0 Then

143. updateCommand.Parameters.AddWithValue("@NewAGE", DBNull.Value)

144. Else

145. updateCommand.Parameters.AddWithValue("@NewAGE", newrider.AGE)

146. End If

147. If newrider.HDY_CAP = "" Then

148. updateCommand.Parameters.AddWithValue("@NewHDY_CAP", DBNull.Value)

149. Else

150. updateCommand.Parameters.AddWithValue("@NewHDY_CAP", newrider.HDY_CAP)

151. End If

152. If newRider.LIFT = "" Then

153. updateCommand.Parameters.AddWithValue("@NewLIFT", DBNull.Value)

154. Else

155. updateCommand.Parameters.AddWithValue("@NewLIFT", newRider.LIFT)

156. End If

157. If newRider.DCN = "" Then

158. updateCommand.Parameters.AddWithValue("@NewDCN", DBNull.Value)

159. Else

160. updateCommand.Parameters.AddWithValue("@NewDCN", newRider.DCN)

161. End If

162. If newRider.CD = "" Then

163. updateCommand.Parameters.AddWithValue("@NewCD", DBNull.Value)

164. Else

165. updateCommand.Parameters.AddWithValue("@NewCD", newRider.CD)

166. End If

167. If newRider.CHILD = "" Then

168. updateCommand.Parameters.AddWithValue("@NewCHILD", DBNull.Value)

169. Else

170. updateCommand.Parameters.AddWithValue("@NewCHILD", newRider.CHILD)

171. End If

172. If newRider.SSAN = "" Then

173. updateCommand.Parameters.AddWithValue("@NewSSAN", DBNull.Value)

174. Else

175. updateCommand.Parameters.AddWithValue("@NewSSAN", newRider.SSAN)

176. End If

177. If newRider.LOCATION = "" Then

178. updateCommand.Parameters.AddWithValue("@NewLOCATION", DBNull.Value)

179. Else

180. updateCommand.Parameters.AddWithValue("@NewLOCATION", newRider.LOCATION)

181. End If

182. If newRider.AREA = "" Then

183. updateCommand.Parameters.AddWithValue("@NewAREA", DBNull.Value)

184. Else

185. updateCommand.Parameters.AddWithValue("@NewAREA", newRider.AREA)

186. End If

187. If newrider.HSE_NO = 0 Then

188. updateCommand.Parameters.AddWithValue("@NewHSE_NO", DBNull.Value)

189. Else

190. updateCommand.Parameters.AddWithValue("@NewHSE_NO", newrider.HSE_NO)

191. End If

192. If newRider.SEX = "" Then

193. updateCommand.Parameters.AddWithValue("@NewSEX", DBNull.Value)

194. Else

195. updateCommand.Parameters.AddWithValue("@NewSEX", newRider.SEX)

196. End If

197. If newRider.RACE = "" Then

198. updateCommand.Parameters.AddWithValue("@NewRACE", DBNull.Value)

199. Else

200. updateCommand.Parameters.AddWithValue("@NewRACE", newRider.RACE)

201. End If

202. If newRider.EMER_CONT = "" Then

203. updateCommand.Parameters.AddWithValue("@NewEMER_CONT", DBNull.Value)

204. Else

205. updateCommand.Parameters.AddWithValue("@NewEMER_CONT", newRider.EMER_CONT)

206. End If

207. If newRider.EMER_PHONE = "" Then

208. updateCommand.Parameters.AddWithValue("@NewEMER_PHONE", DBNull.Value)

209. Else

210. updateCommand.Parameters.AddWithValue("@NewEMER_PHONE", newRider.EMER_PHONE)

211. End If

212. If newRider.COMMENTS = "" Then

213. updateCommand.Parameters.AddWithValue("@NewCOMMENTS", DBNull.Value)

214. Else

215. updateCommand.Parameters.AddWithValue("@NewCOMMENTS", newRider.COMMENTS)

216. End If

217. updateCommand.Parameters.AddWithValue("@OldRIDER_NO", oldRider.RIDER_NO)

218. If newRider.L_NAME = "" Then

219. updateCommand.Parameters.AddWithValue("@OldL_NAME", DBNull.Value)

220. Else

221. updateCommand.Parameters.AddWithValue("@OldL_NAME", oldRider.L_NAME)

222. End If

223. If oldRider.F_NAME = "" Then

224. updateCommand.Parameters.AddWithValue("@OldF_NAME", DBNull.Value)

225. Else

226. updateCommand.Parameters.AddWithValue("@OldF_NAME", oldRider.F_NAME)

227. End If

228. updateCommand.Parameters.AddWithValue("@OldUP_DATE", oldrider.UP_DATE)

229. updateCommand.Parameters.AddWithValue("@OldRIDE_DATE", oldrider.RIDE_DATE)

230. If oldrider.MONTH = "" Then

231. updateCommand.Parameters.AddWithValue("@OldMONTH", DBNull.Value)

232. Else

233. updateCommand.Parameters.AddWithValue("@OldMONTH", oldrider.MONTH)

234. End If

235. If oldrider.ADDRESS = "" Then

236. updateCommand.Parameters.AddWithValue("@OldADDRESS", DBNull.Value)

237. Else

238. updateCommand.Parameters.AddWithValue("@OldADDRESS", oldrider.ADDRESS)

239. End If

240. If oldRider.CITY = "" Then

241. updateCommand.Parameters.AddWithValue("@OldCITY", DBNull.Value)

242. Else

243. updateCommand.Parameters.AddWithValue("@OldCITY", oldRider.CITY)

244. End If

245. If oldrider.STATE = "" Then

246. updateCommand.Parameters.AddWithValue("@OldSTATE", DBNull.Value)

247. Else

248. updateCommand.Parameters.AddWithValue("@OldSTATE", oldrider.STATE)

249. End If

250. If oldRider.ZIP = "" Then

251. updateCommand.Parameters.AddWithValue("@OldZIP", DBNull.Value)

252. Else

253. updateCommand.Parameters.AddWithValue("@OldZIP", oldRider.ZIP)

254. End If

255. If oldRider.COUNTY = "" Then

256. updateCommand.Parameters.AddWithValue("@OldCOUNTY", DBNull.Value)

257. Else

258. updateCommand.Parameters.AddWithValue("@OldCOUNTY", oldRider.COUNTY)

259. End If

260. If oldRider.PHONE = "" Then

261. updateCommand.Parameters.AddWithValue("@OldPHONE", DBNull.Value)

262. Else

263. updateCommand.Parameters.AddWithValue("@OldPHONE", oldRider.PHONE)

264. End If

265. If oldRider.ALT_PHONE = "" Then

266. updateCommand.Parameters.AddWithValue("@OldALT_PHONE", DBNull.Value)

267. Else

268. updateCommand.Parameters.AddWithValue("@OldALT_PHONE", oldRider.ALT_PHONE)

269. End If

270. updateCommand.Parameters.AddWithValue("@OldDOB", oldrider.DOB)

271. updateCommand.Parameters.AddWithValue("@OldAGE", oldrider.AGE)

272. If oldrider.HDY_CAP = "" Then

273. updateCommand.Parameters.AddWithValue("@OldHDy_CAP", DBNull.Value)

274. Else

275. updateCommand.Parameters.AddWithValue("@OldHDy_CAP", oldrider.HDY_CAP)

276. End If

277. If oldRider.LIFT = "" Then

278. updateCommand.Parameters.AddWithValue("@OldLIFT", DBNull.Value)

279. Else

280. updateCommand.Parameters.AddWithValue("@OldLIFT", oldRider.LIFT)

281. End If

282. If oldRider.DCN = "" Then

283. updateCommand.Parameters.AddWithValue("@OldDCN", DBNull.Value)

284. Else

285. updateCommand.Parameters.AddWithValue("@OldDCN", oldRider.DCN)

286. End If

287. If oldRider.CD = "" Then

288. updateCommand.Parameters.AddWithValue("@OldCD", DBNull.Value)

289. Else

290. updateCommand.Parameters.AddWithValue("@OldCD", oldRider.CD)

291. End If

292. If oldRider.CHILD = "" Then

293. updateCommand.Parameters.AddWithValue("@OldCHILD", DBNull.Value)

294. Else

295. updateCommand.Parameters.AddWithValue("@OldCHILD", oldRider.CHILD)

296. End If

297. If oldRider.SSAN = "" Then

298. updateCommand.Parameters.AddWithValue("@OldSSAN", DBNull.Value)

299. Else

300. updateCommand.Parameters.AddWithValue("@OldSSAN", oldRider.SSAN)

301. End If

302. If oldRider.LOCATION = "" Then

303. updateCommand.Parameters.AddWithValue("@OldLOCATION", DBNull.Value)

304. Else

305. updateCommand.Parameters.AddWithValue("@OldLOCATION", oldRider.LOCATION)

306. End If

307. If oldRider.AREA = "" Then

308. updateCommand.Parameters.AddWithValue("@OldAREA", DBNull.Value)

309. Else

310. updateCommand.Parameters.AddWithValue("@OldAREA", oldRider.AREA)

311. End If

312. If oldrider.HSE_NO = 0 Then

313. updateCommand.Parameters.AddWithValue("@OldHSE_NO", DBNull.Value)

314. Else

315. updateCommand.Parameters.AddWithValue("@OldHSE_NO", oldrider.HSE_NO)

316. End If

317. If oldRider.SEX = "" Then

318. updateCommand.Parameters.AddWithValue("@OldSEX", DBNull.Value)

319. Else

320. updateCommand.Parameters.AddWithValue("@OldSEX", oldRider.SEX)

321. End If

322. If oldRider.RACE = "" Then

323. updateCommand.Parameters.AddWithValue("@OldRACE", DBNull.Value)

324. Else

325. updateCommand.Parameters.AddWithValue("@OldRACE", oldRider.RACE)

326. End If

327. If oldRider.EMER_CONT = "" Then

328. updateCommand.Parameters.AddWithValue("@OldEMER_CONT", DBNull.Value)

329. Else

330. updateCommand.Parameters.AddWithValue("@OldEMER_CONT", oldRider.EMER_CONT)

331. End If

332. If oldRider.EMER_PHONE = "" Then

333. updateCommand.Parameters.AddWithValue("@OldEMER_PHONE", DBNull.Value)

334. Else

335. updateCommand.Parameters.AddWithValue("@OldEMER_PHONE", oldRider.EMER_PHONE)

336. End If

337. If oldRider.COMMENTS = "" Then

338. updateCommand.Parameters.AddWithValue("@OldCOMMENTS", DBNull.Value)

339. Else

340. updateCommand.Parameters.AddWithValue("@OldCOMMENTS", oldRider.COMMENTS)

341. End If

342.

343. Try

344. connection.Open()

345.

346. Dim count As Integer = updateCommand.ExecuteNonQuery()

347. If count > 0 Then

348. Return True

349. Else

350. Return False

351. End If

352. Catch ex As SqlException

353. Throw ex

354. Finally

355. connection.Close()

356. End Try

357. End Function

Robbe Morris replied to Lon Hanner on 15-Jan-13 05:16 PM

This is just plain awful code. There could be all sorts of things wrong with this. I'd look at a couple of things to start. You do not explicitly set the parameters data type and data size. So you could be sending in extra spaces around the values and the data type is "char" which could easily cause one or more of your update conditions not to be met because "char" does not trim the values for you if the defined "char" length is longer than the actual value of the string. The other likely issue is code like this:

AND (EMER_CONT = @OldEMER_CONT OR EMER_CONT IS NULL AND @OldEMER_CONT IS NULL)

You are mixing OR and AND "inside" parenthesis without using additional parenthesis inside to force the proper conditional analysis.

Is the test

AND ((EMER_CONT = @OldEMER_CONT OR EMER_CONT IS NULL) and @OldEMER_CONT IS NULL)

or is it

AND (EMER_CONT = @OldEMER_CONT OR (EMER_CONT IS NULL and @OldEMER_CONT IS NULL))

You do not clarify this in your code. So, you "may" be getting unexpected results.