Adding a new row in a web service

[Lance Geeck]

I am new to .NET and SQL Server and thought maybe someone could point out
what I am doing wrong here.



My problem is I can't figure out why this doesn't update the database. I am
calling this web services that in turn should write a row to the database.



**** Begin Code ********



<WebMethod()> _

Public Function AddStatusRecord(ByVal BName As String, _

ByVal StatusNote As String, ByVal ContactName As String) As Boolean

Try

Dim dRow As DataRow

Dim dsStatus1 As New dsStatus

dRow = dsStatus1.Tables("StatusLog").NewRow

dRow.Item("BName") = BName

dRow.Item("StatusNotes") = StatusNote

dRow.Item("ContactName") = ContactName

dRow.Item("DateStamp") = Now.utcNow

'tried many variations on this including

' Now.ToString, Now.ToLocalTime, Now.Now

dRow.EndEdit()

adtStatus.Update(dsStatus1, "StatusLog")

Return True

Catch ex As Exception

Return (False)

End Try

End Function

**** End Code *********

******* Table Description *******

BName char 50
ContactName char 50
DateStamp datetime 8
StatusNotes char 255

The primary key is BName, ContactName and DateStamp. Nulls are not allowed
in any column.

I don't seem to be catching an exception on this. For some reason it isn't
writing a row into the database.

Any suggestions?

Thanks
Lance

 

[Cowboy \(Gregory A. Beamer\)]

You have to have a connection to the database to know what you are
inserting. It is better to do something like this:

strConn = "ConnectionString Here"

Dim objConn As New SqlConnection(strConn)

strSQL = "INSERT INTO StatusLog " & _
"(Bnote, StatusName, ContactName, DateStamp) VALUES ('" & _
BName & "','" & StatusNote & "','" & ContactName & "','"& _
Now.utcNow.ToString() & "')"

Dim objCmd as New SqlCommand(strSql,objConn)

objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
objConn.Dispose()

You can later parameterize, using ? (for OleDb provider) or @variableName
(for SQLClient provider) or make a stored procedure in SQL Server.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[Lance Geeck]

Hi Cowboy,

Thanks for responding to my question. That took care of the problem.

This however generates another question that has been bothering me since
I've started working with .NET. The question is:

It seems that dropping adapters onto the form doesn't work very well or at
least makes things much more complicated than is necessary or reasonable.
Coding the connections and setting up the SQL in code seems to work better.
It also seems to work much more like VB6/ADO.

Should I forget dropping adapters onto the form and just code the
connections, queries and updates? Or should I keep stumbling along trying
to get use to the adapter approach?

Thanks for the help
Lance

 

[Severin]

I use the basics of the code below to insert records through a webservice,
BUT I return to the client an integer that indicates the number of rows
effected (insert always returns 1)

Do this by setting the Function return TYPE as integer

<WebMethod()>_
Public Function AddStatusRecord({variables}) as Integer <- not Boolean

at the ExecuteNonQuery() step put

Return objCmd.ExecuteNonQuery()

then on the client when the webservice returns the integer 1, you know
without doubt that the record was successfully inserted.

If AddStatusRecord(var1, var2, var3) <> 1
' Error occurred, no record inserted, could be an http hiccup -
something went wrong
' display some kind of dialog to alert user that this needs to be
resubmitted
End If

Severin