rvermeulen

Travis, I checked the strsql.length and it's only 157 characters so I know it's not being truncated. Any other thoughts ? Rick

Hi folks, I'm trying to update an Access 2000 table with my asp.net application. I've debugged the statement and the whole statement works with my command object, except when I add the last column to update. I get a Syntax error in UPDATE statement. However, if I cut and paste the statement into Access and run it, it updates the table. here is my code if anyone see's something wrong with it. Much appreciated. Thanks ! Rick Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click conn.Open() Dim strSQL As New StringBuilder(275) strSQL.Append("UPDATE SALES set productid = " & ddlbProducts.SelectedItem.Value.ToString & ", Jan = " & txtJanuary.Text.ToString & ", Feb = " & txtFebruary.Text.ToString & ", Mar = " & txtMarch.Text.ToString & ", Apr = " & txtApril.Text.ToString & ", May = " & txtMay.Text.ToString & ", June = " & txtJune.Text.ToString) strSQL.Append(", July = " & txtJuly.Text.ToString & ", Aug = " & txtAugust.Text.ToString & ", Sept = " & txtSeptember.Text.ToString & ", Oct = " & txtOctober.Text.ToString & ", Nov = " & txtNovember.Text.ToString & ", Dec = " & txtDecember.Text.ToString) strSQL.Append(" where ID = " & LabelID.Text.ToString) Dim cmdUpdate As New OleDbCommand(strSQL.ToString, conn) cmdUpdate.ExecuteNonQuery() conn.Close() End Sub It fails when I add the Dec column. If I comment that column out and run it through asp.net, it updates correctly. Any suggestions ?

Here is the connection string I'm attempting to connect with value="Provider = SQLOLEDB;data source=ServerName;Initial Catalog=DatabaseName;User=GenericUser;Pwd=GenericPwd;persist security info=true" Any help is truly appreciated. It continues to attempt to connect with the IWAM_servername account. Rick

Hello, I have an asp.net application that works fine connecting to an access DB that resides in the wwwroot directory of the webserver. I'm attempting to upgrade the database to SQL server 2000. The SQL server resides on the same box as the webserver. The database is in sql authentication mode, and I've created a userid and password for it. However, when the asp.net application tries to connect to it, I get an error stating. Login failed for user IWAM_servername. Invalid attribute for connection string. I'm not sure why it's attempting to connect as the windows user instead of the credentials I'm passing the connection. Does anyone have similiar issues or resolutions ? Thanks ! Rick

I thought this was fairly straight forward, but I'm having difficulty getting my DataAdapter to accept the values of my parameters. I've created a connection and DataAdapter from the design window, and configured my DataAdapter through the QueryBuilder. In the QueryBuilder, I added 4 where criteria, such as "LIKE @city". From my understanding, I've created 4 parameters that I can pass to the SelectCommand to help qualify the records to be loaded into the dataset. I've verified that the Parameter value is what the user has entered from the WebForm, I'm just not sure why the DataAdapter is not populating the parameter value into the SelectCommand. I've appended the % sign to each parameter since each one is using a LIKE statement in the sql statement. Here is my code: If txtGroupNumber.Text.Trim <> "" Then daGroup.SelectCommand.Parameters.Add("@groupnumber", txtGroupNumber.Text.Trim.ToString & "%") Else daGroup.SelectCommand.Parameters.Add("@groupnumber", "%") End If If txtGroupName.Text.Trim <> "" Then daGroup.SelectCommand.Parameters.Add("@groupname", txtGroupName.Text.Trim.ToString & "%") Else daGroup.SelectCommand.Parameters.Add("@groupname", "%") End If If txtAddress.Text.Trim <> "" Then daGroup.SelectCommand.Parameters.Add("@address1", txtAddress.Text.Trim.ToString & "%") Else daGroup.SelectCommand.Parameters.Add("@address1", "%") End If If txtCity.Text.Trim <> "" Then daGroup.SelectCommand.Parameters.Add("@city", txtCity.Text.Trim.ToString & "%") Else daGroup.SelectCommand.Parameters.Add("@city", "%") End If OleDbConn.Open() daGroup.Fill(DsGroupList1, "group") dgGroupList.DataBind() Please someone help me Thanks in advance. Rick

If I don't do a DataGrid.DataBind(), then the paging won't change when you click on each page number to scroll through the list. You need to rebind the grid to get the items to change. I'm wondering if in this event, I need to do some sort of looping and adding selected items to an array or something? Any ideas?

Are you opening the "popup" window through javascript? Post your code to get more directed valuable help

I've added paging to the datagrid using a method I found on the internet. I'm not sure it is the correct way to do this however. Here is the code I use to load the grid and the corresponding code to allow the paging. OleDbConnection.Open() OleDbDataAdapter.Fill(dsSkillSet,"skillset") Session.Add("ds",dsSkillSet) DataGrid.DataBind() Here is the PageIndexChanged event DataGrid.CurrentPageIndex = e.NewPageIndex dsSkillSet = Session("ds") DataGrid.DataBind() The problem I'm having is that if I select records on the first page, and then select the second page, I lose all those records selected on the first page. Should I create another session object to track all the selected items? How do I loop through the items on a specific page, or do I need to loop through the entire grid contents? Is the session object the proper way to work with this type of paging? I'm still new to this, so please feel free to correct my thinking or ideas. thanks in advance. Rick

Hi folks, I'm getting an error when I attempt to edit a record in my datagrid through a dataset. I'm able to load the grid and select the edit link button which brings the record in Edit mode. In my dataset, I have two dataadapters (one with employee information and another with Job descriptions) that I use to display employee information and the employee Job in the grid. The employee info is in the grid, the job description is a label in a template column. So for a particular employee, you see information and then his Job description as opposed to the jobid on the employee table. Once the record goes into Edit mode, the job description label becomes a drop down list with a list of all possible Job descriptions, and the employees current job description selected. When I click the Update link button, I get the following error: Prepared statement '(@JobID int, @JobDescr varchar(50), @Original_JobID int, @Original_' expects parameter @JobDescr, which was not supplied. The source error shows the following line as the error: sqldataadapter1.update(DsJobsEdit1, "employees") Here is my code: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here If Not Page.IsPostBack Then SqlConnection1.Open() SqlDataAdapter1.Fill(DsJobsEdit1, "jobs") SqlDataAdapter2.Fill(DsJobsEdit1, "employees") dgEditGrid.DataBind() End If End Sub Public Function BindJob(ByVal o As Object) As String Dim drv As DataRowView = o Dim dr As dsJobsEdit.EMPLOYEESRow = drv.Row Return dr.JOBSRow.JobDescr End Function Private Sub reBind() SqlDataAdapter1.Fill(DsJobsEdit1, "jobs") SqlDataAdapter2.Fill(DsJobsEdit1, "employees") dgEditGrid.DataBind() End Sub Public Sub EditCommand(ByVal Sender As Object, ByVal e As DataGridCommandEventArgs) dgEditGrid.EditItemIndex = e.Item.ItemIndex reBind() End Sub Public Sub DeleteCommand(ByVal Sender As Object, ByVal e As DataGridCommandEventArgs) dgEditGrid.EditItemIndex = -1 reBind() End Sub Public Sub CancelCommand(ByVal Sender As Object, ByVal e As DataGridCommandEventArgs) dgEditGrid.EditItemIndex = -1 reBind() End Sub Public Sub UpdateCommand(ByVal Sender As Object, ByVal e As DataGridCommandEventArgs) reBind() Dim dr As dsJobsEdit.EMPLOYEESRow = DsJobsEdit1.EMPLOYEES.Item(e.Item.DataSetIndex) Dim ddljobs As DropDownList ddljobs = e.Item.FindControl("ddlbJobs") dr.JobID = ddljobs.SelectedItem.Value SqlDataAdapter1.Update(DsJobsEdit1, "employees") dgEditGrid.EditItemIndex = -1 reBind() End Sub Public Sub ItemDataBound(ByVal Sender As Object, ByVal e As DataGridItemEventArgs) Dim lit As ListItemType = e.Item.ItemType If lit = ListItemType.EditItem Then Dim ddlbjobs As DropDownList ddlbjobs = e.Item.FindControl("ddlbjobs") Dim drv As DataRowView = e.Item.DataItem If Not drv Is Nothing Then ddlbjobs.Items.FindByValue(drv("jobid")).Selected = True End If End If End Sub Can anyone please help. Thanks in advance. Rick [edit]Please use tags [/ vb][/edit][/color]

I have a datagrid that is bound to a resultset. I have a checkbox and the column returned from the resultset in the grid. The problem I'm having is this. The result set is returning 50 records, which causes the grid to build beyond the page and you have to scroll the page down to see the entire grid. What I would like to do is something like the dataList allows you to do. You can set the RepeatColumn attribute and the RepeatDirection to allow the list to build horizontally and look better. Is there a way to accomplish this with the dataGrid ? Or is the solution putting a scroll bar on the datagrid and set it's height and width and use the scroll bar within the grid to scroll. Does anyone have any insight? Thanks in advance! Rick

Tim, Thanks for your help. That was the ticket. As I mentioned, I have a dataset bound to the data grid. I've included a Button column that has the Edit, Update, and Cancel linked buttons on the grid. What do I have to do to be able to edit the record. When I click the Edit link button, nothing is happening. I'm sorry if this is a stupid question, but I'm missing the idea here. From what I read, I have to program the Grid_Edit event. But what am I looking to do? What I would like to do is open the same form that they insert new users to open with the information for the specific row they have clicked the Edit link button. I have the ID column in the grid hidden, and I'd like to pass that ID to the response.redirect in the Grid_Edit event. Can I do that? Thanks again for your help. Rick

I am getting an error when I attempt to insert a record. Here is my code 'insert sql = "insert into EMPLOYEES values (@UserID,'" + _ txtFirstName.Text + "','" + txtLastName.Text + "','" + _ txtAddress.Text + "','" + txtCity.Text + "','" + txtState.Text + "','" _ + txtZipCode.Text + "',#" + txtHireDate.Text + "#,'" + txtTitle.Text + "')" Dim func As New CommonFunctions() Dim cmdIns = New OleDb.OleDbCommand(sql, conn) Dim parm1 = New OleDb.OleDbParameter("@UserID", OleDbType.Integer) parm1.value = func.getNextID("EMPLOYEES") cmdIns.parameters.add(parm1) conn.Open() cmdIns.executenonquery() conn.Close() cmdIns = Nothing It is failing on the line attempting to do the ExecuteNonQuery(). I noticed that the OleDbCommand object did not recognize the ExecuteNonQuery method when I typed it. I'm assuming that might have something to do with the problem. I am a newbie at this and have a question about how I'm going about doing this. I have created a dataset that is bound to a datagrid. The grid populates fine. I understand, that I should be able to update/delete those records. However, if I want to insert a new record into the datagrid, is this the correct way to do it ? I'm going to a new page, allowing the user to enter the information into a form, and then I have the above code triggering. Am I doing this correctly? Does anyone have any thoughts? I appreciate any advice Rick

I am also getting the same error when I attempt to insert a record. Here is my code 'insert sql = "insert into EMPLOYEES values (@UserID,'" + txtFirstName.Text + "','" + txtLastName.Text + "','" + txtAddress.Text + "','" + txtCity.Text + "','" + txtState.Text + "','" + txtZipCode.Text + "',#" + txtHireDate.Text + "#,'" + txtTitle.Text + "')" Dim func As New CommonFunctions() Dim cmdIns = New OleDb.OleDbCommand(sql, conn) Dim parm1 = New OleDb.OleDbParameter("@UserID", OleDbType.Integer) parm1.value = func.getNextID("EMPLOYEES") cmdIns.parameters.add(parm1) conn.Open() cmdIns.executenonquery() conn.Close() cmdIns = Nothing It is failing on the line attempting to do the ExecuteNonQuery(). I noticed that the OleDbCommand object did not recognize the ExecuteNonQuery method when I typed it. I'm assuming that might have something to do with the problem. Does anyone have any thoughts? Thanks in advance. Rick

I've created a popup control using the sample given. I'm able to open the popup window with the javascript and I'm able to set the Literal string once the Calendar is selected a day. I've programmed the SelectionChanged to do this. However, once that event is triggered and the javascript created for the literal string, the calendar popup does not close and the parent form is not being populated. Here is my parent form call: <td><input id="button1" type="button" value="calendar" onclick="javascript:calendar_window=window.open('test.aspx?formname=form1&textname=txt1','calendar_window','width=260,height=220');calendar_window.focus()"> And this is the event I have programmed in the Popup Calendar: Private Sub Calendar1_SelectionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged Dim strjscript As String = "<script language=""javascript"">" strjscript = strjscript & "window.opener." & HttpContext.Current.Request.QueryString("formname") & ".elements[" & HttpContext.Current.Request.QueryString("textname") & "].value = '" & Calendar1.SelectedDate & "';window.close();" strjscript = strjscript & "</script>" & ">" Literal1.Text = strjscript End Sub Am I doing something wrong that I don't see ?

Robby, Thanks so much for all your help. Turns out that it chokes on the column Position. I switched it to Title instead and now I can select the individual columns I want and it works fine. I guess Position must be a keywork in Access? Now that I have my data bound to the grid, how can I set the column widths so that the data doesn't wrap, etc. Do you have to do that programmatically, or can that be done at design time? Thanks again. Rick

Robbie, I'm still getting the same error. However, check this out. I took one of your suggestions from a different forum and tried to drag the connection to the webform. The connection gets established and the test succeeds. However, when I go through the OleDbDataAdapter wizard, it fails to generate the SELECT statement when it uses the column names in the select statement. If I make the select statement, Select * from EMPLOYEES, it creates successfully. However, it won't stay like this, it keeps going back to the specific column names. Do you know why it wants to do that? Rick

I'm trying to connect to an EMPLOYEES table in an XP Access DB. I was able to earlier connect to a different table and return data. However, when I try to create an OleDBDataAdapter and fill the Dataset to be used to bind to a datagrid, I get an: Unspecified Error: E_FAIL(0x8000405) exception. I'm not sure what I'm doing wrong, seems pretty straightforward. Here is the web.config file where I set my connection string: <appSettings> <add key="ConnectionString" value="Provider = Microsoft.jet.oledb.4.0;data source=c:\InetPub\wwwroot\ASPSample\ASPSample.mdb" /> Here is my code I'm running in the Page_Load: conn = New OleDb.OleDbConnection(ConfigurationSettings.AppSettings("ConnectionString") Dim SQL as string = "select UserID, FirstName, LastName, Address, City, State, ZipCode, HireDate, Position from EMPLOYEES" Dim empDA as new OleDB.OleDbDataAdapter(SQL, conn) Dim ds as new DataSet() empDA.Fill (ds, "employees") EmployeeGrid.DataSource = ds.Tables("employees").DefaultView EmployeeGrid.DataBind() The code fails on the Fill statement. Can anyone please help me. I'm having trouble figuring this out. Thanks in advance. Rick