joe_pool_is

Typically, I build my web applications using the connection to "localhost", and after it works I copy the project files to the website where they are needed. Sometimes (like now), the projects on the web do not quite act the same as they do from "localhost" (specifically, I can not call the same mail server). I tried to open the file on the website with "Open Project > http://www.mydomain.com/aspnet_client" where my project file shows up after I enter the Username/Password. Great! Everything seem to be working. But when I double-click on the solution file to open it, I get the error "The selected file is not a valid Visual Studio solution file." What causes this? How can I get around it? I even tried FTPing the files directly to the site, just in case MS's Visual Studio was uploading special versions.

Thanks Joe Mamma. I got it to working. The code didn't like your Convert.DBNull, and I couldn't get it to accept ... anything else. So, instead, I constructed each field separately, since there were only 4 anyway. Thanks for your help. Case closed.

I used your code almost to the letter, since it fit so well with mine. So, yes, the connection is opened via your Conn.Open() line. I was thinking that maybe the Command1.Parameters needed to be innitialized first, similar to dimensioning an array before adding elements to it. Could this be the case? I still don't understand why Access doesn't like my SQL statement when run through VB.NET. It works fine when I create an Insert Query (other than those two MessageBoxes).

Lots of ideas and suggestions. Let me answer them each in turn: 1) eramgarden: I am running a MS Access database. I was able to create an INSERT query and run it directly from the MS Access program (after changing the Date column to read ClickDate). When it ran, I was first notified that I was about to run an append query that would modify the data in the table, which I had to either confirm was okay or abort. After confirming, I got a second MessageBox telling me that I was about to append 1 row, which I also had to confirm that this is what I wanted to do. After confirming those two MessageBoxes, I opened my table, and my data was there. Do I need something in my VB.NET program that responds to these Microsoft MessageBoxes??? If so, how would I do that? It looks like my SQL statement works. 2) PlausiblyDamp: I changed the Date keyword to ClickDate in the database and in the SQL command. I still get "Syntax error in INSERT INTO statement." Maybe this is a MS Access database problem instead of an error in my SQL statement, but I don't know what that would be. Any ideas? 3) Joe Mamma: Your idea looked interesting, but I tried it last (since it required the greatest change to my code). When the program got to the point where it needed to fill the command parameters, it bombed. Running the "Case Else" Select routine, Command1.Parameters(0).Value = strName(0) produced the following Exception in the "Command Window - Immediate": ?ex.Message "Invalid index 0 for this OleDbParameterCollection with Count=0." Your version does look interesting, and I would like to impliment it. Do you have any idea what this error is and how to patch it? I'm still working on this problem. If anyone has any additional input or suggestions, I am all ears.

This is driving me nuts. Code looks good. Database appears to match. SQL statement looks good. Still, I get: In the Immediate Window: ?sql "INSERT INTO Legal1 (FirstName, MiddleName, LastName, Date, Signature) VALUES ('Timmy', 'Joe', 'Pool, Jr.', '8/10/2004 5:39:47 PM', 'Poojo');" ?ex.Message "Syntax error in INSERT INTO statement." My database is attached, and my code is here: Dim i As Integer = 0 Dim strDB As String, strConnection As String Dim strName() As String strName = txtPurchaserName.Text.Split(Convert.ToChar(" ")) strDB = Server.MapPath("database.mdb") strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB & ";Persist Security Info=False" Select Case UBound(strName, 1) Case 0 ' // Only one word entered. Used as Last name SQL = "INSERT INTO Legal1 (LastName, Date, Signature) " & _ "VALUES ('" & strName(0) & "', '" & _ CStr(DateTime.Now) & "', '" & txtPurchaserSign.Text & "');" Case 1 ' // assumes First and Last names entered SQL = "INSERT INTO Legal1 (FirstName, LastName, Date, Signature) " & _ "VALUES ('" & strName(0) & "', '" & strName(1) & "', '" & _ CStr(DateTime.Now) & "', '" & txtPurchaserSign.Text & "');" Case 2 ' // assumes First, Middle, and Last names entered SQL = "INSERT INTO Legal1 (FirstName, MiddleName, LastName, Date, Signature) " & _ "VALUES ('" & strName(0) & "', '" & strName(1) & "', '" & strName(2) & "', '" & _ CStr(DateTime.Now) & "', '" & txtPurchaserSign.Text & "');" Case Else ' // assumes First, Middle, Last and prefixes entered For i = 3 To UBound(strName) ' // appends the rest to the Last name strName(2) &= " " & strName(i) Next SQL = "INSERT INTO Legal1 (FirstName, MiddleName, LastName, Date, Signature) " & _ "VALUES ('" & strName(0) & "', '" & strName(1) & "', '" & strName(2) & "', '" & _ CStr(DateTime.Now) & "', '" & txtPurchaserSign.Text & "');" End Select Try Command1 = New OleDbCommand Command1.Connection = New OleDbConnection(strConnection) Command1.Connection.Open() Command1.CommandText = SQL Command1.ExecuteNonQuery() Command1.Connection.Close() Catch ex As Exception ' // there were errors Command1.Connection.Close() lblError.Visible = True lblError.Text = "Unable to save/update signature and date to database. Please contact Webmaster." Exit Sub End Try It bombs on "Command1.ExecuteNonQuery()" every time with the message listed at the top. Could anyone help me? It's probably a stupid mistake. Maybe a type, but I just can't see it. database.zip

Ilya, Thanks for the reply; I was beginning to believe that no one knew the answer on here. From what I understand from you, I only need one version or the other. Is this correct? While waiting for a response, I went through my code trying my best to modify it according to the Microsoft example (even though my version required *much* more complexity than their example gave). What I came up with is listed below, which I envite you (and anyone else) to comment on. A few notes: 1) The function objDLD.IFF(StatementToCompare, ReturnTrue, ReturnFalse) is something short I wrote that takes in a StatementToCompare and returns ReturnTrue if the StatementToCompare is True, or returns ReturnFalse if StatementToCompare is False. (I think this is built into VB somewhere, but I can't ever get the built in version to work for me) 2) It looks as if I am being redundant in my SQL setup, but this was the way the example showed me. I didn't understand, but it seems to work. I just worked with it. Private Function WriteToDB(ByVal FirstName As String, ByVal LastName As String, ByVal Phone As String, ByVal ZIP As String, Optional ByVal Email As String = "") As Boolean Dim i As Integer Dim Item As ListItem Dim objDLD As New DropListData Dim SQLsnub As String = "" Dim SQL As String = "" Dim strDB As String = Server.MapPath("database.mdb") Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB & ";Persist Security Info=False" Dim Connection1 As OleDbConnection = New OleDbConnection(strConnection) Dim Adapter1 As New OleDbDataAdapter("SELECT * FROM Customer1", Connection1) Dim Insert1 As New OleDbCommand ' Create a DataTable and DataRow Dim DataTable1 As New DataTable Adapter1.Fill(DataTable1) Dim DataRow1 As DataRow ' Command to Insert Records Insert1.CommandText = "INSERT INTO Customer1 (FirstName, LastName, Address, City, State, Zip, Phone, " & _ "IDType, IDNum, DOB, ServAddr, ServCity, ServState, ServZip, " & _ "SSN, Email, CableTV, SatTV, Electric, IAuto, IHome, ILife, " & _ "IMed, IRent, CallID, CallWait, CallFWD, WaitID, 3Way, " & _ "VM, LongDist, DialUp, DSL) " & _ "VALUES " & _ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" Insert1.Connection = Connection1 Insert1.Parameters.Add(New OleDbParameter("FirstName", OleDbType.VarChar, 50, "FirstName")) Insert1.Parameters.Add(New OleDbParameter("LastName", OleDbType.VarChar, 50, "LastName")) Insert1.Parameters.Add(New OleDbParameter("Address", objDLD.IFF(chkUseSame.Checked, txtAddress2.Text, txtAddress1.Text))) Insert1.Parameters.Add(New OleDbParameter("City", objDLD.IFF(chkUseSame.Checked, txtCity2.Text, txtCity1.Text))) Insert1.Parameters.Add(New OleDbParameter("State", objDLD.IFF(chkUseSame.Checked, ddlState2.SelectedItem.Text, ddlState1.SelectedItem.Text))) Insert1.Parameters.Add(New OleDbParameter("Zip", objDLD.IFF(chkUseSame.Checked, txtZip2.Text, txtZip1.Text))) Insert1.Parameters.Add(New OleDbParameter("Phone", OleDbType.VarChar, 50, "Phone")) Insert1.Parameters.Add(New OleDbParameter("IDType", OleDbType.VarChar, 50, "IDType")) Insert1.Parameters.Add(New OleDbParameter("IDNum", OleDbType.VarChar, 50, "IDNum")) Insert1.Parameters.Add(New OleDbParameter("DOB", OleDbType.VarChar, 50, "DOB")) Insert1.Parameters.Add(New OleDbParameter("ServAddr", OleDbType.VarChar, 50, "ServAddr")) Insert1.Parameters.Add(New OleDbParameter("ServCity", OleDbType.VarChar, 50, "ServCity")) Insert1.Parameters.Add(New OleDbParameter("ServState", OleDbType.VarChar, 50, "ServState")) Insert1.Parameters.Add(New OleDbParameter("ServZip", OleDbType.VarChar, 50, "ServZip")) Insert1.Parameters.Add(New OleDbParameter("SSN", OleDbType.VarChar, 50, "SSN")) Insert1.Parameters.Add(New OleDbParameter("Email", OleDbType.VarChar, 50, "Email")) Insert1.Parameters.Add(New OleDbParameter("CableTV", objDLD.IFF(Me.chkCableTV.Checked, True, False))) Insert1.Parameters.Add(New OleDbParameter("SatTV", objDLD.IFF(Me.chkSatelliteTV.Checked, True, False))) Insert1.Parameters.Add(New OleDbParameter("Electric", objDLD.IFF(Me.chkElectric.Checked, True, False))) Insert1.Parameters.Add(New OleDbParameter("IAuto", objDLD.IFF(Me.chkAuto.Checked, True, False))) Insert1.Parameters.Add(New OleDbParameter("IHome", objDLD.IFF(Me.chkHomeOwners.Checked, True, False))) Insert1.Parameters.Add(New OleDbParameter("ILife", objDLD.IFF(Me.chkLife.Checked, True, False))) Insert1.Parameters.Add(New OleDbParameter("IMed", objDLD.IFF(Me.chkMedical.Checked, True, False))) Insert1.Parameters.Add(New OleDbParameter("IRent", objDLD.IFF(Me.chkRenters.Checked, True, False))) For Each Item In lstLocalPhone.Items Select Case Item.Text Case "Caller ID" Insert1.Parameters.Add(New OleDbParameter("CallID", objDLD.IFF(Item.Selected, True, False))) Case "Call Waiting" Insert1.Parameters.Add(New OleDbParameter("CallWait", objDLD.IFF(Item.Selected, True, False))) Case "Call Forwarding" Insert1.Parameters.Add(New OleDbParameter("CallFWD", objDLD.IFF(Item.Selected, True, False))) Case "Call Waiting ID" Insert1.Parameters.Add(New OleDbParameter("WaitID", objDLD.IFF(Item.Selected, True, False))) Case "3-Way Calling" Insert1.Parameters.Add(New OleDbParameter("3Way", objDLD.IFF(Item.Selected, True, False))) Case "Voicemail" Insert1.Parameters.Add(New OleDbParameter("VM", objDLD.IFF(Item.Selected, True, False))) End Select Next Insert1.Parameters.Add(New OleDbParameter("LongDist", OleDbType.Boolean, 1, "LongDist")) Insert1.Parameters.Add(New OleDbParameter("DialUp", OleDbType.Boolean, 1, "DialUp")) Insert1.Parameters.Add(New OleDbParameter("DSL", OleDbType.Boolean, 1, "DSL")) Adapter1.InsertCommand = Insert1 ' Add rows to DataTable1 DataRow1 = DataTable1.NewRow DataRow1("FirstName") = FirstName DataRow1("LastName") = LastName DataRow1("Address") = objDLD.IFF(chkUseSame.Checked, txtAddress2.Text, txtAddress1.Text) DataRow1("City") = objDLD.IFF(chkUseSame.Checked, txtCity2.Text, txtCity1.Text) DataRow1("State") = objDLD.IFF(chkUseSame.Checked, ddlState2.SelectedItem.Text, ddlState1.SelectedItem.Text) DataRow1("Zip") = objDLD.IFF(chkUseSame.Checked, txtZip2.Text, txtZip1.Text) DataRow1("Phone") = Phone DataRow1("IDType") = ddlIDType.SelectedItem.Text DataRow1("IDNum") = txtIDNumber.Text DataRow1("DOB") = DOB DataRow1("ServAddr") = txtAddress1.Text DataRow1("ServCity") = txtCity1.Text DataRow1("ServState") = ddlState1.SelectedItem.Text DataRow1("ServZip") = txtZip1.Text DataRow1("SSN") = txtSSN.Text DataRow1("Email") = Email DataRow1("CableTV") = objDLD.IFF(Me.chkCableTV.Checked, True, False) DataRow1("SatTV") = objDLD.IFF(Me.chkSatelliteTV.Checked, True, False) DataRow1("Electric") = objDLD.IFF(Me.chkElectric.Checked, True, False) DataRow1("IAuto") = objDLD.IFF(Me.chkAuto.Checked, True, False) DataRow1("IHome") = objDLD.IFF(Me.chkHomeOwners.Checked, True, False) DataRow1("ILife") = objDLD.IFF(Me.chkLife.Checked, True, False) DataRow1("IMed") = objDLD.IFF(Me.chkMedical.Checked, True, False) DataRow1("IRent") = objDLD.IFF(Me.chkRenters.Checked, True, False) For Each Item In lstLocalPhone.Items Select Case Item.Text Case "Caller ID" DataRow1("CallID") = objDLD.IFF(Item.Selected, True, False) Case "Call Waiting" DataRow1("CallWait") = objDLD.IFF(Item.Selected, True, False) Case "Call Forwarding" DataRow1("CallFWD") = objDLD.IFF(Item.Selected, True, False) Case "Call Waiting ID" DataRow1("WaitID") = objDLD.IFF(Item.Selected, True, False) Case "3-Way Calling" DataRow1("3Way") = objDLD.IFF(Item.Selected, True, False) Case "Voicemail" DataRow1("VM") = objDLD.IFF(Item.Selected, True, False) End Select Next DataRow1("LongDist") = objDLD.IFF(Me.chkLongDistance.Checked, True, False) DataRow1("DialUp") = objDLD.IFF(Me.chkDialUp.Checked, True, False) DataRow1("DSL") = objDLD.IFF(Me.chkDSL.Checked, True, False) DataTable1.Rows.Add(DataRow1) ' Delegate for Handling RowUpdated event AddHandler Adapter1.RowUpdated, AddressOf HandleRowUpdated Try Command1 = New OleDbCommand Command1.CommandText = "SELECT @@IDENTITY" ' // gets the ID number of record added Command1.Connection = Connection1 Command1.Connection.Open() Adapter1.Update(DataTable1) ' // gnabs our ID while writing the data to DB Command1.CommandText = "DROP TABLE Customer1" Command1.ExecuteNonQuery() Command1.Connection.Close() Return True Catch ex As Exception ' // there were errors lblError.Visible = True lblError.Text &= "Database Save Error." exMsg = ex.Message Command1.Connection.Close() txtClientID.Text = "Error" Return False End Try 'txtClientID.Text = Reader1.Item("ID").ToString End Function

I am trying to use a DataTable as my method of writing to my Access database. I found an example on MSDN (#815629) with the following: cmdInsert.CommandText = "INSERT INTO AutoIncrementTest (Description) VALUES (?)" cmdInsert.Connection = cnJetDB cmdInsert.Parameters.Add(New OleDbParameter("Description", OleDbType.VarChar, 40, "Description")) oleDa.InsertCommand = cmdInsert ' were oleDa = OleDbDataAdapter Their example is *much* simpler than what I need. Further, their example confuses me on a couple of points: 1) In "cmdInsert.Parameters.Add", there are two "Description" values. Which is the one being read into the "cmdInsert.CommandText" value "(Description)"? 2) Must I add one of these new OleDbParameters for each of the 38 different Parameters that I have in my Table? (Lord, I hope not!) Thanks for helping.

Derek: I've run into something with your version. I left the full SQL string off (because it is long and boring), but the rest looks like this: Dim SQL as String = "INSERT INTO Table1 (...) VALUES (...)" Dim Reader1 as OleDb.OleDbDataReader Command1 = New OleDbCommand Command1.Connection = New OleDbConnection(strConnection) Command1.Connection.Open() Command1.CommandText = SQL Command1.ExecuteNonQuery() Command1.CommandText = "SELECT @@IDENTITY" ' get ID of record Reader1 = Command1.ExecuteReader Command1.Connection.Close() There is no Exception thrown, but on my Reader1 properties, I have: Reader1 Depth - 0 FieldCount - 1 HasRows - True IsClosed - False Item - <cannot view indexed property> RecordsAffected - 0 What do I need to read in? How do I get my ID number off of this data reader?

Derek: Wow! That's short. I like it! VBAHole22: You version looks like it might work too, but I'm going with "short and sweet" first! :)

Cool. Thanks.

After populating a database with new/updated information, what is the best way to get the ID number for that record? Background: I am using an Access database; the ID number is the Primary Key; and I want to store this to use in my next set of options.

TwistedNerve and Derek Stone: I have always used Server.Transfer() to load the next page. Is there another (or better) way? I would be interested to learn how and when to use one method over another.

From a form, how can I carry the data from one Form (say a Customer's Name) to another Form? Example: From Form1: (assume txtName.Text = "Herman Munster") Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click Server.Transfer("Form2.aspx") End Sub From Form2: Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load lblName.Text = "" ' Can't assign to Form1.txtName.Text. ' So how would I do this? End Sub

How embarrassing. Thanks.

my 2 cents I'm a newbie, too. But here's an idea: Part of your SQL statement reads: " ... VALUES ( '2' , 'line_string' , 'sex_string' , 'tel_number_string' , 'callers_name_String' , 'callers_location_string' , 'topic1_string' , 'topic2_string' , 'graphic1_string' , 'time_called_string')" My question is: Are you trying to insert the value 'line_string' into your database, or the variable held by it (i.e. is line_string = "One happy day")? If line_string is a string variable, you would need to write your SQL statement kinda like this: VALUES ( '2' , '" & line_string & "' , '" & sex_string & "' , ... Catch my drift? This is VB.NET version, though. If you are using C#, it is probably a little different. Good luck!

I am testing my VB.NET database utility by entering the same basic information for name, address, and such into an Access database. This SHOULD NOT be a problem, because my database's indexed fields are all set to "No" (except for the ID = Primary Key). Still, when I execute "Command1.ExecuteNonQuery()", I get this Exception thrown: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." How are my database fields set incorrectly? I would like to change the indexed properties on some of these fields to "Yes (Duplicates OK)", but right now I can't even get "No" duplicates to work! Aaaaagh!

If anyone is still curious about this, the case is closed. It was a boneheaded mistake. At some point along the way, I tried to make it so that one of the CheckBoxes would call Server.Transfer() back to itself. With the CheckBox checked every time, this was causing the Page_Load to fire. I had the hardest time figuring that out, though. I even re-wrote the entire webpage, but copied in my VB.NET code (which imported the same error into my new page - Agh!) Thanks to everyone that contributed and spent time looking for my goof-ups.

I think that will fix it. I see now where I have my Text field for State set to 2 characters. Doh! Thanks, PlausiblyDamp!

I am collecting data from a form (finally got the sucker to work!) and posting that data into a small MS Access database. My SQL statement is: INSERT INTO [/font] [font=Tahoma]Customer1([/font] [font=Tahoma]FirstName, LastName, Address, City,[/font] [font=Tahoma]State, Zip, Phone, IDType,[/font] [font=Tahoma]IDNum, DOB, ServAddr, ServCity,[/font] [font=Tahoma]ServState, ServZip, SSN, Email,[/font] [font=Tahoma]CableTV, SatTV, Electric, IAuto,[/font] [font=Tahoma]IHome, ILife, IMed, IRent,[/font] [font=Tahoma]CallID, CallWait, CallFWD, WaitID,[/font] [font=Tahoma]3Way, VM, LongDist, DialUp,[/font] [font=Tahoma]DSL)[/font] [font=Tahoma]VALUES ([/font] [font=Tahoma]'Joe', 'Pool', '1819 Wisteria Road', 'Richardson',[/font] [font=Tahoma]'TX - TEXAS', '75080', '972-555-0881', 'Military',[/font] [font=Tahoma]'888', '09/21/1979', '1819 Wisteria Road', 'Richardson',[/font] [font=Tahoma]'TX - TEXAS', '75080', '123456789', 'jp8mail-spam@yahoo.com',[/font] [font=Tahoma]False, False, False, True,[/font] [font=Tahoma]False, False, False, True,[/font] [font=Tahoma]True, False, False, False,[/font] [font=Tahoma]False, False, True, False,[/font] [font=Tahoma]False); When I tried my ExecuteNonQuery() call, I got the following Exception: ?ex.Message[/font] [font=Tahoma]"The field is too small to accept the amount of data you attempted to add. [/font] [font=Tahoma]Try inserting or pasting less data." What does this mean? What "field"? Does Access only accept SQL strings of a certain length? Is there a better way to add this single record? Now, in my Access database, I do have some fields set to certain formats: Phone's format is "(999) 999-9999" and SSN's format is "999-99-9999". Other formats are either Text fields or Yes/No (boolean) fields. Could these formats be causing problems? Does Access require Yes for True and No for False? Thanks for helping!

Still need help on this, please. I noticed while debugging that there are these freaky "anonymous code" files that are showing up in my signup.aspx form. What are these? Are these causing my problems? (See attachment Image2.gif)

I have been spending more hours trying to debug this, and I have noticed something that may mean something: My form uses ICollections to populate State and date fields. I have noticed that if I try simply clicking the "Submit" button *without* filling in any of the fields on the form, my program goes straight to the "Submit.Click" subroutine, instead of going to the "Page.Load" subroutine first. Does this help? Does this clue offer any insight? Is there something about selecting an item in an ICollection that tells the form to go through the "Page.Load" subroutine? If so, how could I eliminate this?

The SignUp.aspx form still does it. First, I set AutoEventWireup="false" and included the "Handles MyBase.Load" the next time I tried it the other way. Both times it still messes up. When I click Submit, it goes to the Page_Load function and skips over the "If Not Page.IsPostback Then". Then, it immediately runs Page_Load again, but this time it *does* go into the "If Not Page.IsPostback Then" section of my code. What is going on? Should I just restart from scratch?

Where do certificates come from? Are they inserted or declared in the code somewhere? If so, the HTML or the ASP.NET code? Is the certificate something that gets stored on the server?

PlausiblyDamp, I got the AutoEventWireup=false part set. I believe that was mistakenly set while trying to debug the application earlier. As for the "Handles MyBase.Load" declaration at the end of the page load function, isn't this required by .NET? I tried removing that piece, but now the load portion does not execute at all. How do I work around that? Thanks for your help, too!

I have been trying to learn how to establish a secure connection for one of our webforms. Everything I have found seems to be very involved and lengthy, but all I am interested in is how to tell .NET to turn the secure connection "ON" when I need it, and "OFF" when I am finished. Could someone give me a rundown of the basics? Example: PageA.aspx is Non-Secure. The OK button on it directs the customer to Secure PageB.aspx (in the same folder). PageB.aspx directs the customer to a third Non-Secure PageC.aspx when PageB.aspx's OK button is clicked.