I just want to grab one field!!

  • Thread starter Jonathan Schwarz via DotNetMonster.com
  • Start date
J

Jonathan Schwarz via DotNetMonster.com

Hi folks, I am a complete newbie to ASP.NET, VB, etc... I am coming from a
Cold Fusion background where it only takes me 4 lines to query a MSSQL db
and display a field name.

Please, how do I connect to my SQL db using the DSN ("mydsnname") and then
select "thisfieldname" from "thistablename".

Then how do i display the value in my html page?

....later I will ask how to grab an uplaoded files name and update the same
field via another form.....

Big Picture: I ahve an existing aspx page that I need to add a link to. The
hyperlink should point to a file on the server that I can change via an
upload form yet to be created....

As I said, I am a CFer... but have to do this in a .Net environment.

Thanks for any help!!!
 
K

Karl Seguin

You should take a look at:
http://www.dotnetjunkies.com/quickstart/aspplus/doc/webdataaccess.aspx

Once you have your connection and command set up, you can the ExecuteScalar
method of the command to retrieve a single field

public shared function GetValue() as string
dim connection as new SqlConnection("ConnectionString")
dim command as new SqlCommand("select field from table", connection)
try
connection.Open()
return cstr(command.ExecuteScalar)
finally
connection.Dispose()
command.Dispose()
end try
end function

this is assuming "field" is a string/char/varchar/text you'll cint() it if
it's a int/long/xxx, cbool if it's a bit...and so on

As far as using a DSN, you can't use it with SqlConnection..from the docs:
The .NET Framework Data Provider for SQL Server uses its own protocol to
communicate with SQL Server. Therefore, it does not support the use of an
ODBC data source name (DSN) when connecting to SQL Server because it does
not add an ODBC layer.

so you could make use of an OleDbConnection and OleDbCommand instead.

Karl


--
MY ASP.Net tutorials
http://www.openmymind.net/index.aspx - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
 
J

Jonathan Schwarz via DotNetMonster.com

Table name: mei_cc_file
Fields: id(int), filnam(varchar)
Records: 1

I just need to be shown how to display the contents of "filnam" on an aspx
web page. I only plan to use this db and table for this and nothing else.
Can someone show me how this can be done with completely local variables
and parameters (nothing from another file)?

As I said, I am new to VB, and .Net... so please be simple

:)
 
K

Karl Seguin

<%@ Imports Namespace="System.Data.SqlClient
<html>
<head></head>
<body>
<asp:literal id="fileName" runat="server" />
</body>

<script runat="Server" language="vb">
Sub Page_Load
dim connection as new SqlConnection("CONNECTION_STRING")
dim command as new SqlCommand("SELECT Filname from mei_cc_file where id =
@id", connection)
command.Parameters.Add("@Id", SqlDbType.Int).Value = 1
try
connection.open()
lit.text = cstr(command.ExecuteScalar())
finally
connection.dispose()
command.dispose()
end try
End Sub
</script>

Check out http://www.w3schools.com/aspnet/aspnet_dbconnection.asp ...code
might not work as is as I've typed it off the top of my head, but should be
close...

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/index.aspx - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
 
J

Jonathan Schwarz via DotNetMonster.com

Thank You!!!

Any hope of learning how to update that field in a similar fashion but by
using
Dim filename As String = Path.GetFileName(postedFile.FileName)

to get the actual filename of a file that is being uploaded?
 
K

Karl Seguin

Jonathan, you really should look into the link I provide or simply look for
other free online tutorials (or buy a book)....

dim connection as new SqlConnection("CONNECTION_STRING")
dim command as new SqlCommand("update mei_cc_file set filename = @filename
where id = @id", connection)
command.Parameters.Add("@Id", SqlDbType.Int).Value = 1
command.PArameters.Add("@FileName", SqlDbType.VarChar, LENGTH).Value =
filename
try
connection.open()
command.ExecuteNonQuery()
finally
connection.dispose()
command.dispose()
end try

replace Length with the length of the data colum field...

Karl


--
MY ASP.Net tutorials
http://www.openmymind.net/index.aspx - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
 
J

Jonathan Schwarz via DotNetMonster.com

Karl,

Thank You for your help. You are absolutley correct. I need to spend some
time on this. I will be looking for a few good books (recommendations
welcome - remember... not much vb background here - former Cold Fusion guy)
.. I did spend some time at that url you provided, it was helpful.

I wonder if you might take a look at what I have tried and see if you can
tell me what I am doing stupidly. I jsut can't get the update to work.
You'll not I have chnaged a few things from what you had initially
suggested for the update, until I get more founded in this language, I do
need to finish this thing via trial by fire and its not very fun (esp with
..net error messages).

<%@ Import namespace="System.IO"%>
<html>
<head>
<title>Uploading a File</title>
<script language="VB" runat="server">

Dim savePath As String = "D:\Customers\abc\hr\"
Sub Upload_Click(source As Object, e As EventArgs)

If Not (uploadedFile.PostedFile Is Nothing) Then
Try
Dim postedFile = uploadedFile.PostedFile
Dim filename As String = Path.GetFileName(postedFile.FileName)
Dim contentType As String = postedFile.ContentType
Dim contentLength As Integer = postedFile.ContentLength

postedFile.SaveAs(savePath & filename)
message.Text = postedFile.Filename & " uploaded" & _
"<br>content type: " & contentType & _
"<br>content length: " & contentLength.ToString()
Catch exc As Exception
message.Text = "Failed uploading file"
End Try


Dim command As System.Data.SQLClient.SqlCommand
Dim connection as System.Data.SQLClient.SQLConnection

connection = ("server=(local); initial catalog=abc_env; uid=abcd;
pwd=abcde")
command = ("update mei_cc_file set filename = @filename", connection)
command.Parameters.Add("@FileName", SqlDbType.VarChar, 255).Value =
filename
try
connection.open()
command.ExecuteNonQuery()
finally
connection.close()
command = Nothing
end try
End If
End Sub
</script>

</head>
<body>

<form enctype="multipart/form-data" runat="server">
Select File to Upload:
<input id="uploadedFile" type="file" runat="server">
<p>
<input type=button id="upload"
value="Upload"
OnServerClick="Upload_Click"
runat="server">
<p>
<asp:Label id="message" runat="server"/>
</form>

</body>
</html>


Any help woulf be strongly appreciated... and if you ever run into Cold
Fusion and need assistance, let me know! I'd like to return the favor!!
 
K

Karl Seguin

Jonathan:
You didn't tell me what error youa re getting, but my guess is a
NullReference...
you are never creating new instances of the connection and command
objects..simply declaring them and then trying to do stuff with them...

Dim command As System.Data.SQLClient.SqlCommand
Dim connection as System.Data.SQLClient.SQLConnection

needs to change to

Dim command As new System.Data.SQLClient.SqlCommand
Dim connection as new System.Data.SQLClient.SQLConnection

and

connection = ("server=(local); initial catalog=abc_env; uid=abcd;
pwd=abcde")

to

connection.ConnectionString = "server=(local); initial catalog=abc_env;
uid=abcd; pwd=abcde"

Karl



--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
 
J

Jonathan Schwarz via DotNetMonster.com

Karl,

I made the changes you suggested. Still getting error. Unfortunately all
errors are handled by error handler, actual error is posted to an error
log. Here's what it gives me:

2005-03-03 10:26:05 => System.Web.HttpCompileException: External component
has thrown an exception. at
System.Web.Compilation.BaseCompiler.ThrowIfCompilerErrors(CompilerResults
results, CodeDomProvider codeProvider, CodeCompileUnit sourceData, String
sourceFile, String sourceString) at
System.Web.Compilation.BaseCompiler.GetCompiledType() at
System.Web.UI.PageParser.CompileIntoType() at
System.Web.UI.TemplateParser.GetParserCacheItemThroughCompilation()

any of this make sense to you?

my current code:


<%@ Import namespace="System.IO"%>
<html>
<head>
<title>Uploading a File</title>
<script language="VB" runat="server">

Dim savePath As String = "D:\Customers\miller-env\miller_hr\"
Sub Upload_Click(source As Object, e As EventArgs)

If Not (uploadedFile.PostedFile Is Nothing) Then

Try
Dim postedFile = uploadedFile.PostedFile
Dim filename As String = Path.GetFileName(postedFile.FileName)
Dim contentType As String = postedFile.ContentType
Dim contentLength As Integer = postedFile.ContentLength

postedFile.SaveAs(savePath & filename)
message.Text = postedFile.Filename & " uploaded" & _
"<br>content type: " & contentType & _
"<br>content name: " & filename & _
"<br>content length: " & contentLength.ToString()
Catch exc As Exception
message.Text = "Failed uploading file"
End Try

Dim command As new System.Data.SQLClient.SqlCommand
Dim connection as new System.Data.SQLClient.SQLConnection


connection.ConnectionString = ("server=(local); initial
catalog=miller_env; uid=miller; pwd=wat3rt3st")
command = ("update mei_cc_file set filename = @filename", connection)
command.Parameters.Add("@FileName", SqlDbType.VarChar, 255).Value =
filename
try
connection.open()
command.ExecuteNonQuery()
finally
connection.close()
command = Nothing
end try


End If
End Sub
</script>

</head>
<body>

<form enctype="multipart/form-data" runat="server">
Select File to Upload:
<input id="uploadedFile" type="file" runat="server">
<p>
<input type=button id="upload"
value="Upload"
OnServerClick="Upload_Click"
runat="server">
<p>
<asp:Label id="message" runat="server"/>
</form>

</body>
</html>
 
K

Karl Seguin

Jonathan:
Not sure about that error...but there are a number of issues with your
code...not sure if your just sending me a sample...

<%@ Import namespace="System.IO"%>
<%@ Import namespace="System.Data"%>
<html>
<head>
<title>Uploading a File</title>
<script language="VB" runat="server">

Dim savePath As String = "D:\Customers\miller-env\miller_hr\"
Sub Upload_Click(source As Object, e As EventArgs)

If Not (uploadedFile.PostedFile Is Nothing) Then
dim filename as string = nothing
Try
Dim postedFile = uploadedFile.PostedFile
filename = Path.GetFileName(postedFile.FileName)
Dim contentType As String = postedFile.ContentType
Dim contentLength As Integer = postedFile.ContentLength

postedFile.SaveAs(savePath & filename)
message.Text = postedFile.Filename & " uploaded" & _
"<br>content type: " & contentType & _
"<br>content name: " & filename & _
"<br>content length: " & contentLength.ToString()
Catch exc As Exception
message.Text = "Failed uploading file"
End Try

Dim command As new System.Data.SQLClient.SqlCommand
Dim connection as new System.Data.SQLClient.SQLConnection


connection.ConnectionString = ("server=(local); initial catalog=miller_env;
uid=miller; pwd=wat3rt3st")
command.CommandText = "update mei_cc_file set filename = @filename"
command.Connection = connection
command.Parameters.Add("@FileName", SqlDbType.VarChar, 255).Value = filename
try
connection.open()
command.ExecuteNonQuery()
finally
connection.close()
command = Nothing
end try


End If
End Sub
</script>

--
MY ASP.Net tutorials
http://www.openmymind.net/ - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)
 
J

Jonathan Schwarz via DotNetMonster.com

Karl,

Thanks for all your help! I have been able to limp through to a working
solution, thanks to your help! Now that its done, I will try to spend
sometime learning more about this language.

Thanks again

-Jon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,900
Latest member
Nell636132

Latest Threads

Top