UPDATE a SQL database

[Roxie Aho]

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 HospitalProvide rNumber. 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 HospitalSystemI D 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.EventArg s) Handles btnSaveHospital .Click
Dim strConn As String
strConn = "integrated security=SSPI;d ata source=Aho1;per sist security
info=False;init ial catalog=HRRC"
Dim myConnection As New SqlClient.SqlCo nnection(strCon n)
Dim SqlCommandUpdat eHospital As New SqlClient.SqlCo mmand
SqlCommandUpdat eHospital.Conne ction = myConnection

SqlCommandUpdat eHospital.Param eters.Add(New
SqlClient.SqlPa rameter("@Provi derID", txtProviderNumb er.Text))
SqlCommandUpdat eHospital.Param eters.Add(New
SqlClient.SqlPa rameter("@Hospi talName", txtHospitalName .Text))
SqlCommandUpdat eHospital.Comma ndText = "UPDATE tblHospitals SET
HospitalName = @HospitalName WHERE (HospitalProvid erNumber = @ProviderID)"

myConnection.Op en()
SqlCommandUpdat eHospital.Execu teNonQuery()
myConnection.Cl ose()

End sub

Roxie Aho
roxiea at usinternet.com

[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

[pvdg42]

"Roxie Aho" <Ro******@discu ssions.microsof t.com> wrote in message
news:F2******** *************** ***********@mic rosoft.com...

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 HospitalProvide rNumber. 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 HospitalSystemI D 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.EventArg s) Handles btnSaveHospital .Click
Dim strConn As String
strConn = "integrated security=SSPI;d ata source=Aho1;per sist security
info=False;init ial catalog=HRRC"
Dim myConnection As New SqlClient.SqlCo nnection(strCon n)
Dim SqlCommandUpdat eHospital As New SqlClient.SqlCo mmand
SqlCommandUpdat eHospital.Conne ction = myConnection

SqlCommandUpdat eHospital.Param eters.Add(New
SqlClient.SqlPa rameter("@Provi derID", txtProviderNumb er.Text))
SqlCommandUpdat eHospital.Param eters.Add(New
SqlClient.SqlPa rameter("@Hospi talName", txtHospitalName .Text))
SqlCommandUpdat eHospital.Comma ndText = "UPDATE tblHospitals SET
HospitalName = @HospitalName WHERE (HospitalProvid erNumber = @ProviderID)"

myConnection.Op en()
SqlCommandUpdat eHospital.Execu teNonQuery()
myConnection.Cl ose()

End sub

Roxie Aho
roxiea at usinternet.com

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

SqlCommandUpdat eHospital.Comma ndText = "UPDATE tblHospitals SET
HospitalName = @HospitalName WHERE (HospitalProvid erNumber = " & "'" &
@ProviderID &
"')"

[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>.Parame ters.Add(New SqlParameter("@ ProviderID",
SqlDbType.NVarC har, 10)).Value = txtProviderNumb er.Text

<object>.Parame ters.Add(New SqlParameter("@ HospitalName",
SqlDbType.NVarC har, 10)).Value =txtHospitalNam e.Text

"Roxie Aho" <Ro******@discu ssions.microsof t.com> wrote in message
news:F2******** *************** ***********@mic rosoft.com...

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 HospitalProvide rNumber. 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 HospitalSystemI D 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.EventArg s) Handles btnSaveHospital .Click
Dim strConn As String
strConn = "integrated security=SSPI;d ata source=Aho1;per sist security
info=False;init ial catalog=HRRC"
Dim myConnection As New SqlClient.SqlCo nnection(strCon n)
Dim SqlCommandUpdat eHospital As New SqlClient.SqlCo mmand
SqlCommandUpdat eHospital.Conne ction = myConnection

SqlCommandUpdat eHospital.Param eters.Add(New
SqlClient.SqlPa rameter("@Provi derID", txtProviderNumb er.Text))
SqlCommandUpdat eHospital.Param eters.Add(New
SqlClient.SqlPa rameter("@Hospi talName", txtHospitalName .Text))
SqlCommandUpdat eHospital.Comma ndText = "UPDATE tblHospitals SET
HospitalName = @HospitalName WHERE (HospitalProvid erNumber = @ProviderID)"

myConnection.Op en()
SqlCommandUpdat eHospital.Execu teNonQuery()
myConnection.Cl ose()

End sub

Roxie Aho
roxiea at usinternet.com

[Cor Ligthert [MVP]]

Roxie,

Have a look at this simple sample how to use on the most simple way
parameters.

This is good suitable for a webapplication, for a windows application I
would take another style.

http://www.vb-tips.com/default.aspx?...6-7139b8970071

That extended sample is as well on our website.

I hope this helps,

Cor
"Roxie Aho" <Ro******@discu ssions.microsof t.com>

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 HospitalProvide rNumber. 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 HospitalSystemI D 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.EventArg s) Handles btnSaveHospital .Click
Dim strConn As String
strConn = "integrated security=SSPI;d ata source=Aho1;per sist security
info=False;init ial catalog=HRRC"
Dim myConnection As New SqlClient.SqlCo nnection(strCon n)
Dim SqlCommandUpdat eHospital As New SqlClient.SqlCo mmand
SqlCommandUpdat eHospital.Conne ction = myConnection

SqlCommandUpdat eHospital.Param eters.Add(New
SqlClient.SqlPa rameter("@Provi derID", txtProviderNumb er.Text))
SqlCommandUpdat eHospital.Param eters.Add(New
SqlClient.SqlPa rameter("@Hospi talName", txtHospitalName .Text))
SqlCommandUpdat eHospital.Comma ndText = "UPDATE tblHospitals SET
HospitalName = @HospitalName WHERE (HospitalProvid erNumber = @ProviderID)"

myConnection.Op en()
SqlCommandUpdat eHospital.Execu teNonQuery()
myConnection.Cl ose()

End sub

Roxie Aho
roxiea at usinternet.com