Here is what worked for me on a parameterized stored proc named
spExecuteGeneric which takes 2 parameters: @SQLQuery -A query (which is
deceptively named AuthorID) and ReturnVal, a returntype parameter Which
would hold an errror (I think). Hope this helps The biggest problem I had
was the connection string and "Trusted_Connection=False;" had to be declared
if I was using a login/pwd. You should probalby change that to true if using
trusted connection and leave off the UserName and PWD pairs
OK so I need to get uptodate on vbcrlf
Private Function fncRunSproc()
Dim MyDataSet As New DataSet("MyDataSet")
Dim AuthorID As SqlParameter
Dim ReturnVal As SqlParameter
Dim LoopCount As Integer
Dim NewString As New System.Text.StringBuilder
Dim RowCounter As Integer
Dim ConnStr As String = "Server=""ServerName"";workstation
id=""WORKSTATION_NAME"";packet size=4096;integrated security=SSPI;data
source=""SERVERNAME\DATABASENAME"";persist security info=False;initial
catalog=Pubs;Trusted_Connection=False;User
ID=""LoginName"";Password=""Password"""
Dim SQLConn As New SqlConnection(ConnStr)
Dim MySqlCmd As New SqlCommand("spExecuteGeneric", SQLConn)
MySqlCmd.CommandType = CommandType.StoredProcedure
'Dim SQLAdptr As New SqlDataAdapter(MySqlCmd)
txtResults.Text = ""
'txtResults.Refresh()
AuthorID = New SqlParameter
AuthorID.SqlDbType = SqlDbType.VarChar
AuthorID.ParameterName = "@SQLQuery"
AuthorID.Direction = ParameterDirection.Input
AuthorID.Value = txtQuery.Text
MySqlCmd.Parameters.Add(AuthorID)
ReturnVal = New SqlParameter '("RetValue", SqlDbType.Int)
ReturnVal.SqlDbType = SqlDbType.Int
ReturnVal.ParameterName = "RetValue"
ReturnVal.Direction = ParameterDirection.ReturnValue
MySqlCmd.Parameters.Add(ReturnVal)
SQLConn.Open()
Dim myReader As SqlDataReader
myReader = MySqlCmd.ExecuteReader()
NewString.Append(Chr(9))
For LoopCount = 0 To myReader.FieldCount - 1
NewString.Append(Trim(myReader.GetName(LoopCount). ToString) & Chr(9))
Next
NewString.Append(vbCrLf)
Do While myReader.Read
NewString.Append("(" & RowCounter & ")" & Chr(9))
RowCounter += 1
For LoopCount = 0 To myReader.FieldCount - 1
NewString.Append(Trim(myReader.GetSqlValue(LoopCou nt).ToString) &
Chr(9))
Next
NewString.Append(vbCrLf)
Loop
txtResults.Text &= NewString.ToString
myReader.Close()
txtResults.Text &= "Return Value: " & (ReturnVal.Value) & vbCrLf
SQLConn.Close()
End Function
"D. Shane Fowlkes" <sh***@raccoonbob.com> wrote in message
news:O6**************@TK2MSFTNGP12.phx.gbl...
Still learning ASP.NET....(and I was getting so good with classic ASP
too!).
I'm trying to connect to a SQL Server using a simple connection script.
I've checked 2 different books and looked at www.asp.net and still cannot
get past this "error".
Can anyone see the error? This <script> example is almost line by line
from the Sams ASP.NET Unleashed Book. The error is on line 8. Thanks.
Compiler Error Message: BC30188: Declaration expected.
Source Error:
Line 8: DbConn = New SQLConnection("server=drpt-server3;
database=DRPT-TEST")
**********************************
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Dim DbConn As SQLConnection
Dim MySQLCommand As SQLCommand
Dim RS As SQLDataReader
DbConn = New SQLConnection("server=drpt-server3; database=DRPT-TEST")
DbConn.Open()
MySQLCommand = New MySQLCommand("SELECT * FROM Staff;", DbConn)
RS = MySQLCommand.ExecuteReader()
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Untitled Document</title>
</head>
<body>
<%
RS.MoveFirst
Response.Write RS("LastName").Value
DbConn.Close()
RS.Close()
%>
</body>
</html>
--
*********************************
D. Shane Fowlkes - TMM
Saving the world, one web site at a time.
http://www.shanefowlkes.com
*********************************