Error executing query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am getting a 'Login failed for user 'myusername'' when executing an update
query via a web service.

I am using the SqlCommand's ExecuteNonQuery function, I simply give it the
connection string and query.

I have given update and select permissions to the user.

If I use the same connection string but add the variables to the parameters
collection of the SqlCommand object, it works.

So the question is what do I have to do to allow an update query to run?

Many thanks for any pointers, suggestions etc.

Regards,

Steve.
 
According to the error, you didn't provide the correct password for the user
'mysername'. This is not related to permissions the user has, but that the
user can't even be authenticated against sql server at all.
 
Hi Marina,

Thanks for your reply, I now get a different error:

ExceptionMessage : There is an error in XML document (1, 530).
Inner Exception : System.FormatException: Input string was not in a
correct format.
at System.Number.ParseInt32(String s, NumberStyles style,
NumberFormatInfo info)
at System.Int32.Parse(String s, NumberStyles style, IFormatProvider
provider)
at System.Xml.XmlConvert.ToInt32(String s)
at
Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read10_ExecuteSQLResponse()

The SQL is as follows:
UPDATE WSGMenuBarItems SET MenuBarItemOrderID = MenuBarItemOrderID + 1 WHERE
(MenuBarID = 225) AND (MenuBarItemOrderID >= 3)

It works fine directly from enterprise manager

Regards,

Steve
 
Is this 2.0? This does not look like a 1.1 error, nor does ExecuteNonQuery
have an overload that takes a sql connection string.

I haven't done enough with 2.0 to see this error. It may be a bug, in which
you should report it.
 
No I'm using 1.1

My code looks like this:

strSQL = "UPDATE WSGMenuBarItems SET MenuBarItemOrderID = MenuBarItemOrderID
+ 1 WHERE (MenuBarID = 225) AND (MenuBarItemOrderID >= 3)
Dim objConnection As New SqlConnection(“connection string goes hereâ€)
objConnection.Open()
Dim objSqlCommand As New SqlCommand(vstrSQL, objConnection)
Return objSqlCommand.ExecuteNonQuery

Sorry if I explained it badly before.

I am calling this via a web service, I tried taking out the 'greater than'
to determine if it confuses the xml but I still get the same error.

Steve.
 
So the real issue is in your web service. The query executes just fine. It
looks like the problem is returning the value back to the caller.

Firstly though, you are not closing your connection here. As you are
returning the number of records affected by the query - but you are not
closing the connection. That will lead to a connection leak very very
quickly. You need to have proper clean up code in your web method.

The issue lies in how your web service is set up, and how your code is
calling that web service. Your query is just fine, this is not an ADO.NET
issue.
 
Marina thanks for the tip.

I will take a look at the web service.

Many thanks once again

Steve
 
Back
Top