dakota97

Hi all, Can anyone give me an idea of what the going rate for converting an Access 97 Database, with a user interface, modules, etc. to a working .Net application would be? The reason I'm asking is b/c I'm doing some sub-contracting work for a company, and right now their whole accounting package is comprised of a database that was written over the years as different needs arose. The DB is decently complex from what I can see. However, the main issue that I see could potentially be a problem is that their server is in desperate need of an upgrade, and they've had issues with viruses and corrupted files. The way the DB works is that there are 2 copies (one on the server and one on the user's machine) and the tables are linked from one to the other. With an average of 4-7 people working in the DB at one time, and each having a seperate copy open, it seems to me that: 1.) The network is going to significantly slow down 2.) The DB could potentially be corrupted if one of the user's machines has issues Any input would be greatly appreciated, as they have also mentioned the possibility of purchasing a software package that does everything that this DB does. However, there again, I don't think they're going to be able to find a package to meet every need, or they're going to be spending a lot more than if I would convert everything myself. What do you think? Chris

Thanks Robby, Right now I'm using classic ASP b/c for some reason I can't get an ASP.Net web app to run on my development PC. I just installed it on here a few weeks ago, and this is the first web app I'm doing, so I'm sure it's just something stupid. However, that worked perfectly with the single quotes Thanks, Chris

Hi all, Ok, I've got a strange problem happening here. I'm making a product selector for our company so that customers can select certain specs and show models meeting those specs. The whole process is spread over 6 pages, with each page being setup as a form which retrieves the information from the previous page via the request.form() process, and the request.cookies() process. THe problem is that the drop-down menus are populated with the field values exactly as they are entered. However, if the field value is made of up two or more words (with spaces), whenever I use either of the two requests I only get the letters/numbers before the first space. This is throwing everything else off because my queries are setup to use the information from the request commands. The drop-downs are populated from an array that I've removed duplicate entries and split back up in order to get only the pertinent information. The following is an example of the last part of the code that I use to populate the drop-down menus: <select name="capacity" onChange="this.form.submit();"> <% Dim FinalArray varname = RemoveDuplicates (strRotorType, "|") FinalArray = Split(varname, "|") for i = 0 to ubound(FinalArray) %> <option value=<%response.write(FinalArray(i))%>><%response.write(FinalArray(i))%></option> </select> Now, if the capacity is "6 tubes" the drop-down menu shows "6 tubes" But, when I do a request.form("capacity") on the next page, it only shows "6". This in turn throws off my DB query, because no models have "6" as a valid field value for capacity. Any ideas about what's going on? Any help would be greatly appreciated, as I'm pressed for time on getting this project done. Thanks in advance, Chris

Hi all, Is it possible to connect to a database that is stored on a web server? What I'd like to do is create a program that our customers can download which connects to an Access database stored on our website. I know how to connect to a database on a stand-alone computer and across an internal network to a server, but is it possible to connect to one over the internet? Thanks in advance, Chris

Hi all, How can I print a simple horizontal line across the entire width of the page? What I'm trying to do is create a .txt file that has information from the DB and some of the controls on my form. The infomration needs to be separated on the printed page, so all I need is to be able to draw a horizontal line accross the page. Thanks in advance, Chris

Aspnot: I'm using Access as my DB. I know that there are some weaknesses in using Access as oposed to MySQL, but for my purpose it suits just fine. Roey: Thanks for the info. I'll check that book out, as I would like to learn more about using Crystal. From what I hear, it definitely is a powerful tool. And since it does come with VS.Net, why not use it right?

Thanks ASPnot for the reply. I took your advice and created the 2 tables. I also created a quesry in the DB with the following SQL: SELECT tblOrderDetails.Order_Id, tblOrderDetails.ProdID, tblOrderDetails.QtyOrdered, tblOrderDetails.SalesPrice FROM tblOrderDetails INNER JOIN tblOrders ON tblOrderDetails.Order_Id = tblOrders.Order_ID; First questions, is this right? Second question, when the user clicks the chekcout button and I add the information to the DB, do I have to add it to both tables or will the one be updated by the other? THanks again for the reply, Chris

I'd like to use Crystal, but as I stated I've never really used it before, so I don't know where to start. My first idea was to have a table in my DB for all of the transactions, with each record having the transaction number, item number, item price, customer account, etc. The only thing is there would be thousands of records (one for each item on each transaction). For example, if transaction number 001 had 10 items, there would be 10 individual records added to the DB when the transaction was completed. The only downfall that I can see to this is that when the user runs a report on all sales (i.e. say for the last month), I'm afraid the program is going to take to long to search through the DB for the appropriate records. Any ideas on how I can do this? I'm up for anything as I've been trying to figure this one out for some time now. Thanks, Chris

Anyone? Attached here is an example of what I want to do. This is the receipt that I made in Excel.Receipt.zip

Hi all, Ok, what I'm doing here is using VB.Net to create a point-of-sale program for retail use. Everything is going good until I get to the point of printing out a receipt for the customer. My first thought was to update a pre-made Excel spreadsheet, and use that as a receipt. It works good, however in order for the program to work, the end-user would need to have Excel installed. Since not everyone might have Excel, I'm limited on the customers that can purchase my software. My next thought was to use Crystal Reports to generate an invoice, however I've never used Crystal, so I don't even know where to start. Can anyone guide me in the right direction with some code samples or maybe some websites that are tailored to making customized invoices/reports so that anyone can install and use this program? Basically my order form consists of some text boxes, a listbox, and a few variables to pass information such as the transaction number, etc. Thanks in advance, Chris

Ok, but how would I do it in VB.Net?

Hi all, I'm creating a timeclock program, and having a problem calculating the total time that an employee works. I have a form that retrieves the employees information from the database and fills a dataset. The dataset is then displayed in a datagrid. The fields that are displayed are the Date, Start Time, End Time, and Hours worked. What I want to do is calculate the Total Hours worked and display it in a textbox. How can I do that? My code is as follows: ' Attempt to retrieve the data from the database based on the SSN entered and the date range Try strSQL = "SELECT TimeIn.DateIn, Format([TimeIn.StartTime], 'hh:mm') as StartTime, Format([TimeIn.EndTime], 'hh:mm') as EndTime, Format([TimeIn.EndTime]-[TimeIn.StartTime], 'hh:mm') as HoursWorked " strSQL = strSQL & "FROM Employees INNER JOIN TimeIn ON Employees.EmpID = TimeIn.EmpID " strSQL = strSQL & "WHERE (((TimeIn.SSN)=" & "'" & strEmployee & "'" & ") AND ((TimeIn.DateIn) Between #" & strStart & "# And #" & strEnd & "#));" strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & "\timeclock.mdb" Dim myCnn As New OleDbConnection(strConnection) Dim myDA As New OleDbDataAdapter(strSQL, myCnn) myCnn.Open() ' Fill the dataset with the data retrieved from the database Dim ds As New DataSet myDA.Fill(ds, "TimeIn") ' Populate the datagrid with the information in the dataset dgTimeView.DataSource = ds dgTimeView.DataMember = "TimeIn" ' Close the connections, and set all values to nothing myCnn.Close() myCnn.Dispose() myDA.Dispose() ds.Dispose() strSQL = "" ' If there is a problem with the data retrieved from the DB, display the error ' information in Label1 so the user will know what happened Catch ex As Exception Dim strMessage As String strMessage = ex.Message.ToString strMessage = strMessage & " " & ex.ToString Label1.Text = strMessage End Try Thanks in advance, Chris

Hi all, I'm having a problem inserting a functin into an Excel worksheet. What I'm doing is taking information from my DB and inserting it into an Excel worksheet. I'm able to get the values for each field and transfer them successfully, however the problem I'm running into is inserting a function to calculate the total of a column in the worksheet once the data is inserted. My code is as follows: 'Copy the ProductSales.xls file to the REPORTS folder, and rename it 'according to the transaction ID number Dim strPath As String = Application.StartupPath() If Not IO.Directory.Exists(strPath & "\Reports") Then IO.Directory.CreateDirectory(strPath & "\Reports") End If Dim strDate As String = Date.Now.ToShortDateString strDate = strDate.Replace("/", "-") Dim strNewFile As String = strPath & "\Reports\DailySales" & strDate & ".xls" Try strTransNum = DateTime.Now.ToString("MM/dd/yy") & "0001" strTransNum = Convert.ToDecimal(strTransNum.Replace("/", "")) If strTransNum.Length < 10 Then strTransNum = "0" & strTransNum End If 'Retrieve the product information from tblTransaction DbConn.Open() Dim DbCommand As New OleDbCommand("SELECT * FROM tblTransaction WHERE TransId >='" & strTransNum & "'", DbConn) Dim DbReader As OleDbDataReader = DbCommand.ExecuteReader If DbReader.HasRows Then IO.File.Copy("DailySales.xls", strNewFile) 'Open the new .xls file and update the cells with the information 'for the report Dim xlsConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strNewFile & ";" & _ "Extended Properties=""Excel 8.0;HDR=NO""" Dim conn As New System.Data.OleDb.OleDbConnection(xlsConn) conn.Open() Dim XlsCommand As New OleDbCommand Dim i As Integer = 6 Do While DbReader.Read If DbReader("ProdId") Is System.DBNull.Value Then Exit Do Else If i / 56 = 1 Or i / 56 = 2 Or i / 56 = 3 Or i / 56 = 4 Or i / 56 = 5 Or i / 56 = 6 Or i / 56 = 7 Or i / 56 = 8 Or i / 56 = 9 Or i / 56 = 10 Or i / 56 = 11 Or i / 56 = 12 Or i / 56 = 13 Or i / 56 = 14 Or i / 56 = 15 Or i / 56 = 16 Or i / 56 = 17 Or i / 56 = 18 Or i / 56 = 19 Or i / 56 = 20 Then 'Insert the column headings for the next page of the report XlsCommand.CommandText = "UPDATE [sheet1$A" & i & ":A" & i & "] SET F1 ='Item Number'" XlsCommand.Connection = conn XlsCommand.ExecuteNonQuery() XlsCommand.CommandText = "UPDATE [sheet1$B" & i & ":B" & i & "] SET F1 ='Date Sold'" XlsCommand.ExecuteNonQuery() XlsCommand.CommandText = "UPDATE [sheet1$C" & i & ":C" & i & "] SET F1 ='Qty'" XlsCommand.ExecuteNonQuery() XlsCommand.CommandText = "UPDATE [sheet1$D" & i & ":D" & i & "] SET F1 ='Total'" XlsCommand.ExecuteNonQuery() XlsCommand.CommandText = "UPDATE [sheet1$E" & i & ":E" & i & "] SET F1 ='Transaction ID'" XlsCommand.ExecuteNonQuery() XlsCommand.CommandText = "UPDATE [sheet1$F" & i & ":F" & i & "] SET F1 ='Customer Account'" XlsCommand.ExecuteNonQuery() i += 2 Else 'Update the cells with the information for the report XlsCommand.CommandText = "UPDATE [sheet1$A" & i & ":A" & i & "] SET F1 ='" & DbReader("ProdId") & "'" XlsCommand.Connection = conn XlsCommand.ExecuteNonQuery() XlsCommand.CommandText = "UPDATE [sheet1$B" & i & ":B" & i & "] SET F1 ='" & Convert.ToDateTime(DbReader("Timestamp")).ToShortDateString & "'" XlsCommand.ExecuteNonQuery() XlsCommand.CommandText = "UPDATE [sheet1$C" & i & ":C" & i & "] SET F1 ='" & DbReader("Qty") & "'" XlsCommand.ExecuteNonQuery() XlsCommand.CommandText = "UPDATE [sheet1$D" & i & ":D" & i & "] SET F1 ='" & DbReader("Price") & "'" XlsCommand.ExecuteNonQuery() XlsCommand.CommandText = "UPDATE [sheet1$E" & i & ":E" & i & "] SET F1 ='" & DbReader("TransId") & "'" XlsCommand.ExecuteNonQuery() XlsCommand.CommandText = "UPDATE [sheet1$F" & i & ":F" & i & "] SET F1 ='" & DbReader("AccountNum") & "'" XlsCommand.ExecuteNonQuery() i += 1 End If End If Loop '-----RIGHT HERE IS MY PROBLEM!!!---------------------------------------- i += 1 XlsCommand.CommandText = "UPDATE [sheet1$D" & i & ":D" & i & "] SET F1 ='=SUM(D6:D" & i & ")'" XlsCommand.ExecuteNonQuery() '-------------------------------------------------------------------------- conn.Close() XlsCommand.Dispose() DbConn.Close() DbCommand.Dispose() DbReader.Close() What I can see that it is doing is adding a single quote to the text before it enters the string into the cell. I opened up the worksheet after I set a breakpoint, and looked at the value that was entered into the cell. The example that I can give you is as follows: '=SUM(D6:D10) Obviously Excel is interpreting this as a text string and not a function. Any ideas on how I can eliminate the ' at the beginning? Thanks in advance, Chris

Hi all, I just have a quick question about creating real-time apps. I've used VB.Net for all of my apps thus far, and moving onto C# here pretty soon. But, what is the best language (besides JAVA) to write real-time apps? I have a web server that supports all VS.Net, ASP.Net, etc., and I have experience in ASP and VB right now. What would you suggest, and what are the pros and cons that I will be facing? Any input would be greatly appreciated. Thanks, Chris

I get this error An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll Additional information: No data exists for the row/column.

I'm getting the message box stating there are no matches. The only thing is I'm typing the search exactly how it's listed in the DB. This doesn't make any sense to me because I've worked with searching a DB before and never ran into this. I moved the close statements outside of the loop now. Slight oversight on my part that probably would've baffled me later...lol Any suggestions on why this is happening?

All I'm really looking to do is create a program that allows us to search a database for specific information, and display the matching record in a litbox. I work for a sign shop creating vinyl graphics signs. Our art department has managed to save all of the drawings by naming the file something related to the customer, instead of by drawing number (which every drawing has). They've also saved them in 5 or 6 different folders across 2 computers. This makes it very difficult to find a specific drawing, especially since we have drawings dating pre-1998. Our ultimate goals are as follows: 1. Rename all of the drawing files by drawing number, and store them in 1 folder 2. Add information about each drawing to the DB (e.g. Customer, Date, Number, Logos used, etc.) 3. Be able to search the DB based on a text box and combo box (which has the DB fields) 4.) Loop through the DB and add each item found to a listbox, sorted by date (youngest to oldest) BTW...I tried the chaged code, and still get the MyReader error....any other suggestions? Thanks in advance, Chris

techman, When I copied your code into mine I received an error in the execution of the reader. I took out the % symbol, and got the same thing as before with no records. Any other suggestions?

BTW. The code taht sets the value of strSearch is as follows: Private Sub radSearch1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch1.CheckedChanged strSearch = radSearch1.Text ' Sets the value of Search to the radiobutton text value End Sub Private Sub radSearch2_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch2.CheckedChanged strSearch = radSearch2.Text ' Sets the value of Search to the radiobutton text value End Sub Private Sub radSearch3_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch3.CheckedChanged strSearch = radSearch3.Text ' Sets the value of Search to the radiobutton text value End Sub Private Sub radSearch4_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch4.CheckedChanged strSearch = radSearch4.Text ' Sets the value of Search to the radiobutton text value End Sub Private Sub radSearch5_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch5.CheckedChanged strSearch = radSearch5.Text ' Sets the value of Search to the radiobutton text value End Sub Private Sub radSearch6_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radSearch6.CheckedChanged strSearch = radSearch6.Text ' Sets the value of Search to the radiobutton text value End Sub

Hi all, I've got a problem with pulling records from my Access DB, and I can't figure it out. I've checked my field names, table structure, and code, but still can't get it to work. What I have is a form with a text box, 6 radio buttons, a list box, and 2 command buttons. The user types data into the text box, selects a radio button, and clicks a search button. The program then searches the database fields for a match, and populates the listbox with the matching record information. If there's no match, then it notifes the user. The only thing is, whenever I run through the process, it says that there are no matching records, even though I type the data exactly as it's listed in the DB. Here's my code: Public strSearch As String Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load ' Place cursor in text box txtSearch.Select() End Sub Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click ' Check to make sure the user selected a search criteria If radSearch1.Checked And radSearch2.Checked And radSearch3.Checked And radSearch4.Checked And radSearch5.Checked And radSearch6.Checked = False Then MessageBox.Show("Please select a criteria to search by", "Error") Else ' Clear the contents of the list box, and then find matching records in the database lstSearch.Clear() MyConnection.Open() Dim MyCommand As New OleDbCommand("SELECT * FROM Drawings WHERE '" & strSearch & "' = '" & txtSearch.Text & "'", MyConnection) Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader() ' If there are matching records, add all matches to the listbox If MyReader.Read Then While MyReader.Read lstSearch.Items.Add(MyReader("Drawing #")) MyConnection.Close() MyReader.Close() MyCommand.Dispose() End While ' If no matching records are found, notify the user Else MyConnection.Close() MyReader.Close() MyCommand.Dispose() MessageBox.Show("There are no matching records for the criteria entered", "Error") txtSearch.Select() End If End If End Sub Any ideas on what I'm missing? Any help is appreciated. Thanks in advance, Chris

I use Access right now, but I'm thinking about using SQL in my next program. This is the first program that I've written, so I'm still a little wet behind the ears. I was just wondering what would happen in that case. Thanks for the input.

I know this is most likely a naive question, but is it possible to have multiple users accessing the same DB at the same time? For instance, if an user is updating a record in one table, and a user is updating a record in another table at the same time, will one of them get an error message? My thinking is that the program should be written so that the connection is only opened at the time that data is retrieved or sent to the DB, and then the connection should be closed. But what if by some freak chance two users make a change at the exact same time?

hog, Sorry for the delay in response, but something was messed up with my account. Robby helped me get it straightened out (THANKS ROBBY!!!). Anyways, I finally figured out how to get the total hours with the following addition into my sql statement. It added another column to the datagrid with the correct hours and minutes that the employee worked: strSQL = "SELECT TimeIn.DateIn, Format([TimeIn.StartTime], 'hh:mm') as StartTime, Format([TimeIn.EndTime], 'hh:mm') as EndTime, Format([TimeIn.EndTime]-[TimeIn.StartTime], 'hh:mm') as HoursWorked " strSQL = strSQL & "FROM Employees INNER JOIN TimeIn ON Employees.EmpID = TimeIn.EmpID " strSQL = strSQL & "WHERE (((TimeIn.SSN)=" & "'" & strEmployee & "'" & ") AND ((TimeIn.DateIn) Between #" & strStart & "# And #" & strEnd & "#));" However, this poses another question as to how I can add up all of the hours worked and display the result in a textbox, or add another row to the datagrid with only the total hours. I think I'd rather have it in a textbox, but what would you suggest? Thanks in advance, Chris

Hi all, I've got a datagrid that I'm populating from an Access DB. My DB has a table with time and date entries from employee clock in and out times. The problem that I'm having is when I try to calculate the total hours worked on a given day, it's not working out at all. I have tried everything that I can, including spending hours scouring the different posts to see how to determine the hours worked. The table in the DB is set up as follows: TimeID - AutoNumber - Primary Key EmpID - Number SSN - Text DateIn - Date/Time StartTime - Date/Time EndTime - Date/Time My form has a textbox to type in the employee's SSN and 2 date/time pickers to select the timeframe to view. My code to generate the dataset and populate the datagrid is as follows: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strEmployee As String = txtEmpSSN.Text.Trim.ToString Dim strStart As String = dtStart.Value.ToShortDateString Dim strEnd As String = dtEnd.Value.ToShortDateString Dim strSQL As String Dim strConnection As String Try strSQL = "SELECT TimeIn.SSN, TimeIn.DateIn, Format([TimeIn.StartTime], 'hh:mm') as StartTime, Format([TimeIn.EndTime], 'hh:mm') as EndTime " strSQL = strSQL & "FROM Employees INNER JOIN TimeIn ON Employees.EmpID = TimeIn.EmpID " strSQL = strSQL & "WHERE (((TimeIn.SSN)=" & "'" & strEmployee & "'" & ") AND ((TimeIn.DateIn) Between #" & strStart & "# And #" & strEnd & "#));" Dim strPath As String = Application.StartupPath() strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & "\timeclock.mdb" Dim myCnn As New OleDbConnection(strConnection) Dim myDA As New OleDbDataAdapter(strSQL, myCnn) myCnn.Open() Dim ds As New DataSet myDA.Fill(ds, "TimeIn") dgTimeView.DataSource = ds dgTimeView.DataMember = "TimeIn" myCnn.Close() myCnn.Dispose() myDA.Dispose() ds.Dispose() strSQL = "" Catch ex As Exception Dim strMessage As String strMessage = ex.Message.ToString strMessage = strMessage & " " & ex.ToString Label1.Text = strMessage End Try End Sub Any help at all would be greatly appreciated, as I have been racking my brain trying to figure this out. Thanks in advance, Chris

Hi all, Ok, I'm having trouble figuring out how to create an application wide connection to my database. I tried creating a module, and having it be the first object loaded when the program starts. The problem that I'm having is that I've declared the connection in the module, but when I attempt to use it on a form, it says that the connection hasn't been declared. What can I do? Thanks in advance, Chris