connecting to Data.SqlClient

[mambojerry]

I am a new asp.net user and I am trying to use this peace of code inorder to
send data from the website to a console and email address. I have managed to
write all the required functions, but when it comes to sqlConnection and sending
the data, that is way all goes wrong

This is the code I am using

Private strConnect As String = "Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\REALLYEASY.mdf;Integrated
Security=True;User Instance=True"

Public Sub dbSave()

Dim toAddress As String = "", Password As String = "", Messagebody As
String = "", ClientEmail As String = ""

Get_Client_Details(toAddress, Password, ClientEmail)
'the function above returns the requred strings

Messagebody = Render_msgBody()


Dim FromAddress As String = "[e-mail]"
Dim Subject As String = "Online Build"

Dim cnn As New SqlConnection(strConnect)
cnn.Open()
Dim cmd As SqlCommand
Dim sqlString As String

'To console
sqlString = "PH_EMailOutConsole_New " & ClientID.ToString & ",'" &
FromAddress & "','" & Password & "','" & toAddress & "','" & Subject & "','" &
Messagebody & "','',''"
cmd = New SqlCommand(sqlString, cnn)
cmd.ExecuteNonQuery()


'To client Email
Subject = OfficeName.otherNames & " - More details"
Messagebody = vbCrLf & "Contacts Email Address:" & ContactDetails.CareOf
& vbCrLf & vbCrLf
Messagebody = Messagebody & AutherDetails.Additionalinformation

sqlString = "PH_EMailOut_New " & ClientID.ToString & ",'" & FromAddress
& "','" & Password & "','" & ClientEmail & "','" & Subject & "','" & Messagebody
& "','',''"
cmd = New SqlCommand(sqlString, cnn)
cmd.ExecuteNonQuery()

cnn.Close()


End Sub

any help would be much appreaciated

J

[SheSaidImaPregy]

In asp.net you get to use a fabulous thing called parameters. Stops sql injection (well, helps a lot). these parameters would look something like this for you:

sqlString = "PH_EMailOut_New @ClientID, @FromAddress, @Password, @ClientEmail, @Subject, @MessageBody,'',''"
cmd = New SqlCommand(sqlString, cnn)
cmd.Parameters.AddWithValue( "@ClientID", ClientID.ToString() )
cmd.Parameters.AddWithValue( "@FromAddress", FromAddress )
...
...
...
cmd.ExecuteNonQuery()

Anyway, remove the Private part of the declaration of your strConnect. For speed and performace, only open the connection when you have to submit the query. So it should look like:

cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()

Anyway, what error do you receive after removing the private from the strConnect?

[mambojerry]

Thank you ever so much for your reply. I followed your advice and this is the error I am getting

System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file PATH\REALLYEASY.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

cheers
J

[SheSaidImaPregy]

You need to supply the data directory to the MDF file. Then it should work.