| Author |
Topic |
|
Deacon Blues
Starting Member
4 Posts |
 Posted - 2003-04-16 : 11:14:26
|
| Hi all,I am having a problem trying to update a table in SQL Server. My SQL statemet is supposed to set a field in my table to true when a certain actaion has occurred.AblationDataAdapter.UpdateCommand.CommandText = "update specimen_status set ProfilometryComplete = 'T' where matrixid like "' & myfile & "'"When this statement is checked using the Query Analyzer and one specific matrixid, it works.update specimen_status set ProfilometryComplete = 'T' where matrixid like 'H1-146-001s1d'But when I use that statement in my VB code, it does not work. To get the statement to work, I have to insert whitespaces to fill it out to the extent of the column, which is defined as varchar(50).update specimen_status set ProfilometryComplete = 'T' where matrixid like 'H1-146-001s1d (insert whitespaces here) 'Why does it require me to insert all of these whitespaces in the code when I am using it in VB. Is the desin of the table incorrect. Or should I just get VB to insert/delete whitespaces to correct the problem, even though it seems like a bad patch to do this. Any insight into this problem would be greatly appreciated.Thanks,Scott |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-04-16 : 11:25:00
|
Deacon Blue ? Like the Steely Dan song ?Cool, we already have a Peg, we need a Josie and we are all set  OK, the answer is, try putting some wildcards in.I.E. Where matrixid like 'H1-146-001s1d%'Or, if it is an ID, why use like at all ?Where matrixid = 'H1-146-001s1d' Do either of them work for you ?Damian |
 |
|
|
Deacon Blues
Starting Member
4 Posts |
Posted - 2003-04-16 : 11:48:31
|
| Thanks for the quick reply. I tried both and neither worked. (I had tried '=' before and it didn't work then either.) I thought about using wildcards but they don't seem to work here. It shouldn't need them since I am providing the entire ID value and it is defined as a varchar, should it? The strange part to me is that it will work everywhere else but in my VB program. I am using VB.NET if that makes any difference.Yup, just like the Steely Dan song. "They have have a name for the winners in the world, but I want a name when I lose. They call Alabama the Crimson Tide" (referring to the dominance of the University of Alabama's football team) "....Call me Deacon Blues." I have multiple degrees from The University of Alabama and eat, sleep, breathe Alabama football!!!Scott |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-04-16 : 12:05:37
|
| If it works in Query Analyzer but not VB.NET, sounds to me like VB.NET is where the problem is.Maybe post some VB code ?Damian |
|
|
|
Deacon Blues
Starting Member
4 Posts |
Posted - 2003-04-16 : 13:54:16
|
Here is my code.Code for the button to run the query. Private Sub cmdCheckProfilometryFiles_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCheckProfilometryFiles.Click Query_Check() End Sub Code for the subroutine that updates the tablePublic Sub Query_Check() AblationDBConnection.Open() UpdateQuery = "update specimen_status set ProfilometryComplete = 'T' where matrixid like 'H1-146-001s1d'" AblationDataAdapter.UpdateCommand.CommandText = "update specimen_status set ProfilometryComplete = 'T' where matrixid like 'H1-146-001s1d'" AblationDBConnection.Close() MessageBox.Show("Query Completed!!!")End Sub |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-04-16 : 13:59:06
|
| Deacon, Can you double check and make sure it's a VARCHAR field and not an CHAR field?Also, in query analyzer, do a LEN(matrixid) and see what it gives you. See if it's just some bad data for that one row, or all of the rows. I'm in Alabama as well. War Eagle!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-16 : 14:05:26
|
| OR.....why not create a stored procedure?Brett8-) |
|
|
|
Deacon Blues
Starting Member
4 Posts |
Posted - 2003-04-16 : 14:15:33
|
| Michael,Oh no, not an Aubie!!! What part of the state are you in? Both campuses are beautiful this time of year. Kinda makes me wish I was at either one right now.I doulbe-checked the table design and verified that it was a varchar. I then ran the query to give me the matrixid and length of the field. It verified that it was 13 for 'H1-146-001s1d'. It is still upset when I try to tell it to update the table for the matrixid without having 50-len(matrixid) whitespaces in the search criteria field. It seems as though I am doing everything correctly, right?Scott |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-04-16 : 14:37:24
|
I'm gonan go with Brett on this. Use a stored proc. I think that's really going to help you out. I'm in Mobile.Here's a code example for caling a stored proc in VB.Net.The GetConnection() function returns a closed SQLConnection. Dim oCmd As New SqlCommand("p_User_UpdateUser", GetConnection()) oCmd.CommandType = CommandType.StoredProcedure oCmd.Parameters.Add("@UserID", SqlDbType.Int).Value = nUserID oCmd.Parameters.Add("@CustomerID", SqlDbType.Int).Value = nCustomerID oCmd.Parameters.Add("@UserTypeID", SqlDbType.Int).Value = nUserTypeID oCmd.Parameters.Add("@UserName", SqlDbType.Int).Value = nUserName oCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = sFirstName oCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = sLastName oCmd.Parameters.Add("@Email", SqlDbType.VarChar, 100).Value = sEmail Try oCmd.Connection.Open() oCmd.ExecuteNonQuery() Catch e As Exception Throw e Finally If oCmd.Connection.State = ConnectionState.Open Then oCmd.Connection.Close() End If End TryMichael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
SQL In VB Problem