Here is what worked for me on a parameterized stored proc named
spExecuteGeneri c 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_Connec tion=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("MyData Set")
Dim AuthorID As SqlParameter
Dim ReturnVal As SqlParameter
Dim LoopCount As Integer
Dim NewString As New System.Text.Str ingBuilder
Dim RowCounter As Integer
Dim ConnStr As String = "Server=""Serve rName"";worksta tion
id=""WORKSTATIO N_NAME"";packet size=4096;integ rated security=SSPI;d ata
source=""SERVER NAME\DATABASENA ME"";persist security info=False;init ial
catalog=Pubs;Tr usted_Connectio n=False;User
ID=""LoginName" ";Password=""Pa ssword"""
Dim SQLConn As New SqlConnection(C onnStr)
Dim MySqlCmd As New SqlCommand("spE xecuteGeneric", SQLConn)
MySqlCmd.Comman dType = CommandType.Sto redProcedure
'Dim SQLAdptr As New SqlDataAdapter( MySqlCmd)
txtResults.Text = ""
'txtResults.Ref resh()
AuthorID = New SqlParameter
AuthorID.SqlDbT ype = SqlDbType.VarCh ar
AuthorID.Parame terName = "@SQLQuery"
AuthorID.Direct ion = ParameterDirect ion.Input
AuthorID.Value = txtQuery.Text
MySqlCmd.Parame ters.Add(Author ID)
ReturnVal = New SqlParameter '("RetValue", SqlDbType.Int)
ReturnVal.SqlDb Type = SqlDbType.Int
ReturnVal.Param eterName = "RetValue"
ReturnVal.Direc tion = ParameterDirect ion.ReturnValue
MySqlCmd.Parame ters.Add(Return Val)
SQLConn.Open()
Dim myReader As SqlDataReader
myReader = MySqlCmd.Execut eReader()
NewString.Appen d(Chr(9))
For LoopCount = 0 To myReader.FieldC ount - 1
NewString.Appen d(Trim(myReader .GetName(LoopCo unt).ToString) & Chr(9))
Next
NewString.Appen d(vbCrLf)
Do While myReader.Read
NewString.Appen d("(" & RowCounter & ")" & Chr(9))
RowCounter += 1
For LoopCount = 0 To myReader.FieldC ount - 1
NewString.Appen d(Trim(myReader .GetSqlValue(Lo opCount).ToStri ng) &
Chr(9))
Next
NewString.Appen d(vbCrLf)
Loop
txtResults.Text &= NewString.ToStr ing
myReader.Close( )
txtResults.Text &= "Return Value: " & (ReturnVal.Valu e) & vbCrLf
SQLConn.Close()
End Function
"D. Shane Fowlkes" <sh***@raccoonb ob.com> wrote in message
news:O6******** ******@TK2MSFTN GP12.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="Syst em.Data.SqlClie nt" %>
<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("S ELECT * FROM Staff;", DbConn)
RS = MySQLCommand.Ex ecuteReader()
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Untitl ed 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
*************** *************** ***
