UPDATE a SQL database

G

Guest

I'm trying to update a SQL2000 database through a web form. Visual Basic.Net
2003,This is an abbreviated chunk of code.

The primary key field in the table is HospitalProviderNumber. It is an
NVarChar (10). Some of the Hospital Provider Numbers begin with zero
(020026).

The table won't update with the WHERE clause. In my attempts to find what's
working and not, I've easily filled the Hospital Name column with the name of
one hospital by removing the WHERE so I know the update works.

I have a similar web form in which the HospitalSystemID is a long integer
(autonumber) and similar code to update that table works as expected.

I'm guessing the difference is a long integer in the one that works, an
NVarChar in the one that doesn't

Any suggestions?

Private Sub btnSaveHospital_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSaveHospital.Click
Dim strConn As String
strConn = "integrated security=SSPI;data source=Aho1;persist security
info=False;initial catalog=HRRC"
Dim myConnection As New SqlClient.SqlConnection(strConn)
Dim SqlCommandUpdateHospital As New SqlClient.SqlCommand
SqlCommandUpdateHospital.Connection = myConnection

SqlCommandUpdateHospital.Parameters.Add(New
SqlClient.SqlParameter("@ProviderID", txtProviderNumber.Text))
SqlCommandUpdateHospital.Parameters.Add(New
SqlClient.SqlParameter("@HospitalName", txtHospitalName.Text))
SqlCommandUpdateHospital.CommandText = "UPDATE tblHospitals SET
HospitalName = @HospitalName WHERE (HospitalProviderNumber = @ProviderID)"

myConnection.Open()
SqlCommandUpdateHospital.ExecuteNonQuery()
myConnection.Close()

End sub

Roxie Aho
roxiea at usinternet.com
 
P

pvdg42

Roxie Aho said:
I'm trying to update a SQL2000 database through a web form. Visual
Basic.Net
2003,This is an abbreviated chunk of code.

The primary key field in the table is HospitalProviderNumber. It is an
NVarChar (10). Some of the Hospital Provider Numbers begin with zero
(020026).

The table won't update with the WHERE clause. In my attempts to find
what's
working and not, I've easily filled the Hospital Name column with the name
of
one hospital by removing the WHERE so I know the update works.

I have a similar web form in which the HospitalSystemID is a long integer
(autonumber) and similar code to update that table works as expected.

I'm guessing the difference is a long integer in the one that works, an
NVarChar in the one that doesn't

Any suggestions?

Private Sub btnSaveHospital_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSaveHospital.Click
Dim strConn As String
strConn = "integrated security=SSPI;data source=Aho1;persist security
info=False;initial catalog=HRRC"
Dim myConnection As New SqlClient.SqlConnection(strConn)
Dim SqlCommandUpdateHospital As New SqlClient.SqlCommand
SqlCommandUpdateHospital.Connection = myConnection

SqlCommandUpdateHospital.Parameters.Add(New
SqlClient.SqlParameter("@ProviderID", txtProviderNumber.Text))
SqlCommandUpdateHospital.Parameters.Add(New
SqlClient.SqlParameter("@HospitalName", txtHospitalName.Text))
SqlCommandUpdateHospital.CommandText = "UPDATE tblHospitals SET
HospitalName = @HospitalName WHERE (HospitalProviderNumber = @ProviderID)"

myConnection.Open()
SqlCommandUpdateHospital.ExecuteNonQuery()
myConnection.Close()

End sub

Roxie Aho
roxiea at usinternet.com
Click to expand...

I believe you need to enclose the ProviderID parameter in single quotes,
something roughly like this:

SqlCommandUpdateHospital.CommandText = "UPDATE tblHospitals SET
HospitalName = @HospitalName WHERE (HospitalProviderNumber = " & "'" &
@ProviderID &
"')"
 
T

TechGladiator

Roxie,

I have not tried updating my DB the way you are mentioning above BUT
if i were to guess by looking at your code I would enclose your
@HospitalName with '@HospitalName' ...Let me know if it works, as I
have not tried it myself yet.

--Miguel
 
S

Stephany Young

When you use the particualr constructor for a SQLParameter object that you
are using, the SqlDBType for the SQLParameter object is inferred from the
..NET Framework type of the object that you are passing as a value.

The .NET Framework type String (type of TextBox.Text) infers to an NVarChar
however I would expect the inferred length to be an issue.

If I were writing you program then I would code the SQLParameters as
follows:
(For the purpose of the exercise I am assuming that roviderID is also
NvarChar(10).)

<object>.Parameters.Add(New SqlParameter("@ProviderID",
SqlDbType.NVarChar, 10)).Value = txtProviderNumber.Text

<object>.Parameters.Add(New SqlParameter("@HospitalName",
SqlDbType.NVarChar, 10)).Value =txtHospitalName.Text
 
Top