How to connect to SQL from .net

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

How to connect to SQL from .net

Author

Topic

poornima
Starting Member

32 Posts

Posted - 2006-09-30 : 04:08:06

Hi
I have a windows application in VB.net which creates a dataset and updates the dataAdapter by connecting to the SQL Server 2005 and sends the dataset as a parameter to my webService.My code is

Dim strCon As String
Dim sValue As String
Dim strQuery As String
Dim dsEventTransac As New DataSet
Dim daEventTransac As New SqlClient.SqlDataAdapter
Dim rowCount As New Integer
Dim oWebService As New WebReference.TkmService
'Dim nwRow As DataRow
'Dim delCmd As SqlClient.SqlCommand

strQuery = "SELECT * FROM EventTransaction"

strCon = "Data Source=localhost;integrated security=SSPI;initial catalog=KmsDb;User ID=sa;password="
objCon = New SqlClient.SqlConnection(strCon)
If objCon.State = ConnectionState.Closed Then
objCon.Open()
End If
daEventTransac = New SqlClient.SqlDataAdapter(strQuery, objCon)
Dim cmdBuilder As New SqlClient.SqlCommandBuilder(daEventTransac)
daEventTransac.Fill(dsEventTransac)

'update the DataAdapter
daEventTransac.Update(dsEventTransac)
oWebService.Url = "http://192.168.0.10/TkmService1/TkmService.asmx"
sValue = oWebService.AcceptDataset(dsEventTransac)

'MsgBox(sValue)
'oWebService.AcceptDataset(dsEventTransac)
'dgEventTransac.DataSource = Nothing

''display the table in a grid
'dgEventTransac.DataSource = dsEventTransac.Tables(0)

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

'Private Sub DataGrid1_Navigate(ByVal sender As System.Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles dgEventTransac.Navigate

'End Sub
End Class

When running the above code in Visual Studio iam getting an error like

"System.WebServices.Protocols.SoapException:Server was unable to process request---->
System.Data.SqlClient.SqlException:Loginfailed for user "DESIGN-EMB/ASPNET.

where DESIGN-EMB is the name of my machine.

My webservice code is

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.Data.SqlClient
<WebService(Namespace:="http://192.168.0.10/TkmService1")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class TkmService
Inherits System.Web.Services.WebService

<WebMethod()> _
Public Function AcceptDataset(ByVal mDsEventTransac As DataSet) As String
Dim objCon As New SqlConnection
Dim strCon As String
Dim strQuery As String
Dim wDsEventTransac As New DataSet
Dim daEventTransac As New SqlClient.SqlDataAdapter

strQuery = "SELECT * FROM EventTransaction"

strCon = "Data Source=localhost;integrated security=SSPI;initial catalog=KmsDb;"
objCon = New SqlClient.SqlConnection(strCon)
If objCon.State = ConnectionState.Closed Then
objCon.Open()
End If
daEventTransac = New SqlClient.SqlDataAdapter(strQuery, objCon)
Dim cmdBuilder As New SqlClient.SqlCommandBuilder(daEventTransac)
daEventTransac.Fill(wDsEventTransac)

'update the DataAdapter
daEventTransac.Update(wDsEventTransac)

'merge .sdf records with the .mdf records
wDsEventTransac.Merge(mDsEventTransac)

wDsEventTransac.AcceptChanges()
wDsEventTransac = Nothing
Return "Hello World"
End Function

End Class

Can you tell me where iam wrong.

Thanks In Advance

Poornima

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-30 : 13:21:52

This line is wrong

strCon = "Data Source=localhost;integrated security=SSPI;initial catalog=KmsDb;User ID=sa;password="

You must use the "integrated security" option or the "user id" and "password" but not both.
If you use "integrated security" then you must grant permissions in the database to the DESIGN-EMB/ASPNET user.
Otherwise - if you use "user id" and "password" you must create a SQL Server login in SQL Server and make sure you have SQL Server Authentication enabled on the server.

This doesn't make sense in the Windows app code, you're reading the data (Fill) and then immediately trying to update it. You don't need the Update call.

daEventTransac.Fill(dsEventTransac)

'update the DataAdapter
daEventTransac.Update(dsEventTransac)

In the web service code you're opening another DataSet on the same data (which doesn't seem to make sense, if the Windows app is going to connect to the data itself). Again you update the data immediately after reading it, which won't do anything. Then you merge the two DataSets (the one you created in the web service and the one that was passed in as a parameter) - and after merging them, do nothing with the merged DataSet.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-09-30 : 18:33:58

see this http://www.connectionstrings.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources